The overloaded constructor for the DataAdapter allows four different ways to create the data adapter, of which two are most commonly used. The following example creates a DataAdapter specifying the SELECT statement and connection string in the constructor.
String connString = "Data Source=(local);Integrated security=SSPI;" + "Initial Catalog=Northwind;"; String selectSql = "SELECT * FROM Orders"; SqlDataAdapter da = new SqlDataAdapter(selectSql, connString);
While this approach is common, it is awkward when using parameterized queries or stored procedures. The following example creates a DataAdapter specifying a Command object for the SelectCommand property of the DataAdapter in the constructor:
// create the Connection String connString = "Data Source = (local);Integrated security = SSPI;" + "Initial Catalog = Northwind;"; SqlConnection conn = new SqlConnection(connString); // create a Command object based on a stored procedure String selectSql = "MyStoredProcedure"; SqlCommand selectCmd = new SqlCommand(selectSql, conn); selectCmd.CommandType = CommandType.StoredProcedure; SqlDataAdapter da = new SqlDataAdapter(selectCmd);
It should be noted that there is no best way to create a DataAdapter, and it makes no real difference how it is created.