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 = ;
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 == ? result.DataCount / result.PageSize : result.DataCount / result.PageSize + ;
}
}
}
}
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; } }
Student Model
/// <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);
}
Student Dal