急待解决sql中的循环添加批量数据的存储过程怎么写

Question

是两表个的内容都要添加  ，而且第二个表是根据第一个id添加的  这有段  数据访问层的代码  ，  哪位大侠 觉简单的 帮小妹 完成下 ，我从昨天纠结到今天了，帮帮忙吧  拜托了 
     public static string AddAirInfos(AirInfoModel air)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = new SqlConnection(SqlDbHelper.connectionString);
            
            cmd.CommandType = CommandType.Text;
            try
            {
                cmd.Connection.Open();
                cmd.Transaction = cmd.Connection.BeginTransaction();                cmd.CommandText = "select CityName from City where AirportCode='" + air.FromCity + "'";
                string from = cmd.ExecuteScalar().ToString();                cmd.CommandText = "select CityName from City where AirportCode='" + air.ToCity + "'";
                string to = cmd.ExecuteScalar().ToString();                cmd.Parameters.Clear();                cmd.Parameters.Add("@FromCity", SqlDbType.VarChar).Value = from;
                cmd.Parameters.Add("@ToCity", SqlDbType.VarChar).Value = to;                cmd.Parameters.Add("@FlyDate", SqlDbType.DateTime).Value = air.FlyDate;
                cmd.Parameters.Add("@Mileage", SqlDbType.Int).Value = air.Mileage;                //decimal fulerax = 0;
                //if (air.Mileage < 800)
                //{
                //    fulerax = 20;
                //}
                //else
                //{
                //    fulerax = 40;
                //}                cmd.CommandText = "insert into AirInfo([FromCity],[ToCity],[FlyDate],[Mileage])values(@FromCity,@ToCity,@FlyDate,@Mileage)";
                cmd.ExecuteNonQuery();                cmd.CommandText = "SELECT @@IDENTITY";
                int airInfoID = int.Parse(cmd.ExecuteScalar().ToString());
                foreach (FlightInfoModel flight in air.Flights)
                {
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@AirInfoID", SqlDbType.Int).Value = airInfoID;
                    cmd.Parameters.Add("@IsStop", SqlDbType.Int).Value = flight.IsStop;
                    cmd.Parameters.Add("@AirCompany", SqlDbType.VarChar).Value = flight.AirCompany;
                    cmd.Parameters.Add("@LineNumber", SqlDbType.VarChar).Value = flight.LineNumber;
                    cmd.Parameters.Add("@PlantModel", SqlDbType.VarChar).Value = flight.PlantModel;
                    cmd.Parameters.Add("@FromName", SqlDbType.VarChar).Value = flight.FromName;
                    cmd.Parameters.Add("@ToName", SqlDbType.VarChar).Value = flight.ToName;
                    cmd.Parameters.Add("@FromCode", SqlDbType.VarChar).Value = flight.FromCode;
                    cmd.Parameters.Add("@ToCode", SqlDbType.VarChar).Value = flight.ToCode;
                    cmd.Parameters.Add("@FromTime", SqlDbType.VarChar).Value = flight.FromTime.Substring(0, 2) + ":" + flight.FromTime.Substring(2);
                    cmd.Parameters.Add("@ToTime", SqlDbType.VarChar).Value = flight.ToTime.Substring(0, 2) + ":" + flight.ToTime.Substring(2);
                    cmd.Parameters.Add("@TotalDuration", SqlDbType.Int).Value = flight.TotalDuration;
                    cmd.Parameters.Add("@Airrax", SqlDbType.Decimal).Value = flight.Airrax;
                    cmd.Parameters.Add("@Fulerax", SqlDbType.Decimal).Value = flight.Fulerax;                    cmd.CommandText = "insert into FlightInfo([AirInfoID],[IsStop],[AirCompany],[LineNumber],[PlantModel],[FromTime],[ToTime],[FromName],[ToName],[FromCode],[ToCode],[TotalDuration],[Airrax],[Fulerax])values(@AirInfoID,@IsStop,@AirCompany,@LineNumber,@PlantModel,@FromTime,@ToTime,@FromName,@ToName,@FromCode,@ToCode,@TotalDuration,@Airrax,@Fulerax)";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "SELECT @@IDENTITY";
                    int flightID = int.Parse(cmd.ExecuteScalar().ToString());
                    foreach (CabinModel cabin in flight.Cabins)
                    {
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@FlightInfoID", SqlDbType.Int).Value = flightID;
                        cmd.Parameters.Add("@CabinGrade", SqlDbType.Int).Value = cabin.CabinGrade;
                        cmd.Parameters.Add("@CabinCode", SqlDbType.VarChar).Value = cabin.CabinCode;
                        cmd.Parameters.Add("@Discount", SqlDbType.Decimal).Value = cabin.Discount;
                        cmd.Parameters.Add("@Price", SqlDbType.Decimal).Value = cabin.Price;
                        cmd.Parameters.Add("@Chdprice", SqlDbType.Decimal).Value = cabin.Chdprice;
                        cmd.Parameters.Add("@Remain", SqlDbType.VarChar).Value = cabin.Remain;
                        cmd.Parameters.Add("@Description", SqlDbType.Text).Value = cabin.Description;
                        cmd.Parameters.Add("@Point", SqlDbType.Decimal).Value = cabin.Point;
                        cmd.Parameters.Add("@Chdpoint", SqlDbType.Decimal).Value = cabin.Chdprice;
                        cmd.Parameters.Add("@PolicyId", SqlDbType.VarChar).Value = cabin.PolicyId.ToString();                        cmd.CommandText = "insert into Cabin([FlightInfoID],[CabinGrade],[CabinCode],[Discount],[Price],[Chdprice],[Remain],[Description],[Point],[Chdpoint],[PolicyId])values(@FlightInfoID,@CabinGrade,@CabinCode,@Discount,@Price,@Chdprice,@Remain,@Description,@Point,@Chdpoint,@PolicyId)";
                        cmd.ExecuteNonQuery();
                    }
                }                cmd.Transaction.Commit();
            }
            catch
            {
                cmd.Transaction.Rollback();
                throw;
            }
            finally
            {
                cmd.Connection.Close();
            }
            return "";
        }        public static void UpdateAirInfos()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = new SqlConnection(SqlDbHelper.connectionString);
            cmd.CommandType = CommandType.Text;
            try
            {
                cmd.Connection.Open();
                cmd.Transaction = cmd.Connection.BeginTransaction();                cmd.CommandText = "update Cabin set IsDelete=1";
                cmd.ExecuteNonQuery();                cmd.CommandText = "update FlightInfo set IsDelete=1";
                cmd.ExecuteNonQuery();                cmd.CommandText = "update AirInfo set IsDelete=1";
                cmd.ExecuteNonQuery();                cmd.Transaction.Commit();
            }
            catch
            {
                cmd.Transaction.Rollback();
                throw;
            }
            finally
            {
                cmd.Connection.Close();
            }
        }

Answer 1

cmd.CommandText = "select CityName from City where AirportCode='" + air.FromCity + "'";
  string from = cmd.ExecuteScalar().ToString();  cmd.CommandText = "select CityName from City where AirportCode='" + air.ToCity + "'";
  string to = cmd.ExecuteScalar().ToString();  cmd.Parameters.Clear();  cmd.Parameters.Add("@FromCity", SqlDbType.VarChar).Value = from;
  cmd.Parameters.Add("@ToCity", SqlDbType.VarChar).Value = to;  cmd.Parameters.Add("@FlyDate", SqlDbType.DateTime).Value = air.FlyDate;
  cmd.Parameters.Add("@Mileage", SqlDbType.Int).Value = air.Mileage;  //decimal fulerax = 0;
  //if (air.Mileage < 800)
  //{
  // fulerax = 20;
  //}
  //else
  //{
  // fulerax = 40;
  //}  cmd.CommandText = "insert into AirInfo([FromCity],[ToCity],[FlyDate],[Mileage])values(@FromCity,@ToCity,@FlyDate,@Mileage)";
  cmd.ExecuteNonQuery();  cmd.CommandText = "SELECT @@IDENTITY";
  int airInfoID = int.Parse(cmd.ExecuteScalar().ToString())
这一长串，可以写成一个sql执行那个id用输出参数获取

Answer 2

同理，中间那里也有同样的操作，也使用一个sql，然后用输出参数取值。。

Answer 3

cmd.CommandText = "SELECT @@IDENTITY";
改为
cmd.CommandText = "SELECT SCOPE_IDENTITY( )";