在VB.net中使用SQLDataAdapter将数据插入到多个表中 [英] Insert Data to multiple tables using SQLDataAdapter in VB.net

问题描述

您好，所有访客，
我有4个表(1个TransferMaster 2个TransferDetail 3个PartnerDeptMaster 4个PartnerDeptDetail).在我的表单中，当我单击保存"按钮时，我希望代码插入到4个表中.我是否可能只想使用一个SQLDadaAdapter和一个DataSet?
谢谢
TONY

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

推荐答案

托尼，

有可能的.但是每次都必须将选择，插入，更新和删除命令重新分配给数据适配器.

代码应该是这样的

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

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

托尼，

我认为有可能.但是您每次都要打开和关闭连接.

我不知道为什么您使用1个datan数据集.但是最好使用多个数据集n数据适配器进行多个查询.

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.

根据您的要求，在插入多个表时，U还应对sql命令使用事务处理.下面是示例代码，可能对您有用.

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.

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.
}

问候，
Santhosh Kumar G

Regards,
Santhosh Kumar G

这篇关于在VB.net中使用SQLDataAdapter将数据插入到多个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！