如何正确地同时更新数据库中的两个表？

Question

场景：

我使用的是visual studio 2010，我正在制作一个数据库示例应用程序。我得把这些光谱文件存储在数据库里。这些光谱文件被加载到spectradata对象中。我计划将这些对象存储在数据库中。这些对象有许多不同的属性(int、double、string等)。此外，该对象还包括y分量数据值的数组。

我将我的数据库设计为由两个表组成(一个表用于spectradata对象属性，另一个表用于双数组点)。我创建了第一个具有ID自动增量的表。

​

下面是我放在一起的一些代码，这些代码基本上是获取这些对象值并将它们赋给表参数。

SPCFile spc = new SPCFile();
TSpectraData spectra = new TSpectraData();
spectra = spc.LoadSPC(openSPCFile.FileName);

using(SqlConnection mySqlConnect = new SqlConnection(ConfigurationManager.ConnectionStrings["DBLocal"].ConnectionString))
{
      SqlCommand command = mySqlConnect.CreateCommand();
      command.CommandText = "INSERT INTO Spectras VALUES"
                                     + "(@DateTime, "
                                     + "@Name, "
                                     + "@Version, "
                                     + "@SerialHighNumber, "
                                     + "@SerialLowNumber, "
                                     + "@Completed, "
                                     + "@SpectrometerID, "
                                     + "@GasCellID, "
                                     + "@Format, "
                                     + "@Apodization, "
                                     + "@PhaseApodization, "
                                     + "@Temperature, "
                                     + "@Pressure, "
                                     + "@NumScans, "
                                     + "@Resolution, "
                                     + "@Gain, "
                                     + "@PathLength, "
                                     + "@FirstPoint, "
                                     + "@LastPoint, "
                                     + "@MaxFrequency, "
                                     + "@MaxLocPoint, "
                                     + "@MinLocPoint, "
                                     + "@NumDataPoints, "
                                     + "@NumDataPhase, "
                                     + "@Step, "
                                     + "@IgramType)";

                //Adding parameters to command
                //Value for ID will be added automatically since it is set to auto increment

                command.Parameters.Add("@DateTime",         SqlDbType.DateTime, Int32.MaxValue);
                command.Parameters.Add("@Name",             SqlDbType.NVarChar, Int32.MaxValue);
                command.Parameters.Add("@Version",          SqlDbType.Int, Int32.MaxValue);
                command.Parameters.Add("@SerialHighNumber", SqlDbType.Int, Int32.MaxValue);
                command.Parameters.Add("@SerialLowNumber",  SqlDbType.Float, Int32.MaxValue);
                command.Parameters.Add("@Completed",        SqlDbType.Float, Int32.MaxValue);
                command.Parameters.Add("@SpectrometerID",   SqlDbType.Int, Int32.MaxValue);
                command.Parameters.Add("@GasCellID",        SqlDbType.Int, Int32.MaxValue);
                command.Parameters.Add("@Format",           SqlDbType.SmallInt, Int32.MaxValue);
                command.Parameters.Add("@Apodization",      SqlDbType.SmallInt, Int32.MaxValue);
                command.Parameters.Add("@PhaseApodization", SqlDbType.SmallInt, Int32.MaxValue);
                command.Parameters.Add("@Temperature",      SqlDbType.Float, Int32.MaxValue);
                command.Parameters.Add("@Pressure",         SqlDbType.Float, Int32.MaxValue);
                command.Parameters.Add("@NumScans",         SqlDbType.Int, Int32.MaxValue);
                command.Parameters.Add("@Resolution",       SqlDbType.Float, Int32.MaxValue);
                command.Parameters.Add("@Gain",             SqlDbType.Float, Int32.MaxValue);
                command.Parameters.Add("@PathLength",       SqlDbType.Float, Int32.MaxValue);
                command.Parameters.Add("@FirstPoint",       SqlDbType.Float, Int32.MaxValue);
                command.Parameters.Add("@LastPoint",        SqlDbType.Float, Int32.MaxValue);
                command.Parameters.Add("@MaxFrequency",     SqlDbType.Float, Int32.MaxValue);
                command.Parameters.Add("@MaxLocPoint",      SqlDbType.Int, Int32.MaxValue);
                command.Parameters.Add("@MinLocPoint",      SqlDbType.Int, Int32.MaxValue);
                command.Parameters.Add("@NumDataPoints",    SqlDbType.Int, Int32.MaxValue);
                command.Parameters.Add("@NumDataPhase",     SqlDbType.Int, Int32.MaxValue);
                command.Parameters.Add("@Step",             SqlDbType.Float, Int32.MaxValue);
                command.Parameters.Add("@IgramType",        SqlDbType.SmallInt, Int32.MaxValue);
                //Adding values to these parameters
                command.Parameters["@DateTime"].Value = DateTime.Now;
                command.Parameters["@Name"].Value = spectra.Info.Name;
                command.Parameters["@Version"].Value = spectra.Info.Version;
                command.Parameters["@SerialHighNumber"].Value = spectra.Info.SerialHighNumber;
                command.Parameters["@SerialLowNumber"].Value = spectra.Info.SerialLowNumber;
                command.Parameters["@Completed"].Value = spectra.Info.Completed;
                command.Parameters["@SpectrometerID"].Value = spectra.Info.SpectrometerID;
                command.Parameters["@GasCellID"].Value = spectra.Info.GasCellID;
                command.Parameters["@Format"].Value = (short)spectra.Info.Format;
                command.Parameters["@Apodization"].Value = (short)spectra.Info.Apodization;
                command.Parameters["@PhaseApodization"].Value = (short)spectra.Info.PhaseApodization;
                command.Parameters["@Temperature"].Value = spectra.Info.Temperature;
                command.Parameters["@Pressure"].Value = spectra.Info.Pressure;
                command.Parameters["@NumScans"].Value = spectra.Info.NumScans;
                command.Parameters["@Resolution"].Value = spectra.Info.Resolution;
                command.Parameters["@Gain"].Value = spectra.Info.Gain;
                command.Parameters["@PathLength"].Value = spectra.Info.PathLength;
                command.Parameters["@FirstPoint"].Value = spectra.Info.FirstPoint;
                command.Parameters["@LastPoint"].Value = spectra.Info.LastPoint;
                command.Parameters["@MaxFrequency"].Value = spectra.Info.MaxFrequency;
                command.Parameters["@MaxLocPoint"].Value = spectra.Info.MaxLocPoint;
                command.Parameters["@MinLocPoint"].Value = spectra.Info.MinLocPoint;
                command.Parameters["@NumDataPoints"].Value = spectra.Info.NumDataPoints;
                command.Parameters["@NumDataPhase"].Value = spectra.Info.NumDataPhase;
                command.Parameters["@Step"].Value = spectra.Info.Step;
                command.Parameters["@IgramType"].Value = (short)spectra.Info.IgramType;
                mySqlConnect.Open();
                command.ExecuteNonQuery();
                fileChosen = false;
                //Don't have to call connection close due tp using statment
                label1.Hide();
            }

因为ID是自动递增的，所以我不需要在VALUES语句中包含它。我的流程的下一步是添加到数据点表中。我计划重用命令对象并重新分配命令文本。我的问题是，如何获得这个数据点数组并在另一个表中分配它们，同时使两个表中的ID保持一致。我假设，关系基本上是说“如果主键和外键匹配，则它们是相关的”。

Answer 1

我在这里假设了很多事情，但是，我希望给出这个伪代码。

首先，可以使用查询SELECT SCOPE_IDENTITY()检索连接上最后插入的自动增量编号(IDENTITY)。

其次，知道了这个数字，您可以很容易地准备一个循环来插入数据点，其中的参数是用循环外部的虚拟值创建的，并替换为数据点上循环内的有效值。

事务应该是一个好处，因为如果在数据点上循环时出现错误，您不需要清除已经插入的数据

// Start a TransactionScope to handle all/or/nothing scenario
using(TransactionScope scope = new TransactionScope())
{
    using(SqlConnection mySqlConnect = new SqlConnection(ConfigurationManager.ConnectionStrings["DBLocal"].ConnectionString))
    {
          SqlCommand command = mySqlConnect.CreateCommand();

          // Append the SELECT SCOPE_IDENTITY as a second command on this text.....
          command.CommandText = "INSERT INTO Spectras VALUES"
                                         + "(......)"
                                         + ";SELECT SCOPE_IDENTITY();"
          mySqlConnect.Open();

          // HERE YOU EXECUTE THE COMMAND AND GET BACK THE LAST IDENTITY USED 
          SqlDataReader reader = command.ExecuteReader();
          if (reader.HasRows)
          {
              reader.Read();
              int spectraID = Convert.ToInt32(reader[0]);
              // Ready to start the insert of points

              SqlCommand cmdPoints = new SqlCommand("INSERT INTO DataPoints " + 
                                      "VALUES (@ID, @IDX, @VAL)", mySqlConnect);
              cmdPoint.Parameters.AddWithValue("@ID",spectraID);
              cmdPoint.Parameters.AddWithValue("@IDX",0);
              cmdPoint.Parameters.AddWithValue("@VAL",0);

              foreach(DataPoint dp in DataPoints)
              {
                  cmdPoint.Parameters["@IDX"].Value = dp.Index);
                  cmdPoint.Parameters["@VAL"].Value = dp.Value);
                  cmdPoint.ExecuteNonQuery();
              }
        }      
    }
    scope.Complete(); // This will write everything on the database

}

如果由于异常而退出，作用域将不会完成，整个记录集将被丢弃

注意:不要在INSERT INTO命令中指定列名。这意味着框架将尝试插入每一列，包括ID (标识)和第一个参数的值(日期时间？)。您应该按照传递参数时的确切顺序插入字段的名称。

command.CommandText = "INSERT INTO Spectras " 
                      + "(Name, Version, SerialHighNumber, .......)"
                      + "VALUES (@Name, @Version, @SerialHighNumber, ......)"
                      + ";SELECT SCOPE_IDENTITY();"