ADO.net

Why do we need a dataprovider?

If there was no dataprovider at all, you would have to write your own native code to connect your asp.net application to a SQL Server. (Which would be equivalent to writing your own data provider). But there are better options out there. You could instead use a dataprovider.

What is a dataprovider?

A dataprovider is a piece of software that gives you a standard way of connecting to different datasources and retrieving data from them. 

 Consider this you come up with a new database system of your own. If you want asp.net developers to be able to easily switch from Microsoft SQL Server or Oracle to your DBMS, what you would do is, you would provide an interface that conforms to oledb standards. This would be your own oledb provider. Lets call it the DilbertProvider. So now all the asp.net application developer would have to do is, to replace the "providerName" in his connection string from System.Data.SqlClient to DilbertProvider. Except for the fact that there will be some change in the class names (for instance SqlConnection might be replaced by DilbertConnection etc) most of the data access code would remain the same.

The list below shows the chronological order in which some of the currently used dataproviders came into the market

ODBC-1992

OLEDB-1998

ADO.NET -2003

OLEDB is meant as a replacement for ODBC. But ADO.net on the other hand is NOT a replacement for OLEDB.  

.

ADO.net dataproviders
ADO.net is the part of .net framework that is used for accessing data. Everything in the System.Data namespace is a part of ADO.net. When your asp.net application needs to read data from a SQL database, it uses the code in the ADO.net part of the .net framework. Fig 1. below shows you a high level overview of ADO.net data providers.

Fig 1.ADO.net dataproviders

 Most of the commonly used datasources can be accessed using the the dataproviders that come with .net framework. However if you ever need to access a less commonly used datasource (like a db2 datasource), you would need to install some custom providers.


Basics of accessing data using ADO.net
When writing code to access data using ADO.net these the three things you first need to know
Connection: Connection tells the CLR the connections string and the kind of datasource you are accessing.
For eg

 System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();

conn.ConnectionString = "Data Source=servername;Initial Catalog=databasename;Integrated Security=False; User Id=myuserName;Password=myPassword;Connect Timeout=600";

Once you define the connection as shown above, the CLR knows it needs to connect to a SQL database. It also knows you want to use the dataprovider for SQL Server to access this database.


Command: The command tells the CLR what SQL statement to execute against the database
For eg

System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("Select * from tableUsers", conn);

This tell the CLR to select everything in the table named tableUsers. It also tell CLR to use the connection defined by the variable conn.


Datareader: The datareader helps you go row by row through the results. Consider the example below

System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();

while(reader.Read())

{

}

The while loop above goes row by row through the result set. When there are no more rows to be read, the loop exits. It is worth mentioning here that the results are NOT fetched row by row from the database. Instead the entire result set from the database is fetched into the network buffer in one shot. Then the reader reads row by row from this network buffer.

ADO.NET data access examples
Don’t forget to qualify the namespace System.Data.SqlClient on the top of the page by typing

using System.Data.SqlClient;

In the examples below, conn represents a  SqlConnection.

(

SqlConnection conn = new SqlConnection();

conn.ConnectionString = ConfigurationManager.ConnectionStrings["myConnStringName"].ConnectionString;

)

Purpose	Code
Execute a SQL command that does not return data	SqlCommand cmd = new SqlCommand("Delete from tblUsers where fName=@FirstName", conn); cmd.Parameters.AddWithValue("@FirstName", "George"); using (conn)  {     conn.Open();     cmd.ExecuteNonQuery(); }
Execute SQL stored procedure that does not return data	SqlCommand cmd = new SqlCommand("usp_DeleteUser", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@FirstName", "George"); using (conn) {     conn.Open();     cmd.ExecuteNonQuery(); }
Execute SQL stored procedure to get an integer return value	int result; SqlCommand cmd = new SqlCommand("usp_DeleteUser", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@FirstName", "George"); cmd.Parameters.Add("@myReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue; using (conn) {     conn.Open();     cmd.ExecuteNonQuery();     result=(int)cmd.Parameters["@myReturnValue"].Value; } Note: To use this,the stored procedure would have the keyword Return
Execute a stored procedure to return a single value	string result; SqlCommand cmd = new SqlCommand("usp_GetName", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@id", 1); using (conn) {     conn.Open();     result= cmd.ExecuteScalar().ToString(); } Note: To use this the stored procedure should accept the parameter as output parameter eg @id int out
Execute a stored procedure to get multiple return values	SqlCommand cmd = new SqlCommand("usp_userDetails", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@id", SqlDbType.Int).Value = 1; cmd.Parameters.Add("@name", SqlDbType.VarChar,50).Direction = ParameterDirection.Output; cmd.Parameters.Add("@age", SqlDbType.Int).Direction = ParameterDirection.Output; using (conn) { conn.Open(); cmd.ExecuteNonQuery(); userName = (string)cmd.Parameters["@name"].Value; age = (int)cmd.Parameters["@age"].Value; } NOTE: If you are using a datareader, you need to close the datareader before you can access the output variables.
Execute stored procedure to return a resultset with multiple rows of data	System.Text.StringBuilder s = new System.Text.StringBuilder(); SqlCommand cmd = new SqlCommand("usp_GetUserNames", conn); cmd.CommandType = CommandType.StoredProcedure; using (conn) {     conn.Open();     SqlDataReader reader = cmd.ExecuteReader();     while(reader.Read())     {         s.Append(reader["name"].ToString());     }     reader.Close(); } Note: The Read() method does two things            It checks if there is a next row of data            If it exists it proceeds to the next row
Fill the return table from a stored procedure into a datatable	SqlCommand cmd = new SqlCommand("usp_GetUserNames", conn); cmd.CommandType = CommandType.StoredProcedure; DataTable dtUsers = new DataTable(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dtUsers);

Further reading:
How to write stored procs with output parameter