ADO.NET链接数据库封装方法

        /// <summary>
        /// 获取一个表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public DataTable GetTable(string sql, params SqlParameter[] pars)
        {
            // using (var con = new SqlConnection(hisConstr))


            using (var con = new SqlConnection(ConnectionString))
            {
                try
                {
                    var cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(pars);
                    con.Open();
                    var dr = cmd.ExecuteReader();
                    var dt = new DataTable();
                    dt.Load(dr);
                    dr.Close();
                    return dt;
                }
                catch (Exception exc)
                {
                    throw exc;
                }
                finally
                {
                    con.Close();
                }

            }
        }
        /// <summary>
        /// 查询数据返回list
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public List<Dictionary<string, dynamic>> GetList(string sql,params SqlParameter[] pars)
        {
            using (var con = new SqlConnection(ConnectionString))
            {
                var cmd =new SqlCommand();
                cmd.Connection = con;
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(pars);
                con.Open();
                var dr = cmd.ExecuteReader();
                var list = new List<Dictionary<string,dynamic>>();
                while (dr.Read())
                {
                    var newdic = new Dictionary<string,dynamic>();
                    foreach (var item in Enumerable.Range(0,dr.FieldCount).ToDictionary(dr.GetName,dr.GetValue))
                    {
                        newdic.Add(item.Key,item.Value==DBNull.Value?"":item.Value);

                    }
                    list.Add(newdic);
                }
            return list;
            }
        }
        /// <summary>
        /// 查询数据，返回单个值
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="pars">参数</param>
        /// <returns></returns>
        public object GetValue(string sql, params SqlParameter[] pars)
        {
            using (var con = new SqlConnection(ConnectionString))
            {
                var cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(pars);
                con.Open();
                var value = cmd.ExecuteScalar();
                return value;
            }
        }
        /// <summary>
        /// 增删改数据
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="pars">参数</param>
        /// <returns></returns>
        public int SavaData(string sql, params SqlParameter[] pars)
        {
            using (var con = new SqlConnection(ConnectionString))
            {
                var cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(pars);
                con.Open();
                return cmd.ExecuteNonQuery();
            }
        }
        /// <summary>
        /// 执行事务的SQL语句
        /// </summary>
        /// <param name="childSqls">SQL语句集合</param>
        /// <param name="childParametersList">参数列表集合</param>
        /// <returns></returns>
        public bool ChageDataWithTransaction(List<string> sqls, List<List<SqlParameter>> parametersList)
        {

            using (var con = new SqlConnection(ConnectionString))
            {
                con.Open();
                var tran = con.BeginTransaction();
                try
                {
                    var cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.Transaction = tran;

                    for (int i = 0; i < sqls.Count; i++)
                    {
                        cmd.Parameters.Clear();
                        cmd.CommandText = sqls[i];

                        cmd.Parameters.AddRange(parametersList[i].ToArray());

                        cmd.ExecuteNonQuery();
                    }
                    tran.Commit();
                    return true;
                }
                catch (Exception exc)
                {
                    tran.Rollback();

                    throw exc;
                }
                finally
                {
                    con.Close();
                }

            }
        }

转载于:https://www.cnblogs.com/ljknlb/p/9273957.html