/// <summary>
/// 通用主键查询操作
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <returns></returns>
public T Find <T>(int id) where T : BaseModel, new()
{
Type type = typeof(T);
//string columnsString = string.Join(",", type.GetProperties().Select(p => $"[{p.GetMappingName()}]"));
//string sql = $"SELECT {columnsString} FROM [{type.GetMappingName()}] WHERE ID={id} ";
string sql = $"{SqlBuilder<T>.GetFindSql()}{id}";
string connString = SqlConnectionPool.GetConnectionString(SqlConnectionPool.SqlConnectionType.Read);
Console.WriteLine($"当前查询的字符串为{connString}");
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand command = new SqlCommand(sql, conn);
conn.Open();
var reader = command.ExecuteReader();
if (reader.Read())
{
T t = new T();
foreach (var prop in type.GetProperties())
{
string propName = prop.GetMappingName(); //查询时as一下，可以省下一轮
prop.SetValue(t, reader[propName] is DBNull ? null : reader[propName]); //可空类型 设置成null而不是数据库查询的值
}
return(t);
}
else
{
return(default(T));
}
}
}
/// <summary>
/// 通用主键查询操作
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <returns></returns>
public static T Find <T>(int id) where T : BaseModel //增加约束
{
Type type = typeof(T);
string sql = $"{SqlBuilder<T>.GetFindSql()}{id}";
string connectionStr = SqlConnectionPool.GetConnectionString(SqlConnectionPool.SqlConnectionType.Read);
using (SqlConnection connection = new SqlConnection(connectionStr))
{
SqlCommand command = new SqlCommand(sql, connection);
connection.Open();
var reader = command.ExecuteReader();
if (reader.Read())
{
T t = (T)Activator.CreateInstance(type);
foreach (var prop in type.GetProperties())
{
var propName = prop.GetMappingName(); //优化想法，查询时as一下，可以省下一轮
prop.SetValue(t, reader[propName] is DBNull ? null : reader[propName]); //可控类型 设置成null而不是数据库查询的值
}
return(t);
}
else
{
return(default(T));
}
}
}
public void SaveChange()
{
string connString = SqlConnectionPool.GetConnectionString(SqlConnectionPool.SqlConnectionType.Write);
if (this._SqlCommandList.Count > 0)
{
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
foreach (var command in this._SqlCommandList)
{
command.Connection = conn;
command.Transaction = trans;
command.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception)
{
trans.Rollback();
throw;
}
finally
{
this._SqlCommandList?.Clear();
}
}
}
}
}
public static void BatchInsert(DataTable dataTable, string destinationTableName, int batchSize = 0)
{
string connectionStr = SqlConnectionPool.GetConnectionString(SqlConnectionPool.SqlConnectionType.Write);
using (SqlConnection connection = new SqlConnection(connectionStr))
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
using (SqlTransaction transaction = connection.BeginTransaction())
{
//SqlBulkCopy 要求 DataTable 的列必须和表列顺序一致，并且不能多也不能少，所以实体类的字段顺序要和数据库表字段顺序和类型一致...
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
{
bulkCopy.BatchSize = batchSize;
bulkCopy.DestinationTableName = destinationTableName;
try
{
bulkCopy.WriteToServer(dataTable);
transaction.Commit();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
transaction.Rollback();
}
}
}
}
}
/// <summary>
/// 根据条件lambda式进行查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="func"></param>
/// <returns></returns>
public IList <T> FindCondition <T>(Expression <Func <T, bool> > func) where T : BaseModel, new()
{
Type type = typeof(T);
string columnsString = string.Join(",", type.GetProperties().Select(p => $"[{p.GetMappingName()}]"));
string where = func.ToWhere <T>(out List <SqlParameter> parameters);
string sql = $"SELECT {columnsString} FROM [{type.GetMappingName()}] WHERE {where}";
string connString = SqlConnectionPool.GetConnectionString(SqlConnectionPool.SqlConnectionType.Read);
Console.WriteLine($"当前查询的字符串为{sql}");
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddRange(parameters.ToArray());
conn.Open();
var reader = command.ExecuteReader();
IList <T> result = new List <T>();
while (reader.Read())
{
T t = new T();
foreach (var prop in type.GetProperties())
{
string propName = prop.GetMappingName();
prop.SetValue(t, reader[propName] is DBNull ? null : reader[propName]);
}
result.Add(t);
}
return(result);
}
}
/// <summary>
/// 通用实体更新操作
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
public static bool Update <T>(T t) where T : BaseModel
{
Type type = t.GetType();
string sql = $"{SqlBuilder<T>.GetUpdateSql()}";
var paraArray = type.GetProperties().Select(p => new SqlParameter($"@{p.GetMappingName()}", p.GetValue(t) ?? DBNull.Value)).ToArray();
string connectionStr = SqlConnectionPool.GetConnectionString(SqlConnectionPool.SqlConnectionType.Write);
using (SqlConnection connection = new SqlConnection(connectionStr))
{
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddRange(paraArray);
connection.Open();
return(command.ExecuteNonQuery() > 0);
}
}
/// <summary>
/// 通用主键删除操作
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <returns></returns>
public static bool Delete <T>(int id) where T : BaseModel
{
Type type = typeof(T);
string sql = $"{SqlBuilder<T>.GetDeleteSql()}";
string connectionStr = SqlConnectionPool.GetConnectionString(SqlConnectionPool.SqlConnectionType.Write);
using (SqlConnection connection = new SqlConnection(connectionStr))
{
SqlCommand command = new SqlCommand(sql, connection);
SqlParameter parameter = new SqlParameter("@id", id);
command.Parameters.Add(parameter);
connection.Open();
return(command.ExecuteNonQuery() > 0);
}
}
/// <summary>
/// 通用实体新增操作
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
public static int Insert <T>(T t) where T : BaseModel //增加约束
{
Type type = t.GetType();
string sql = $"{SqlBuilder<T>.GetInsertSql()}";
var paraArray = type.GetPropertiesWithoutKey().Select(p => new SqlParameter($"@{p.GetMappingName()}", p.GetValue(t) ?? DBNull.Value)).ToArray();
string connectionStr = SqlConnectionPool.GetConnectionString(SqlConnectionPool.SqlConnectionType.Write);
using (SqlConnection connection = new SqlConnection(connectionStr))
{
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddRange(paraArray);
connection.Open();
object oId = command.ExecuteScalar();
return(int.TryParse(oId?.ToString(), out int iId) ? iId : -1);
}
}
/// <summary>
/// 按条件删除
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="func"></param>
/// <param name="paraArray">属性-值 属性-值</param>
public bool DeleteCondition <T>(Expression <Func <T, bool> > func, params object[] paraArray) where T : BaseModel, new()
{
Type type = typeof(T);
string stringSet = string.Join(",", type.GetPropertiesWithoutKey().Select(p => $"{p.GetMappingName()}=@{p.Name}"));
string where = func.ToWhere <T>(out List <SqlParameter> parameters);
string sql = $"DELETE FROM [{type.GetMappingName()}] WHERE {where};";
string connString = SqlConnectionPool.GetConnectionString(SqlConnectionPool.SqlConnectionType.Write);
Console.WriteLine($"当前删除的字符串为{sql}");
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddRange(paraArray);
conn.Open();
return(command.ExecuteNonQuery() > 0);
}
}
//按需更新--界面上只修改了几个字段--大家有什么思路，可以自己动手，然后show出来
//1 传递一个列表---实体三个属性 字段名称--操作符--值
//2 改造成json---{"Name":"zzzz","Password":"******"}
public int Update <T>(string json, int id) where T : BaseModel, new()
{
Type type = typeof(T);
T t = Newtonsoft.Json.JsonConvert.DeserializeObject <T>(json);//JObject
string stringSet = string.Join(",", type.GetPropertiesInJson(json).Select(p => $"{p.GetMappingName()}=@{p.Name}"));
string sql = $"UPDATE {type.GetMappingName()} SET {stringSet} WHERE Id=@Id;";
var paraArray = type.GetPropertiesInJson(json).Select(p => new SqlParameter($"@{p.Name}", p.GetValue(t) ?? DBNull.Value)).Append(new SqlParameter("@Id", id)).ToArray();
string connString = SqlConnectionPool.GetConnectionString(SqlConnectionPool.SqlConnectionType.Write);
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddRange(paraArray);
conn.Open();
return(command.ExecuteNonQuery());
}
}
/// <summary>
/// 根据实体主键删除
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
public bool Delete <T>(T t) where T : BaseModel, new()
{
Type type = t.GetType();
string sql = $"DELETE FROM [{type.GetMappingName()}] WHERE Id=@Id;";
var paraArray = new SqlParameter[] { new SqlParameter("@Id", t.Id) };
string connString = SqlConnectionPool.GetConnectionString(SqlConnectionPool.SqlConnectionType.Write);
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddRange(paraArray);
conn.Open();
//var trans = conn.BeginTransaction();
//trans.Commit();
//trans.Rollback();
return(1 == command.ExecuteNonQuery());
}
}
/// <summary>
/// 执行sql命名返回一个影响行数,针对于增删改操作
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="isProcedure">是否是过程</param>
/// <param name="pms">sql语句所需要的参数</param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql, bool isProcedure, params SqlParameter[] pms)
{
string connectionStr = SqlConnectionPool.GetConnectionString(SqlConnectionPool.SqlConnectionType.Write);
using (SqlConnection conn = new SqlConnection(connectionStr))
{
SqlCommand cmd = new SqlCommand(sql, conn);
if (isProcedure == true)
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
conn.Open();
return(cmd.ExecuteNonQuery());
}
}
public int Insert <T>(T t) where T : BaseModel, new()
{
Type type = t.GetType();
//string columnsString = string.Join(",", type.GetPropertiesWithoutKey().Select(p => $"[{p.GetMappingName()}]"));
//string valuesString = string.Join(",", type.GetPropertiesWithoutKey().Select(p => $"@{p.GetMappingName()}"));
//string sql = $"INSERT INTO [{type.GetMappingName()}] ({columnsString}) VALUES({valuesString});";//不能直接拼装值---Sql注入问题
string sql = SqlBuilder <T> .GetInsertSql();
var paraArray = type.GetProperties().Select(p => new SqlParameter($"@{p.GetMappingName()}", p.GetValue(t) ?? DBNull.Value)).ToArray();
string connString = SqlConnectionPool.GetConnectionString(SqlConnectionPool.SqlConnectionType.Write);
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddRange(paraArray);
conn.Open();
object oId = command.ExecuteScalar();
return(int.TryParse(oId?.ToString(), out int iId) ? iId : -1);
}
}
/// <summary>
/// 执行命令得到一个SqlDataReader对象,你可以用这个对象的Reader对象读取数据----使用后需要Close()释放
/// </summary>
/// <param name="sql">sql语句或过程名</param>
/// <param name="isProcedure">是否是过程</param>
/// <param name="pms">执行所要的参数</param>
/// <returns></returns>
public static SqlDataReader GetReader(string sql, bool isProcedure, params SqlParameter[] pms)
{
string connectionStr = SqlConnectionPool.GetConnectionString(SqlConnectionPool.SqlConnectionType.Read);
SqlConnection conn = new SqlConnection(connectionStr);
SqlCommand cmd = new SqlCommand(sql, conn);
if (isProcedure == true)
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
conn.Open();
//***CommandBehavior.CloseConnection 在sdr执行命令完毕后的行为是自动关闭连接,也就意味着手动调用sdr.Close()方法时，conn也close()***
SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
//bool b = sdr.Read();
//conn.Close();
return(sdr);
}
public int Update <T>(T t) where T : BaseModel, new()
{
if (!t.Validate <T>())
{
throw new Exception("数据校验没有通过");//大家可以再返回点提示信息
}
Type type = t.GetType();
string stringSet = string.Join(",", type.GetPropertiesWithoutKey().Select(p => $"{p.GetMappingName()}=@{p.Name}"));
string sql = $"UPDATE [{type.GetMappingName()}] SET {stringSet} WHERE Id=@Id;";
//string sql = SqlBuilder<T>.GetInsertSql();
var paraArray = type.GetProperties().Select(p => new SqlParameter($"@{p.Name}", p.GetValue(t) ?? DBNull.Value)).ToArray();
string connString = SqlConnectionPool.GetConnectionString(SqlConnectionPool.SqlConnectionType.Write);
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddRange(paraArray);
conn.Open();
return(command.ExecuteNonQuery());
}
}
/// <summary>
/// 执行sql语句或存储过程返回一个表
/// sql=select * from student where sex=@a
/// sql2=select * from grade where score >@x and stuid>@n
/// </summary>
/// <param name="sql">sql语句或过程名</param>
/// <param name="isProcedure">是否是过程</param>
/// <param name="pms">执行所要的参数</param>
/// <returns></returns>
public static DataTable GetTable(string sql, bool isProcedure, params SqlParameter[] pms)
{
string connectionStr = SqlConnectionPool.GetConnectionString(SqlConnectionPool.SqlConnectionType.Read);
using (SqlConnection conn = new SqlConnection(connectionStr)) //using创建的对象，自动释放资源
{
SqlCommand cmd = new SqlCommand(sql, conn);
if (isProcedure == true)
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
conn.Open();
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds, "aa");
DataTable dt = ds.Tables["aa"];
return(dt);
}
}
