Basic Database Operations in ADO.NET

Description 

This Article will show the basic database transactions  select, insert update  and delete. I am using MS-Sql database northwind  for  the demonstration. I  am not going to use the class SqlCommandBuilder and I will tell you the reason later.

Here are the steps:

1. Establish a connection to the SQL server
   
   

   string strConnection="user id=sa;password=mozart;";
   strConnection+="database=northwind;server=frankfurt";
   SqlConnection objSqlConnection = new SqlConnection(strConnection);
   objSqlConnection.Open();

2. Create a Dataset.( A DataSet  is  a snapshot  cache from the database.)
   
   

   DataSet objDataSet = new DataSet("OrderAdmin");

3. Fill the table, which you want to work with it.
   
   a) Create a  SELECT   SqlCommand  and a SqlDataAdapter 
   b) Map the table to SqlDataAdapter
   c) connect the SqlCommand and the SqlDataAdapter 
   d) Fill the DataSet
   
   // Retrive data from the table Order Detail
   string strOrderDetails = "SELECT * FROM [Order Details]";
   SqlCommand objOrdDetailCommand = new SqlCommand(strOrderDetails,objSqlConnection);
   SqlDataAdapter objODAdapter = new SqlDataAdapter();
   objODAdapter.TableMappings.Add("Table","Order Details"); objODAdapter.SelectCommand = objOrdDetailCommand;
   /*SqlCommandBuilder objODCB = new SqlCommandBuilder(objODAdapter);*/ objODAdapter.Fill(objDataSet);

4. Change the DataSet  and Update with the database
   
   Now for an example, you want  update a data row in the table "[Order Details]" and you make a change in the Dataset and call the method Update method.
   
   This action will cause an error, because DataSet is not able generate SqlCommands. So you have to supply  an appropriate sql statement to the SqlDataAdapter. 
   
   a) Update the DataRow
   b) Create an suitable SQLCommand  
   c) Connect the SQLCommand and the SqlDataAdapter
   d) Update the DataSet 
   
   

   // Update the record of the table Orders, which has OrderID= 10248
   foreach(DataRow objRow in objOrdTable.Rows)
   {
   string strID = objRow["OrderID"].ToString();
   string strShip = "HMS Invisible";
   if(strID == "10248" )
   {
   string strUpDate = "UPDATE Orders SET ShipName= "+"'"+strShip+"'"; strUpDate+=" WHERE OrderID = " + strID;
   objRow["ShipName"]=strShip ;
   objOrdAdapter.UpdateCommand = new SqlCommand (strUpDate,objSqlConnection);
   objOrdAdapter.Update(objDataSet,"Orders");
   break;
   }
   }

You can follow the same pattern for delete, insert and add transactions. 

Note:

1. You dont need to supply a suitable SqlCommand to the SqlDataAdapter, if  you use SqlCommandBuilder.  But the use of the class SqlCommandBuilder is restricted. It is not able to  supply SqlCommands, if the tables have relationship within  a DataSet. 
   
2. You must give an appropriate SqlCommand to the SqlDataAdapter, other wise you will damage the table. For  an example you have deleted only one data row in the DataSet and you supply
   
   a SqlCommand like this
   
   

   objODAdapter.DeleteCommand = new SqlCommand("DELETE FROM [Order Details]")   

The SqlAdapter will then delete all the records in the table. In other words Dataset is not able to check  the SqlCommand.