7.7. The Data Provider

All interactions with a database are carried out through a data provider. In Section 5.10 we walked through the OLE DB data provider. This section steps through the analogous use of the SQL Server data provider defined within the System.Data.SqlClient namespace. It can be used with Microsoft SQL Server 7.0 or later versions.

Here are the steps necessary, using the SQL Server data provider, first to connect to and select data from a database, and then to fill a DataSet object with the retrieved data:

1.
Create a connection string. The string must identify the server, user ID, password, and database—for example,

string cs = "server=localhost;uid=sa;pwd=;database=northwind";

2.
Create a selection command string. This minimally consists of a SELECT part and a FROM part. The SELECT part determines which columns are read from each row. The FROM part identifies the table from which to read—for example,

"SELECT LastName,FirstName,Title,HomePhone FROM Employees";

3.
Create a SqlConnection object initialized with the connection string. We must be careful to explicitly close the connection when we are finished with it through either Close() or Dispose():

using System.Data.SqlClient;
SqlConnection db_conn = new SqlConnection( cs );
// OK: access the data source ...
db_conn.Close();

4.
Create a SqlDataAdapter data adapter object. Next create a SqlCommand object initialized with the command string and the connection object. SqlCommand retrieves the data. Finally, assign the command object to the data adapter's SelectCommand property:

SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand  = new SqlCommand( cmd, conn );

5.
Create an instance of a DataSet object and pass it to the data adapter's Fill() method, together with a name for the table within which to place the data. This executes the select command and places the data retrieved into the named DataTable. If the DataTable does not exist, it is created:

DataSet ds = new DataSet();
adapter.Fill( ds, "Employees" );

Once the data is within the DataSet object, we manipulate all of the data through that object. In our case we choose to display the data within a Web Forms DataGrid control.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.191.237.79