What is data provider?
Data Providers is a software library in .NET consisting of classes that let you connect to a data source, execute commands to access and fetch data from a data source with support to execute commands within transactions. Example of a data provider in .NET Framework is ADO.NET Data Provider.
Components of ADO.NET data providers
Below are the main objects of data providers in ADO.NET
The Connection Object
It represents the connection to data source. The object has a connection string property that contains all the information need to establish a connection.
The Command Object
It is used to execute stored procedures and commands on the database.
The DataReader Object
It provides a record-only recordset from a database. The command ExecuteReader method creates and returns a DataReader object. It connects to the database throughout its life span, which requires exclusive use of connection object.
The DataAdapter Object
It is used to communicate between the database and a dataset. It fills the dataset with data from the data source. The dataset stores the data in the memory. The DataAdapter’s update method transfer changes to the database.
In this article, I am going to explain DataAdapter – an integral part of ADO.NET Data Provider.
DataAdapter is like a bridge between the data source and the data set. It allows performing to perform the four SQL operations: select, insert, update and delete. A Data Adapter contains a connection object and a command object. It opens and closes the connection automatically while reading or writing to a database. It also manages data in a disconnected mode.
DataAdapter Properties
It has four properties.
- Delete Command – It is used to delete records from the data source.
da.DeleteCommand.CommandString =”Delete from TABLE_NAME where Condition”; - Insert Command – it is used to insert records from the data source
da.InsertCommand.CommandString =”Insert into TABLE_NAME values (values1,Values2,….. , ValueN); - Select Command – it is used to select records from the data source
da.SelectCommand.CommandString =”select * from TABLE_NAME”; - Update Command – it is used to update records from the data source
da.UpdateCommand.CommandString =”update TABLE set column_name= value where Condition”;
DataAdapter Methods
Below are the commonly used method in DataAdapters:
Fill Method – It used to fill data into a DataSet or DataTable using a DataAdapter. Let’s say we have this table “stock”. Use the fill method to fetch the records from the data source. At the user interface a button will be used to fetch the data from a database. Example:
string sql = "select * from stock";
DataSet ds = new DataSet("products");
SqlConnection conn = new SqlConnection("String connection goes here");
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds, "products");
Update Method – This method can be used to update any value of any record set. Example:
string sql = "select * from stock";
DataSet ds = new DataSet("products");
SqlConnection conn = new SqlConnection("String connection goes here");
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds, "products");
if (ds.Tables["product"].Rows.Count > 0)
{
ds.Tables["Product"].Rows[0]["unit"] = "Pound";
da.Update(ds, "Product");
}
Now if you want to insert a new record into the database using the Update() method, use the .NewRow() method to do so. Example:
string sql = "select * from stock";
DataSet ds = new DataSet("products");
SqlConnection conn = new SqlConnection("String connection goes here");
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds, "products");
DataRow row = ds.Tables["product"].NewRow();
row["product_id"] = 1;
row["unit"] = "KG";
ds.Tables["product"].Rows.Add(row);
Related Articles
Function Returns DataTable Using C#
Insert Update Delete example in C# and VB.NET Using ExecuteNonQuery method
Comments