Inserting Data from ADO.NET

Whatever method you use in T-SQL to insert information into your tables, you need similar code in your application to execute the insertion. In an insert operation, you need two ADO.NET objects to accomplish the operation:

• ADO.NET Connection object

• ADO.NET Command object

The Connection object establishing the connection to the database defines the context in which to execute the insert such as which user , which access rights, and occasionally which transaction. The Command object contains your T-SQL sentence or the stored procedures name to be executed.

Note 

The following examples use a new AdventureWorks table named TestTable. The script to create the new table is included in the sample files in the SQL Server Management Studio Solution in the \Ch10\Sample Codes\Chapter 10 Scripts folder.

Using ADO.NET Objects

1. Open Visual Studio 2005 and create a new Windows Application Project.

2. You need a Command object:

    Dim myCommand As New SqlClient.SqlCommand() 

   If you want to use a T-SQL sentence, you must assign the sentence to the CommandText property and assign CommandType.Text to the CommandType property. Since this is the default configuration, you may skip setting the CommandType. In the following example, the T-SQL sentence is inside the txtTSQL textbox.

    With myCommand     .CommandText = txtTSQL.Text     .CommandType = CommandType.Text 

3. You must then assign the connection and call the ExecuteNonQuery method. Because you are not retrieving information in this chapter, this method uses fewer resources during execution.

      .Connection = myConnection     Try         myConnection.Open()         .ExecuteNonQuery()         myConnection.Close()     Catch ex As Exception         MsgBox(ex.Message)     End Try End With   

4. When you want to use a stored procedure, you must assign the stored procedures name to the CommandText property and change the CommandType property to StoredProcedure.

   However, you must add additional steps. If you intend to use a stored procedure, you must define its parameters and add them one by one with at least their names and datatypes.

5. You then assign the values to the parameters and finally execute the command with the ExecuteNonQuery method.

    With myCommand     .CommandText = "TestTable_Insert"     .CommandType = CommandType.StoredProcedure     .Connection = myConnection     .Parameters.Add("@Name", SqlDbType.NVarChar)     .Parameters.Add("@Description", SqlDbType.NVarChar)     .Parameters("@Name").Value = txtName.Text     .Parameters("@Description").Value = txtDescription.Text     Try         myConnection.Open()         .ExecuteNonQuery()         myConnection.Close()     Catch ex As Exception         MsgBox(ex.Message)     End Try End With