What is Ado.net?
Ado.net is the code in the System.Data.dll. It provides an API for your code to access data from different datasources such as SQL server, Excel and so on. This is how Sytem.Data.dll looks like when opened using JetBrains dotpeek decompiler.
Given below is some sample code that uses the ado.net classes to access data.
SQL:
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand("Select * from users", conn);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds, "Users");
Excel:
OleDbConnection conn = new OleDbConnection(connString);
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", conn);
DataSet ds = new DataSet();
OleDbDataAdapter da= new OleDbDataAdapter(cmd);
da.Fill(ds, "Users");
As you can see the code to access data from SQL server and Excel is very similar. What differs is the syntax of the connection string.
Ado.net is composed of dataproviders and dataset.
What is a dataprovider?
A dataprovider is a piece of software that gives you a way of connecting to different datasources and retrieving data from them.
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.
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.
What is a dataset?
Dataset is the application in memory representation of data. This data is stored in the RAM of your the machine that hosts your web application. This data is disconnected from the Database Server.
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);
|
