SqlCommandBuilder

Setting up the SQL Connection

1. Start by adding "System.Data" to your references. After that add "System.Data.SqlClient" to your using statements.
   
   
2. Create a new SqlConnection object and provide it with a connection string. Substitute your own parameters within the brackets.
   
   i.e: SqlConnection newCon = new SqlConnection("Server=[Ip Address];Database=[Database Name];User Id=[DB Username];Password=[DB Password];");
   
   
3. Next call the open function for your connection.
   
   i.e: newCon.Open();
   
   

4. You should now have a connection to your database.

Adding to the Database

1. Begin by creating a SqlCommand object and setting the connection to the one you made above.
   
   i.e: SqlCommand myCommand = new SqlCommand();
         myCommand.connection = newCon;
   
   
2. Next set the command text for the add, and the parameters. Substitute your own parameters within the brackets.
   
   i.e: myCommand.CommandText = "INSERT INTO [Table Name] ([Column Name]) values (@[Column Name])";
         myCommand.Parameters.Add("@[Column Name]", [Column Type]).Value = "Value";
   
   
3. Execute the command.
   
   i.e: myCommand.ExecuteNonQuery();
   
   

Reading from the Database

1. Begin by creating a SqlCommand object and setting the connection to the one you made above.
   
   i.e: SqlCommand myCommand = new SqlCommand();
         myCommand.connection = newCon;
   
   
2. Next set the command text for the read. Substitute your own parameters within the brackets.
   
   i.e: myCommand.CommandText = "SELECT * FROM [Table Name]";
   
   
3. For this example we will be using a DataReader to read the lines from the table.
   Create a SqlDataReader object and set it equal to the execution statement of your command.
   
   i.e: SqlDataReader sdr = myCommand.ExecuteReader();
   
   
4. Set up a while loop to run while the SqlDataReader reads from the table, and fetch the values from specified columns. Substitute your own parameters within the brackets.
   
   i.e: while (sdr.Read())
         {
               Console.Write(sdr["[Column Name]"].ToString().Trim());
         }
   
   
5. Close the SqlDataRader.
   
   i.e: sdr.Close();
   
   

Deleting from the Database

1. Begin by creating a SqlCommand object and setting the connection to the one you made above.
   
   i.e: SqlCommand myCommand = new SqlCommand();
         myCommand.connection = newCon;
   
   
2. Next set the command text for the delete. Substitute your own parameters within the brackets.
   
   i.e: myCommand.CommandText = "DELETE FROM [Table Name] WHERE [Column Name]='Value'";
   
   
3. Execute the command. If you would like, you can capture the number of rows deleted.
   
   i.e: int rowsAffected = myCommand.ExecuteNonQuery();
   
   

Setting up a SqlCommandBuilder for the Database

1. Begin by creating a SqlDataAdapter object, a DataTable object and a SqlCommandBuilder Object
   
   i.e: SqlDataAdapter da = null;
         DataTable dtOne = new DataTable();
         SqlCommandBuilder scb = null;
   
   
2. Next, create a SqlCommand object and set the connection to the one you made above.
   
   i.e: SqlCommand myCommand = new SqlCommand();
         myCommand.connection = newCon;
   
   
3. Set the command text as if you are doing a read. Substitute your own parameters within the brackets.
   
   i.e: myCommand.CommandText = "SELECT * FROM [Table Name]";
   
   
4. Set the SqlDataAdapter to a new SqlDataAdapter object and pass in your SqlCommand.
   
   i.e: da = new SqlDataAdapter(sc);
   
   
5. Next, set the SqlCommandBuilder to a new SqlCommandBuilder object and pass in the SqlDataAdapter.
   
   i.e: scb = new SqlCommandBuilder(da);
   
   This will automatically set up the rest of the commands for working the the table.
   
   
6. Use the SqlDataAdapter to fill your DataTable.
   
   i.e: da.Fill(dtOne);
   
   
7. You may now edit the data within your DataTable. Adding, Modifying and Deleting will all work.
   When you wish to submit your changes simply call the SqlDataAdapters update command and pass in the DataTable.
   
   i.e: da.Update(dtOne);