话不多说,直接上码:
using ConsoleAppTestEF.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;
namespace ConsoleAppTestEF
{
public class CpcRepository
{
#region Execute
//普通执行
public static void Execute(Action<LingbugCpcContext> action)
{
using (var dbContext = new LingbugCpcContext())
{
action.Invoke(dbContext);
}
}
//事务执行
public static void Execute(Action<LingbugCpcContext> action, bool isDefaultTranLevel, IsolationLevel transactionLevel = IsolationLevel.Unspecified)
{
using (var dbContext = new LingbugCpcContext())
{
using (var transaction = isDefaultTranLevel ? dbContext.Database.BeginTransaction() : dbContext.Database.BeginTransaction(transactionLevel))
{
try
{
action.Invoke(dbContext);
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
throw;
}
}
}
}
//EF事务在ado.net事务融合执行
public static void Execute(Action<LingbugCpcContext, SqlTransaction> action, bool isDefaultTranLevel, IsolationLevel transactionLevel = IsolationLevel.Unspecified)
{
using (var connection = new SqlConnection(GetConnectionString()))
{
OpenConnection(connection);
using (var transaction = isDefaultTranLevel ? connection.BeginTransaction() : connection.BeginTransaction(transactionLevel))
{
using (var dbContext = new LingbugCpcContext(connection, false))
{
dbContext.Database.UseTransaction(transaction);
try
{
action.Invoke(dbContext, transaction);
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
throw;
}
}
}
}
}
#endregion
#region 修改
//修改(对外提供的接口)
public static int Update<T>(T updateModel, LingbugCpcContext dbContext = null) where T : BaseModel
{
int count = 0;
if (dbContext == null)
{
Execute(dbctx => count = UpdateMain(updateModel, dbctx));
}
else
{
count = UpdateMain(updateModel, dbContext);
}
return count;
}
//修改(核心)
private static int UpdateMain<T>(T model, LingbugCpcContext dbContext) where T : BaseModel
{
dbContext.Entry(model).State = EntityState.Modified;
foreach (var name in model.GetUnChanged())
{
dbContext.Entry(model).Property(name).IsModified = false;
}
return dbContext.SaveChanges();
}
#endregion
#region 查询
//使用ado.net查询DataTable(对外提供接口)
public static DataTable QueryDataTable(string sql, Dictionary<string, object> dicParameter = null, SqlTransaction transaction = null)
{
var dataContainer = new DataTable();
if (transaction == null)
{
using (var connection = new SqlConnection(GetConnectionString()))
{
QueryFromSql(connection, null, sql, dicParameter, da => da.Fill(dataContainer));
}
}
else
{
QueryFromSql(GetConnectionByTransaction(transaction), transaction, sql, dicParameter, da => da.Fill(dataContainer));
}
return dataContainer;
}
//使用ado.net查询DataSet(对外提供接口)
public static DataSet QueryDataSet(string sql, Dictionary<string, object> dicParameter = null, SqlTransaction transaction = null)
{
var dataContainer = new DataSet();
if (transaction == null)
{
using (var connection = new SqlConnection(GetConnectionString()))
{
QueryFromSql(connection, null, sql, dicParameter, da => da.Fill(dataContainer));
}
}
else
{
QueryFromSql(GetConnectionByTransaction(transaction), transaction, sql, dicParameter, da => da.Fill(dataContainer));
}
return dataContainer;
}
#endregion
#region 私有方法
//通过事务获取到数据库连接
private static SqlConnection GetConnectionByTransaction(SqlTransaction transaction)
{
return transaction.Connection;
}
//通过EF获取到数据库连接
private static string GetConnectionString()
{
string connectionString = null;
Execute(dbContext => connectionString = dbContext.Database.Connection.ConnectionString);
return connectionString;
}
//使用ado.net执行sql(核心)
private static void QueryFromSql(SqlConnection connection, SqlTransaction transaction, string sql, Dictionary<string, object> dicParameter, Action<SqlDataAdapter> action)
{
OpenConnection(connection);
using (var command = new SqlCommand())
{
InitCommand(command, connection, transaction);
command.CommandText = sql;
command.Parameters.Clear();
if (dicParameter != null && dicParameter.Any())
{
foreach (var dicItem in dicParameter)
{
command.Parameters.Add(new SqlParameter(GetParameterName(dicItem.Key), dicItem.Value));
}
}
using (var da = new SqlDataAdapter(command))
{
action.Invoke(da);
}
}
}
//获取到参数名称(自动增加前缀@)
private static string GetParameterName(string parameterName)
{
return parameterName.StartsWith("@") ? parameterName : "@" + parameterName;
}
//初始化ado.net的命令对象
private static void InitCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction)
{
command.Connection = connection;
if (transaction != null)
{
command.Transaction = transaction;
}
command.CommandType = CommandType.Text;
}
//打开数据库连接
private static void OpenConnection(SqlConnection connection)
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
}
#endregion
}
}
测试代码:
普通测试:
private static void TestEfHelperNew()
{
var updateModel = new TestUpdateWithFlagModel()
{
Id = "c8b8566a-db7c-4eeb-a5f5-24ff8d0abd12",
Name = "TestEfHelperNewUpdate",
UpdateDate = DateTime.Now
};
Console.WriteLine("修改结果为:" + EfHelperNew.Update(updateModel));
var dtTask = EfHelperNew.QueryDataTable("SELECT * FROM dbo.Task WHERE ProjectName = @ProjectName", new Dictionary<string, object>() { { "@ProjectName", "Ling、bug任务" } });
Console.WriteLine("共查询出{0}条数据", dtTask.Rows.Count);
int rowIndex = 0;
foreach (DataRow dr in dtTask.Rows)
{
Console.WriteLine("第{0}个:{1}", ++rowIndex, dr["ProjectName"].ToString());
}
var ds = EfHelperNew.QueryDataSet("SELECT * FROM dbo.TestUpdateWithFlagInfo;SELECT * FROM dbo.Task");
Console.WriteLine("共查询出{0}个table", ds.Tables.Count);
int tableIndex = 0;
foreach (DataTable dtItem in ds.Tables)
{
Console.WriteLine("第{0}个table名字是:{1},有{2}行数据", ++tableIndex, dtItem.TableName, dtItem.Rows.Count);
}
}
测试结果:
测试事务以及事务隔离级别:
private static void TestEfHelperNewTransaction()
{
var task1 = Task.Run(() =>
{
EfHelperNew.Execuse((dbContext, conn, tran) =>
{
//查询操作
var dt = EfHelperNew.QueryDataTable("SELECT * FROM dbo.TestUpdateWithFlagInfo", null, conn, tran);
Console.WriteLine("查询出{0}条数据", dt.Rows.Count);
int i = 1;
//模拟耗时操作
Console.WriteLine("开始模拟耗时操作");
for (i = 1; i <= 5; i++)
{
Console.WriteLine(i);
Thread.Sleep(1000);
}
Console.WriteLine("开始事务内部修改操作");
//修改数据
var updateCount = dbContext.Database.ExecuteSqlCommand("UPDATE dbo.TestUpdateWithFlagInfo SET Name = '6666',UpdateDate = GETDATE() WHERE Id = '2fdbc379-def0-4f18-a34b-f3997c86587b'");
//提示修改结果
Console.WriteLine("事务内部修改完毕,count = " + updateCount);
Console.WriteLine("继续耗时操作");
for (i = 6; i <= 10; i++)
{
Console.WriteLine(i);
Thread.Sleep(1000);
}
}, false, IsolationLevel.Serializable);
});
var task2 = Task.Run(() =>
{
Thread.Sleep(3000);
Console.WriteLine("事务外部准备进行修改操作");
EfHelperNew.Execuse(dbContext =>
{
//不使用事务,修改数据
var count = dbContext.Database.ExecuteSqlCommand(string.Format(@"UPDATE dbo.TestUpdateWithFlagInfo SET Name = '线程{0}在修改',UpdateDate = GETDATE() WHERE Id = '5707d129-6b25-4be9-9665-02a53bcddff2'", Thread.CurrentThread.ManagedThreadId));
Console.WriteLine("事务外部修改完毕,count = " + count);
});
});
var task3 = Task.Run(() =>
{
Thread.Sleep(3000);
Console.WriteLine("事务外部准备进行新增操作");
EfHelperNew.Execuse(dbContext =>
{
//不使用事务,新增数据
dbContext.TestUpdateWithFlag.Add(new TestUpdateWithFlagModel()
{
Id = Guid.NewGuid().ToString(),
Name = "线程" + Thread.CurrentThread.ManagedThreadId + "在新增",
Password = "123456",
Status = true,
CreateDate = DateTime.Now
});
var count = dbContext.SaveChanges();
Console.WriteLine("事务外部新增完毕,count = " + count);
});
});
}
测试结果:
2020-4-29修改了一版,增加了一些扩展方法,代码如下:
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using LevoxCpc.Core;
using LevoxCpc.Model;
namespace LevoxCpc.Dao
{
/// <summary>
/// 此类是EF扩展使用类,请勿改动此类,如有需要,请自己建立扩展类去扩展
/// </summary>
public class EfDepositoryExtension
{
#region Execuse
public static void Execuse(Action<LevoxCpcDbContext> action)
{
using (var dbContext = new LevoxCpcDbContext())
{
action.Invoke(dbContext);
}
}
public static void Execuse(Action<LevoxCpcDbContext> action, bool isDefaultTranLevel, IsolationLevel transactionLevel = IsolationLevel.Unspecified)
{
using (var dbContext = new LevoxCpcDbContext())
{
using (var transaction = isDefaultTranLevel ? dbContext.Database.BeginTransaction() : dbContext.Database.BeginTransaction(transactionLevel))
{
try
{
action.Invoke(dbContext);
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
throw;
}
}
}
}
public static void Execuse(Action<LevoxCpcDbContext, SqlTransaction> action, bool isDefaultTranLevel, IsolationLevel transactionLevel = IsolationLevel.Unspecified)
{
using (var connection = new SqlConnection(GetConnectionString()))
{
OpenConnection(connection);
using (var transaction = isDefaultTranLevel ? connection.BeginTransaction() : connection.BeginTransaction(transactionLevel))
{
using (var dbContext = new LevoxCpcDbContext(connection, false))
{
dbContext.Database.UseTransaction(transaction);
try
{
action.Invoke(dbContext, transaction);
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
throw;
}
}
}
}
}
#endregion
#region 修改
public static int Update<T>(T updateModel, LevoxCpcDbContext dbContext = null) where T : BaseDbModel
{
int count = 0;
if (dbContext == null)
{
Execuse(dbctx => count = UpdateMain(updateModel, dbctx));
}
else
{
count = UpdateMain(updateModel, dbContext);
}
return count;
}
private static int UpdateMain<T>(T model, LevoxCpcDbContext dbContext) where T : BaseDbModel
{
var entryModel = dbContext.Entry(model);
entryModel.State = EntityState.Modified;
foreach (var name in model.GetUnChanged())
{
entryModel.Property(name).IsModified = false;
}
return dbContext.SaveChanges();
}
#endregion
#region 查询
public static DataTable QueryDataTable(string sql, Dictionary<string, object> dicParameter = null, SqlTransaction transaction = null)
{
var dataContainer = new DataTable();
if (transaction == null)
{
using (var connection = new SqlConnection(GetConnectionString()))
{
QueryFromSql(connection, null, sql, dicParameter, da => da.Fill(dataContainer));
}
}
else
{
QueryFromSql(GetConnectionByTransaction(transaction), transaction, sql, dicParameter, da => da.Fill(dataContainer));
}
return dataContainer;
}
public static DataSet QueryDataSet(string sql, Dictionary<string, object> dicParameter = null, SqlTransaction transaction = null)
{
var dataContainer = new DataSet();
if (transaction == null)
{
using (var connection = new SqlConnection(GetConnectionString()))
{
QueryFromSql(connection, null, sql, dicParameter, da => da.Fill(dataContainer));
}
}
else
{
QueryFromSql(GetConnectionByTransaction(transaction), transaction, sql, dicParameter, da => da.Fill(dataContainer));
}
return dataContainer;
}
public static List<T> QueryList<T>(string sql, Dictionary<string, object> dicParameter = null, SqlTransaction transaction = null)
{
return QueryDataTable(sql, dicParameter, transaction).ToList<T>();
}
public static int QueryCountForPage(string sql, Dictionary<string, object> dicParameter = null, SqlTransaction transaction = null)
{
var dt = QueryDataTable(sql, dicParameter, transaction);
return dt.Rows[0][0].ToInt32();
}
#endregion
#region 私有方法
private static SqlConnection GetConnectionByTransaction(SqlTransaction transaction)
{
return transaction.Connection;
}
private static string GetConnectionString()
{
string connectionString = null;
Execuse(dbContext => connectionString = dbContext.Database.Connection.ConnectionString);
return connectionString;
}
private static void QueryFromSql(SqlConnection connection, SqlTransaction transaction, string sql, Dictionary<string, object> dicParameter, Action<SqlDataAdapter> action)
{
OpenConnection(connection);
using (var command = new SqlCommand())
{
command.Connection = connection;
if (transaction != null) command.Transaction = transaction;
command.CommandType = CommandType.Text;
command.CommandText = sql;
command.Parameters.Clear();
CreateSqlParameterListByDicPara(dicParameter).LingbugFor(r => command.Parameters.Add(r));
using (var da = new SqlDataAdapter(command))
{
action.Invoke(da);
}
}
}
private static SqlParameter[] CreateSqlParameterListByDicPara(Dictionary<string, object> dicPara)
{
return dicPara.IsNullOrEmpty() ? new SqlParameter[0] : dicPara.Select(r =>
{
string key = r.Key.StartsWith("@") ? r.Key : "@" + r.Key;
return new SqlParameter(key, r.Value);
}).ToArray();
}
private static void OpenConnection(SqlConnection connection)
{
if (connection.State != ConnectionState.Open) connection.Open();
}
#endregion
#region 其他扩展方法
public static int DeleteDataMain<T>(DeleteDataRequestModel requestModel) where T : BaseDbModel
{
if (requestModel.Ids.IsNullOrEmpty()) throw new LevoxCpcException("没有需要删除的数据!");
var efData = GetTableInfo<T>();
string sql = string.Format(@"UPDATE dbo.{0} SET IsDel = 1,LastUpdateDate = GETDATE(),LastUpdateUserId = {1} WHERE {2} IN ({3})", efData.TableName, requestModel.CurrentUserId, efData.PrimaryKeyName, requestModel.Ids.LingbugJoin());
int count = 0;
Execuse(dbContext => count = dbContext.Database.ExecuteSqlCommand(sql));
return count.ValidateExecuteResultCount();
}
private static EfTableInfo GetTableInfo<T>() where T : BaseDbModel
{
var t = typeof(T);
var tName = t.FullName;
var primaryKeyProcList = t.GetProperties().Where(r => r.GetAttribute<KeyAttribute>() != null).ToArray();
if (primaryKeyProcList.IsNullOrEmpty()) throw new LevoxCpcException(string.Format("未找到类型{0}的主键信息!", tName));
if (primaryKeyProcList.Length > 1) throw new LevoxCpcException(string.Format("类型{0}的主键不唯一,有{1}个:{2}", tName, primaryKeyProcList.Length, primaryKeyProcList.Select(r => r.Name).LingbugJoin()));
var tableInfo = t.GetAttribute<TableAttribute>();
if (tableInfo == null) throw new LevoxCpcException(string.Format("未找到类型{0}的表名信息!", tName));
var data = new EfTableInfo()
{
TableName = tableInfo.Name,
PrimaryKeyName = primaryKeyProcList[0].Name,
Schema = tableInfo.Schema
};
LogTestService.Info("GetTableInfo - Result", data.SerializeObject());
return data;
}
public static TResult QuerySingleById<TTable, TResult>(int? id, SqlTransaction transaction) where TTable : BaseDbModel
{
if (!id.HasValue) return CoreService.GetDefaultValue<TResult>();
var efData = GetTableInfo<TTable>();
string sql = string.Format(@"SELECT * FROM dbo.{0} a WHERE a.IsDel = 0 AND a.{1} = {2}", efData.TableName, efData.PrimaryKeyName, id.Value);
var dataList = QueryList<TResult>(sql, null, transaction);
return dataList.FirstOrDefault();
}
public static List<TResult> QueryListByIds<TTable, TResult>(int[] ids, SqlTransaction transaction, bool isCanNoSqlwhere) where TTable : BaseDbModel
{
if (ids.IsNullOrEmpty() && !isCanNoSqlwhere) return new List<TResult>();
var efData = GetTableInfo<TTable>();
var sb = new StringBuilder(string.Format(@"SELECT * FROM dbo.{0} a WHERE a.IsDel = 0", efData.TableName));
if (!ids.IsNullOrEmpty()) sb.AppendFormat(@" AND a.{0} IN ({1}) ", efData.PrimaryKeyName, ids.LingbugJoin());
string sql = sb.ToString();
return QueryList<TResult>(sql, null, transaction);
}
#endregion
}
}
Ending~