/// <summary>
2 /// 执行Sql语句
3 /// </summary>
4 public static class DatabaseExtend
5 {
6 /// <summary>
7 /// 执行任何SQL语句，通用所有的数据库（SQLServer，MySql，Oracle等），利用EMIT快速反射，得到动态类型实体，支持参数查询，防止sql注入。 Created by ZhangQC
8 /// </summary>
9 /// <param name="db">数据库操作</param>
10 /// <param name="sql">执行的sql命令</param>
11 /// <param name="parameters">参数</param>
12 /// <param name="serverName">sql服务提供者</param>
13 /// <returns></returns>
14 public static IEnumerable SqlQueryForDynamic(
this Database db,
string sql, SystemEnum.DataProvider serverName = SystemEnum.DataProvider.MySql,
params object[] parameters)
15 {
16 //获取数据库连接提供者
17 IDbConnection defaultConn =
GetConnection(serverName);
18 return SqlQueryForDynamicOtherDb(db, sql, defaultConn, parameters);
19 }
20
21 public static IEnumerable<T> SqlQueryForSpecificType<T>(
this Database db,
string sql,SqlParameter[] parameters,
22 SystemEnum.DataProvider serverName =
SystemEnum.DataProvider.SqlServer)
23 {
24 //获取数据库连接提供者
25 IDbConnection defaultConn =
GetConnection(serverName);
26 return SqlQueryForSpecificTypeOtherDb<T>
(db, sql, defaultConn, parameters);
27 }
28
29
30
31 /// <summary>
32 /// 获取连接字符串 Created by ZhangQC
33 /// </summary>
34 /// <param name="providerType"></param>
35 /// <returns></returns>
36 public static IDbConnection GetConnection(SystemEnum.DataProvider providerType)
37 {
38 IDbConnection iDbConnection;
39 switch (providerType)
40 {
41 case SystemEnum.DataProvider.SqlServer:
42 iDbConnection =
new SqlConnection();
43 break;
44 case SystemEnum.DataProvider.OleDb:
45 iDbConnection =
new OleDbConnection();
46 break;
47 case SystemEnum.DataProvider.Odbc:
48 iDbConnection =
new OdbcConnection();
49 break;
50 case SystemEnum.DataProvider.Oracle:
51 //iDbConnection = new OracleConnection();
52 iDbConnection =
new SqlConnection();
53 break;
54 case SystemEnum.DataProvider.MySql:
55 //iDbConnection = new MySqlConnection();
56 iDbConnection =
new SqlConnection();
57 break;
58 default:
59 return null;
60 }
61 return iDbConnection;
62 }
63
64 /// <summary>
65 /// 执行sql语句，要求提供接受实体
66 /// </summary>
67 /// <typeparam name="T"></typeparam>
68 /// <param name="db"></param>
69 /// <param name="sql"></param>
70 /// <param name="conn"></param>
71 /// <param name="parameters"></param>
72 /// <returns></returns>
73 public static IEnumerable<T> SqlQueryForSpecificTypeOtherDb<T>(
this Database db,
string sql, IDbConnection conn,
74 object[] parameters)
75 {
76 if (parameters !=
null)
77 {
78 return db.SqlQuery<T>(sql, parameters).Cast<T>
().ToList();
79 }
80 return db.SqlQuery<T>
(sql).ToList();
81 }
82
83
84
85
86 /// <summary>
87 /// 使用反射发出（快速反射EMIT）动态创建返回 Created by ZhangQC
88 /// </summary>
89 /// <param name="db"></param>
90 /// <param name="sql"></param>
91 /// <param name="conn"></param>
92 /// <param name="parameters"></param>
93 /// <returns></returns>
94 public static IEnumerable SqlQueryForDynamicOtherDb(
this Database db,
string sql, IDbConnection conn,
params object[] parameters)
95 {
96 conn.ConnectionString =
db.Connection.ConnectionString;
97
98 if (conn.State !=
ConnectionState.Open)
99 {
100 conn.Open();
101 }
102
103 IDbCommand cmd =
conn.CreateCommand();
104
105 cmd.CommandText =
sql;
106 //开启防止SQL注入，这里万分重要啊 ZhangqC
107 if (parameters !=
null)
108 {
109 foreach (
var obj
in parameters)
110 {
111 cmd.Parameters.Add(obj);
112 }
113 }
114
115 IDataReader dataReader =
cmd.ExecuteReader();
116
117 if (!
dataReader.Read())
118 {
119 return null;
//无结果返回Null
120 }
121
122 #region 构建动态字段
123
124 TypeBuilder builder = CreateTypeBuilder(
"EF_DynamicModelAssembly",
"DynamicModule",
"DynamicType");
125
126 int fieldCount =
dataReader.FieldCount;
127 for (
int i =
0; i < fieldCount; i++
)
128 {
129 //此处类型需要特殊处理（比较尴尬，需要优化）
130 if (dataReader.GetFieldType(i) ==
typeof(
decimal))
131 {
132 CreateAutoImplementedProperty(builder, dataReader.GetName(i),
typeof(
decimal?
));
133 }
134 else if (dataReader.GetFieldType(i) ==
typeof(DateTime))
135 {
136 CreateAutoImplementedProperty(builder, dataReader.GetName(i),
typeof(DateTime?
));
137 }
138 else if (dataReader.GetFieldType(i) ==
typeof(
int))
139 {
140 CreateAutoImplementedProperty(builder, dataReader.GetName(i),
typeof(
int?
));
141 }
142 else
143 {
144 CreateAutoImplementedProperty(builder, dataReader.GetName(i), dataReader.GetFieldType(i));
145 }
146 }
147
148 #endregion
149
150 dataReader.Close();
151 dataReader.Dispose();
152 cmd.Dispose();
153 conn.Close();
154 conn.Dispose();
155
156 Type returnType =
builder.CreateType();
157
158 if (parameters !=
null)
159 {
160 var result=
db.SqlQuery(returnType, sql, parameters);
161 //解决循环调用的报错问题
162 cmd.Parameters.Clear();
163 return result;
164 }
165 else
166 {
167 return db.SqlQuery(returnType, sql);
168 }
169 }
170
171 /// <summary>
172 /// 动态创建程序集 ZhangQC
173 /// </summary>
174 /// <param name="assemblyName"></param>
175 /// <param name="moduleName"></param>
176 /// <param name="typeName"></param>
177 /// <returns></returns>
178 public static TypeBuilder CreateTypeBuilder(
string assemblyName,
string moduleName,
string typeName)
179 {
180 TypeBuilder typeBuilder =
AppDomain.CurrentDomain.DefineDynamicAssembly(
181 new AssemblyName(assemblyName),
182 AssemblyBuilderAccess.Run).DefineDynamicModule(moduleName).DefineType(typeName,
183 TypeAttributes.Public);
184 typeBuilder.DefineDefaultConstructor(MethodAttributes.Public);
185 return typeBuilder;
186 }
187
188
189 /// <summary>
190 /// 动态创建实体 ZhangQC
191 /// </summary>
192 /// <param name="builder"></param>
193 /// <param name="propertyName"></param>
194 /// <param name="propertyType"></param>
195 public static void CreateAutoImplementedProperty(TypeBuilder builder,
string propertyName, Type propertyType)
196 {
197 const string privateFieldPrefix =
"m_";
198 const string getterPrefix =
"get_";
199 const string setterPrefix =
"set_";
200
201
202
203 // Generate the field.
204 FieldBuilder fieldBuilder =
builder.DefineField(
205 string.Concat(
206 privateFieldPrefix, propertyName),
207 propertyType,
208 FieldAttributes.Private);
209
210 // Generate the property
211 PropertyBuilder propertyBuilder =
builder.DefineProperty(
212 propertyName,
213 System.Reflection.PropertyAttributes.HasDefault,
214 propertyType,
null);
215
216 // Property getter and setter attributes.
217 MethodAttributes propertyMethodAttributes =
MethodAttributes.Public
218 |
MethodAttributes.SpecialName
219 |
MethodAttributes.HideBySig;
220
221 // Define the getter method.
222 MethodBuilder getterMethod =
builder.DefineMethod(
223 string.Concat(
224 getterPrefix, propertyName),
225 propertyMethodAttributes,
226 propertyType,
227 Type.EmptyTypes);
228
229 // Emit the IL code.
230 // ldarg.0
231 // ldfld,_field
232 // ret
233 ILGenerator getterIlCode =
getterMethod.GetILGenerator();
234 getterIlCode.Emit(OpCodes.Ldarg_0);
235 getterIlCode.Emit(OpCodes.Ldfld, fieldBuilder);
236 getterIlCode.Emit(OpCodes.Ret);
237
238 // Define the setter method.
239 MethodBuilder setterMethod =
builder.DefineMethod(
240 string.Concat(setterPrefix, propertyName),
241 propertyMethodAttributes,
242 null,
243 new Type[] { propertyType });
244
245 // Emit the IL code.
246 // ldarg.0
247 // ldarg.1
248 // stfld,_field
249 // ret
250 ILGenerator setterIlCode =
setterMethod.GetILGenerator();
251 setterIlCode.Emit(OpCodes.Ldarg_0);
252 setterIlCode.Emit(OpCodes.Ldarg_1);
253 setterIlCode.Emit(OpCodes.Stfld, fieldBuilder);
254 setterIlCode.Emit(OpCodes.Ret);
255
256 propertyBuilder.SetGetMethod(getterMethod);
257 propertyBuilder.SetSetMethod(setterMethod);
258 }
259 }
执行Sql语句，包括存储过程，可以动态生成返回实体
标签：cas hang 快速 接受 region else 使用 生成 this
