怎么同时往两条表中插入数据
怎么同时往两条表中插入数据
我在添加一条数据(表A)的时候，如果操作成功的话，将表B中的某条数据进行修改
public static bool InsertProject(NameValueCollection nvc, string userID, string userName, string departmentID, string departName)
{
int count = 0;
string isloan = "";
string loanid = "";
string type = nvc["expenseType"];
if (nvc["loanid"] == null) { loanid = "0"; isloan = "否"; } else { loanid = nvc["loanid"]; isloan = "是";}
string sqlLoan = "update tb_loanBill set ispayment=:ispayment where loanid in" + '(' + (loanid) + ')';
string sqlproject = "insert into tb_expenseaccount(expenseid,expensename,departmentid,departmentname,expenseuid,expenseuname,bankrollpurpose,expensestate,expensedate,expensetype,promanagername,amountofmoney,isloan,loanid,fangzu,phone,moble,chailv,yan,jiu,lipin,yunfei,cailiao,buchang,oil,street,parking,fix,shenghuo,bangong,qita) "
+ "values(:expenseid,:expensename,:departmentid,:departmentname,:expenseuid,:expenseuname,:bankrollpurpose,:expensestate,:expensedate,:expensetype,:promanagername,:amountofmoney,:isloan,:loanid,:fangzu,:phone,:moble,:chailv,:yan,:jiu,:lipin,:yunfei,:cailiao,:buchang,:oil,:street,:parking,:fix,:shenghuo,:bangong,:qita)";
string strProject = "";
if (!string.IsNullOrEmpty(nvc["txt_buchang"])) { strProject = "补偿:" + nvc["txt_buchang"] + '；'; }
if (nvc["txt_fangzu"] != "") { strProject += "房租费:" + nvc["txt_fangzu"] + '；'; }
if (nvc["txt_dianhua"] != "") { strProject += "电话费:" + nvc["txt_dianhua"] + '；'; }
if (nvc["txt_chailv"] != "") { strProject += "差旅费:" + nvc["txt_chailv"] + '；'; }
if (nvc["txt_zhaodai"] != "") { strProject += "招待费:" + nvc["txt_zhaodai"] + '；'; }
if (nvc["txt_lipin"] != "") { strProject += "礼品:" + nvc["txt_lipin"] + '；'; }
if (nvc["txt_yunfei"] != "") { strProject += "运费:" + nvc["txt_yunfei"] + '；'; }
if (nvc["txt_cailiao"] != "") { strProject += "材料费:" + nvc["txt_cailiao"] + '；'; }
if (nvc["txt_qiche"] != "") { strProject += "汽车费:" + nvc["txt_qiche"] + '；'; }
if (nvc["txt_shenghuo"] != "") { strProject += "生活用品:" + nvc["txt_shenghuo"] + '；'; }
if (nvc["txt_bangong"] != "") { strProject += "办公用品:" + nvc["txt_bangong"] + '；'; }
if (nvc["txt_qita"] != "") { strProject += nvc["txt_qita"] + '。'; }
DataParameter[] pList = new DataParameter[]
{
new DataParameter("expenseid",DbType.Int32,18,Common.Common.GetDBpk("tb_expenseaccount")),
new DataParameter("expensename",DbType.String,50,Common.Common.ParaDecode(nvc["expenseName"])),
new DataParameter("departmentid",DbType.Int32,18,departmentID),
new DataParameter("departmentname",DbType.String,18,departName),
new DataParameter("expenseuid",DbType.Int32,18,userID),
new DataParameter("expenseuname",DbType.String,18,userName),
new DataParameter("expensestate",DbType.String,10,"未评审"),
new DataParameter("expensetype",DbType.String,12,type),
new DataParameter("expensedate",DbType.Date,18,DateTime.Now.ToString("yyyy-MM-dd")),
new DataParameter("bankrollpurpose",DbType.String,400,strProject),
new DataParameter("promanagername",DbType.String,18,Common.Common.ParaDecode(nvc["reviewperson"])),
new DataParameter("amountofmoney",DbType.String,12,Common.Common.ParaDecode(nvc["amountofmoney"])),
new DataParameter("isloan",DbType.String,10,isloan),
new DataParameter("loanid",DbType.String,18,loanid),
new DataParameter("fangzu",DbType.String,50,Common.Common.ParaDecode(nvc["txt_fangzu"])),
new DataParameter("phone",DbType.String,50,Common.Common.ParaDecode(nvc["phone"])),
new DataParameter("moble",DbType.String,50,Common.Common.ParaDecode(nvc["moble"])),
new DataParameter("chailv",DbType.String,50,Common.Common.ParaDecode(nvc["txt_chailv"])),
new DataParameter("yan",DbType.String,50,Common.Common.ParaDecode(nvc["yan"])),
new DataParameter("jiu",DbType.String,50,Common.Common.ParaDecode(nvc["jiu"])),
new DataParameter("lipin",DbType.String,50,Common.Common.ParaDecode(nvc["txt_lipin"])),
new DataParameter("yunfei",DbType.String,50,Common.Common.ParaDecode(nvc["txt_yunfei"])),
new DataParameter("cailiao",DbType.String,50,Common.Common.ParaDecode(nvc["txt_cailiao"])),
new DataParameter("buchang",DbType.String,50,Common.Common.ParaDecode(nvc["txt_buchang"])),
new DataParameter("oil",DbType.String,50,Common.Common.ParaDecode(nvc["oil"])),
new DataParameter("street",DbType.String,50,Common.Common.ParaDecode(nvc["street"])),
new DataParameter("parking",DbType.String,50,Common.Common.ParaDecode(nvc["parking"])),
new DataParameter("fix",DbType.String,50,Common.Common.ParaDecode(nvc["fix"])),
new DataParameter("shenghuo",DbType.String,50,Common.Common.ParaDecode(nvc["txt_shenghuo"])),
new DataParameter("bangong",DbType.String,50,Common.Common.ParaDecode(nvc["txt_bangong"])),
new DataParameter("qita",DbType.String,50,Common.Common.ParaDecode(nvc["txt_qita"])),
};
//DataParameter[] plist = new DataParameter[]
//{
// new DataParameter("ispayment",DbType.String,10,"已报账"),
//};
try
{
count = DBAccess.Instance.ExcuteNonequery(sqlproject, pList);
//count = DBAccess.Instance.ExcuteNonequery(sqlLoan, plist);
}
catch
{
throw;
}
}
return count.Equals(1) ? true : false;
}
[解决办法]
count = DBAccess.Instance.ExcuteNonequery(sqlproject, pList);
if (count>0)
{
//更新语句
}
[解决办法]
给你个方法
- C# code
/// <summary> /// 事务处理 执行sql语句返回影响行数 /// </summary> /// <param name="StrSql"></param> /// <returns></returns> public int execTransactionInt(string[] StrSql) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand()) { SqlTransaction sqlTransaction = null; try { connection.Open(); cmd.Connection = connection; sqlTransaction = connection.BeginTransaction(IsolationLevel.ReadCommitted); cmd.Transaction = sqlTransaction; int len = StrSql.Length; int affectRows = 0; for (int i = 0; i < len; i++) { cmd.CommandText = StrSql[i]; affectRows += cmd.ExecuteNonQuery(); } sqlTransaction.Commit(); cmd.Parameters.Clear(); connection.Close(); return affectRows; } catch (System.Data.SqlClient.SqlException e) { sqlTransaction.Rollback(); connection.Close(); return -1; } } } } }