SqlCommandBuilder

Setting up the SQL Connection

  1. Create a new SqlConnection object and provide it with a connection string. Substitute your own parameters within the brackets.

    i.e: Dim connection = New SqlClient.SqlConnection("Server=[Ip Address];Database=[Database Name];User Id=[DB Username];Password=[DB Password];")

  2. Next call the open function for your connection.

    i.e: connection.Open()

  3. 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: Dim sqlCommand = New SqlClient.SqlCommand()
          sqlCommand.Connection = con

  2. Next set the command text for the add, and the parameters. Substitute your own parameters within the brackets.

    i.e: sqlCommand.CommandText = "INSERT INTO [Table Name] ([Column Name]) values (@[Column Name])"
          sqlCommand.Parameters.Add("@[Column Name]", [Column Type]).Value = "Value"

  3. Execute the command. If you would like, you can capture the number of rows deleted.

    i.e: sqlCommand.ExecuteNonQuery()

Reading from the Database

  1. Begin by creating a SqlCommand object and setting the connection to the one you made above.

    i.e: Dim sqlCommand = New SqlClient.SqlCommand()
          sqlCommand.Connection = con

  2. Next set the command text for the read. Substitute your own parameters within the brackets.

    i.e: sqlCommand.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: Dim sdr As SqlClient.SqlDataReader = sqlCommand.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.WriteLine(sdr.Item("[Column Name]"))
          End While

  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: Dim sqlCommand = New SqlClient.SqlCommand()
          sqlCommand.Connection = con

  2. Next set the command text for the delete. Substitute your own parameters within the brackets.

    i.e: sqlCommand.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: Dim rowsAffected As Integer = sqlCommand.ExecuteNonQuery

Setting up a SqlCommandBuilder for the Database

  1. Begin by creating a SqlDataAdapter object, a DataTable object and a SqlCommandBuilder Object

    i.e: Dim da As SqlClient.SqlDataAdapter
          Dim dtOne As DataTable = New DataTable()
          Dim scb As SqlClient.SqlCommandBuilder

  2. Next, create a SqlCommand object and set the connection to the one you made above.

    i.e: Dim sqlCommand = New SqlClient.SqlCommand()
          sqlCommand.Connection = con

  3. Set the command text as if you are doing a read. Substitute your own parameters within the brackets.

    i.e: sqlCommand.CommandText = "SELECT * FROM FirstTable"

  4. Set the SqlDataAdapter to a new SqlDataAdapter object and pass in your SqlCommand.

    i.e: da = New SqlClient.SqlDataAdapter(sqlCommand)

  5. Next, set the SqlCommandBuilder to a new SqlCommandBuilder object and pass in the SqlDataAdapter.

    i.e: scb = New SqlClient.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)