ado.net封装的基本操作数据库的公共方法

using System;

using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace SchoolMis.DAL
{
    public class SQLHelper
    {
        public static readonly string connStr = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
        /// <summary>
        /// 非查询操作方法
        /// </summary>
        /// <param name="sql">sql命令</param>
        /// <param name="parameters">查询参数</param>
        /// <returns>影响的表中数据的行数</returns>
        public static int ExecuteNonQuery(string sql,params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql,conn);
                cmd.Parameters.AddRange(parameters);
                return cmd.ExecuteNonQuery();
            }
        }
        /// <summary>
        /// 从表中查询单个值
        /// </summary>
        /// <param name="sql">命令内容</param>
        /// <param name="parameters">命令中要使用的参数对象数组</param>
        /// <returns>查询的结果</returns>
        public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddRange(parameters);
                return cmd.ExecuteScalar();
            }
        }
        /// <summary>
        /// 查询多行多列的值
        /// </summary>
        /// <param name="sql">查询命令</param>
        /// <param name="parameters">命令参数数组</param>
        /// <returns>查询结果表</returns>
        public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
        {
            DataSet set = new DataSet();
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddRange(parameters);
                using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                {
                    adapter.Fill(set);
                }
            }
            return set.Tables[0];
        }

               public virtual bool ExecuteTransaction(Dictionary<string, SqlParameter[]> dict)
        {
            if (dict == null || dict.Count <= 0)
            {
                throw new ArgumentNullException("dict");
            }
            using (SqlConnection conn = new SqlConnection(this.strConn))
            {
                conn.Open();
                SqlTransaction tran = conn.BeginTransaction();
                SqlCommand cmd = new SqlCommand()
                {
                    Connection = conn,
                    Transaction = tran,
                    CommandType = CommandType.Text
                };
                try
                {
                    foreach (var item in dict)
                    {
                        if (string.IsNullOrEmpty(item.Key))
                        {
                            throw new ArgumentNullException("dict.Key");
                        }
                        cmd.CommandText = item.Key;
                        if (item.Value != null && item.Value.Length > 0)
                        {
                            cmd.Parameters.Clear();
                            cmd.Parameters.AddRange(item.Value);
                        }
                        cmd.ExecuteNonQuery();
                    }
                    tran.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    return false;
                }
            }
        }

        public virtual bool ExecuteTransaction(List<SqlParam> sqlParamList)
        {
            if (sqlParamList == null || sqlParamList.Count <= 0)
            {
                throw new ArgumentNullException("sqlParamList");
            }
            using (SqlConnection conn = new SqlConnection(this.strConn))
            {
                conn.Open();
                SqlTransaction tran = conn.BeginTransaction();
                SqlCommand cmd = new SqlCommand()
                {
                    Connection = conn,
                    Transaction = tran,
                    CommandType = CommandType.Text
                };
                try
                {
                    foreach (var item in sqlParamList)
                    {
                        if (string.IsNullOrEmpty(item.Sql))
                        {
                            throw new ArgumentNullException("dict.Sql");
                        }
                        cmd.CommandText = item.Sql;
                        if (item.Param != null && item.Param.Length > 0)
                        {
                            cmd.Parameters.Clear();
                            cmd.Parameters.AddRange(item.Param);
                        }
                        cmd.ExecuteNonQuery();
                    }
                    tran.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    return false;
                }
            }
        }
    }

    public class SqlParam
    {
        public string Sql { get; set; }
        public SqlParameter[] Param { get; set; }
    }

    }
}

 int dr = -1;
            string purNum = purDeliveryList.Select(s => s.BillNo).FirstOrDefault();
            string documentsCode = "SHD" + purNum.Substring(6) + DateTime.Now.ToString("yyyyMMddhhmmss");
            fDocumentsCode = documentsCode;
            string sql = string.Format(@" insert into  [DeliverySingle]
              (FDocumentsCode,FPurchaseOrder,FDocumentsType,FBusinessTypes,FPurchaseDate,
               FMaterialNubmer,FMaterialName,FSpecifications,FUnit,FQty,FDeliveryNumber,FStayDelivery,FSupplierNumber,FStockUnit,FStockQty,FCreateDate,FGroup,FConvertType,FSeq,DeliveryQty,VolumeCount,IsPrint,BatchNumber,DeliveryDate,FStayQty)
               VALUES
              (@FDocumentsCode,@FPurchaseOrder,@FDocumentsType,@FBusinessTypes,@FPurchaseDate,@FMaterialNubmer,
               @FMaterialName,@FSpecifications,@FUnit,@FQty,@FDeliveryNumber,@FStayDelivery,@FSupplierNumber,@FStockUnit,@FStockQty,@FCreateDate,@FGroup,@FConvertType,@FSeq,@DeliveryQty,@VolumeCount,@IsPrint,@BatchNumber,@DeliveryDate,@FStayQty)");

            List<SqlParam> sqlParamList = new List<SqlParam>();
            foreach (PurDelivery purDelivery in purDeliveryList)
            {
                List<SqlParameter> param = new List<SqlParameter>();
                param.Add(new SqlParameter("@FDocumentsCode", SqlDbType.NVarChar) { Value = documentsCode });
                param.Add(new SqlParameter("@FPurchaseOrder", SqlDbType.NVarChar) { Value = purDelivery.BillNo });
                param.Add(new SqlParameter("@FDocumentsType", SqlDbType.NVarChar) { Value = "标准送货单" });
                param.Add(new SqlParameter("@FBusinessTypes", SqlDbType.NVarChar) { Value = "标准采购" });
                param.Add(new SqlParameter("@FPurchaseDate", SqlDbType.NVarChar) { Value = purDelivery.PurDate });
                param.Add(new SqlParameter("@FMaterialNubmer", SqlDbType.NVarChar) { Value = purDelivery.MateNumber });
                param.Add(new SqlParameter("@FMaterialName", SqlDbType.NVarChar) { Value = purDelivery.MateName });
                param.Add(new SqlParameter("@FSpecifications", SqlDbType.NVarChar) { Value = purDelivery.FSPECIFICATION });
                param.Add(new SqlParameter("@FUnit", SqlDbType.NVarChar) { Value = purDelivery.priceUnit });
                param.Add(new SqlParameter("@FQty", SqlDbType.NVarChar) { Value = purDelivery.priceQty });
                param.Add(new SqlParameter("@FDeliveryNumber", SqlDbType.Decimal) { Value = purDelivery.FSTOCKBASESTOCKINQTY });
                //param.Add(new SqlParameter("@FStayDelivery", SqlDbType.Decimal) { Value = purDelivery.stockQty - Convert.ToDecimal(purDelivery.FSTOCKBASESTOCKINQTY) });//待交货数量
                param.Add(new SqlParameter("@FStayDelivery", SqlDbType.Decimal) { Value = purDelivery.RDeliveryQty * purDelivery.VolumeCount });//待交货数量
                param.Add(new SqlParameter("@FSupplierNumber", SqlDbType.NVarChar) { Value = supplierNumber });
                param.Add(new SqlParameter("@FStockUnit", SqlDbType.NVarChar) { Value = purDelivery.stockUnit });
                param.Add(new SqlParameter("@FStockQty", SqlDbType.Decimal) { Value = purDelivery.stockQty });
                param.Add(new SqlParameter("@FCreateDate", SqlDbType.DateTime) { Value = DateTime.Now });
                param.Add(new SqlParameter("@FGroup", SqlDbType.NVarChar) { Value = purDelivery.F_SQ_ZUBIE });
                param.Add(new SqlParameter("@FConvertType", SqlDbType.NVarChar) { Value = purDelivery.FConvertType });
                param.Add(new SqlParameter("@FSeq", SqlDbType.Int) { Value = purDelivery.Fseq });
                param.Add(new SqlParameter("@DeliveryQty", SqlDbType.Decimal) { Value = purDelivery.RDeliveryQty });
                param.Add(new SqlParameter("@VolumeCount", SqlDbType.Decimal) { Value = purDelivery.VolumeCount });
                param.Add(new SqlParameter("@IsPrint", SqlDbType.Int) { Value = 0 });
                param.Add(new SqlParameter("@BatchNumber", SqlDbType.VarChar) { Value = purDelivery.BatchNumber });
                param.Add(new SqlParameter("@DeliveryDate", SqlDbType.DateTime) { Value = purDelivery.DeliveryDate });
                param.Add(new SqlParameter("@FStayQty", SqlDbType.Decimal) { Value = purDelivery.priceQty * purDelivery.VolumeCount });
                sqlParamList.Add(new SqlParam()
                {
                    Sql = sql,
                    Param = param.ToArray()
                });
            }
            var purList = purDeliveryList.GroupBy(m => m.Fseq);
            foreach (var pur in purList)
            {
                decimal rDeliveryQty = 0;
                foreach (var seq in pur)
                {
                    rDeliveryQty += (seq.RDeliveryQty * seq.VolumeCount);
                }
                PurDelivery purDelivery = new PurDeliveryDAL().GetEntity(purNum, pur.Key.ToString());
                if (purDelivery == null)
                {
                    sqlParamList.Add(new SqlParam()
                    {
                        Sql = string.Format("INSERT INTO [dbo].[PurDelivery]([PONumber],[POSeq],[DeliveryQty]) VALUES('{0}','{1}','{2}')", purNum, pur.Key, rDeliveryQty)
                    });
                }
                else
                {
                    sqlParamList.Add(new SqlParam()
                    {
                        Sql = string.Format("UPDATE [dbo].[PurDelivery] SET [DeliveryQty] = '{0}' WHERE [PONumber] = '{1}' and [POSeq] = '{2}'", purDelivery.DeliveryQty + rDeliveryQty, purNum, pur.Key)
                    });
                }
            }
            return new SqlHelperForPlat().ExecuteTransaction(sqlParamList);