C# SQL数据级联插入

StringBuilder strSql = new StringBuilder();
strSql.Append("insert into article(");
strSql.Append("channel_id,call_index,title,content,status,add_time)");
strSql.Append(" values (");
strSql.Append("@channel_id,@call_index,@title,@content,@status,@add_time)");
strSql.Append(";set @article_id= @@IDENTITY");
SqlParameter[] parameters = {
new SqlParameter("@channel_id", SqlDbType.Int,4),
new SqlParameter("@call_index", SqlDbType.NVarChar,50),
new SqlParameter("@title", SqlDbType.NVarChar,100),
new SqlParameter("@content", SqlDbType.NText),
new SqlParameter("@status", SqlDbType.TinyInt,1),
new SqlParameter("@add_time", SqlDbType.DateTime),
new SqlParameter("@article_id",SqlDbType.Int)};
parameters[0].Value = model.channel_id;
parameters[1].Value = model.call_index;
parameters[2].Value = model.title;
parameters[3].Value = model.content;
parameters[4].Value = model.status;
parameters[5].Value = model.add_time;
parameters[6].Direction = ParameterDirection.Output;

List<CommandInfo> sqllist = new List<CommandInfo>();
CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
sqllist.Add(cmd);

//文章附件
if (model.attach != null)
{
StringBuilder strSql4;
foreach (Model.article_attach modelt in model.attach)
{
strSql4 = new StringBuilder();
strSql4.Append("insert into article_attach(");
strSql4.Append("article_id,file_name,file_path)");
strSql4.Append(" values (");
strSql4.Append("@article_id,@file_name,@file_path)");
SqlParameter[] parameters4 = {
new SqlParameter("@article_id", SqlDbType.Int,4),
new SqlParameter("@file_name", SqlDbType.NVarChar,100),
new SqlParameter("@file_path", SqlDbType.NVarChar,255)
};
parameters4[0].Direction = ParameterDirection.InputOutput;
parameters4[1].Value = modelt.file_name;
parameters4[2].Value = modelt.file_path;
cmd = new CommandInfo(strSql4.ToString(), parameters4);
sqllist.Add(cmd);
}

DbHelperSQL.ExecuteSqlTranWithIndentity(sqllist);
return (int)parameters[6].Value;

 

执行

/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List<CommandInfo> SQLStringList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
try
{
int indentity = 0;
//循环
foreach (CommandInfo myDE in SQLStringList)
{
string cmdText = myDE.CommandText;
SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
foreach (SqlParameter q in cmdParms)
{
if (q.Direction == ParameterDirection.InputOutput)
{
q.Value = indentity;
}
}
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
foreach (SqlParameter q in cmdParms)
{
if (q.Direction == ParameterDirection.Output)
{
indentity = Convert.ToInt32(q.Value);
}
}
cmd.Parameters.Clear();
}
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
}
}

 

 

 

private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;//cmdType;
if (cmdParms != null)
{

foreach (SqlParameter parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}