14.1 Creating DataAdapter Object

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.