none
Confused with SqlDataAdapter command properties RRS feed

  • Question

  • http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.selectcommand.aspx

     

    I tried out the above MSDN sample function CreateCustomerAdapter in VB 2008 and .NET 3.5.

     

    I put in my own codes to run the sample function and I am confused with the results.

     

    Here is where I am confused:

     

    1. After inserting Staples in the database, the ID, an identity column, for staples is 7. When I print out the dataset, it showed a value of 6. Is this correct ie. having a value 7 in database and a 6 in dataset? How will VB or ADO knows which row in the dataset belongs to which row in the database?

     

    Table Sponsor, After INSERT, 7 row(s)
    1 / Citibank / Springfield / IL / 500000 / 1000
    2 / Kodak / Denver / CO / 250000 / 500
    3 / Dow Jones / Boston / MA / 750000 / 3000
    4 / Pepsico / Springfield / IL / 300000 / 10500
    5 / UPS / Springfield / IL / 30000 / 250
    6 / Hallmark / Denver / CO / 10000 / 750
    7 / Staples / San Diego / CA / 100000 / 2000
    End of table Sponsor

     

     

    Data in ds.Tables(0) after UPDATE command

    1 Citibank Springfield IL 500000 1000
    4 Pepsico Springfield IL 300000 10500
    5 UPS Springfield IL 35000 280
    6 Staples San Diego CA 100000 2000

     

    2. Hallmark was not loaded in the dataset (it has a different city) but I was able to update the row as follows:

     

    row = dt.Rows.Find(6) ' Hallmark

    row.BeginEdit()

    row("Pledged") = 12000 ' update 10000 -> 12000

    row("Paid") = 800 ' update 750 -> 800

    row.EndEdit()

    da.Update(ds, "Sponsor")

     

    VB will update Hallmark in the database to the new Pledged and Paid values. How is it the update is possible when Hallmark is not in the dataset?

     

    Any explaination to these "strange" behaviors of the dataset? More likely, I think I'm not understanding something.

     

     

    Test Results:

    Code Snippet

    Data in ds.Tables(0) after SELECT Springfield, IL

     

    1 Citibank Springfield IL 500000 1000
    4 Pepsico Springfield IL 300000 10500
    5 UPS Springfield IL 30000 250

     

    Table Sponsor, Before INSERT, 6 row(s)
    1 / Citibank / Springfield / IL / 500000 / 1000
    2 / Kodak / Denver / CO / 250000 / 500
    3 / Dow Jones / Boston / MA / 750000 / 3000
    4 / Pepsico / Springfield / IL / 300000 / 10500
    5 / UPS / Springfield / IL / 30000 / 250
    6 / Hallmark / Denver / CO / 10000 / 750
    End of table Sponsor

     

    Table Sponsor, After INSERT, 7 row(s)
    1 / Citibank / Springfield / IL / 500000 / 1000
    2 / Kodak / Denver / CO / 250000 / 500
    3 / Dow Jones / Boston / MA / 750000 / 3000
    4 / Pepsico / Springfield / IL / 300000 / 10500
    5 / UPS / Springfield / IL / 30000 / 250
    6 / Hallmark / Denver / CO / 10000 / 750
    7 / Staples / San Diego / CA / 100000 / 2000
    End of table Sponsor

     

    Data in ds.Tables(0) after UPDATE command

     

    1 Citibank Springfield IL 500000 1000
    4 Pepsico Springfield IL 300000 10500
    5 UPS Springfield IL 35000 280
    6 Staples San Diego CA 100000 2000

     

    Table Sponsor, After UPDATE, 7 row(s)
    1 / Citibank / Springfield / IL / 500000 / 1000
    2 / Kodak / Denver / CO / 250000 / 500
    3 / Dow Jones / Boston / MA / 750000 / 3000
    4 / Pepsico / Springfield / IL / 300000 / 10500
    5 / UPS / Springfield / IL / 35000 / 280
    6 / Hallmark / Denver / CO / 10000 / 750
    7 / Staples / San Diego / CA / 100000 / 2000
    End of table Sponsor

     

    Data in ds.Tables(0) after DELETE command

     

    1 Citibank Springfield IL 500000 1000
    5 UPS Springfield IL 35000 280
    6 Staples San Diego CA 100000 2000

     

    Table Sponsor, After DELETE, 6 row(s)
    1 / Citibank / Springfield / IL / 500000 / 1000
    2 / Kodak / Denver / CO / 250000 / 500
    3 / Dow Jones / Boston / MA / 750000 / 3000
    5 / UPS / Springfield / IL / 35000 / 280
    6 / Hallmark / Denver / CO / 10000 / 750
    7 / Staples / San Diego / CA / 100000 / 2000
    End of table Sponsor

     

     

     

     

    Here is my codes:

     

    The table I used:

    Code Snippet

    CREATE TABLE Sponsor (

    ID int PRIMARY KEY IDENTITY,

    CompanyName nvarchar(25),

    City nvarchar(25),

    State nvarchar(2),

    Pledged int,

    Paid int)

     

     

    The sample data delimited by "/", prior to running the program:

    Code Snippet
    1 / Citibank / Springfield / IL / 500000 / 1000 /
    2 / Kodak / Denver / CO / 250000 / 500 /
    3 / Dow Jones / Boston / MA / 750000 / 3000 /
    4 / Pepsico / Springfield / IL / 300000 / 10500 /
    5 / UPS / Springfield / IL / 30000 / 250 /
    6 / Hallmark / Denver / CO / 10000 / 750 /

     

     

    The function CreateSponsorsAdapter based on the sample MSDN function:

    Code Snippet

    Public Function CreateSponsorsAdapter( _

    ByVal conn As SqlConnection) As SqlDataAdapter

     

    Dim da As SqlDataAdapter = New SqlDataAdapter()

     

    ' Create the SelectCommand.

    Dim cmd As SqlCommand = New SqlCommand( _

    "SELECT * FROM Sponsor " & _

    "WHERE City = @City AND State = @State", conn)

     

    ' Add the parameters for the SelectCommand.

    cmd.Parameters.Add("@City", SqlDbType.NVarChar, 25)

    cmd.Parameters.Add("@State", SqlDbType.NVarChar, 2)

    da.SelectCommand = cmd

     

    ' Create the InsertCommand.

    cmd = New SqlCommand( _

    "INSERT INTO Sponsor (CompanyName, City, State, Pledged, Paid) " & _

    "VALUES (@CompanyName, @City, @State, @Pledged, @Paid)", conn)

     

    ' Add the parameters for the InsertCommand.

    cmd.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 25, "CompanyName")

    cmd.Parameters.Add("@City", SqlDbType.NVarChar, 25, "City")

    cmd.Parameters.Add("@State", SqlDbType.NVarChar, 2, "State")

    cmd.Parameters.Add("@Pledged", SqlDbType.Int, 10, "Pledged")

    cmd.Parameters.Add("@Paid", SqlDbType.Int, 10, "Paid")

    da.InsertCommand = cmd

     

    ' Create the UpdateCommand.

    cmd = New SqlCommand( _

    "UPDATE Sponsor SET Pledged = @Pledged, Paid = @Paid " & _

    "WHERE ID = @ID", conn)

     

    ' Add the parameters for the UpdateCommand.

    cmd.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")

    cmd.Parameters.Add("@Pledged", SqlDbType.Int, 10, "Pledged")

    cmd.Parameters.Add("@Paid", SqlDbType.Int, 10, "Paid")

    Dim parameter As SqlParameter = cmd.Parameters.Add( _

    "@oldID", SqlDbType.Int, 4, "ID")

    parameter.SourceVersion = DataRowVersion.Original

    da.UpdateCommand = cmd

     

    ' Create the DeleteCommand.

    cmd = New SqlCommand( _

    "DELETE FROM Sponsor WHERE ID = @ID", conn)

     

    ' Add the parameters for the DeleteCommand.

    cmd.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")

    parameter.SourceVersion = DataRowVersion.Original

    da.DeleteCommand = cmd

    Return da

    End Function

     

    The code I used to call the function, CreateSponsorsAdapter.

    Code Snippet

    Private Sub btnSqlDataAdapterCommands_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSqlDataAdapterCommands.Click

     

    Dim strConn As String = "Data Source=.\SQLExpress;" & _

    "AttachDbFilename=" & Chr(34) & _

    "C:\Baseball.mdf" & Chr(34) & _

    ";Integrated Security=True;User Instance=True;"

     

    Using conn As New SqlConnection(strConn)

     

    Dim da As New SqlDataAdapter()

    da = CreateSponsorsAdapter(conn)

     

    Dim ds As New DataSet

     

    ' Configure parameters for SELECT

    da.SelectCommand.Parameters("@City").Value = "Springfield"

    da.SelectCommand.Parameters("@State").Value = "IL"

    da.FillSchema(ds, SchemaType.Source, "Sponsor") ' this line needed for Primary Key

    da.Fill(ds, "Sponsor")

     

    Console.WriteLine("Data in ds.Tables(0) after SELECT Springfield, IL" & vbCrLf)

    For Each row1 As DataRow In ds.Tables(0).Rows

    For i As Integer = 0 To ds.Tables(0).Columns.Count - 1

    Console.Write("{0} ", row1(i))

    Next i

    Console.Write(vbCrLf)

    Next

    Console.Write(vbCrLf)

     

    '--------------------

    ' INSERT a row

    '--------------------

    PrintTable(strConn, "Sponsor", "Before INSERT")

    Dim dt As DataTable = ds.Tables("Sponsor")

    ' Create a DataRow, ID not required - Identity column

    Dim row As DataRow = dt.NewRow()

    row("CompanyName") = "Staples"

    row("City") = "San Diego"

    row("State") = "CA"

    row("Pledged") = 100000

    row("Paid") = 2000

    dt.Rows.Add(row) ' Add row to Data Table

    da.Update(ds, "Sponsor") ' Insert row to table by calling da.Update

    PrintTable(strConn, "Sponsor", "After INSERT")

     

    '--------------------

    ' UPDATE a row

    '--------------------

    row = dt.Rows.Find(5) ' UPS

    row.BeginEdit()

    row("Pledged") = 35000 ' 30000 -> 35000

    row("Paid") = 280 ' 250 -> 280

    row.EndEdit()

    Dim builder As New SqlCommandBuilder(da)

    da.Update(ds, "Sponsor") ' Without the SqlCommandBuilder this line would fail

    Console.WriteLine("Data in ds.Tables(0) after UPDATE command" & vbCrLf)

    For Each row2 As DataRow In ds.Tables(0).Rows

    For i As Integer = 0 To ds.Tables(0).Columns.Count - 1

    Console.Write("{0} ", row2(i))

    Next i

    Console.Write(vbCrLf)

    Next

    Console.Write(vbCrLf)

    PrintTable(strConn, "Sponsor", "After UPDATE")

     

    '--------------------

    ' DELETE a row

    '--------------------

    row = dt.Rows.Find(4) ' Pepsico

    row.Delete()

    da.Update(ds, "Sponsor")

    Console.WriteLine("Data in ds.Tables(0) after DELETE command" & vbCrLf)

    For Each row3 As DataRow In ds.Tables(0).Rows

    For i As Integer = 0 To ds.Tables(0).Columns.Count - 1

    Console.Write("{0} ", row3(i))

    Next i

    Console.Write(vbCrLf)

    Next

    Console.Write(vbCrLf)

    PrintTable(strConn, "Sponsor", "After DELETE")

    End Using

     

     

    End Sub

     

     

    The PrintTable function loops through each row in the table Sponsor and prints the rows.

    Code Snippet

    Private Sub PrintTable(ByVal strConn As String, ByVal tablename As String, ByVal commentText As String)

     

    Dim conn As New SqlConnection(strConn)

    ' Sql Query

    Dim sql As String = "SELECT * FROM " & tablename

     

    Try

    ' Create Data Adapter

    Dim da As New SqlDataAdapter

    da.SelectCommand = New SqlCommand(sql, conn)

    ' Create and fill Dataset

    Dim ds As New DataSet

    da.Fill(ds, tablename)

    ' Get the Data Table

    Dim dt As DataTable = ds.Tables(tablename)

    ' Display Rows in Data Table

     

    Console.WriteLine("Table {0}, {1}, {2} row(s)", tablename, commentText, dt.Rows.Count)

     

    For Each row As DataRow In dt.Rows

    Console.WriteLine(row(0) & " / " & row(1) & " / " & row(2) & " / " & row(3) & " / " & row(4) & " / " & row(5))

    Next

     

    Catch ex As SqlException

    ' Display error

    Console.WriteLine("Error: " & ex.ToString())

    Finally

    ' Close Connection

    conn.Close()

    Console.WriteLine("End of table {0}" & vbCrLf, tablename)

    End Try

     

    End Sub

     

     

    /end of post

    Wednesday, September 3, 2008 9:00 PM