一,CommonDataAccess using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Collections; namespace CommonDataAccessLayer { public class CommonDataAccess { private DataControl datacontrol; public DataControl Datacontrol { get { return datacontrol; } set { datacontrol = value; } } public CommonDataAccess() { datacontrol = new DataControl(); } public DataTable GetData(string TableName,Hashtable hs) { DataTable dt = new DataTable(); datacontrol.GetDbDataAdapter(TableName, hs).Fill(dt); return dt; } public DataTable GetData(string TableName) { return GetData(TableName, null); } public void ModifyDataRow(DataRow row) { row.AcceptChanges(); row.SetModified(); datacontrol.GetDbDataAdapter(row.Table.TableName).Update(row.Table); } public void RemoveDataRow(DataRow row) { row.AcceptChanges(); row.Delete(); datacontrol.GetDbDataAdapter(row.Table.TableName).Update(row.Table); } public void AddDataRow(DataRow row) { row.Table.Rows.Add(row); datacontrol.GetDbDataAdapter(row.Table.TableName).Update(row.Table); } public void SetDataBaseProperty(string providername, string connectionstring) { } public DataTable GetSchema(string TableName) { return datacontrol.GetSchema(TableName); } public bool CheckTableName(string TableName) { return datacontrol.CheckTableName(TableName); } } } 二,DataControl using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.Common; using System.Data; using System.Collections; namespace CommonDataAccessLayer { public class DataControl { private DbConnection dbcon; private DbCommand dbcom; private DbProviderFactory providerfactory; private string providername; private string connectionstring; public DataControl() { providername = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName; connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; providerfactory = DbProviderFactories.GetFactory(providername); } public DataControl(string providername, string connectionstring) { this.providername = providername; this.connectionstring = connectionstring; providerfactory = DbProviderFactories.GetFactory(providername); } public DbConnection GetDbConnection() { if (this.dbcon == null) { this.dbcon = providerfactory.CreateConnection(); this.dbcon.ConnectionString = connectionstring; } return this.dbcon; } public DbCommand GetDbCommand() { GetDbConnection(); this.dbcom = dbcon.CreateCommand(); return this.dbcom; } public DbDataAdapter GetDbDataAdapter(string TableName) { string sql = "SELECT * FROM " + TableName; DbDataAdapter dbadapter = providerfactory.CreateDataAdapter(); if (this.dbcom == null) { GetDbCommand(); } dbcom.CommandText = sql; dbadapter.SelectCommand = dbcom; DbCommandBuilder dbcommandbuilder = providerfactory.CreateCommandBuilder(); dbcommandbuilder.DataAdapter = dbadapter; dbadapter.InsertCommand = dbcommandbuilder.GetInsertCommand(); dbadapter.DeleteCommand = dbcommandbuilder.GetDeleteCommand(); dbadapter.UpdateCommand = dbcommandbuilder.GetUpdateCommand(); return dbadapter; } public DbDataAdapter GetDbDataAdapter(string TableName, Hashtable hs) { string sql = "SELECT * FROM " + TableName + " WHERE 1=1 "; if (hs != null) { IDictionaryEnumerator ide = hs.GetEnumerator(); while (ide.MoveNext()) { sql += " AND " + ide.Key + "=" + ide.Value ; } } if (this.dbcom == null) { GetDbCommand(); } DbDataAdapter dbadapter = providerfactory.CreateDataAdapter(); dbcom.CommandText = sql; dbadapter.SelectCommand = dbcom; return dbadapter; } public DataTable GetSchema(string TableName) { DataTable dt = new DataTable(); this.GetDbDataAdapter(TableName).FillSchema(dt, SchemaType.Source); return dt; } public bool CheckTableName(string TableName) { GetDbCommand(); string sql = @"SELECT COUNT(*) FROM SYSOBJECTS WHERE NAME = "+"'"+TableName+"'"; this.dbcon.Open(); this.dbcom.CommandText = sql; int count = (int)this.dbcom.ExecuteScalar(); return count == 0 ? false : true; } } } 三,IDataConvert using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.CodeDom; using Microsoft.CSharp; using System.IO; namespace CommonDataAccessLayer { public interface IDataConvert { object ConvertDataType(string data); } public class ConvertFactory { static Dictionary<Type, IDataConvert> convertList = new Dictionary<Type,IDataConvert>(); public static IDataConvert GetConvert(Type type) { if (convertList.Keys.Contains(type) == true) { return convertList[type]; } else { System.CodeDom.CodeTypeDeclaration convertClass = new System.CodeDom.CodeTypeDeclaration(type.Name + "Convert"); convertClass.IsClass = true; convertClass.BaseTypes.Add(new System.CodeDom.CodeTypeReference(typeof(IDataConvert))); convertClass.TypeAttributes = System.Reflection.TypeAttributes.Public; System.CodeDom.CodeMemberMethod method = new System.CodeDom.CodeMemberMethod(); method.Name = "ConvertDataType"; method.Attributes = MemberAttributes.Public; method.ReturnType =new CodeTypeReference(typeof(object)); method.Parameters.Add(new CodeParameterDeclarationExpression(new CodeTypeReference(typeof(string)),"data")); var convertVariable = new CodeVariableReferenceExpression("Convert"); var toMethod=new CodeMethodInvokeExpression(convertVariable,"To"+type.Name,new CodeVariableReferenceExpression("data")); method.Statements.Add(new CodeMethodReturnStatement(toMethod)); convertClass.Members.Add(method); System.CodeDom.CodeNamespace nameSpace=new CodeNamespace("CommonDataAccessLayer"); nameSpace.Types.Add(convertClass); StringWriter Writer = new StringWriter(); CodeCompileUnit Unit = new CodeCompileUnit(); Unit.Namespaces.Add(nameSpace); CSharpCodeProvider Provider = new CSharpCodeProvider(); //建立编译参数 Provider.GenerateCodeFromCompileUnit(Unit, Writer, new System.CodeDom.Compiler.CodeGeneratorOptions()); string aaaa= Writer.GetStringBuilder().ToString(); var stream=File.CreateText(@"d:/aa.cs"); stream.Write(aaaa); stream.Close(); System.CodeDom.Compiler.CompilerParameters cp = new System.CodeDom.Compiler.CompilerParameters(new string[] { "System.dll" }, @"d:/aa.cs",false); // cp.GenerateInMemory = true; //获得编译结果 System.CodeDom.Compiler.CompilerResults Result = Provider.CompileAssemblyFromDom(cp, Unit); Console.WriteLine(Result.Errors[0].ErrorText); return null; } } } } 四,Main using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Collections; namespace CommonDataAccessLayer { class Program { static void Main(string[] args) { //string TableName = Console.ReadLine(); //DataControl dc = new DataControl(); //CommonDataAccess com = new CommonDataAccess(); //查询表中所有数据 //DataTable dt = com.GetData(TableName); //根据条件查询 //Hashtable hs = new Hashtable(); //hs.Add("GroupId", 2); //DataTable dt = com.GetData(TableName, hs); string key = ""; while (key != "Q" && key != "q") { Console.Write("请输入你想操作的数据表名:"); string TableName = Console.ReadLine(); DataControl dc = new DataControl(); if (dc.CheckTableName(TableName)) { showData(TableName); string KEY = ""; while (KEY != "Q" && KEY != "q") { Console.WriteLine("请选择一项操作:输入操作前的数字,退出请按'Q'"); Console.WriteLine("一,添加 二,修改 三,删除 四,Q"); KEY = Console.ReadLine(); switch (KEY) { case "1": addData(TableName); break; case "2": modifyData(TableName); break; case "3": removeData(TableName); break; case "Q": break; case "q": break; default: Console.WriteLine("输入无效"); break; } } } else { Console.WriteLine("找不到当前表名,是否继续操作,输入任意键继续,输入'Q'则退出"); } key = Console.ReadLine(); } } public static void addData(string TableName) { Console.WriteLine("请输入新数据:"); CommonDataAccess com = new CommonDataAccess(); DataTable dt = com.GetSchema(TableName); DataRow row = dt.NewRow(); for (int j = 0; j < dt.Columns.Count; j++) { object o = null; while (o == null) { Console.Write(dt.Columns[j].ColumnName + "="); string data = Console.ReadLine(); o = ConvertDataType(data, dt.Columns[j].DataType); } row[j] = o; } com.AddDataRow(row); Console.WriteLine("添加完成"); showData(TableName); } public static void modifyData(string TableName) { Console.Write("请输入需修改的主键列的值:"); string value = Console.ReadLine(); DataControl dc = new DataControl(); DataTable dt = dc.GetSchema(TableName); dc.GetDbDataAdapter(TableName).Fill(dt); DataColumn[] primary = dt.PrimaryKey; string where = primary[0].ColumnName + "=" + value; DataRow[] rows = dt.Select(where); showData(TableName); Console.WriteLine("请输入新数据:"); for (int j = 0; j < dt.Columns.Count; j++) { if (dt.Columns[j].ColumnName != primary[0].ColumnName) { object o = null; while (o == null) { Console.Write(dt.Columns[j].ColumnName + "="); string data = Console.ReadLine(); o = ConvertDataType(data, dt.Columns[j].DataType); } rows[0][j] = o; } } dc.GetDbDataAdapter(TableName).Update(rows); Console.WriteLine("修改完成"); showData(TableName); } public static void removeData(string TableName) { DataControl dc = new DataControl(); DataTable dt = dc.GetSchema(TableName); dc.GetDbDataAdapter(TableName).Fill(dt); string value; Console.Write("输入需删除的主键列的值:"); value = Console.ReadLine(); DataColumn[] primary = dt.PrimaryKey; string where = primary[0].ColumnName + "=" + value; DataRow[] rows = dt.Select(where); foreach (DataRow row in rows) { row.Delete(); } dc.GetDbDataAdapter(TableName).Update(rows); Console.WriteLine("删除完成"); showData(TableName); } public static void showData(string TableName) { DataControl dc = new DataControl(); DataTable dt = new DataTable(); dc.GetDbDataAdapter(TableName).Fill(dt); Console.WriteLine("================================================================================"); if (dt.Rows.Count != 0) { for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { Console.Write(dt.Columns[j].ColumnName + "=" + dt.Rows[i][j].ToString() + "/t"); } Console.WriteLine(); } } Console.WriteLine("================================================================================"); } public static object ConvertDataType(string data, Type type) { try { if (type == typeof(System.Int32)) { return Convert.ToInt32(data); } if (type == typeof(System.String)) { return data; } if (type == typeof(System.Boolean)) { return Convert.ToBoolean(data); } } catch (Exception e) { Console.Write(e.Message); Console.WriteLine("请重新输入"); } //var convertType = typeof(Convert); //var methodInfo = convertType.GetMethod("To" + type.Name,System.Reflection.BindingFlags.Static); //var xxx = methodInfo.Invoke(null,new object[]{data}); //return xxx; //IDataConvert convert = ConvertFactory.GetConvert(type); return null; } } }