subsonic事务

 

private void button1_Click(object sender, EventArgs e)
        {
            QueryCommandCollection qcc = new QueryCommandCollection(CsdKlszMigrateVehicleSerialDef.VehicleSeriesIdColumn.ColumnName);
            CsdKlszMigrateVehicleSerialDef seri = new CsdKlszMigrateVehicleSerialDef();
            seri.VehicleSeriesCode = "0008";
            seri.VehicleSeriesName = "oooo";
            seri.IsEnable = true;
            seri.StatusCode = "--";
            qcc.Add(seri.GetSaveCommand());

            //获得sql语句
           string s= seri.GetSaveCommand().CommandSql;

            CsdKlszGroupStruc str = new CsdKlszGroupStruc();
            str.GroupCode = "0003";
            str.IsEnable = true;
            str.VehicleSeriesId = seri.VehicleSeriesId;
            qcc.Add(str.GetSaveCommand());
            DataService.ExecuteTransaction(qcc, DataService.Provider.Name);

            //---------------------

            using (AutomaticConnectionScope com = new AutomaticConnectionScope(DataService.Provider))
            {
                string sql1 = @"/* ExecuteTransaction() */ /* GetInsertSql(csd_klsz_migrate_vehicle_serial_def) */ INSERT INTO [dbo].[csd_klsz_migrate_vehicle_serial_def]([vehicle_series_code],[vehicle_series_name],[vehicle_series_desc],[is_enable],[status_code],[remark],[modify_userid],[modify_time],[create_userid],[create_time]) VALUES('1','1','1',1,'1','1','1',getdate(),'1',getdate());SELECT SCOPE_IDENTITY() AS newID;";
                if (com.Connection.State == ConnectionState.Closed)
                    com.Connection.Open();

                SqlTransaction trans = (SqlTransaction)com.Connection.BeginTransaction();

                SqlCommand cmd = new SqlCommand(sql1, (SqlConnection)com.Connection, trans);

                object o = cmd.ExecuteScalar();
                int t = Convert.ToInt16(o);
                sql1 = @" /* ExecuteTransaction() */ /* GetInsertSql(csd_klsz_group_struc) */ INSERT INTO [dbo].[csd_klsz_group_struc]([group_code],[seq_no],[qty],[parent_id],[level],[is_enable],[ref_group_id],[choic_mod_id],[remark],[vehicle_series_id],[modify_userid],[modify_time],[create_userid],[create_time]) VALUES('1','1',1,1,1,1,1,1,'@remark'," + t + ",'@remark',getdate(),'@remark',getdate());SELECT SCOPE_IDENTITY() AS newID;";
                cmd = new SqlCommand(sql1, (SqlConnection)com.Connection, trans);
                cmd.ExecuteScalar();

                trans.Commit();
            }
        }

 

public override void ExecuteTransaction(QueryCommandCollection commands)
        {
            //make sure we have at least one
            if(commands.Count == 0)
                throw new ArgumentOutOfRangeException("commands", "No commands present");

            //a using statement will make sure we close off the connection
            using(AutomaticConnectionScope conn = new AutomaticConnectionScope(this))
            {
                
                //open up the connection and start the transaction
                if(conn.Connection.State == ConnectionState.Closed)
                    conn.Connection.Open();

                SqlTransaction trans = (SqlTransaction)conn.Connection.BeginTransaction();

                SqlCommand cmd;
                foreach(QueryCommand qry in commands)
                {
                    if(qry.CommandType == CommandType.Text)
                        qry.CommandSql = String.Concat("/* ExecuteTransaction() */ ", qry.CommandSql);
                    cmd = new SqlCommand(qry.CommandSql, (SqlConnection)conn.Connection, trans);
                    cmd.CommandType = qry.CommandType;

                    if (commands.IsMasterDetail  && qry.Parameters.Contains("@" + commands.RelationFieldName))
                    {
                        qry.Parameters.GetParameter("@" + commands.RelationFieldName).ParameterValue = commands.RelationFieldValue;
                    }

                    AddParams(cmd, qry);

                  
                    try
                    {
                        if (commands.IsMasterDetail && commands.RelationFieldValue == null)
                            {
                                object retValue = cmd.ExecuteScalar();

                                commands.RelationFieldValue = retValue;
                            }
                            else
                            {
                                cmd.ExecuteNonQuery();
                            }
                    }
                    catch(Exception ex)
                    {
                    //catch(SqlException)
                    //{
                        //if there'commands.RelationFieldName an error, roll everything back
                        trans.Rollback();

                        //throw the error retaining the stack.
                        throw;
                    }
                }
                //if we get to this point, we're good to go
                trans.Commit();
            }
        }