ADO.NET操作SQL Server：数据库操作类(已封装)

1.增、删、改通用方法 

        /// <summary>
        /// 增、删、改通用方法      
        /// </summary>
        /// <param name="commandText">SqlCommand对象的CommandText属性(sql语句或存储过程名称)</param>
        /// <param name="commandParameters">SqlCommand对象的Parameters属性（sql语句或存储过程参数）</param>
        /// <param name="commandType">SqlCommand对象的CommandType属性(执行sql语句还是存储过程)</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string commandText, SqlParameter[] commandParameters, CommandType commandType = CommandType.Text)
        {
            using (SqlConnection conn = new SqlConnection(datalink.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(commandText, conn) )
                {
                    cmd.CommandType = commandType;
                    cmd.Parameters.AddRange(commandParameters ?? new SqlParameter[] { });
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }

2.读取1行记录

        /// <summary>
        /// 读取1行记录
        /// </summary>
        /// <typeparam name="T">结果集对应的Model</typeparam>
        /// <param name="Reader">读取结果集的SqlDataReader</param>
        /// <param name="commandText">SqlCommand对象的CommandText属性(sql语句或存储过程名称)</param>
        /// <param name="commandParameters">SqlCommand对象的Parameters属性（sql语句或存储过程参数）</param>
        /// <param name="commandType">SqlCommand对象的CommandType属性(执行sql语句还是存储过程)</param>
        /// <returns></returns>
        public static T ExecuteReader<T>(Func<SqlDataReader, T> Reader, string commandText, SqlParameter[] commandParameters, CommandType commandType = CommandType.Text)
        {
            T entity = default(T);
            using (SqlConnection conn = new SqlConnection(datalink.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(commandText, conn))
                {
                    cmd.CommandType = commandType;
                    cmd.Parameters.AddRange(commandParameters ?? new SqlParameter[] { });
                    conn.Open();
                    using (SqlDataReader sr = cmd.ExecuteReader())
                    {
                        while (sr.Read())
                        {
                            entity = Reader(sr);
                        }
                    }
                }
            }
            return entity;
        }

 

3.读取n行记录

        /// <summary>
        /// 读取n行记录
        /// </summary>
        /// <typeparam name="T">结果集对应的Model</typeparam>
        /// <param name="Reader">读取结果集的SqlDataReader</param>
        /// <param name="commandText">SqlCommand对象的CommandText属性(sql语句或存储过程名称)</param>
        /// <param name="commandParameters">SqlCommand对象的Parameters属性（sql语句或存储过程参数）</param>
        /// <param name="commandType">SqlCommand对象的CommandType属性(执行sql语句还是存储过程)</param>
        /// <returns></returns>
        public static List<T> ExecuteReaderList<T>(Func<SqlDataReader, T> Reader, string commandText, SqlParameter[] commandParameters, CommandType commandType = CommandType.Text)
        {
            List<T> list = new List<T>();
            using (SqlConnection conn = new SqlConnection(datalink.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(commandText, conn))
                {
                    cmd.CommandType = commandType;
                    cmd.Parameters.AddRange(commandParameters ?? new SqlParameter[] { });
                    conn.Open();
                    using (SqlDataReader sr = cmd.ExecuteReader())
                    {
                        while (sr.Read())
                        {
                            list.Add(Reader(sr));
                        }
                    }
                }
            }
            return list;
        }

 

4.读取第1行第1列记录

        /// <summary>
        /// 读取第1行第1列记录
        /// </summary>
        /// <param name="commandText">SqlCommand对象的CommandText属性(sql语句或存储过程名称)</param>
        /// <param name="commandParameters">SqlCommand对象的Parameters属性（sql语句或存储过程参数）</param>
        /// <param name="commandType">SqlCommand对象的CommandType属性(执行sql语句还是存储过程)</param>
        /// <returns></returns>
        public static object ExecuteScalar(string commandText, SqlParameter[] commandParameters, CommandType commandType = CommandType.Text)
        {
            using (SqlConnection conn = new SqlConnection(datalink.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(commandText, conn))
                {
                    cmd.CommandType = commandType;
                    cmd.Parameters.AddRange(commandParameters ?? new SqlParameter[] { });
                    conn.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }

 

5.执行事务

        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="commandModel">Command参数对象列表</param>
        /// <param name="message">如果事务提交，返回受影响行数；如果事务回滚，返回异常信息。</param>
        /// <returns></returns>
        public static bool ExecuteTransaction(List<CommandModel> commandModel, out string message)
        {
            message = string.Empty;
            int rows = 0;
            using (SqlConnection connection = new SqlConnection(datalink.ConnectionString))
            {
                connection.Open();

                SqlCommand command = connection.CreateCommand();
                SqlTransaction transaction = connection.BeginTransaction(); ;

                command.Connection = connection;
                command.Transaction = transaction;

                try
                {
                    foreach (var item in commandModel)
                    {
                        command.CommandText = item.CommandText;
                        command.Parameters.Clear();
                        command.Parameters.AddRange(item.CommandParameters ?? new SqlParameter[] { });
                        rows += command.ExecuteNonQuery();
                    }
                    message = rows.ToString();
                    transaction.Commit();
                    return true;
                }
                catch (Exception e)
                {
                    message = e.Message;
                    transaction.Rollback();
                    return false;
                }
            }
        }

 

6.批量添加

        /// <summary>
        /// 批量添加
        /// </summary>
        /// <param name="destinationTableName">目标数据表表名称</param>
        /// <param name="sqlBulkCopyColumnMappings">缓存数据表的列与数据表的列对应关系</param>
        /// <param name="dataTable">缓存数据表</param>
        public static void BulkInsert(string destinationTableName, List<SqlBulkCopyColumnMapping> sqlBulkCopyColumnMappings, DataTable dataTable)
        {
            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(datalink.ConnectionString))
            {
                sqlBulkCopy.DestinationTableName = destinationTableName;
                foreach (var item in sqlBulkCopyColumnMappings)
                {
                    sqlBulkCopy.ColumnMappings.Add(item.SourceColumn, item.DestinationColumn);
                }
                sqlBulkCopy.WriteToServer(dataTable);
            }
        }

7.分页查询

        /// <summary>
        /// 分页查询（SQL Server2012及以上版本支持）
        /// </summary>
        /// <typeparam name="T">结果集对应的Model</typeparam>
        /// <param name="Reader">读取结果集的SqlDataReader</param>
        /// <param name="table">数据表名称</param>
        /// <param name="limitation">查询条件</param>
        /// <param name="sidx">排序字段名称</param>
        /// <param name="sord">排序方式</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页的数据量</param>
        /// <returns></returns>
        public static PagedData<T> SearchPagedList<T>(Func<SqlDataReader, T> Reader, string table, string limitation, string sidx, string sord, int pageIndex, int pageSize)
        {
            PagedData<T> result = new PagedData<T> { PageIndex = pageIndex, PageSize = pageSize };

            string sql = string.Empty;
            if (string.IsNullOrWhiteSpace(limitation))
            {
                sql = @"select * from " + table + " order by " + sidx + " " + sord + " offset (@PageIndex -1) * @PageSize rows fetch next @PageSize rows only;select count(*) DataCount from " + table;
            }
            else
            {
                sql = @"select * from " + table + " where " + limitation + " order by " + sidx + " " + sord + " offset (@PageIndex -1) * @PageSize rows fetch next @PageSize rows only;select count(*) DataCount from " + table + " where " + limitation;
            }
            using (SqlConnection conn = new SqlConnection(datalink.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
                    cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                    conn.Open();
                    using (SqlDataReader sr = cmd.ExecuteReader())
                    {
                        result.DataList = new List<T>();
                        while (sr.Read())
                        {
                            result.DataList.Add(Reader(sr));
                        }
                        bool bln = sr.NextResult();
                        while (sr.Read())
                        {
                            result.DataCount = (int)sr["DataCount"];
                            result.PageCount = result.DataCount % result.PageSize == 0 ? result.DataCount / result.PageSize : result.DataCount / result.PageSize + 1;
                        }
                    }
                }
            }
            return result;
        }

8.辅助类 CommandModel 和 PageData<T>

    /// <summary>
    /// CommandModel
    /// </summary>
    public struct CommandModel
    {
        /// <summary>
        /// CommandText
        /// </summary>
        public string CommandText { set; get; }

        /// <summary>
        /// CommandParameters
        /// </summary>
        public SqlParameter[] CommandParameters { set; get; }
    }

    public class PagedData<T>
    {
        /// <summary>
        /// 页索引
        /// </summary>
        public int PageIndex { set; get; }

        /// <summary>
        /// 每页的数据行数
        /// </summary>
        public int PageSize { set; get; }

        /// <summary>
        /// 总页码数
        /// </summary>
        public int PageCount { set; get; }

        /// <summary>
        /// 总行数
        /// </summary>
        public int DataCount { set; get; }

        /// <summary>
        /// 数据列表
        /// </summary>
        public List<T> DataList { set; get; }
    }

 

 以下是调用，需要Student实体类。

/// <summary>
    /// 测试使用Model
    /// </summary>
    public class Student
    {
        /// <summary>
        /// Constructor
        /// </summary>
        public Student() { }

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="studentId"></param>
        /// <param name="studentNo"></param>
        /// <param name="idCard"></param>
        /// <param name="realName"></param>
        /// <param name="gender"></param>
        /// <param name="phone"></param>
        /// <param name="birthday"></param>
        /// <param name="address"></param>
        /// <param name="isValid"></param>
        /// <param name="remark"></param>
        public Student(Guid studentId, int studentNo, string idCard, string realName, short gender, string phone, DateTime birthday, string address, bool isValid, string remark)
        {
            StudentId = studentId;
            StudentNo = studentNo;
            IdCard = idCard;
            RealName = realName;
            Gender = gender;
            Phone = phone;
            Birthday = birthday;
            Address = address;
            IsValid = isValid;
            Remark = remark;
        }

        /// <summary>
        /// 
        /// </summary>
        public Guid StudentId { set; get; }

        /// <summary>
        /// 
        /// </summary>
        public int StudentNo { set; get; }

        /// <summary>
        /// 
        /// </summary>
        public string IdCard { set; get; }

        /// <summary>
        /// 
        /// </summary>
        public string RealName { set; get; }

        /// <summary>
        /// 
        /// </summary>
        public short Gender { set; get; }

        /// <summary>
        /// 
        /// </summary>
        public string Phone { set; get; }

        /// <summary>
        /// 
        /// </summary>
        public DateTime Birthday { set; get; }

        /// <summary>
        /// 
        /// </summary>
        public string Address { set; get; }

        /// <summary>
        /// 
        /// </summary>
        public bool IsValid { set; get; }

        /// <summary>
        /// 
        /// </summary>
        public string Remark { set; get; }

    }

        /// <summary>
        /// Insert
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public static int Insert(Student entity)
        {
            string sql = @"insert into [Student] ([StudentId],[StudentNo],[IdCard],[RealName]) values (@StudentId,@StudentNo,@IdCard,@RealName)";
            SqlParameter[] cmdParams = {
                new SqlParameter("@StudentId", SqlDbType.UniqueIdentifier) { Value = entity.StudentId },
                new SqlParameter("@StudentNo", SqlDbType.Int) { Value = entity.StudentNo },
                new SqlParameter("@IdCard", SqlDbType.NVarChar) { Value = entity.IdCard ?? (object)DBNull.Value },
                new SqlParameter("@RealName", SqlDbType.NVarChar) { Value = entity.RealName ?? (object)DBNull.Value }
            };
            return SqlBaseDal.ExecuteNonQuery(sql, cmdParams);
        }

        /// <summary>
        /// Update
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public static int Update(Student entity)
        {
            string sql = @"update [Student] set [StudentNo]=@StudentNo,[IdCard]=@IdCard,[RealName]=@RealName where [StudentId]=@StudentId";
            SqlParameter[] cmdParams = {
                new SqlParameter("@StudentId", SqlDbType.UniqueIdentifier) { Value = entity.StudentId },
                new SqlParameter("@StudentNo", SqlDbType.Int) { Value = entity.StudentNo },
                new SqlParameter("@IdCard", SqlDbType.NVarChar) { Value = entity.IdCard ?? (object)DBNull.Value },
                new SqlParameter("@RealName", SqlDbType.NVarChar) { Value = entity.RealName ?? (object)DBNull.Value }
            };
            return SqlBaseDal.ExecuteNonQuery(sql, cmdParams);
        }

        /// <summary>
        /// Delete
        /// </summary>
        /// <param name="studentId"></param>
        /// <returns></returns>
        public static int Delete(Guid studentId)
        {
            string sql = @"delete from [Student] where [StudentId]=@StudentId";
            SqlParameter[] cmdParams = {
                new SqlParameter("@StudentId", SqlDbType.UniqueIdentifier) { Value = studentId },
            };
            return SqlBaseDal.ExecuteNonQuery(sql, cmdParams);
        }

        /// <summary>
        /// Reader
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        private static Student Reader(SqlDataReader reader)
        {
            Student newEntity = new Student();
            if (reader != null && !reader.IsClosed)
            {
                if (reader["StudentId"] != DBNull.Value) newEntity.StudentId = (Guid)reader["StudentId"];
                if (reader["StudentNo"] != DBNull.Value) newEntity.StudentNo = (int)reader["StudentNo"];
                if (reader["IdCard"] != DBNull.Value) newEntity.IdCard = (string)reader["IdCard"];
                if (reader["RealName"] != DBNull.Value) newEntity.RealName = (string)reader["RealName"];
            }
            return newEntity;
        }

        /// <summary>
        /// GetEntity
        /// </summary>
        /// <param name="studentId"></param>
        /// <returns></returns>
        public static Student GetEntity(Guid studentId)
        {
            string sql = @"select * from [Student] where [StudentId]=@StudentId";
            SqlParameter[] cmdParams = {
                new SqlParameter("@StudentId", SqlDbType.UniqueIdentifier) { Value = studentId },
            };
            return SqlBaseDal.ExecuteReader(Reader, sql, cmdParams);
        }

        /// <summary>
        /// Insert
        /// </summary>
        /// <param name="entityList"></param>
        /// <param name="message">添加成功后，message存储影响的行数；添加失败后，message存储失败原因。</param>
        /// <returns></returns>
        public static bool Insert(List<Student> entityList, out string message)
        {
            List<CommandModel> list = new List<CommandModel>();
            foreach (var entity in entityList)
            {
                CommandModel commandModel = new CommandModel();
                commandModel.CommandText = @"insert into [Student] ([StudentId],[StudentNo],[IdCard],[RealName]) values (@StudentId,@StudentNo,@IdCard,@RealName)";
                commandModel.CommandParameters = new SqlParameter[]{
                    new SqlParameter("@StudentId", SqlDbType.UniqueIdentifier) { Value = entity.StudentId },
                    new SqlParameter("@StudentNo", SqlDbType.Int) { Value = entity.StudentNo },
                    new SqlParameter("@IdCard", SqlDbType.NVarChar) { Value = entity.IdCard ?? (object)DBNull.Value },
                    new SqlParameter("@RealName", SqlDbType.NVarChar) { Value = entity.RealName ?? (object)DBNull.Value }
                };
                list.Add(commandModel);
            }
            return SqlBaseDal.ExecuteTransaction(list, out message);
        }

        /// <summary>
        /// Update
        /// </summary>
        /// <param name="entityList"></param>
        /// <param name="message"></param>
        /// <returns></returns>
        public static bool Update(List<Student> entityList, out string message)
        {
            message = string.Empty;
            List<CommandModel> list = new List<CommandModel>();
            foreach (var entity in entityList)
            {
                CommandModel commandModel = new CommandModel();
                commandModel.CommandText = @"update [Student] set [StudentNo]=@StudentNo,[IdCard]=@IdCard,[RealName]=@RealName where [StudentId]=@StudentId";
                commandModel.CommandParameters = new SqlParameter[]{
                     new SqlParameter("@StudentId", SqlDbType.UniqueIdentifier) { Value = entity.StudentId },
                     new SqlParameter("@StudentNo", SqlDbType.Int) { Value = entity.StudentNo },
                     new SqlParameter("@IdCard", SqlDbType.NVarChar) { Value = entity.IdCard ?? (object)DBNull.Value },
                     new SqlParameter("@RealName", SqlDbType.NVarChar) { Value = entity.RealName ?? (object)DBNull.Value }
                };
                list.Add(commandModel);
            }
            return SqlBaseDal.ExecuteTransaction(list, out message);
        }

        /// <summary>
        /// Delete
        /// </summary>
        /// <param name="studentIdList"></param>
        /// <param name="message"></param>
        /// <returns></returns>
        public static bool Delete(List<Guid> studentIdList, out string message)
        {
            message = string.Empty;
            List<CommandModel> list = new List<CommandModel>();
            foreach (var item in studentIdList)
            {
                CommandModel commandModel = new CommandModel();
                commandModel.CommandText = @"delete from [Student] where [StudentId]=@StudentId";
                commandModel.CommandParameters = new SqlParameter[]{
                    new SqlParameter("@StudentId", SqlDbType.UniqueIdentifier) { Value = item }
                };
                list.Add(commandModel);
            }
            return SqlBaseDal.ExecuteTransaction(list, out message);
        }