怎么同时往两条表中插入数据

[llingshui]

怎么同时往两条表中插入数据

我在添加一条数据(表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;
}

[nikolaichow]

直接对A表进行触发器不是更好吗？不要写程序了。

[thinkingforever]

可以用对插入表的insert 语句加入触发器；或者判断插入成功后在做修改操作（这个地方需要用事务）

[DENQH]

if (count>0)
{
//更新语句
}

[快溜]

你可以放在一个事务里，确保数据完整性。

[mohugomohu]

count = DBAccess.Instance.ExcuteNonequery(sqlproject, pList);
if (count>0)
{
//更新语句
}

[tajear]

进行A表操作，获取返回影响的行数，大于0的话就可以进行B表的数据操作——把这两和操作放在事务里执行

1、定义开始事务
2、执行A表操作(不成功回滚事务)
3、执行B表操作(不成功回滚事务)
4、提交事务
5、异常回滚事务
6、结束事务

[llingshui]

[Quote=引用 3 楼 denqh 的回复:]
if (count>0)
{
//更新语句
}
[/Quote]

执行数据库操作后，count的值必须为1，不如的话，虽然在数据库中，数据虽然改变了，但前台报错啊

[mjp1234airen4385]

不可能同时，只能是放在一个事务里了。

[sunny007sun]

给你个方法

   /// <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;
                    }
                }
            }
        }
    }

把你的SQL 语句组合成数组 ，然后传入这个方法中。

[1800包吃包住]

事务
触发器

[fgzangel]

用事务吧 我刚写过 这样保准点

[josephSC]

[Quote=引用 10 楼 chaichangaini 的回复:]

事务
触发器
[/Quote]是这样的