202309U/YZXYH2017/CommonTool/DynamicSqlQuery.cs

using System;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Reflection;
using System.Reflection.Emit;

namespace CommonTool
{
	public static class DynamicSqlQuery
	{
		public static DbRawSqlQuery DynamicSqlQueryMethod(Database database, string sql, params object[] parameters)
		{
			TypeBuilder builder = CreateTypeBuilder(
				"MyDynamicAssembly", "MyDynamicModule", "MyDynamicType");

			using (System.Data.IDbCommand command = database.Connection.CreateCommand())
			{
				try
				{
					database.Connection.Open();
					command.CommandText = sql;
					command.CommandTimeout = command.Connection.ConnectionTimeout;
					//foreach (var param in parameters)
					//{
					//    command.Parameters.Add(param);
					//}

					using (System.Data.IDataReader reader = command.ExecuteReader())
					{
						var schema = reader.GetSchemaTable();

						if (schema != null)
							foreach (System.Data.DataRow row in schema.Rows)
							{
								string name = (string) row["ColumnName"];
								//var a=row.ItemArray.Select(d=>d.)
								Type type = (Type) row["DataType"];
								if (type != typeof(string) && (bool) row.ItemArray[schema.Columns.IndexOf("AllowDbNull")])
								{
									type = typeof(Nullable<>).MakeGenericType(type);
								}
								CreateAutoImplementedProperty(builder, name, type);
							}
					}
				}
				finally
				{
					database.Connection.Close();
					command.Parameters.Clear();
				}
			}

			Type resultType = builder.CreateType();
			return database.SqlQuery(resultType, sql);

			//return database.SqlQuery(resultType, sql, parameters);
		}

		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;
		}

		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, 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[] { 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);
		}
	}
}