Basic Database Operations in ADO.NET

Description 

 

This article will show the basic database transactions select, insert update, and delete. I am using the 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 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 to generate SqlCommands. So you have to supply an appropriate SQL statement to the SqlDataAdapter. 
    
   a) Update the DataRow
   b) Create a 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 to 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 a relationship within a DataSet. 
    
2. You must give an appropriate SqlCommand to the SqlDataAdapter, otherwise, you will damage the table. For 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.