在数据库中,经验有两个表关联的情况,也就是一个表中的主键为另一个表的外键,在ADO.NET时,如果向两个表中同时添中数据,我们通常做三步SQL操作,第一步:添加主表数据,第二步:得到主表中的主键,第三步:添加子表数据,在通常状况下,这些都是在事务中。
先看一下数据库表结构。一个表为父表(这里为了说明技术,表没有具体意义),名字为FatherTable如下:
还有一个子表,名字为SonTable如下:
两个表之间有下图这样一个关系:
 
先看一下ADO.NET如何实现:
class TestDemo
    {
        string constr = "Data Source=.;Initial Catalog=MyTestDB;Persist Security Info=True;User ID=sa;Password=sa";
        public static void Main()
        {
            TestDemo TD = new TestDemo();
            SqlConnection con = new SqlConnection(TD.constr);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            SqlTransaction tran = null;
            try
            {
                con.Open();
                tran = con.BeginTransaction();
                cmd.Transaction = tran;
 
                cmd.CommandText = "insert into fathertable([name]) values(@name)";
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = "李四";
                cmd.ExecuteNonQuery();
 
                cmd.CommandText = "select @@identity";
                int id = Convert.ToInt32(cmd.ExecuteScalar());
 
                cmd.CommandText = "insert into sontable(fid,names) values(@id,@names)";
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
                cmd.Parameters.Add("@names", SqlDbType.VarChar).Value = "李四A";
                cmd.ExecuteNonQuery();
 
                tran.Commit();
            }
            catch (Exception exc)
            {
                Console.WriteLine(exc.Message);
                tran.Rollback();
            }
            finally
           {
                con.Close();
            }
        }
}
ADO.NET的事务没有什么好说的,现在来看看用Linq怎么实现,首先添加一个“LINQ to SQL类”,在“服务器资源管理器”中连接数据库,然后选中FatherTable和SonTable表,如下图
接下来,编程来实现添加操作,代码如下:
class TestDemo
    {
        string constr = "Data Source=.;Initial Catalog=MyTestDB;Persist Security Info=True;User ID=sa;Password=sa";
        public static void Main()
        {
            TestDemo td=new TestDemo ();
            DataClasses1DataContext DCDC = new DataClasses1DataContext(td.constr);
 
            FatherTable ft = new FatherTable();         
            ft.name = "王五";
 
            SonTable st1 = new SonTable();
            st1.names = "王五A";
            ft.SonTable.Add(st1);
            SonTable st2 = new SonTable();
            st2.names = "王五B";
            ft.SonTable.Add(st2);
 
            DCDC.FatherTable.InsertOnSubmit(ft);
            DCDC.SubmitChanges();
        }
    }
运后的结果是一样的。用ADO.NET,我们自己写的SQL语句,清楚我们对数据执行了什么操作,上面的用Linq to Sql做的话,就不知道系统执行了什么操作。我们看见系统调用了DataClasses1DataContext类的SubmitChanges,这个方法在DataClasses1DataContext找不到,它是从DataContext继承下来的。我们用工具Reflector查看,SubmitChanges是调用有参的SubmitChanges(ConflictMode),这个方法代码如下:
public virtual void SubmitChanges(ConflictMode failureMode)
{
    this.CheckDispose();
    this.VerifyTrackingEnabled();
    this.conflicts.Clear();
    try
    {
        this.isInSubmitChanges = true;
        if ((Transaction.Current == null) && (this.provider.Transaction == null))
        {
            bool flag = false;
            DbTransaction transaction = null;
            try
            {
                try
                {
                    if (this.provider.Connection.State == ConnectionState.Open)
                    {
                        this.provider.ClearConnection();
                    }
                    if (this.provider.Connection.State == ConnectionState.Closed)
                    {
                        this.provider.Connection.Open();
                        flag = true;
                    }
                    transaction = this.provider.Connection.BeginTransaction(IsolationLevel.ReadCommitted);
                    this.provider.Transaction = transaction;
                    new ChangeProcessor(this.services, this).SubmitChanges(failureMode);
                    this.AcceptChanges();
                    this.provider.ClearConnection();
                    transaction.Commit();
                }
                catch
                {
                    if (transaction != null)
                    {
                        try
                        {
                            transaction.Rollback();
                        }
                        catch
                        {
                        }
                    }
                    throw;
                }
                return;
            }
            finally
            {
                this.provider.Transaction = null;
                if (flag)
                {
                    this.provider.Connection.Close();
                }
            }
        }
        new ChangeProcessor(this.services, this).SubmitChanges(failureMode);
        this.AcceptChanges();
    }
    finally
    {
        this.isInSubmitChanges = false;
    }
}
我们清楚的看到,这里面启用了事务,与我们ADO.NET的做法是一样的。事务有了,SQL语句怎么样?继续查找SubmitChanges方法,代码如下:
internal void SubmitChanges(ConflictMode failureMode)
{
    this.TrackUntrackedObjects();
    this.ApplyInferredDeletions();
    this.BuildEdgeMaps();
    int totalUpdatesAttempted = 0;
    foreach (TrackedObject obj2 in orderedList)
    {
        try
        {
            if (obj2.IsNew)
            {
                obj2.SynchDependentData();
                this.changeDirector.Insert(obj2);
            }
            else if (obj2.IsDeleted)
            {
                totalUpdatesAttempted++;
                if (this.changeDirector.Delete(obj2) == 0)
                {
                }
                else
                {
                }
            }
            else if (obj2.IsPossiblyModified)
            {
                obj2.SynchDependentData();
                if (obj2.IsModified)
                {
                    CheckForInvalidChanges(obj2);
                    totalUpdatesAttempted++;
                    if (this.changeDirector.Update(obj2) <= 0)
                    {
                    }
                }
            }
        }
        catch (ChangeConflictException)
        {
        }
        {
            break;
        }
    }
    {
    }
}
如果继续下付出,会发现查找许多类,能隐约看出系统在组织sql语句。
与其在这里找,不如到SQL里等它生成的SQL语句看看。
打开SQL的跟踪工具,运行结果如下图:
起作用的是ApplicationName为.NET SqlClient Data Provider的四五列,第一列为登录数连库,最后注销数据库,语句为中间三列,先看第一列,语句如下:
exec sp_executesql N'INSERT INTO [dbo].[FatherTable]([name])
VALUES (@p0)
SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]',N'@p0 varchar(4)',@p0='王五'
执行了两个操作,一个添中,一个查询,查询的是添加后自动生成的ID值,另外两个语句如出一辙,单从SQL语句的角度看,多了两个查询,这在一定程度上没有我们自己写的SQL语句简单,多了子表中的一个查询。