同时对两张表执行update和insert

Question

 cn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["cn"]);
                        cn.Open();
                       
                        SqlCommand cmd = new SqlCommand("UPDATE book SET T1= '" + Label1.Text.Replace("'", "''") + "'+'" + "/" + "'+'" + DropDownList3.Text + "'+'" + "/" + "'+'" + DropDownList1.Text + "'+'" + "-" + "'+'" + DropDownList2.Text + "'+'" + "/" + "'+ '" + TextBox4.Text.Replace("'", "''") + "'+'" + "/" + "'+'" + TextBox3.Text.Replace("'", "''") + "'+'" + "/" + "'+'" + Labelp.Text.Replace("'", "''") + "'+'" + "/" + "'+'" + Labelv.Text.Replace("'", "''") + "'+'" + "/" + "'+'" + TextBox5.Text.Replace("'", "''") + "'  where booktime= '" + TextBox1.Text.Replace("'", "''") + "'", cn);
                        cmd.ExecuteNonQuery();
                        cn.Close();
                        Response.Write("<script>alert('预定成功')</script>");
                        string strm1 = "server=po-pc;database=aspnet;uid='sa';pwd='123'";
                        SqlConnection con = new SqlConnection(strm1); //创建连接对象
                        con.Open();
                        SqlCommand cmdm1 = new SqlCommand("insert into details (booktime,bookname) value ('" + TextBox1.Text + "', '" + Label1.Text.Replace("'", "''") + "')", con);
                        cmdm1.ExecuteNonQuery(); //执行SQL命令
                        con.Close();
对booktime表执行update,对detail表执行insert，但是update执行了，insert没有执行

Answer 1

可以在代码里面写个事务。
conn.BeginTransaction();//开启事务
tr.Commit();//提交事务
tr.Rollback();//回滚事务

Answer 2

参考：
SqlConnection con = new SqlConnection();
                con.ConnectionString = "xxx";//xxx连接字符串
                con.Open();
                SqlTransaction st = con.BeginTransaction();
                try
                {
                    string sql1 = "insert into ...";
                    string sql2 = "update ....";
                    string[] sqls = new string[] { sql1, sql2 };                    foreach (string item in sqls)
                    {
                        SqlCommand cmd = new SqlCommand(sql1, con);
                        cmd.ExecuteNonQuery();
                    }
                    st.Commit();
                }
                catch
                {
                    st.Rollback();
                }

Answer 3

参考：
SqlConnection con = new SqlConnection();
                con.ConnectionString = "xxx";//xxx连接字符串
                con.Open();
                SqlTransaction st = con.BeginTransaction();
                try
                {
                    string sql1 = "insert into ...";
                    string sql2 = "update ....";
                    string[] sqls = new string[] { sql1, sql2 };                    foreach (string item in sqls)
                    {
                        SqlCommand cmd = new SqlCommand(sql1, con);
                        cmd.ExecuteNonQuery();
                    }
                    st.Commit();
                }
                catch
                {
                    st.Rollback();
                }
          SqlConnection con = new SqlConnection();
                        con.ConnectionString = "Data Source=po-pc;Initial Catalog=aspnetdb;User ID=sa;Password=123";//xxx连接字符串
                        con.Open();
                        SqlTransaction st = con.BeginTransaction();
                        try
                        {
                            string sqlm1 = "insert into details (booktime,bookname) value ('" + TextBox1.Text + "', '" + Label1.Text.Replace("'", "''") + "')";
                            string sqlm2 = "UPDATE book SET T1= '" + Label1.Text.Replace("'", "''") + "'+'" + "/" + "'+'" + DropDownList3.Text + "'+'" + "/" + "'+'" + DropDownList1.Text + "'+'" + "-" + "'+'" + DropDownList2.Text + "'+'" + "/" + "'+ '" + TextBox4.Text.Replace("'", "''") + "'+'" + "/" + "'+'" + TextBox3.Text.Replace("'", "''") + "'+'" + "/" + "'+'" + Labelp.Text.Replace("'", "''") + "'+'" + "/" + "'+'" + Labelv.Text.Replace("'", "''") + "'+'" + "/" + "'+'" + TextBox5.Text.Replace("'", "''") + "'  where booktime= '" + TextBox1.Text.Replace("'", "''") + "'";
                            string[] sqls = new string[] { sqlm1, sqlm2 };                            foreach (string item in sqls)
                            {
                                SqlCommand cmdm1 = new SqlCommand(sqlm1, con);
                                cmdm1.ExecuteNonQuery();
                            }
                            st.Commit();                            Response.Write("<script>alert('预定成功')</script>");
                        }
                        catch
                        {
                            st.Rollback();
                        }
                      
                        con.Close(); 改成这样还是不行

Answer 4

command.Transaction = transaction;//sorry，少一个这个
具体你参考一下：http://www.cnblogs.com/yank/archive/2008/07/20/1246896.html

Answer 5

参考：
SqlConnection con = new SqlConnection();
                con.ConnectionString = "xxx";//xxx连接字符串
                con.Open();
                SqlTransaction st = con.BeginTransaction();
                try
                {
                    string sql1 = "insert into ...";
                    string sql2 = "update ....";
                    string[] sqls = new string[] { sql1, sql2 };                    foreach (string item in sqls)
                    {
                        SqlCommand cmd = new SqlCommand(sql1, con);
                        cmd.ExecuteNonQuery();
                    }
                    st.Commit();
                }
                catch
                {
                    st.Rollback();
                }
          SqlConnection con = new SqlConnection();
                        con.ConnectionString = "Data Source=po-pc;Initial Catalog=aspnetdb;User ID=sa;Password=123";//xxx连接字符串
                        con.Open();
                        SqlTransaction st = con.BeginTransaction();
                        try
                        {
                            string sqlm1 = "insert into details (booktime,bookname) value ('" + TextBox1.Text + "', '" + Label1.Text.Replace("'", "''") + "')";
                            string sqlm2 = "UPDATE book SET T1= '" + Label1.Text.Replace("'", "''") + "'+'" + "/" + "'+'" + DropDownList3.Text + "'+'" + "/" + "'+'" + DropDownList1.Text + "'+'" + "-" + "'+'" + DropDownList2.Text + "'+'" + "/" + "'+ '" + TextBox4.Text.Replace("'", "''") + "'+'" + "/" + "'+'" + TextBox3.Text.Replace("'", "''") + "'+'" + "/" + "'+'" + Labelp.Text.Replace("'", "''") + "'+'" + "/" + "'+'" + Labelv.Text.Replace("'", "''") + "'+'" + "/" + "'+'" + TextBox5.Text.Replace("'", "''") + "'  where booktime= '" + TextBox1.Text.Replace("'", "''") + "'";
                            string[] sqls = new string[] { sqlm1, sqlm2 };                            foreach (string item in sqls)
                            {
                                SqlCommand cmdm1 = new SqlCommand(sqlm1, con);
                                cmdm1.ExecuteNonQuery();
                            }
                            st.Commit();                            Response.Write("<script>alert('预定成功')</script>");
                        }
                        catch
                        {
                            st.Rollback();
                        }
                      
                        con.Close(); 改成这样还是不行try里面的没有执行，catch的执行了

Answer 6

 foreach (string item in sqls)
                            {
                                SqlCommand cmdm1 = new SqlCommand(item, con);//这里应该是item吧
                                cmdm1.ExecuteNonQuery();
                            }

Answer 7

     DbTransaction tra = null;
                 DbConnection conn = dbHelper.CreateConnection();
                 conn.Open();
                 try
                 {
                 
                     tra = dbHelper.CreateDbTransaction(conn);
                     int result = 0;
                     result = dbHelper.ExecuteNonQuery(tra, CommandType.StoredProcedure, "baby_condom_copy_updateById", list.ToArray());
                     baby_condom_user_log logModel = new baby_condom_user_log();
                     if (model.state == 3)
                     {
                         logModel.operating = baby_condom_user_log.managerMessage.审核经纬度数据不通过_baby_condom_copy.ToString();
                         logModel.operatingId = model.baby_condomID;
                         logModel.userLoginId = model.auditor;
                         #region 添加日志
                         if (new baby_condom_user_logDAL().add(tra, logModel) > 0)
                         {
                             tra.Commit(); conn.Close();
                             return result;
                         }
                         #endregion
                     }
                     else
                     {
                         result = 0;
                         baby_condom oldModel = new baby_condom();
                         #region 处理操作
                         if (model.baby_condomID != null && model.baby_condomID > 0)
                         {
                             oldModel.c_id = model.baby_condomID;
                             if (!string.IsNullOrEmpty(model.c_title))
                             {
                                 oldModel.c_title = model.c_title;
                             }                             if (!string.IsNullOrEmpty(model.c_detailname))
                             {
                                 oldModel.c_detailname = model.c_detailname;
                             }
                             if (!string.IsNullOrEmpty(model.c_addre))
                             {
                                 oldModel.c_addre = model.c_addre;
                             }                             if (!string.IsNullOrEmpty(model.c_phone))
                             {
                                 oldModel.c_phone = model.c_phone;
                             }                             if (!string.IsNullOrEmpty(model.c_server))
                             {
                                 oldModel.c_server = model.c_server;
                             }                             if (!string.IsNullOrEmpty(model.c_type))
                             {
                                 oldModel.c_type = model.c_type;
                             }
                             if (!string.IsNullOrEmpty(model.longitude_baidu))
                             {
                                 oldModel.longitude_baidu = model.longitude_baidu;
                             }                             if (!string.IsNullOrEmpty(model.latitude_baidu))
                             {
                                 oldModel.latitude_baidu = model.latitude_baidu;
                             }                             if (!string.IsNullOrEmpty(model.c_time))
                             {
                                 oldModel.c_time = model.c_time;
                             }
                             if (!string.IsNullOrEmpty(model.location))
                             {
                                 oldModel.location = model.location;
                             }                             if (!string.IsNullOrEmpty(model.cname))
                             {
                                 oldModel.cname = model.cname;
                             }
                             result = new baby_condomDAL().UpdateModel(tra, oldModel);
                         }
                         #endregion
                             logModel.operating = baby_condom_user_log.managerMessage.审核经纬度数据通过_baby_condom_copy.ToString();
                             logModel.operatingId = model.baby_condomID;
                             logModel.userLoginId = model.auditor;
                             #region 添加日志
                             if (new baby_condom_user_logDAL().add(tra, logModel) > 0)
                             {
                                 tra.Commit(); conn.Close();
                                 return result;
                             }
                             #endregion
                     }
                 }
                 catch
                 {
                     tra.Rollback(); conn.Close();
                     return 0;                 }                 tra.Rollback();
                 conn.Close();
            }写个事务来处理！

Answer 8

我调试了一下
 try
                        {
                            command.CommandText =
                                "insert into details (booktime,bookname) values ('" + TextBox1.Text + "', '" + Label1.Text.Replace("'", "''") + "')";
                            command.ExecuteNonQuery();
                            command.CommandText =
                                "UPDATE book SET T1= '" + Label1.Text.Replace("'", "''") + "'+'" + "/" + "'+'" + DropDownList3.Text + "'+'" + "/" + "'+'" + DropDownList1.Text + "'+'" + "-" + "'+'" + DropDownList2.Text + "'+'" + "/" + "'+ '" + TextBox4.Text.Replace("'", "''") + "'+'" + "/" + "'+'" + TextBox3.Text.Replace("'", "''") + "'+'" + "/" + "'+'" + Labelp.Text.Replace("'", "''") + "'+'" + "/" + "'+'" + Labelv.Text.Replace("'", "''") + "'+'" + "/" + "'+'" + TextBox5.Text.Replace("'", "''") + "'  where booktime= '" + TextBox1.Text.Replace("'", "''") + "'";
                            command.ExecuteNonQuery();
                            transaction.Commit();
                            Response.Write("<script>alert('预定成功')</script>");
                        }
这句 command.CommandText =
                                "insert into details (booktime,bookname) values ('" + TextBox1.Text + "', '" + Label1.Text.Replace("'", "''") + "')";
                            command.ExecuteNonQuery();有问题，没有执行，是不是insert语句写的有问题？

Answer 9

你按照我这个改吧，我刚刚特地测试过了，通过！
SqlConnection con = new SqlConnection();
            con.ConnectionString = "server=.;database=test;pwd=123456;uid=sa;";//xxx连接字符串
            con.Open();
            SqlTransaction st = con.BeginTransaction();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.Transaction = st;
            try
            {
                string sql1 = "insert into test(id) values('1')";
                string sql2 = "update test set id='22' where id='1'";
                string[] sqls = new string[] { sql1, sql2 };                foreach (string item in sqls)
                {
                    cmd.CommandText = item;
                    cmd.ExecuteNonQuery();
                }
                st.Commit();
            }
            catch
            {
                st.Rollback();
            }

Answer 10

嗯，刚才忘说了，在insert之前要加个判断，判断数据库中是否已写入过相同的值，如果写入过就return,没写入过就insert，是这么一个过程，这个存储过程里能不能加上这个判断条件？
"select bookdetail from tb_details where bookdate='" + TextBox1.Text + "'and bookname='"+Label1.Text+"'"
SqlCommand cmd1 = new SqlCommand(Sql1, connstring);
            SqlDataReader dr1 = cmd1.ExecuteReader();
            if (dr1.Read())
            {
                Response.Write("<script>alert('不能添加')</script>");
                return;
            }
            else
            {
                "insert......";
            }

Answer 11

这次insert和update都不执行了，刚才还能执行一个update