Basic Database Operations in ADO.NET
This Article will show the basic database transactions select, insert update and delete.
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:
You can follow the same pattern for delete, insert and add transactions.
- Establish a connection to the SQL server
string strConnection="user id=sa;password=mozart;";
SqlConnection objSqlConnection = new SqlConnection(strConnection);
- Create a Dataset.( A DataSet is a snapshot cache from the database.)
DataSet objDataSet = new DataSet("OrderAdmin");
- 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);
- 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;
objOrdAdapter.UpdateCommand = new SqlCommand (strUpDate,objSqlConnection);
- 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.
- 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.