none
SqlDataAdapter.Fill这个会提交事务,有没有什么禁止它提交的办法? RRS feed

全部回复

  • 可以自己加事务 Transactions Using a DataAdapter

    String connString = "Data Source=(local);Integrated security=SSPI;" + 
    
        "Initial Catalog=Northwind;";
    
    
    
    String sqlSelect = "SELECT * FROM Orders";
    
    
    
    SqlConnection conn = new SqlConnection(connString);
    
    conn.Open();
    
    
    
    SqlDataAdapter da = new SqlDataAdapter(sqlSelect, conn);
    
    DataSet ds = new DataSet();
    
    
    
    // define update logic for the data adapter
    
    
    
    // load  data from the data source into the DataSet
    
    da.Fill(ds, "Orders");
    
    
    
    // start the transaction
    
    SqlTransaction tran = conn.BeginTransaction();
    
    
    
    // associate transaction with the data adapter command objects
    
    da.DeleteCommand.Transaction = tran;
    
    da.InsertCommand.Transaction = tran;
    
    da.UpdateCommand.Transaction = tran;
    
    
    
    // ... modify the data in the DataSet
    
    
    
    // submit changes, commit or rollback, and close the connection
    
    try
    
    {
    
        da.Update(ds, "Orders");
    
        
    
        // commit if successful
    
        tran.Commit();
    
    }
    
    catch (Exception)
    
    {
    
        tran.Rollback();
    
    }
    
    finally
    
    {
    
        conn.Close();
    
    }


    专注于.NET ERP/CRM开发框架,C/S架构,SQL Server + ORM(LLBL Gen Pro) + Infragistics WinForms


    2018年6月6日 0:19
  • 参考源代码, SqlDataAdapter继承于DbDataAdapter,它的Fill方法如下,没有找到事务提交代码

    public override int Fill(DataSet dataSet)
    {
        int num;
        IntPtr ptr;
        Bid.ScopeEnter(out ptr, "<comm.DbDataAdapter.Fill|API> %d#, dataSet\n", base.ObjectID);
        try
        {
            IDbCommand selectCommand = this._IDbDataAdapter.SelectCommand;
            CommandBehavior fillCommandBehavior = this.FillCommandBehavior;
            num = this.Fill(dataSet, 0, 0, "Table", selectCommand, fillCommandBehavior);
        }
        finally
        {
            Bid.ScopeLeave(ref ptr);
        }
        return num;
    }

     

     
      public override int Fill(DataSet dataSet)
    {
        int num;
        IntPtr ptr;
        Bid.ScopeEnter(out ptr, "<comm.DbDataAdapter.Fill|API> %d#, dataSet\n", base.ObjectID);
        try
        {
            IDbCommand selectCommand = this._IDbDataAdapter.SelectCommand;
            CommandBehavior fillCommandBehavior = this.FillCommandBehavior;
            num = this.Fill(dataSet, 0, 0, "Table", selectCommand, fillCommandBehavior);
        }
        finally
        {
            Bid.ScopeLeave(ref ptr);
        }
        return num;
    }

     

     
     public int Fill(DataTable dataTable)
    {
        int num;
        IntPtr ptr;
        Bid.ScopeEnter(out ptr, "<comm.DbDataAdapter.Fill|API> %d#, dataTable\n", base.ObjectID);
        try
        {
            DataTable[] dataTables = new DataTable[] { dataTable };
            IDbCommand selectCommand = this._IDbDataAdapter.SelectCommand;
            CommandBehavior fillCommandBehavior = this.FillCommandBehavior;
            num = this.Fill(dataTables, 0, 0, selectCommand, fillCommandBehavior);
        }
        finally
        {
            Bid.ScopeLeave(ref ptr);
        }
        return num;
    }

     

     
      public int Fill(DataTable dataTable)
    {
        int num;
        IntPtr ptr;
        Bid.ScopeEnter(out ptr, "<comm.DbDataAdapter.Fill|API> %d#, dataTable\n", base.ObjectID);
        try
        {
            DataTable[] dataTables = new DataTable[] { dataTable };
            IDbCommand selectCommand = this._IDbDataAdapter.SelectCommand;
            CommandBehavior fillCommandBehavior = this.FillCommandBehavior;
            num = this.Fill(dataTables, 0, 0, selectCommand, fillCommandBehavior);
        }
        finally
        {
            Bid.ScopeLeave(ref ptr);
        }
        return num;
    }

     

     public int Fill(DataSet dataSet, string srcTable)
    {
        int num;
        IntPtr ptr;
        Bid.ScopeEnter(out ptr, "<comm.DbDataAdapter.Fill|API> %d#, dataSet, srcTable='%ls'\n", base.ObjectID, srcTable);
        try
        {
            IDbCommand selectCommand = this._IDbDataAdapter.SelectCommand;
            CommandBehavior fillCommandBehavior = this.FillCommandBehavior;
            num = this.Fill(dataSet, 0, 0, srcTable, selectCommand, fillCommandBehavior);
        }
        finally
        {
            Bid.ScopeLeave(ref ptr);
        }
        return num;
    }

     

     
      public int Fill(DataSet dataSet, string srcTable)
    {
        int num;
        IntPtr ptr;
        Bid.ScopeEnter(out ptr, "<comm.DbDataAdapter.Fill|API> %d#, dataSet, srcTable='%ls'\n", base.ObjectID, srcTable);
        try
        {
            IDbCommand selectCommand = this._IDbDataAdapter.SelectCommand;
            CommandBehavior fillCommandBehavior = this.FillCommandBehavior;
            num = this.Fill(dataSet, 0, 0, srcTable, selectCommand, fillCommandBehavior);
        }
        finally
        {
            Bid.ScopeLeave(ref ptr);
        }
        return num;
    }

     

     protected virtual int Fill(DataTable dataTable, IDbCommand command, CommandBehavior behavior)
    {
        int num;
        IntPtr ptr;
        Bid.ScopeEnter(out ptr, "<comm.DbDataAdapter.Fill|API> dataTable, command, behavior=%d{ds.CommandBehavior}%d#\n", base.ObjectID, (int) behavior);
        try
        {
            DataTable[] dataTables = new DataTable[] { dataTable };
            num = this.Fill(dataTables, 0, 0, command, behavior);
        }
        finally
        {
            Bid.ScopeLeave(ref ptr);
        }
        return num;
    }

     

     
      protected virtual int Fill(DataTable dataTable, IDbCommand command, CommandBehavior behavior)
    {
        int num;
        IntPtr ptr;
        Bid.ScopeEnter(out ptr, "<comm.DbDataAdapter.Fill|API> dataTable, command, behavior=%d{ds.CommandBehavior}%d#\n", base.ObjectID, (int) behavior);
        try
        {
            DataTable[] dataTables = new DataTable[] { dataTable };
            num = this.Fill(dataTables, 0, 0, command, behavior);
        }
        finally
        {
            Bid.ScopeLeave(ref ptr);
        }
        return num;
    }

     
    public int Fill(int startRecord, int maxRecords, params DataTable[] dataTables)
    {
        int num;
        IntPtr ptr;
        Bid.ScopeEnter(out ptr, "<comm.DbDataAdapter.Fill|API> %d#, startRecord=%d, maxRecords=%d, dataTable[]\n", base.ObjectID, startRecord, maxRecords);
        try
        {
            IDbCommand selectCommand = this._IDbDataAdapter.SelectCommand;
            CommandBehavior fillCommandBehavior = this.FillCommandBehavior;
            num = this.Fill(dataTables, startRecord, maxRecords, selectCommand, fillCommandBehavior);
        }
        finally
        {
            Bid.ScopeLeave(ref ptr);
        }
        return num;
    }

     

     
      public int Fill(int startRecord, int maxRecords, params DataTable[] dataTables)
    {
        int num;
        IntPtr ptr;
        Bid.ScopeEnter(out ptr, "<comm.DbDataAdapter.Fill|API> %d#, startRecord=%d, maxRecords=%d, dataTable[]\n", base.ObjectID, startRecord, maxRecords);
        try
        {
            IDbCommand selectCommand = this._IDbDataAdapter.SelectCommand;
            CommandBehavior fillCommandBehavior = this.FillCommandBehavior;
            num = this.Fill(dataTables, startRecord, maxRecords, selectCommand, fillCommandBehavior);
        }
        finally
        {
            Bid.ScopeLeave(ref ptr);
        }
        return num;
    }

     
    public int Fill(DataSet dataSet, int startRecord, int maxRecords, string srcTable)
    {
        int num;
        IntPtr ptr;
        Bid.ScopeEnter(out ptr, "<comm.DbDataAdapter.Fill|API> %d#, dataSet, startRecord=%d, maxRecords=%d, srcTable='%ls'\n", base.ObjectID, startRecord, maxRecords, srcTable);
        try
        {
            IDbCommand selectCommand = this._IDbDataAdapter.SelectCommand;
            CommandBehavior fillCommandBehavior = this.FillCommandBehavior;
            num = this.Fill(dataSet, startRecord, maxRecords, srcTable, selectCommand, fillCommandBehavior);
        }
        finally
        {
            Bid.ScopeLeave(ref ptr);
        }
        return num;
    }

     

     
      public int Fill(DataSet dataSet, int startRecord, int maxRecords, string srcTable)
    {
        int num;
        IntPtr ptr;
        Bid.ScopeEnter(out ptr, "<comm.DbDataAdapter.Fill|API> %d#, dataSet, startRecord=%d, maxRecords=%d, srcTable='%ls'\n", base.ObjectID, startRecord, maxRecords, srcTable);
        try
        {
            IDbCommand selectCommand = this._IDbDataAdapter.SelectCommand;
            CommandBehavior fillCommandBehavior = this.FillCommandBehavior;
            num = this.Fill(dataSet, startRecord, maxRecords, srcTable, selectCommand, fillCommandBehavior);
        }
        finally
        {
            Bid.ScopeLeave(ref ptr);
        }
        return num;
    }

     
    protected virtual int Fill(DataTable[] dataTables, int startRecord, int maxRecords, IDbCommand command, CommandBehavior behavior)
    {
        int num;
        IntPtr ptr;
        Bid.ScopeEnter(out ptr, "<comm.DbDataAdapter.Fill|API> %d#, dataTables[], startRecord, maxRecords, command, behavior=%d{ds.CommandBehavior}\n", base.ObjectID, (int) behavior);
        try
        {
            if (((dataTables == null) || (dataTables.Length == 0)) || (dataTables[0] == null))
            {
                throw ADP.FillRequires("dataTable");
            }
            if (startRecord < 0)
            {
                throw ADP.InvalidStartRecord("startRecord", startRecord);
            }
            if (maxRecords < 0)
            {
                throw ADP.InvalidMaxRecords("maxRecords", maxRecords);
            }
            if ((1 < dataTables.Length) && ((startRecord != 0) || (maxRecords != 0)))
            {
                throw ADP.OnlyOneTableForStartRecordOrMaxRecords();
            }
            if (command == null)
            {
                throw ADP.MissingSelectCommand("Fill");
            }
            if (1 == dataTables.Length)
            {
                behavior |= CommandBehavior.SingleResult;
            }
            num = this.FillInternal(null, dataTables, startRecord, maxRecords, null, command, behavior);
        }
        finally
        {
            Bid.ScopeLeave(ref ptr);
        }
        return num;
    }

     

     
      protected virtual int Fill(DataTable[] dataTables, int startRecord, int maxRecords, IDbCommand command, CommandBehavior behavior)
    {
        int num;
        IntPtr ptr;
        Bid.ScopeEnter(out ptr, "<comm.DbDataAdapter.Fill|API> %d#, dataTables[], startRecord, maxRecords, command, behavior=%d{ds.CommandBehavior}\n", base.ObjectID, (int) behavior);
        try
        {
            if (((dataTables == null) || (dataTables.Length == 0)) || (dataTables[0] == null))
            {
                throw ADP.FillRequires("dataTable");
            }
            if (startRecord < 0)
            {
                throw ADP.InvalidStartRecord("startRecord", startRecord);
            }
            if (maxRecords < 0)
            {
                throw ADP.InvalidMaxRecords("maxRecords", maxRecords);
            }
            if ((1 < dataTables.Length) && ((startRecord != 0) || (maxRecords != 0)))
            {
                throw ADP.OnlyOneTableForStartRecordOrMaxRecords();
            }
            if (command == null)
            {
                throw ADP.MissingSelectCommand("Fill");
            }
            if (1 == dataTables.Length)
            {
                behavior |= CommandBehavior.SingleResult;
            }
            num = this.FillInternal(null, dataTables, startRecord, maxRecords, null, command, behavior);
        }
        finally
        {
            Bid.ScopeLeave(ref ptr);
        }
        return num;
    }

     
    protected virtual int Fill(DataSet dataSet, int startRecord, int maxRecords, string srcTable, IDbCommand command, CommandBehavior behavior)
    {
        int num;
        IntPtr ptr;
        Bid.ScopeEnter(out ptr, "<comm.DbDataAdapter.Fill|API> %d#, dataSet, startRecord, maxRecords, srcTable, command, behavior=%d{ds.CommandBehavior}\n", base.ObjectID, (int) behavior);
        try
        {
            if (dataSet == null)
            {
                throw ADP.FillRequires("dataSet");
            }
            if (startRecord < 0)
            {
                throw ADP.InvalidStartRecord("startRecord", startRecord);
            }
            if (maxRecords < 0)
            {
                throw ADP.InvalidMaxRecords("maxRecords", maxRecords);
            }
            if (ADP.IsEmpty(srcTable))
            {
                throw ADP.FillRequiresSourceTableName("srcTable");
            }
            if (command == null)
            {
                throw ADP.MissingSelectCommand("Fill");
            }
            num = this.FillInternal(dataSet, null, startRecord, maxRecords, srcTable, command, behavior);
        }
        finally
        {
            Bid.ScopeLeave(ref ptr);
        }
        return num;
    }

     

     
      protected virtual int Fill(DataSet dataSet, int startRecord, int maxRecords, string srcTable, IDbCommand command, CommandBehavior behavior)
    {
        int num;
        IntPtr ptr;
        Bid.ScopeEnter(out ptr, "<comm.DbDataAdapter.Fill|API> %d#, dataSet, startRecord, maxRecords, srcTable, command, behavior=%d{ds.CommandBehavior}\n", base.ObjectID, (int) behavior);
        try
        {
            if (dataSet == null)
            {
                throw ADP.FillRequires("dataSet");
            }
            if (startRecord < 0)
            {
                throw ADP.InvalidStartRecord("startRecord", startRecord);
            }
            if (maxRecords < 0)
            {
                throw ADP.InvalidMaxRecords("maxRecords", maxRecords);
            }
            if (ADP.IsEmpty(srcTable))
            {
                throw ADP.FillRequiresSourceTableName("srcTable");
            }
            if (command == null)
            {
                throw ADP.MissingSelectCommand("Fill");
            }
            num = this.FillInternal(dataSet, null, startRecord, maxRecords, srcTable, command, behavior);
        }
        finally
        {
            Bid.ScopeLeave(ref ptr);
        }
        return num;
    }

     
    private int FillInternal(DataSet dataset, DataTable[] datatables, int startRecord, int maxRecords, string srcTable, IDbCommand command, CommandBehavior behavior)
    {
        bool flag = null == command.Connection;
        try
        {
            IDbConnection connection = GetConnection3(this, command, "Fill");
            ConnectionState open = ConnectionState.Open;
            if (MissingSchemaAction.AddWithKey == base.MissingSchemaAction)
            {
                behavior |= CommandBehavior.KeyInfo;
            }
            try
            {
                QuietOpen(connection, out open);
                behavior |= CommandBehavior.SequentialAccess;
                using (IDataReader reader = null)
                {
                    reader = command.ExecuteReader(behavior);
                    if (datatables != null)
                    {
                        return this.Fill(datatables, reader, startRecord, maxRecords);
                    }
                    return this.Fill(dataset, srcTable, reader, startRecord, maxRecords);
                }
            }
            finally
            {
                QuietClose(connection, open);
            }
        }
        finally
        {
            if (flag)
            {
                command.Transaction = null;
                command.Connection = null;
            }
        }
        return 0;
    }

     

     
      private int FillInternal(DataSet dataset, DataTable[] datatables, int startRecord, int maxRecords, string srcTable, IDbCommand command, CommandBehavior behavior)
    {
        bool flag = null == command.Connection;
        try
        {
            IDbConnection connection = GetConnection3(this, command, "Fill");
            ConnectionState open = ConnectionState.Open;
            if (MissingSchemaAction.AddWithKey == base.MissingSchemaAction)
            {
                behavior |= CommandBehavior.KeyInfo;
            }
            try
            {
                QuietOpen(connection, out open);
                behavior |= CommandBehavior.SequentialAccess;
                using (IDataReader reader = null)
                {
                    reader = command.ExecuteReader(behavior);
                    if (datatables != null)
                    {
                        return this.Fill(datatables, reader, startRecord, maxRecords);
                    }
                    return this.Fill(dataset, srcTable, reader, startRecord, maxRecords);
                }
            }
            finally
            {
                QuietClose(connection, open);
            }
        }
        finally
        {
            if (flag)
            {
                command.Transaction = null;
                command.Connection = null;
            }
        }
        return 0;
    }

    专注于.NET ERP/CRM开发框架,C/S架构,SQL Server + ORM(LLBL Gen Pro) + Infragistics WinForms

    2018年6月6日 0:26
  • 你好,

    SqlDataAdapter.Fill 只是把加载到dataset或者datatable, 你说的如果是SQL Server select 的隐形事务的话,这个是和SQL Server相关的, 和ado.net 没有关系。

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018年6月7日 5:23
    版主