Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
' This test app needs a sql database on the localhost named 'Test' and a table named 'Test' created with the following script:
'CREATE TABLE [dbo].[Test]
' (
' [RowID] [int] IDENTITY(1, 1)
' NOT NULL,
' [SKU] [char](10) NOT NULL,
' [FieldName] [varchar](20) NOT NULL,
' [FieldValue] [varchar](200) NOT NULL,
' CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ( [RowID] ASC )
' )
Dim intRowCount As Integer
Dim dt As New DataTable("Test")
dt.Columns.Add("RowID", GetType(Integer))
dt.Columns.Add("SKU", GetType(String)).MaxLength = 10
dt.Columns.Add("FieldName", GetType(String)).MaxLength = 20
dt.Columns.Add("FieldValue", GetType(String)).MaxLength = 200
dt.PrimaryKey = New DataColumn() {dt.Columns("RowID")}
dt.Columns("RowID").AutoIncrement = True
dt.Columns("RowID").AutoIncrementSeed = -1
dt.Columns("RowID").AutoIncrementStep = -1
For intIndex As Integer = 1 To 10
Dim dr As DataRow = dt.NewRow
dr("SKU") = "SKU" & intIndex.ToString
dr("FieldName") = "FieldName" & intIndex.ToString
dr("FieldValue") = "FieldValue" & intIndex.ToString
dt.Rows.Add(dr)
Next
Using conn As New SqlConnection("server=localhost;database=test;integrated security=yes;")
Dim cmd As SqlCommand = conn.CreateCommand
cmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
conn.Open()
For Each dr As DataRow In dt.Rows
cmd.Parameters.Clear()
cmd.CommandText = "INSERT INTO Test (SKU, FieldName, FieldValue) VALUES (@SKU, @FieldName, @FieldValue); SELECT @RowID = @@IDENTITY;"
cmd.Parameters.Add(New SqlParameter("@SKU", SqlDbType.Char, 10, ParameterDirection.Input, False, 0, 0, "SKU", DataRowVersion.Current, dr("SKU")))
cmd.Parameters.Add(New SqlParameter("@FieldName", SqlDbType.VarChar, 200, ParameterDirection.Input, False, 0, 0, "FieldName", DataRowVersion.Current, dr("FieldName")))
cmd.Parameters.Add(New SqlParameter("@FieldValue", SqlDbType.VarChar, 200, ParameterDirection.Input, False, 0, 0, "FieldValue", DataRowVersion.Current, dr("FieldValue")))
cmd.Parameters.Add(New SqlParameter("@RowID", SqlDbType.Int, 4, ParameterDirection.Output, False, 10, 0, "RowID", DataRowVersion.Current, dr("RowID")))
intRowCount += cmd.ExecuteNonQuery()
Next
For Each dr As DataRow In dt.Rows
ListBox1.Items.Add(dr("RowID").ToString())
Next
End Using
End Sub
Private Function CreateDataAdapter(conn AS SQLConnection) As SqlDataAdapter
Dim da As New SqlDataAdapter("SELECT * FROM Test", conn)
Dim dtm As Common.DataTableMapping
dtm = da.TableMappings.Add("Table", "Test")
dtm.ColumnMappings.Add("RowID", "RowID")
dtm.ColumnMappings.Add("SKU", "SKU")
dtm.ColumnMappings.Add("FieldName", "FieldName")
dtm.ColumnMappings.Add("FieldValue", "FieldValue")
Dim cmd As SqlCommand
da.UpdateCommand = da.SelectCommand.Connection.CreateCommand()
cmd = da.UpdateCommand
cmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
cmd.CommandText = "UPDATE [Test] SET [SKU] = @SKUCurrent, [FieldName] = @FieldNameCurrent, [FieldValue] = @FieldValueCurrent WHERE [RowID] = @RowIDOriginal AND [SKU] = @SKUOriginal AND [FieldName] = @FieldNameOriginal AND [FieldValue] = @FieldValueOriginal; SELECT * FROM Test WHERE [RowID] = @RowIDCurrent"
cmd.Parameters.Add(New SqlParameter("@SKUCurrent", SqlDbType.Char, 10, ParameterDirection.Input, False, 0, 0, "SKU", DataRowVersion.Current, Nothing))
cmd.Parameters.Add(New SqlParameter("@FieldNameCurrent", SqlDbType.VarChar, 20, ParameterDirection.Input, False, 0, 0, "FieldName", DataRowVersion.Current, Nothing))
cmd.Parameters.Add(New SqlParameter("@FieldValueCurrent", SqlDbType.VarChar, 200, ParameterDirection.Input, False, 0, 0, "FieldValue", DataRowVersion.Current, Nothing))
cmd.Parameters.Add(New SqlParameter("@RowIDOriginal", SqlDbType.Int, 4, ParameterDirection.Input, False, 10, 0, "RowID", DataRowVersion.Original, Nothing))
cmd.Parameters.Add(New SqlParameter("@SKUOriginal", SqlDbType.Char, 10, ParameterDirection.Input, False, 0, 0, "SKU", DataRowVersion.Original, Nothing))
cmd.Parameters.Add(New SqlParameter("@FieldNameOriginal", SqlDbType.VarChar, 20, ParameterDirection.Input, False, 0, 0, "FieldName", DataRowVersion.Original, Nothing))
cmd.Parameters.Add(New SqlParameter("@FieldValueOriginal", SqlDbType.VarChar, 200, ParameterDirection.Input, False, 0, 0, "FieldValue", DataRowVersion.Original, Nothing))
cmd.Parameters.Add(New SqlParameter("@RowIDCurrent", SqlDbType.Int, 4, ParameterDirection.Input, False, 10, 0, "RowID", DataRowVersion.Current, Nothing))
da.InsertCommand = da.SelectCommand.Connection.CreateCommand()
cmd = da.InsertCommand
cmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
cmd.CommandText = "INSERT INTO [Test] ([SKU], [FieldName], [FieldValue]) VALUES (@SKU, @FieldName, @FieldValue); SELECT * FROM Test WHERE [RowID] = SCOPE_IDENTITY()"
cmd.Parameters.Add(New SqlParameter("@SKU", SqlDbType.Char, 10, ParameterDirection.Input, False, 0, 0, "SKU", DataRowVersion.Current, Nothing))
cmd.Parameters.Add(New SqlParameter("@FieldName", SqlDbType.VarChar, 20, ParameterDirection.Input, False, 0, 0, "FieldName", DataRowVersion.Current, Nothing))
cmd.Parameters.Add(New SqlParameter("@FieldValue", SqlDbType.VarChar, 200, ParameterDirection.Input, False, 0, 0, "FieldValue", DataRowVersion.Current, Nothing))
da.DeleteCommand = da.SelectCommand.Connection.CreateCommand()
cmd = da.DeleteCommand
cmd.UpdatedRowSource = UpdateRowSource.None
cmd.CommandText = "DELETE FROM [Test] WHERE [RowID] = @RowID AND [SKU] = @SKU AND [FieldName] = @FieldName AND [FieldValue] = @FieldValue"
cmd.Parameters.Add(New SqlParameter("@RowID", SqlDbType.Int, 4, ParameterDirection.Input, False, 10, 0, "RowID", DataRowVersion.Original, Nothing))
cmd.Parameters.Add(New SqlParameter("@SKU", SqlDbType.Char, 10, ParameterDirection.Input, False, 0, 0, "SKU", DataRowVersion.Original, Nothing))
cmd.Parameters.Add(New SqlParameter("@FieldName", SqlDbType.VarChar, 20, ParameterDirection.Input, False, 0, 0, "FieldName", DataRowVersion.Original, Nothing))
cmd.Parameters.Add(New SqlParameter("@FieldValue", SqlDbType.VarChar, 200, ParameterDirection.Input, False, 0, 0, "FieldValue", DataRowVersion.Original, Nothing))
Return da
End Function
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
From novice to tech pro — start learning today.
We use the Microsoft Enterprise Library for data connections and I will look there to see how they implement the UpdateDataset calls...