执行Sql语句，包括存储过程，可以动态生成返回实体

/// <summary>
/// 执行Sql语句
/// </summary>
public static class DatabaseExtend
{
/// <summary>
/// 执行任何SQL语句，通用所有的数据库（SQLServer，MySql，Oracle等），利用EMIT快速反射，得到动态类型实体，支持参数查询，防止sql注入。 Created by ZhangQC 
/// </summary>
/// <param name="db">数据库操作</param>
/// <param name="sql">执行的sql命令</param>
/// <param name="parameters">参数</param>
/// <param name="serverName">sql服务提供者</param>
/// <returns></returns>
public static IEnumerable SqlQueryForDynamic(this Database db, string sql, SystemEnum.DataProvider serverName = SystemEnum.DataProvider.MySql, params object[] parameters)
{
//获取数据库连接提供者
IDbConnection defaultConn = GetConnection(serverName);
return SqlQueryForDynamicOtherDb(db, sql, defaultConn, parameters);
}

public static IEnumerable<T> SqlQueryForSpecificType<T>(this Database db, string sql,SqlParameter[] parameters,
SystemEnum.DataProvider serverName = SystemEnum.DataProvider.SqlServer)
{
//获取数据库连接提供者
IDbConnection defaultConn = GetConnection(serverName);
return SqlQueryForSpecificTypeOtherDb<T>(db, sql, defaultConn, parameters);
}

 

/// <summary>
/// 获取连接字符串 Created by ZhangQC 
/// </summary>
/// <param name="providerType"></param>
/// <returns></returns>
public static IDbConnection GetConnection(SystemEnum.DataProvider providerType)
{
IDbConnection iDbConnection;
switch (providerType)
{
case SystemEnum.DataProvider.SqlServer:
iDbConnection = new SqlConnection();
break;
case SystemEnum.DataProvider.OleDb:
iDbConnection = new OleDbConnection();
break;
case SystemEnum.DataProvider.Odbc:
iDbConnection = new OdbcConnection();
break;
case SystemEnum.DataProvider.Oracle:
//iDbConnection = new OracleConnection();
iDbConnection = new SqlConnection();
break;
case SystemEnum.DataProvider.MySql:
//iDbConnection = new MySqlConnection();
iDbConnection = new SqlConnection();
break;
default:
return null;
}
return iDbConnection;
}

/// <summary>
/// 执行sql语句，要求提供接受实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="db"></param>
/// <param name="sql"></param>
/// <param name="conn"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static IEnumerable<T> SqlQueryForSpecificTypeOtherDb<T>(this Database db, string sql, IDbConnection conn,
object[] parameters)
{
if (parameters != null)
{
return db.SqlQuery<T>(sql, parameters).Cast<T>().ToList();
}
return db.SqlQuery<T>(sql).ToList();
}

 


/// <summary>
/// 使用反射发出（快速反射EMIT）动态创建返回 Created by ZhangQC 
/// </summary>
/// <param name="db"></param>
/// <param name="sql"></param>
/// <param name="conn"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static IEnumerable SqlQueryForDynamicOtherDb(this Database db, string sql, IDbConnection conn, params object[] parameters)
{
conn.ConnectionString = db.Connection.ConnectionString;

if (conn.State != ConnectionState.Open)
{
conn.Open();
}

IDbCommand cmd = conn.CreateCommand();

cmd.CommandText = sql;
//开启防止SQL注入，这里万分重要啊 ZhangqC
if (parameters != null)
{
foreach (var obj in parameters)
{
cmd.Parameters.Add(obj);
}
}

IDataReader dataReader = cmd.ExecuteReader();

if (!dataReader.Read())
{
return null; //无结果返回Null
}

#region 构建动态字段

TypeBuilder builder = CreateTypeBuilder("EF_DynamicModelAssembly", "DynamicModule", "DynamicType");

int fieldCount = dataReader.FieldCount;
for (int i = 0; i < fieldCount; i++)
{
//此处类型需要特殊处理（比较尴尬，需要优化）
if (dataReader.GetFieldType(i) == typeof(decimal))
{
CreateAutoImplementedProperty(builder, dataReader.GetName(i), typeof(decimal?));
}
else if (dataReader.GetFieldType(i) == typeof(DateTime))
{
CreateAutoImplementedProperty(builder, dataReader.GetName(i), typeof(DateTime?));
}
else if (dataReader.GetFieldType(i) == typeof(int))
{
CreateAutoImplementedProperty(builder, dataReader.GetName(i), typeof(int?));
}
else
{
CreateAutoImplementedProperty(builder, dataReader.GetName(i), dataReader.GetFieldType(i));
}
}

#endregion

dataReader.Close();
dataReader.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();

Type returnType = builder.CreateType();

if (parameters != null)
{
var result= db.SqlQuery(returnType, sql, parameters);
//解决循环调用的报错问题
cmd.Parameters.Clear();
return result;
}
else
{
return db.SqlQuery(returnType, sql);
}
}

/// <summary>
/// 动态创建程序集 ZhangQC
/// </summary>
/// <param name="assemblyName"></param>
/// <param name="moduleName"></param>
/// <param name="typeName"></param>
/// <returns></returns>
public static TypeBuilder CreateTypeBuilder(string assemblyName, string moduleName, string typeName)
{
TypeBuilder typeBuilder = AppDomain.CurrentDomain.DefineDynamicAssembly(
new AssemblyName(assemblyName),
AssemblyBuilderAccess.Run).DefineDynamicModule(moduleName).DefineType(typeName,
TypeAttributes.Public);
typeBuilder.DefineDefaultConstructor(MethodAttributes.Public);
return typeBuilder;
}


/// <summary>
/// 动态创建实体 ZhangQC
/// </summary>
/// <param name="builder"></param>
/// <param name="propertyName"></param>
/// <param name="propertyType"></param>
public static void CreateAutoImplementedProperty(TypeBuilder builder, string propertyName, Type propertyType)
{
const string privateFieldPrefix = "m_";
const string getterPrefix = "get_";
const string setterPrefix = "set_";

 

// Generate the field.
FieldBuilder fieldBuilder = builder.DefineField(
string.Concat(
privateFieldPrefix, propertyName),
propertyType,
FieldAttributes.Private);

// Generate the property
PropertyBuilder propertyBuilder = builder.DefineProperty(
propertyName,
System.Reflection.PropertyAttributes.HasDefault,
propertyType, null);

// Property getter and setter attributes.
MethodAttributes propertyMethodAttributes = MethodAttributes.Public
| MethodAttributes.SpecialName
| MethodAttributes.HideBySig;

// Define the getter method.
MethodBuilder getterMethod = builder.DefineMethod(
string.Concat(
getterPrefix, propertyName),
propertyMethodAttributes,
propertyType,
Type.EmptyTypes);

// Emit the IL code.
// ldarg.0
// ldfld,_field
// ret
ILGenerator getterIlCode = getterMethod.GetILGenerator();
getterIlCode.Emit(OpCodes.Ldarg_0);
getterIlCode.Emit(OpCodes.Ldfld, fieldBuilder);
getterIlCode.Emit(OpCodes.Ret);

// Define the setter method.
MethodBuilder setterMethod = builder.DefineMethod(
string.Concat(setterPrefix, propertyName),
propertyMethodAttributes,
null,
new Type[] { propertyType });

// Emit the IL code.
// ldarg.0
// ldarg.1
// stfld,_field
// ret
ILGenerator setterIlCode = setterMethod.GetILGenerator();
setterIlCode.Emit(OpCodes.Ldarg_0);
setterIlCode.Emit(OpCodes.Ldarg_1);
setterIlCode.Emit(OpCodes.Stfld, fieldBuilder);
setterIlCode.Emit(OpCodes.Ret);

propertyBuilder.SetGetMethod(getterMethod);
propertyBuilder.SetSetMethod(setterMethod);
}
}