使用MyGeneration 1.3.0.3生成C#代码的脚本
贺桐
%>
//#####################################################################
//作者：jace
//时间：2008-5
//使用环境：MSSQL Server 2005＋．Net 2.0
//使用MyGeneration 1.3.0.3
//建议将四个类分成四个文件存储(分解成单独文...
<%
public class GeneratedTemplate : DotNetScriptTemplate
{
public GeneratedTemplate(ZeusContext context) : base(context) {}
//---------------------------------------------------
// Render() is where you want to write your logic
//---------------------------------------------------
private ITable table;
private bool mainKeyIsAutoStep;
private bool chkDefaultValue;
public override void Render()
{
string databaseName = input["databaseName"].ToString();//数据库名称
string tableName = input["tableName"].ToString();//选择操作的表名
IDatabase database = MyMeta.Databases[databaseName];
table = database.Tables[tableName];
string mainKey=input["Columns"].ToString();//选择的主键列名称
string myNameSpace=input["txtNamespace"].ToString();//命名空间名称
string myClassName=table.Alias;//类名
//mainKeyIsAutoStep=(bool)input["chkBox"];//在界面中加入＂主键是否自动增长？＂的标志来处理生成的主键相关代码
mainKeyIsAutoStep=GetMainKeyColumnState(mainKey);//取得主键的默认值状态
chkDefaultValue=(bool)input["chkBox_defaultvalue"];//在新增/更新时是否路过默认值的处理 int自增长,getdate()
%>
//#####################################################################
//作者：jace
//时间：2008-5
//使用环境：MSSQL Server 2005＋．Net 2.0
//使用MyGeneration 1.3.0.3
//建议将四个类分成四个文件存储(分解成单独文件后,请将相关引用的注释去掉)
//#####################################################################
#region 实体类对象-数据层
using System;//Model
using System.Data;//DAL
using System.Text;//DAL
using System.Data.SqlClient;//DAL
//通用D类
//using System;
using System.Collections;
using System.Collections.Specialized;
//using System.Data;
//using System.Data.SqlClient;
using System.Configuration;
namespace <%= myNameSpace%>.Model
{
public class <%= myClassName %>
{
#region 私有字段<%
output.write("rn");
foreach (IColumn column in table.Columns)
{
string name = column.Alias;
output.write(" private "+column.LanguageType +" _"+name+";"+"//"+column.Description+"rn");
}%>
#endregion
public <%= myClassName %>()
{
//构造函数-设置私有字段默认值<%
foreach(IColumn column in table.Columns)
{
string fieldName = " _"+column.Alias;
switch(column.LanguageType)
{
default:%>
<%= fieldName %> = null; //<%=column.Description%><%
break;
case "string":%>
<%= fieldName %> = String.Empty; //<%=column.Description%><%
break;
case "DateTime":%>
<%= fieldName %> = DateTime.MaxValue; //<%=column.Description%>
//DateTime.MinValue此常数的值等效于 0001 年 1 月 1 日 00:00:00.0000000会造成SQL溢出
//DateTime.MaxValue此常数的值等效于 9999 年 12 月 31 日 23:59:59.9999999，恰好在 10000 年 1 月 1 日 00:00:00 之前一个 100 毫微秒刻度处<%
break;
case "bool":%>
<%= fieldName %> = false; //<%=column.Description%><%
break;
case "decimal":
case "short":
case "int":
case "long":%>
<%= fieldName %> = 0; //<%=column.Description%><%
break;
}
}%>
}
#region 实体类对象<%= myClassName %>
<%
foreach (IColumn column in table.Columns)
{
string name = column.Alias;
output.write(" /// <summary>");
output.write("rn /// "+ column.Description);
output.write("rn /// </summary>");
%>
public <%=column.LanguageType+" "+ column.Alias%>
{
set {<%switch(column.LanguageType)
{
default:
break;
case "byte":%>
if( value.Length > <%= column.CharacterMaxLength.ToString() %>)
throw new ArgumentOutOfRangeException("超过 <%= column.Alias %> 字段长度定义:", value, value.ToString());<%
break;
case "string":%>
if( value.Length > <%= column.CharacterMaxLength.ToString() %>)
throw new ArgumentOutOfRangeException("超过 <%= column.Alias %> 字段长度定义:", value, value.ToString());<%
break;
}%>
<%="_"+column.Alias%> = value;
}
get { return <%="_"+column.Alias%>; }
}
<%
}
%>
#endregion
}
}
#endregion
//#########################################################################################################
#region 数据访问层 DAL ----与实体类有关系,实际使用中可以分成两个不同的CS类文件来保存
//using System;//使用不同的文件时要引用此命名空间
//using System.Data;
//using System.Text;
//using System.Data.SqlClient;
//using jace.DbHelperSQL;//数据层的基础操作类
namespace <%= myNameSpace %>.DAL
{
/// <summary>
/// 数据访问类<%= myClassName %> ,本类应该通过BLL调用
/// </summary>
partial class <%= myClassName %>
{
public <%= myClassName %>()
{
//构造函数
}
#region 成员方法<%= myClassName %>
/// <summary>
/// 是否存在主键记录
/// </summary>
public bool Exists(<%=GetColumnCSType(mainKey)+" "+mainKey%>)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(1) from <%= table.Alias %>");
strSql.Append(" where <% output.write(mainKey +"=@"+mainKey);%>");
SqlParameter[] parameters = {
new SqlParameter(<%writeParameterFormat(mainKey);%>)
};
parameters[0].Value = <%output.write(mainKey);%>;
return DbHelperSQL.Exists(strSql.ToString(), parameters);
}
/// <summary>
/// 取得指定条件的记录数
/// </summary>
public int GetRecCount(string where)
{
return DbHelperSQL.GetCount("1","<%= table.Alias %>",where);
}
/// <summary>
/// 返回表中指定字段的数组
/// </summary>
public System.Collections.ArrayList GetOneFieldToArray(string fieldName)
{
System.Collections.ArrayList arrayList = new System.Collections.ArrayList();
try
{
string strSelect=@"Select "+fieldName+@" From <%= table.Alias %>";
{
using (SqlDataReader dr = DbHelperSQL.ExecuteReader(strSelect))
{
while (dr.Read())
{
arrayList.Add(dr.GetValue(0));//因为这个函数是为了得到一个字段的全部内容，而不是多个
}
dr.Close();
}
}
}
catch
{
return arrayList;
}
return arrayList;
}
/// <summary>
/// 增加一条数据,参数是对应jace Model生成的实体对象类
/// </summary>
public void Add(<% output.write(myNameSpace+".Model."+myClassName);%> model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into <% output.write(myClassName);%>(");
strSql.Append("<%= getAllFields(mainKey,false)%>)");
strSql.Append(" values (");
strSql.Append("<%= getAllFields(mainKey,true)%>)");
SqlParameter[] parameters = {
<%=getAllFieldsParameter(mainKey)%>
};
<%=getAllFieldsValues(mainKey)%>
DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
}
/// <summary>
/// 增加多条数据,参数是对应jace Model生成的实体对象类
/// </summary>
public void Add(<% output.write(myNameSpace+".Model."+myClassName);%>[] models)
{
if (models.Length <= 0)
return;
string[] sqls = new string[1];
object[] paras = new object[models.Length];
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into <% output.write(myClassName);%>(");
strSql.Append("<%= getAllFields(mainKey,false)%>)");
strSql.Append(" values (");
strSql.Append("<%= getAllFields(mainKey,true)%>)");
for (int i = 0; i < models.Length; i++)
{
<% output.write(myNameSpace+".Model."+myClassName);%> model = models[i];
SqlParameter[] parameters = {
<%=getAllFieldsParameter(mainKey)%>
};
<%=getAllFieldsValues(mainKey)%>
paras[i] = parameters;
}
sqls[0] = strSql.ToString();
DbHelperSQL.ExecuteSqlTran(sqls, paras);
}
/// <summary>
/// 更新一条数据,参数是对应jace Model生成的实体对象类
/// </summary>
public int Update(<% output.write(myNameSpace+".Model."+myClassName);%> model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update <% output.write(myClassName);%> set ");
<%getSQLAddpendUpdate(mainKey);%>
SqlParameter[] parameters = {
<%=getAllFieldsParameterUPdate(mainKey)%>
};
<%=getAllFieldsValuesUpdate(mainKey)%>
return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
}
/// <summary>
/// 更新多条数据,参数是对应jace Model生成的实体对象类数组
/// </summary>
public int Update(<% output.write(myNameSpace+".Model."+myClassName);%>[] models)
{
if (models.Length <= 0)
return 0;
string[] sqls = new string[1];
object[] paras = new object[models.Length];
StringBuilder strSql = new StringBuilder();
strSql.Append("update <% output.write(myClassName);%> set ");
<%getSQLAddpendUpdate(mainKey);%>
for (int i = 0; i < models.Length; i++)
{
<% output.write(myNameSpace+".Model."+myClassName);%> model = models[i];
SqlParameter[] parameters = {
<%=getAllFieldsParameterUPdate(mainKey)%>
};
<%=getAllFieldsValuesUpdate(mainKey)%>
paras[i] = parameters;
}
sqls[0] = strSql.ToString();
return DbHelperSQL.ExecuteSqlTran(sqls, paras);
}
/// <summary>
/// 删除一条数据,参数是主键
/// </summary>
public void Delete(<%=GetColumnCSType(mainKey)+" "+mainKey%>)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from <% output.write(myClassName);%> ");
strSql.Append(" where <% output.write(mainKey +"=@"+mainKey);%>");
SqlParameter[] parameters = {
new SqlParameter(<%writeParameterFormat(mainKey);%>)
};
parameters[0].Value = <%output.write(mainKey);%>;
DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
}
/// <summary>
/// 删除多条数据,参数是主键数组
/// </summary>
public void Delete(<%=GetColumnCSType(mainKey)+"[] "+mainKey%>s)
{
if (<%=mainKey%>s.Length <= 0)
return;
string[] sqls = new string[1];
object[] paras = new object[<%=mainKey%>s.Length];
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from <% output.write(myClassName);%> ");
strSql.Append(" where <% output.write(mainKey +"=@"+mainKey);%>");
for (int i = 0; i < <%=mainKey%>s.Length; i++)
{
SqlParameter[] parameters = {
new SqlParameter(<%writeParameterFormat(mainKey);%>)
};
parameters[0].Value = <%output.write(mainKey);%>s[i];
paras[i] = parameters;
}
sqls[0] = strSql.ToString();
DbHelperSQL.ExecuteSqlTran(sqls, paras);
}
/// <summary>
/// 获得数据列表,select * from table 不要写where，直接写条件就行了，可以为""
/// </summary>
public DataSet GetDataSet(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from <% output.write(myClassName);%> ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
strSql.Append(" order by <%output.write(mainKey);%> ");
return DbHelperSQL.Query(strSql.ToString());
}
/// <summary>
/// 获得数据列表,select * from table 不要写where，直接写条件就行了，可以为""
/// </summary>
public DataTable GetDataTable(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from <% output.write(myClassName);%> ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
strSql.Append(" order by <%output.write(mainKey);%> ");
return DbHelperSQL.QueryTable(strSql.ToString());
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public <% output.write(myNameSpace+".Model."+myClassName);%> GetModel(<%=GetColumnCSType(mainKey)+" "+mainKey%>)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from <% output.write(myClassName);%> ");
strSql.Append(" where <% output.write(mainKey +"=@"+mainKey);%>");
SqlParameter[] parameters = {
new SqlParameter(<%writeParameterFormat(mainKey);%>)};
parameters[0].Value = <%output.write(mainKey);%>;
<% output.write(myNameSpace+".Model."+myClassName);%> model=new <% output.write(myNameSpace+".Model."+myClassName);%>();
DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
model.<%output.write(mainKey);%> = <%output.write(mainKey);%>;
if (ds.Tables[0].Rows.Count > 0)
{
<%=getAllFieldsValuesToModel(mainKey)%>
return model;
}
else
{
return null;
}
}
#endregion
}
}
#endregion
//#########################################################################################################
#region 业务逻辑层 BLL
//using System;//使用不同的文件时要引用此命名空间
//using System.Text;
//using System.Data;
namespace <%= myNameSpace %>.BLL
{
/// <summary>
/// 数据访问类<%= myClassName %>
/// </summary>
public class <%= myClassName %>
{
<%=myNameSpace%>.DAL.<%=myClassName%> dal=new <%=myNameSpace%>.DAL.<%=myClassName%>();
public <%= myClassName %>()
{
//构造函数
}
#region 成员方法<%= myClassName %>
/// <summary>
/// 是否存在主键记录
/// </summary>
public bool Exists(<%=GetColumnCSType(mainKey)+" "+mainKey%>)
{
return dal.Exists(<%=mainKey%>);
}
/// <summary>
/// 取得指定条件的记录数
/// </summary>
public int GetRecCount(string where)
{
return dal.GetRecCount(where);
}
/// <summary>
/// 返回表中指定字段的数组
/// </summary>
public System.Collections.ArrayList GetOneFieldToArray(string fieldName)
{
return dal.GetOneFieldToArray(fieldName);
}
/// <summary>
/// 增加一条数据,参数是对应jace Model生成的实体对象类
/// </summary>
public void Add(<% output.write(myNameSpace+".Model."+myClassName);%> model)
{
dal.Add(model);
}
/// <summary>
/// 增加多条数据,参数是对应jace Model生成的实体对象类
/// </summary>
public void Add(<% output.write(myNameSpace+".Model."+myClassName);%>[] models)
{
dal.Add(models);
}
/// <summary>
/// 更新一条数据,参数是对应jace Model生成的实体对象类
/// </summary>
public int Update(<% output.write(myNameSpace+".Model."+myClassName);%> model)
{
return dal.Update(model);
}
/// <summary>
/// 更新多条数据,参数是对应jace Model生成的实体对象类数组
/// </summary>
public int Update(<% output.write(myNameSpace+".Model."+myClassName);%>[] models)
{
return dal.Update(models);
}
/// <summary>
/// 删除一条数据,参数是主键
/// </summary>
public void Delete(<%=GetColumnCSType(mainKey)+" "+mainKey%>)
{
dal.Delete(<%=mainKey%>);
}
/// <summary>
/// 删除多条数据,参数是主键数组
/// </summary>
public void Delete(<%=GetColumnCSType(mainKey)+"[] "+mainKey%>s)
{
dal.Delete(<%=mainKey%>s);
}
/// <summary>
/// 获得数据列表,select * from table 不要写where，直接写条件就行了，可以为""
/// </summary>
public DataSet GetDataSet(string strWhere)
{
return dal.GetDataSet(strWhere);
}
/// <summary>
/// 获得数据列表,select * from table 不要写where，直接写条件就行了，可以为""
/// </summary>
public DataTable GetDataTable(string strWhere)
{
return dal.GetDataTable(strWhere);
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public <% output.write(myNameSpace+".Model."+myClassName);%> GetModel(<%=GetColumnCSType(mainKey)+" "+mainKey%>)
{
return dal.GetModel(<%=mainKey%>);
}
#endregion
}
}
#endregion
//#########################################################################################################
#region 通用数据层类
//using System;
//using System.Collections;
//using System.Collections.Specialized;
//using System.Data;
//using System.Data.SqlClient;
//using System.Configuration;
namespace <%= myNameSpace%>
{
/// <summary>
/// Copyright (C) 2004-2008 LiTianPing
/// 数据访问基础类(基于SQLServer)
/// 用户可以修改满足自己项目的需要。
/// jace 2007年3月7日10时48分 注:在这个类中,因为其连接串是从配置文件中读取(连接串名称"myConnection")
/// 所以在通用性上不行,要求调用者有此源码于工程中,或将配置文件中加入"myConnection"串名.
/// </summary>
public abstract class DbHelperSQL
{
//数据库连接字符串(web.config来配置)
//<add key="ConnectionString" value="server=127.0.0.1;database=DATABASE;uid=sa;pwd=" />
//protected static string connectionString;
//看实际要求来修改连接串
//protected static string connectionString =@"server=WORKGROU-JTR8X7JACE;database=master;uid=sa;pwd=skydragon;";
protected static string connectionString = @"server=zkd-szkd;database=test;uid=sa;pwd=12345;";
//protected static string connectionString = ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString;
//protected static string connectionString = comm.getPos_1_WEBConnectionString();
//public DbHelperSQL(string strSqlConnectionString)
//{
// connectionString = strSqlConnectionString;
//}
public DbHelperSQL()
{
//connectionString = this.setSqlConnectionString();
}
/// <summary>
/// 重写这个方法,以设置一个连接字符串
/// </summary>
/// <returns></returns>
//public virtual string setSqlConnectionString()
//{
// return ConfigurationManager.ConnectionStrings["SQLConnString"].ConnectionString;
//}
#region 公用方法
/// <summary>
/// 取得指定表中特定条件下记录的条数
/// </summary>
/// <param name="FieldName">字段名,意义不大</param>
/// <param name="TableName">表名</param>
/// <param name="where">条件,要包含where关键字</param>
/// <returns>返回一个整数,记录条数</returns>
public static int GetCount(string FieldName, string TableName, string where)
{
string strsql = "select count(" + FieldName + ") from " + TableName + " ";
if (where.Length > 0)
{
strsql += where;
}
object obj = GetSingle(strsql);
if (obj == null)
{
return 0;
}
else
{
return int.Parse(obj.ToString());
}
}
/// <summary>
/// 得指定指定表中数字字段的的最大值+1
/// </summary>
/// <param name="FieldName">字段名</param>
/// <param name="TableName">表名</param>
/// <returns>返回指定指定表中数字字段的的最大值+1</returns>
public static int GetMaxID(string FieldName, string TableName)
{
string strsql = "select max(" + FieldName + ")+1 from " + TableName;
object obj = GetSingle(strsql);
if (obj == null)
{
return 1;
}
else
{
return int.Parse(obj.ToString());
}
}
/// <summary>
/// 是否存在指定记录
/// </summary>
/// <param name="strSql">带参数形式的SQL查询语句</param>
/// <param name="cmdParms">各参数值</param>
/// <returns></returns>
public static bool Exists(string strSql, params SqlParameter[] cmdParms)
{
object obj = GetSingle(strSql, cmdParms);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
#endregion
#region 执行简单SQL语句
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
connection.Close();
throw new Exception(E.Message);
}
}
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static void ExecuteSqlTran(ArrayList SQLStringList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
SqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (System.Data.SqlClient.SqlException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
}
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString, string content)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(SQLString, connection);
System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(strSQL, connection);
System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
myParameter.Value = fs;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果（object）,第一条记录的第一个字段值</returns>
public static object GetSingle(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw new Exception(e.Message);
}
}
}
}
/// <summary>
/// 执行查询语句，返回SqlDataReader
/// </summary>
/// <param name="strSQL">查询语句</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader ExecuteReader(string strSQL)
{
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(strSQL, connection);
try
{
connection.Open();
SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return myReader;
}
catch (System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
}
/// <summary>
/// 执行查询语句，返回DataTable
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataTable</returns>
public static DataTable QueryTable(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataTable dt = new DataTable();
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
command.Fill(dt);
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return dt;
}
}
#endregion
#region 执行带参数的SQL语句
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
int rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
}
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。(每条SQL语句都不应该一样)
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
public static int ExecuteSqlTran(Hashtable SQLStringList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
int val=0;
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
try
{
//循环
foreach (DictionaryEntry myDE in SQLStringList)
{
string cmdText = myDE.Key.ToString();
SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
val += cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
trans.Commit();
return val;
}
catch//(System.Exception ex)
{
trans.Rollback();
//throw ex;
return 0;
}
}
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。(SQL语句可以一样)
/// </summary>
/// <param name="SQLStringList">SQL语句数组</param>
/// <param name="SQLParameters">SQL语句各数组元素对应的参数对象数组,SQLParameters将转换成SqlParameter[]）</param>
public static int ExecuteSqlTran(string[] SQLStringList, object[] SQLParameters)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
int val=0;
try
{
SqlCommand cmd = new SqlCommand();
string lastSql = SQLStringList[SQLStringList.Length - 1];
string cmdText = "";
for (int i = 0; i < SQLParameters.Length; i++)
{
if (i >= SQLStringList.Length)
{
cmdText = lastSql;
}
else
{
cmdText = SQLStringList[i];
}
SqlParameter[] cmdParms = (SqlParameter[])SQLParameters[i];//myDE.Value;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
val += cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
trans.Commit();
return val;
}
catch //(System.Exception ex)
{
trans.Rollback();
//throw ex;
return 0;
}
}
}
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果（object）,第一行第一列的值</returns>
public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
object obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}
}
}
}
/// <summary>
/// 执行查询语句，返回SqlDataReader
/// </summary>
/// <param name="strSQL">查询语句</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
{
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return myReader;
}
catch (System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
try
{
da.Fill(ds, "ds");
cmd.Parameters.Clear();
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
}
}
/// <summary>
/// 执行查询语句，返回DataTable
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataTable</returns>
public static DataTable QueryTable(string SQLString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
try
{
da.Fill(dt);
cmd.Parameters.Clear();
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return dt;
}
}
}
/// <summary>
/// 构建一个带参数的命令对象
/// </summary>
/// <param name="cmd">命令对象</param>
/// <param name="conn">连接对象</param>
/// <param name="trans">事务对象</param>
/// <param name="cmdText">SQL命令文本</param>
/// <param name="cmdParms">SQL命令参数</param>
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;//cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
#endregion
#region 存储过程操作
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
{
SqlConnection connection = new SqlConnection(connectionString);
SqlDataReader returnReader;
try{
connection.Open();
SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
return returnReader;
}
catch
{
return null;
}
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</param>
/// <returns>DataSet</returns>
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
sqlDA.Fill(dataSet, tableName);
connection.Close();
return dataSet;
}
}
/// <summary>
/// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlCommand</returns>
private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
return command;
}
/// <summary>
/// 执行存储过程，返回影响的行数
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="rowsAffected">影响的行数</param>
/// <returns></returns>
public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
int result;
connection.Open();
SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
rowsAffected = command.ExecuteNonQuery();
result = (int)command.Parameters["ReturnValue"].Value;
//Connection.Close();
return result;
}
}
/// <summary>
/// 创建 SqlCommand 对象实例(用来返回一个整数值)
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlCommand 对象实例</returns>
private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
command.Parameters.Add(new SqlParameter("ReturnValue",
SqlDbType.Int, 4, ParameterDirection.ReturnValue,
false, 0, 0, string.Empty, DataRowVersion.Default, null));
return command;
}
#endregion
}
}
#endregion
<%
}//Render结束===生成代码样式的部分结束
//取得model的对像串,如：model.MD5ICCard = ds.Tables[0].Rows[0]["MD5ICCard"].ToString();
private string getAllFieldsValuesToModel(string mainKey)
{
string str="";
foreach(IColumn column in table.Columns)
{
if(column.Alias==mainKey&&mainKeyIsAutoStep)
{
//当主键是自动增长的int型数据，排除主键
}
else
{
if(str!="")
{
str+=" ";
}
//str+="model."+column.Alias+"= ds.Tables[0].Rows[0][""+column.Alias+""].ToString();"; //"+column.Description;
str+="model."+column.Alias+"="+GetCSConvertType(column.DataTypeName)+"(ds.Tables[0].Rows[0][""+column.Alias+""]);"; //"+column.Description;
str+="rn";
}
}
return str;
}//getAllFieldsValuesToModel结束
//生成所有字段的UPDATA语法SET参数格式＂field=@field＂
private void getSQLAddpendUpdate(string mainKey)
{
string str="",flag="";
string strAppBegin="strSql.Append("";
string strAppEnd="");";
string strWhere="";
foreach(IColumn column in table.Columns)
{
if(column.Alias==mainKey)
{
//当是主键时，要使用where
strWhere="strSql.Append(" where "+column.Alias+"=@"+column.Alias+"");";
}
else
{
//-此处做默认值处理 ,为真则continue;
if(GetColumnState(column))
continue;
if(str!="")
{
flag=",";
}
str+=strAppBegin+flag+column.Alias+"=@"+column.Alias+strAppEnd;
str+="rn ";
}
}
str+=strWhere;
output.write(str);
}//getSQLAddpendUpdate结束
//生成UPdate所有参数的赋值语句 parameters[17].Value = model.sendTime;
private string getAllFieldsValuesUpdate(string mainKey)
{
string str="";
int i=0;
foreach(IColumn column in table.Columns)
{
if(column.Alias!=mainKey)
{
//-此处做默认值处理 ,为真则continue;
if(chkDefaultValue)
{
if(GetColumnState(column))
continue;
}
}
if(str!="")
{
str+=" ";//,
}
str+="parameters["+i.ToString()+"].Value = model."+column.Alias+"; //"+column.Description;
str+="rn";
i++;
}
return str;
}//getAllFieldsValuesUpdate结束
//返回Update参数对象
private string getAllFieldsParameterUPdate(string mainKey)
{
string str="";
foreach(IColumn column in table.Columns)
{
if(column.Alias!=mainKey)
{
//-此处做默认值处理 ,为真则continue;
if(chkDefaultValue)
{
if(GetColumnState(column))
continue;
}
}
if(str!="")
{
str+=" ,";
}
str+="new SqlParameter("@"+column.Alias+"","+column.DbTargetType+","+column.CharacterMaxLength.ToString()+")//"+column.Description;
str+="rn";
}
return str;
}//getAllFieldsParameterUPdate结束
//生成所有参数的赋值语句 parameters[17].Value = model.sendTime;
private string getAllFieldsValues(string mainKey)
{
string str="";
int i=0;
foreach(IColumn column in table.Columns)
{
if(column.Alias==mainKey&&mainKeyIsAutoStep)
{
//当主键是自动增长的int型数据，排除主键
}
else
{
//-此处做默认值处理 ,为真则continue;
if(chkDefaultValue)
{
if(GetColumnState(column))
continue;
}
if(str!="")
{
str+=" ";//,
}
str+="parameters["+i.ToString()+"].Value = model."+column.Alias+"; //"+column.Description;
str+="rn";
i++;
}
}
return str;
}//getAllFieldsValues结束
//返回参数对象
private string getAllFieldsParameter(string mainKey)
{
string str="";
foreach(IColumn column in table.Columns)
{
if(column.Alias==mainKey&&mainKeyIsAutoStep)
{
//当主键是自动增长的int型数据，排除主键
}
else
{
//-此处做默认值处理 ,为真则continue;
if(chkDefaultValue)
{
if(GetColumnState(column))
continue;
}
if(str!="")
{
str+=" ,";
}
str+="new SqlParameter("@"+column.Alias+"","+column.DbTargetType+","+column.CharacterMaxLength.ToString()+")//"+column.Description;
str+="rn";
}
}
return str;
}//getAllFieldsParameter结束
//返回SQL语句中insert的字段列表部分,不包括主键(仅当主键是int自动增长时才这样)
//mainKey:主键
//isParameters:是否参数,如果true则返回@xxx
private string getAllFields(string mainKey,bool isParameters)
{
string fields="";
foreach (IColumn column in table.Columns)
{
if(column.Alias==mainKey&&mainKeyIsAutoStep)
{
//当主键是自动增长的int型数据，排除主键
}
else
{
//-此处做默认值处理 ,为真则continue;
if(chkDefaultValue)
{
if(GetColumnState(column))
continue;
}
if(fields!="")
{
fields+=",";
}
if(isParameters)//返回符合字段参数的格式
{
fields+="@"+column.Alias;
}
else
{
fields+=column.Alias;//返回正常的字段格式
}
}
}
return fields;
}//getAllFields结束
//返回列的参数格式,如:"@miMa", SqlDbType.VarChar,250
private void writeParameterFormat(string columnName)
{
string sqldbtype="";
int dblen=0;
GetColumnsCSDBtype(columnName,out sqldbtype,out dblen);
string res=""@"+columnName+"","+sqldbtype+","+dblen.ToString();
output.write(res);
//return res;
}//writeParameterFormat结束
//取得指定列的DBtype数据类型
private void GetColumnsCSDBtype(string columnName,out string SqlDbType,out int lenght)
{
SqlDbType="";
lenght=0;
foreach (IColumn column in table.Columns)
{
if(column.Alias==columnName)
{
SqlDbType=column.DbTargetType;
lenght=column.CharacterMaxLength ;
}
}
}//GetColumnsCSDBtype
//取得指定列的C#数据类型
private string GetColumnCSType(string columnName)
{
foreach (IColumn column in table.Columns)
{
if(column.Alias==columnName)
{
return column.LanguageType;
//return GetCSType(column.DataTypeName);
//return column.DataTypeName;
}
}
return "";//这是错误的
}//GetColumnCSType结束
//查看一列是否是自动增长列,是否默认值为getdate()
private bool GetColumnState(IColumn column)
{
if(column.IsAutoKey)//是自动增长列
{
return true;
}
if(column.HasDefault)//有默认值
{
if(column.Default=="(getdate())")
{
return true;
}
//output.writeln(column.Default);
}
return false;
}
//查看一列是否是自动增长列,是否默认值为getdate()
private bool GetMainKeyColumnState(string mainKey)
{
foreach (IColumn column in table.Columns)
{
if(column.Alias==mainKey)
{
if(column.IsAutoKey)//是自动增长列
{
return true;
}
if(column.HasDefault)//有默认值
{
if(column.Default=="(getdate())")
{
return true;
}
//output.writeln(column.Default);
}
}
}
return false;
}
//将SQL2005数据库中的类型转换成C#中的数据转换类型
private string GetCSConvertType(string datetype)
{
switch (datetype.ToLower())
{
case "bit":
return "Convert.ToBoolean";//"bool";
case "decimal":
return "Convert.ToDecimal";//"decimal";
case "float":
return "Convert.ToDouble";//"double";
case "numeric":
return "Convert.ToDecimal";//"decimal";
case "money":
return "Convert.ToDecimal";//"decimal";
case "smallmoney":
return "Convert.ToDecimal";//"decimal";
case "real":
return "Convert.ToSingle";//"float";
case "tinyint":
return "Convert.ToByte";//"byte";
case "smallint":
return "Convert.ToInt32";//"short";
case "int":
return "Convert.ToInt32";//"int";
case "bigint":
return "Convert.ToInt32";//"long";
case "smalldatetime":
return "Convert.ToDateTime";//"DateTime";
case "datetime":
return "Convert.ToDateTime";//"DateTime";
case "varchar":
return "Convert.ToString";//"string";
case "char":
return "Convert.ToString";//"string";
case "nvarchar":
return "Convert.ToString";//"string";
case "nchar":
return "Convert.ToString";//"string";
case "text":
return "Convert.ToString";//"String";
case "xml":
return "Convert.ToString";//"string";
case "varbinary":
return "Convert.ToString";//"byte[]";
case "binary":
return "Convert.ToString";//"object";
case "image":
return "Convert.ToString";//"byte[]";
case "timestamp":
return "Convert.ToString";//"byte[]";
case "uniqueidentifier":
return "Convert.ToString";//"Guid";
case "sql_variant":
return "Convert.ToString";//"object";
default:
类似资料：