Insert Data to multiple tables using SQLDataAdapter in VB.net

Question

0.00/5 (No votes)

See more:

Hi all visitors,
I have 4 tables(1 TransferMaster 2 TransferDetail 3 PartnerDeptMaster 4 PartnerDeptDetail).In my form,when i click Save Button, i want the code insert to 4 tables. Is it possible that i want to use only one SQLDadaAdapter and one DataSet?
Thanks
TONY

Posted 11-Jan-12 16:29pm

soeun tony

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

thams · Answer 1 · 2012-01-11T20:31:00

Hi Tony,

It is possible. But you have to reassign the select,insert,update and delete commands to the data Adapter each time.

the code should be something like this

VB

Imports System.Data.SqlClient
Public Class Form1
    Dim ds As DataSet 'which contains your tables.
    Dim adp As SqlDataAdapter 'create the connection accordingly
    Dim con As SqlConnection

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        For Each tbl As DataTable In ds.Tables
            adp = New SqlDataAdapter
            If tbl.TableName = "TransferMaster" Then
                adp.SelectCommand = New SqlCommand("your select command", con)
                adp.InsertCommand = New SqlCommand("Your insert command", con)
                adp.UpdateCommand = New SqlCommand("Your update command", con)
                adp.DeleteCommand = New SqlCommand("Your delete command", con)
            ElseIf tbl.TableName = "TransferDetail" Then
                adp.SelectCommand = New SqlCommand("your select command", con)
                adp.InsertCommand = New SqlCommand("Your insert command", con)
                adp.UpdateCommand = New SqlCommand("Your update command", con)
                adp.DeleteCommand = New SqlCommand("Your delete command", con)
                'esleif --continue as many tables you have
            End If
            adp.Update(tbl)
        Next
    End Sub
End Class

ranjeet.waje · Answer 2 · 2012-01-11T18:13:00

Solution 1

Hi Tony,

I think it possible. But u hav to open and close connection everytime.

I dont know why ur using 1 dataadpter n dataset. but it is good that to use multiple dataset n dataadpter for multiple query.

Posted 11-Jan-12 18:13pm

ranjeet.waje

Santhosh Kumar Gudise · Answer 3 · 2012-01-17T20:24:00

Hi,
Based on ur requirement, U should also use Transactions for ur sql commands while inserting into multiple tables. Below is the sample code might be useful for u.

C#

public void save(DataSet newdsEstimation){
sqlDataAdapter = (SqlDataAdapter)SqlDB.GetDataAdapter();
            if (sqlConnection.State == System.Data.ConnectionState.Closed)
                sqlConnection.Open();
            sqlTrans = sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
foreach (DataTable dt in newdsEstimation.Tables)
                {
                    if (dt.TableName == "Material")
                    {
                        PrepareEstimationCommand(sqlTrans, sqlDataAdapter, dt.TableName);
                        sqlDataAdapter.Update(objdsEstimations.EstimationMaterial);
                    }
                    else if (dt.TableName == "Service")
                    {
                        PrepareEstimationCommand(sqlTrans, sqlDataAdapter, dt.TableName);
                        sqlDataAdapter.Update(objdsEstimations.EstimationService);
                    }
                   
                }
                sqlTrans.Commit();
}
void PrepareEstimationCommand(SqlTransaction sqlTrans, SqlDataAdapter sqlDataAdapter, string strTableName)
        {
            string strInsertSQL = string.Empty;
            string strUpdateSql = string.Empty;
            if (strTableName == "Material")
            {
                strInsertSQL = @"INSERT INTO Materials
                                        (
                                            ----
                                        )
                                        VALUES
                                        (
                                            ----
                                        )";
            }
            else{
                  strInsertSQL = @"INSERT INTO Services
                                        (
                                            ----
                                        )
                                        VALUES
                                        (
                                            ----
                                        )";
SqlCommand sqlCmdInsert = (SqlCommand)SqlDB.GetSqlStringCommand(strInsertSQL);
            sqlCmdInsert.Connection = sqlConnection;
            sqlCmdInsert.Transaction = sqlTrans;
            SetInsertParam_EstimationDetail(sqlCmdInsert, strTableName);
            sqlDataAdapter.InsertCommand = sqlCmdInsert;

           //U can write update qry also like this way.
}

Regards,
Santhosh Kumar G

Insert Data to multiple tables using SQLDataAdapter in VB.net

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0