none
Update database. RRS feed

  • Question

  • Hi,

    I creating manual data-table and filling it. like-

                    DataTable dt = new DataTable(tableName);
                    DataColumn columnName;

                    columnName = new DataColumn("pk_id", typeof(System.Int32));
                    // column.AutoIncrement = true;               
                    dt.Columns.Add(columnName);

                    columnName = new DataColumn("time_of_ping", typeof(System.DateTime));
                    columnName.DefaultValue = null;
                    dt.Columns.Add(columnName);

     

    Now I want to store data-table record in database using data-adapter. For that I used-

    da.Update(dt);

    where da= OdbcDataAdapter object.

    Now my question -

    1- since we are not using query. So how to assign data base connection string to data-adapter.

    2- How to store data-table record to database.

    Once more I want to mention that, I am not querying to database.

    I am confuse here.

    Please help me out.

    Its urgent requirement.

    Thursday, November 17, 2011 6:21 AM

Answers

  • You could utilize ExecuteNonQuery by looping thru each row in the DataTable and issue inserts.  Since you don't specify the database you are using, I'll just provide a solution with what I am familiar with (SQL Server).  You also didn't say this, but this code also assumes you have a table in the database that matches to the DataTable you are using. It would look something like this:

    First, you'd need to add a StoredProcedure:

    --**** The StoredProcedure
    ALTER PROCEDURE [sido].[mySProc_InsertNewPingTimes] 
    @pk_id int,
    @time_of_ping datetime
    
    AS
    BEGIN
    SET NOCOUNT ON;
    
    INSERT INTO myTable (pk_id, time_of_ping)
    VALUES (@pk_id, @time_of_ping)
    

     

    Then in your application tier, this would perform the insert:

    Private Sub InsertNewPingTimes(ByRef inDataTable As DataTable)
    
    Dim spCmd As SqlCommand = New SqlCommand()
    spCmd.CommandText = "mySProc_InsertNewPingTimes"
    spCmd.CommandType = CommandType.StoredProcedure
    
    Dim paramPKID As New SqlParameter()
    paramPKID.ParameterName = "@pk_id"
    paramPKID.SqlDbType = SqlDbType.Int
    paramPKID.Direction = ParameterDirection.Input
    
    Dim paramPing As New SqlParameter()
    paramPing.ParameterName = "@time_of_ping"
    paramPing.SqlDbType = SqlDbType.DateTime
    paramPing.Direction = ParameterDirection.Input
    
    Dim r As DataRow
    
      Try
        Using spCmd
           spCmd.Connection = Me.sqlCn <--- or whatever your conn string is
             For Each r In insDT.Rows
              paramPKID.Value = CInt(r.Item("pk_id"))
              paramPing.Value = CDate(r.Item("time_of_ping"))
    
              spCmd.Parameters.Add(paramPKID)
              spCmd.Parameters.Add(paramPing)
    
              spCmd.ExecuteNonQuery()
              spCmd.Parameters.Clear
     
             Next r
        End Using
    
      Catch ex As Exception
           MsgBox(ex.ToString)
      End Try
    End Sub
    

     

    Good Luck!

     


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Thursday, November 17, 2011 7:28 PM

All replies

  • You could utilize ExecuteNonQuery by looping thru each row in the DataTable and issue inserts.  Since you don't specify the database you are using, I'll just provide a solution with what I am familiar with (SQL Server).  You also didn't say this, but this code also assumes you have a table in the database that matches to the DataTable you are using. It would look something like this:

    First, you'd need to add a StoredProcedure:

    --**** The StoredProcedure
    ALTER PROCEDURE [sido].[mySProc_InsertNewPingTimes] 
    @pk_id int,
    @time_of_ping datetime
    
    AS
    BEGIN
    SET NOCOUNT ON;
    
    INSERT INTO myTable (pk_id, time_of_ping)
    VALUES (@pk_id, @time_of_ping)
    

     

    Then in your application tier, this would perform the insert:

    Private Sub InsertNewPingTimes(ByRef inDataTable As DataTable)
    
    Dim spCmd As SqlCommand = New SqlCommand()
    spCmd.CommandText = "mySProc_InsertNewPingTimes"
    spCmd.CommandType = CommandType.StoredProcedure
    
    Dim paramPKID As New SqlParameter()
    paramPKID.ParameterName = "@pk_id"
    paramPKID.SqlDbType = SqlDbType.Int
    paramPKID.Direction = ParameterDirection.Input
    
    Dim paramPing As New SqlParameter()
    paramPing.ParameterName = "@time_of_ping"
    paramPing.SqlDbType = SqlDbType.DateTime
    paramPing.Direction = ParameterDirection.Input
    
    Dim r As DataRow
    
      Try
        Using spCmd
           spCmd.Connection = Me.sqlCn <--- or whatever your conn string is
             For Each r In insDT.Rows
              paramPKID.Value = CInt(r.Item("pk_id"))
              paramPing.Value = CDate(r.Item("time_of_ping"))
    
              spCmd.Parameters.Add(paramPKID)
              spCmd.Parameters.Add(paramPing)
    
              spCmd.ExecuteNonQuery()
              spCmd.Parameters.Clear
     
             Next r
        End Using
    
      Catch ex As Exception
           MsgBox(ex.ToString)
      End Try
    End Sub
    

     

    Good Luck!

     


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Thursday, November 17, 2011 7:28 PM
  • Hi Amit,

    If you have a table which schema is as same as the table you created, you can adopt james solution. Otherwise, you have to write a stored procedure or T-SQL to create a table with the same schema in your database first.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Monday, November 21, 2011 3:45 AM
    Moderator