Database Operations with Data Adapter and Command Builder

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 !!!!

 

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish