.net运用事务向数据库中添加记录

晨曦之光 发布于 2012/05/16 17:15
阅读 389
收藏 0

/// <summary>
        /// 新增 多条sql 事务处理
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Add(List<UserModel> model)
        {
            //Hashtable hatable = new Hashtable(); 

            SortedList sl = new SortedList();
            for (int i = 0; i < model.Count; i++)
            {
                string sql = " insert into 用户 (姓名,密码,公司编号,部门编号,职位编号,可用状态) values(@xingming,@mima,@gongsibianhao,@bumjenbianhao,@zhiweibianhao,@keyongzhuangtai) ";
                SqlParameter[] field = {
                                       new SqlParameter("@xingming",SqlDbType.VarChar),
                                       new SqlParameter("@mima",SqlDbType.VarChar),
                                       new SqlParameter("@gongsibianhao",SqlDbType.VarChar),
                                       new SqlParameter("@bumjenbianhao",SqlDbType.VarChar),
                                       new SqlParameter("@zhiweibianhao",SqlDbType.VarChar),
                                       new SqlParameter("@keyongzhuangtai",SqlDbType.VarChar)
                                   };
                field[0].Value = model[i].姓名;
                field[1].Value = model[i].密码;
                field[2].Value = model[i].公司编号;
                field[3].Value = model[i].部门编号;
                field[4].Value = model[i].职位编号;
                field[5].Value = model[i].可用状态;
                for (int j = 0; j < i; j++)
                {
                    sql += ";";
                }
                sl.Add(sql, field);
                //hatable.Add(sql, field);
            }

           

            //bool bl = DbHelperSQL.ExecuteSqlTran(hatable);
            bool bl = DbHelperSQL.ExecuteSqlTranSL(sl);
            return bl;
        }

DbHelperSQL中的方法

/// <summary>
        /// 执行多条SQL语句,实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param>
        public static bool ExecuteSqlTranSL(SortedList SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                        return true;
                    }
                    catch
                    {
                        trans.Rollback();
                        return false;
                        throw;

                    }
                }

            }

        }

 

注:使用Hashtable是无序的,而使用SortedList是按顺序排列的

如果我们想按照一定的顺序添加数据上面的方法就不好用了。我们可以使用ArrayList进行转换一下。

我们也可以根据需要对list进行排序

/// <summary>
        /// 新增 多条sql 事务处理
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Add(List<UserModel> model)
        {

             ArrayList list = new ArrayList();
            //Hashtable hatable = new Hashtable(); 

            SortedList sl = new SortedList();
            for (int i = 0; i < model.Count; i++)
            {
                string sql = " insert into 用户 (姓名,密码,公司编号,部门编号,职位编号,可用状态) values(@xingming,@mima,@gongsibianhao,@bumjenbianhao,@zhiweibianhao,@keyongzhuangtai) ";
                SqlParameter[] field = {
                                       new SqlParameter("@xingming",SqlDbType.VarChar),
                                       new SqlParameter("@mima",SqlDbType.VarChar),
                                       new SqlParameter("@gongsibianhao",SqlDbType.VarChar),
                                       new SqlParameter("@bumjenbianhao",SqlDbType.VarChar),
                                       new SqlParameter("@zhiweibianhao",SqlDbType.VarChar),
                                       new SqlParameter("@keyongzhuangtai",SqlDbType.VarChar)
                                   };
                field[0].Value = model[i].姓名;
                field[1].Value = model[i].密码;
                field[2].Value = model[i].公司编号;
                field[3].Value = model[i].部门编号;
                field[4].Value = model[i].职位编号;
                field[5].Value = model[i].可用状态;
                for (int j = 0; j < i; j++)
                {
                    sql += ";";
                }
                sl.Add(sql, field);
                //hatable.Add(sql, field);

               list.Add(sql);//向list中添加键
            }

           

            //bool bl = DbHelperSQL.ExecuteSqlTran(hatable);
            bool bl = DbHelperSQL.ExecuteSqlTranSL(sl);
            return bl;
        }

DbHelperSQL中的方法

/// <summary>
        /// 执行多条SQL语句,实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param>
        public static bool ExecuteSqlTranSL(SortedList SQLStringList,ArrayList list )
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        //循环
                        foreach (string key in list)
                        {
                            string cmdText = key;

                            SqlParameter[] cmdParms = (SqlParameter[])SQLStringList[key];

                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                        return true;
                    }
                    catch
                    {
                        trans.Rollback();
                        return false;
                        throw;

                    }
                }

            }

        }


原文链接:http://blog.csdn.net/maji9370/article/details/5610972
加载中
返回顶部
顶部