重复的数据库操作,无休止的写存储过程参数列表,不停的对数据库的内容进行映射,无聊而且又容易出错,你是否已经厌倦了这样写代码。
其实这些代码的格式都几乎是固定的,完全可以自动生成。
我最近写了这么个小东东,希望能给大家一点帮助
思路路下
用OleDbConnection的GetOleDbSchemaTable方法获取数据库的机构存放在自己定义的对象中。
//根据数据库获得数据库映射。
public Schema GetSchema()
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
Schema schema=new Schema();
int i=0;
con.Open();
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
DataTable schemaTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[]
{null, null, null, "TABLE"});
foreach (DataRow dr in schemaTable.Rows)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
//表名
schema.Tables.Add(new SchemaTable(dr["TABLE_NAME"].ToString()));
//字段名
DataTable columnTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
new object[]
{null, null, ((SchemaTable)schema.Tables[i]).TableName,null});
foreach(DataRow dr2 in columnTable.Rows)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
int j=0;
SchemaColumn col=new SchemaColumn(dr2["COLUMN_NAME"].ToString(),((OleDbType)dr2["DATA_TYPE"]));
if (dr2["CHARACTER_MAXIMUM_LENGTH"]!=DBNull.Value)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
col.ColumnSize=Convert.ToInt32(dr2["CHARACTER_MAXIMUM_LENGTH"]);
}
col.IsNullAble=(bool)dr2["IS_NULLABLE"];
((SchemaTable)schema.Tables[i]).Columns.Add(col);
j++;
}
i++;
}
DataTable pkTable=con.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, null);
foreach (DataRow dr in pkTable.Rows)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
for (int x=0;x<schema.Tables.Count;x++)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (schema.Tables[x].TableName==dr["TABLE_NAME"].ToString())
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
for(int y=0;y<schema.Tables[x].Columns.Count;y++)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (schema.Tables[x].Columns[y].ColumnName==dr["COLUMN_NAME"].ToString())
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
schema.Tables[x].Columns[y].IsPK=true;
break;
}
}
break;
}
}
}
![](/Images/OutliningIndicators/InBlock.gif)
con.Close();
return schema;
}
可能有人会问为什么使用OleDbConnection的GetOleDbSchemaTable方法,而不使用select * from sysobjects查询对象,我在这里考虑的是可以支持多种数据库平台,因为GetOleDbSchemaTable可以获得多种数据库的结构可以说是一劳永益,但这样做也会带来问题,因为得到的结构会使用同意的OleDbType来表示,在使用时必须转换到对应的SqlDBType和OracleDbType等,转换时类型可能不正确,在这里可以做了一些限制来保证转换的正确,
case OleDbType.WChar:
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
if (size<50)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return "SqlDbType.NChar";
}
else if (size<1000)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return "SqlDbType.NVarChar";
}
else
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return "SqlDbType.NText";
}
}
例如SqlServer数据库中的NChar,NVarchar和NText都被映射成OleDbType.WChar,所以我做出三个字段大小的一个限制来确保映射的正确性。
用类似如下代码生成相应的代码
public override string BuildInsertFun(SchemaTable table)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
StringBuilder sb = new StringBuilder() ;
sb.Append("public bool Add() ") ;
sb.Append("{ ") ;
sb.Append(" ") ;
string strInsert = ""insert into " + table.TableName + "(";
string strFiledList = "" ;
string strParamList = "" ;
for(int i=0;i<table.Columns.Count;i++)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string strName = table.Columns[i].ColumnName ;
strFiledList = strFiledList + strName + ", " ;
strParamList = strParamList + "@" + strName + ", " ;
}
strFiledList = strFiledList.Trim().TrimEnd(',') ;
strParamList = strParamList.Trim().TrimEnd(',') ;
sb.Append(" string strSql = " + strInsert + strFiledList+ ")" ") ;
sb.Append(" +"values(" + strParamList+ ")" ; ") ;
sb.Append(" ") ;
sb.Append(" SqlCommand command = new SqlCommand(strSql,conn) ; ") ;
sb.Append(" ") ;
sb.Append(helper.BuildParameterString(table,false,true));
![](/Images/OutliningIndicators/InBlock.gif)
sb.Append(helper.AddCatchString()) ;
sb.Append("} ") ;
return sb.ToString();
}
这是一段生成的代码
using System;
using System.Data;
using System.Data.SqlClient;
![](/Images/OutliningIndicators/None.gif)
namespace 命名空间
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
{
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// </summary>
///注释
/// </summary>
public class AdminDB
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
private SqlConnection conn=new SqlConnection();
private SqlCommand command=new SqlCommand();
public AdminDB()
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
conn.ConnectionString=ConStrLib.GetConStr();
}
private string adminID ;
public string AdminID
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return adminID ;
}
set
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
adminID = value ;
}
}
private string passWord ;
public string PassWord
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return passWord ;
}
set
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
passWord = value ;
}
}
public bool Add()
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string strSql = "insert into Admin(AdminID, PassWord)"
+"values(@AdminID, @PassWord)" ;
SqlCommand command = new SqlCommand(strSql,conn) ;
command.Parameters.Add("@AdminID",SqlDbType.Char) ;
command.Parameters["@AdminID"].Value = AdminID ;
command.Parameters.Add("@PassWord",SqlDbType.Char) ;
command.Parameters["@PassWord"].Value = PassWord ;
try
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
conn.Open() ;
command.ExecuteNonQuery() ;
return true ;
}
catch(Exception e)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw(new Exception("Error in the Database"+e.Message)) ;
}
finally
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
conn.Close() ;
}
}
public bool Modify()
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string strSql ="update Admin set AdminID = @AdminID, PassWord = @PassWord "
+ " where AdminID=@AdminID";
SqlCommand command = new SqlCommand(strSql,conn) ;
command.Parameters.Add("@AdminID",SqlDbType.Char) ;
command.Parameters["@AdminID"].Value = AdminID ;
command.Parameters.Add("@PassWord",SqlDbType.Char) ;
command.Parameters["@PassWord"].Value = PassWord ;
try
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
conn.Open() ;
command.ExecuteNonQuery() ;
return true ;
}
catch(Exception e)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw(new Exception("Error in the Database"+e.Message)) ;
}
finally
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
conn.Close() ;
}
}
public bool Delete()
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string strSql ="delete from Admin"
+ " where AdminID=@AdminID";
SqlCommand command = new SqlCommand(strSql,conn) ;
command.Parameters.Add("@AdminID",SqlDbType.Char) ;
command.Parameters["@AdminID"].Value = AdminID ;
try
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
conn.Open() ;
command.ExecuteNonQuery() ;
return true ;
}
catch(Exception e)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw(new Exception("Error in the Database"+e.Message)) ;
}
finally
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
conn.Close() ;
}
}
public DataSet GetAll()
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string strSql ="select * from Admin";
SqlDataAdapter da = new SqlDataAdapter(strSql,conn) ;
DataSet ds = new DataSet() ;
try
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
da.Fill(ds) ;
}
catch(Exception e)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw(new Exception("Error in the Database"+e.Message)) ;
}
finally
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
da.Dispose() ;
}
return ds ;
}
public DataSet GetById()
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string strSql ="select * from Admin"
+ " where AdminID=@AdminID";
SqlDataAdapter da = new SqlDataAdapter(strSql,conn) ;
DataSet ds = new DataSet() ;
da.SelectCommand.Parameters.Add("@AdminID",SqlDbType.Char) ;
da.SelectCommand.Parameters["@AdminID"].Value = AdminID ;
try
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
da.Fill(ds) ;
}
catch(Exception e)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw(new Exception("Error in the Database"+e.Message)) ;
}
finally
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
da.Dispose() ;
}
return ds ;
}
}
}
![](/Images/OutliningIndicators/None.gif)
通过修改代码可以生成不同格式(包括数据操作或映射),不同语言,支持多种数据库的代码。
在支持多数据库时,我使用了工厂,通过不同的连接字符串创建不同的对象。
public CodeMakerFactory CreateCodeMakerFactory(string conStr,CodeLanguageType lanType)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
conStr=conStr.ToUpper();
//Sql
if (conStr.IndexOf("SQLOLEDB",0,conStr.Length-1)>0)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
switch (lanType)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
case CodeLanguageType.CSharp:
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return new CSharpSQLMakerFactory();
}
default:
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw new CodeMakerException("还没做!");
}
}
}
//Oracle
else if(conStr.IndexOf("MSDAORA",0,conStr.Length-1)>0)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw new CodeMakerException("还没做!");
}
else
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw new CodeMakerException("不支持的数据库类型!");
}
}