急待解决sql中的循环添加批量数据的存储过程怎么写
是两表个的内容都要添加 ，而且第二个表是根据第一个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();
}
} --------------------编程问答-------------------- 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用输出参数获取。。。
--------------------编程问答-------------------- 同理，中间那里也有同样的操作，也使用一个sql，然后用输出参数取值。。 --------------------编程问答-------------------- 我是非常迷茫呀 ，这个方法我都不是很明白是什么意思 写成存储过程 我更加不知所措 ，希望好心人帮帮忙 ，会写的耽误以下宝贵的时间帮忙写一下，有机会请你吃饭 ！！ --------------------编程问答-------------------- cmd.CommandText = "SELECT @@IDENTITY";
改为
cmd.CommandText = "SELECT SCOPE_IDENTITY( )";
补充：.NET技术 , ASP.NET
