We have all fired SQL operations by specifying the SQL statement and using the command object to do the needful. Here we see how we can do the same using the Data Adapter object and the Command builder object to achieve the same. There is no need to specify the SQL statement. We can do Inserts, Updates and Deletes with the two classes specified above.
Given below are the code samples for Insert, Update and Delete operation. The code is self-explanatory.
Insert a new Row
|
// the Database string
string sDBstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=xxx";
//the sql statement
string sSQL = "SELECT * from Name where ID=1";
//the connection object
OleDbConnection oCn = new OleDbConnection(sDBstr);
//create the data adapter and execute the query
OleDbDataAdapter oDA = new OleDbDataAdapter(sSQL,oCn);
//create the DataSet
DataSet oDs = new DataSet();
try
{
//open the connection
oCn.Open();
//Fill the dataset with the data adapter
oDA.Fill(oDs,"Name");
//create the Data Row
DataRow oDR = oDs.Tables["Name"].NewRow();
//Populate the datarow with values
oDR["Name"] = "Yateen"
oDR["Id"] = "10";
//Add the datarow to the dataset
oDs.Tables["Name"].Rows.Add(oDR);
//Use the Command Bulder object to generate Insert Command dynamically
OleDbCommandBuilder oCB = new OleDbCommandBuilder(oDA);
//Update the DB with values from Dataset with the Data Adapter
oDA.Update(oDs,"Name");
}
catch(Exception ex)
{
// catch the error message and put it in the string "msg"
string msg = ex.Message ;
//show the error message on the screen
Console.WriteLine(msg) ;
}
finally
{
//Clean up
oCn. Close();
oCn = null ;
oDA = null;
oCB = null;
}
|
For the Update and the Delete operations to work, the database table you are working with requires a Primary Key to be present.
Update a Row
|
// the Database string
string sDBstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=xxx";
//the sql statement
string sSQL = "SELECT * from Name where ID=1";
//the connection object
OleDbConnection oCn = new OleDbConnection(sDBstr);
//create the data adapter and execute the query
OleDbDataAdapter oDA = new OleDbDataAdapter(sSQL,oCn);
//create the DataSet
DataSet oDs = new DataSet();
try
{
//open the connection
oCn.Open();
//Fill the dataset with the data adapter
oDA.Fill(oDs,"Name");
//create the Data Row assigning it to the row u want to update
DataRow oDR = oDs.Tables["Name"] .Rows[3];
//Populate the datarow with values
oDR["Name"] = "Yateen"
//Use the Command Bulder object to generate Update Command dynamically
OleDbCommandBuilder oCB = new OleDbCommandBuilder(oDA);
//Update the DB with values from Dataset with the Data Adapter
oDA.Update(oDs,"Name");
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
//Clean Up
oCn.Close();
oCn = null ;
oDA = null;
oDs = null;
oCB= null
}
|
Delete a Row
|
// the Database string
string sDBstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\test.mdb";
//the sql statement
string sSQL = "SELECT * from Name";
//the connection object
OleDbConnection oCn = new OleDbConnection(sDBstr);
//create the data adapter and execute the query
OleDbDataAdapter oDA = new OleDbDataAdapter(sSQL,oCn);
//create the DataSet
DataSet oDs = new DataSet();
try
{
//open the connection
oCn.Open();
//Fill the dataset with the data adapter
oDA.Fill(oDs,"Name");
//Delete the row u want to delete
oDs.Tables["Name"].Rows[3].Delete();
//Use the Command Bulder object to generate Delete Command dynamically
OleDbCommandBuilder oCB = new OleDbCommandBuilder(oDA);
//Update the DB with values from Dataset with the Data Adapter
oDA.Update(oDs,"Name");
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
//Clean Up
oDA=null;
oDs=null;
oCB=null;
oCn.Close();
oCn=null;
}
|
Happy Coding !!!!
