下面写一下相对来说不变的地方
SQL语句部分,改成了静态函数的形式。
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using JYK;
namespace JYK.DataAccessLibrary
{
//DataAccessLibrary 数据访问库
public class DAL
{
//查询语句部分
#region 运行查询语句返回记录(DataSet、DataTable、DataRow、String[]、String)
public static DataSet RunSqlDataSet(string sql)
{
string msg = "";
return RunSqlDataSet(sql, out msg);
}
/// <summary>
/// 运行SQL查询语句 返回DataSet。可以传入多条查询语句,返回的DataSet里会有多个DataTable
/// </summary>
/// <param name="SQL">查询语句。比如select * from tableName</param>
/// <returns>返回DataSet</returns>
#region 函数实现 — — RunSqlDataSet
public static DataSet RunSqlDataSet(string sql,out string ErrorMsg)
{
ErrorMsg = "";
//设置DataAdapter
DbDataAdapter da = Factory.CreateDataAdapter(sql);
try
{
DataSet DS = new DataSet();
da.Fill(DS);
return DS;
}
catch (Exception ex)
{
WriteLog.SetErrorMsg("RunSqlDataSet", sql, ex.Message); //处理错误
ErrorMsg = ex.Message + "<BR>RunSqlDataSet_SQL:" + sql;
return null;
}
finally
{
//关闭数据库,销毁实例
Factory.DbDataAdapterDispose(da);
}
}
#endregion
public static DataTable RunSqlDataTable(string sql)
{
string msg = "";
return RunSqlDataTable(sql, out msg);
}
/// <summary>
/// 运行SQL查询语句 返回DataTable。
/// </summary>
/// <param name="SQL">查询语句。比如select * from tableName</param>
/// <returns>返回DataTable</returns>
#region 函数实现 — — RunSqlDataTable
public static DataTable RunSqlDataTable(string sql, out string ErrorMsg)
{
ErrorMsg = "";
//设置DataAdapter
DbDataAdapter da = Factory.CreateDataAdapter(sql);
try
{
DataTable DT = new DataTable();
da.Fill(DT);
return DT;
}
catch (Exception ex)
{
WriteLog.SetErrorMsg("RunSqlDataTable", sql, ex.Message); //处理错误
ErrorMsg = ex.Message + "<BR>RunSqlDataSet_SQL:" + sql;
return null;
}
finally
{
//关闭数据库,销毁实例
Factory.DbDataAdapterDispose(da);
}
}
#endregion
/// <summary>
/// 运行SQl语句返回第一条记录。返回DataRow
/// </summary>
/// <param name="SQL">查询语句。比如select * from tableName</param>
/// <returns></returns>
#region 函数实现 — — RunSqlDataRow
public static DataRow RunSqlDataRow(string sql)
{
//设置DataAdapter
DbDataAdapter da = Factory.CreateDataAdapter(sql);
try
{
DataTable DT = new DataTable();
da.Fill(DT);
if (DT.Rows.Count > )
return DT.Rows[];
else
return null;
}
catch (Exception ex)
{
WriteLog.SetErrorMsg("RunSqlDataRow", sql, ex.Message); //处理错误
return null;
}
finally
{
//关闭数据库,销毁实例
Factory.DbDataAdapterDispose(da);
}
}
#endregion
/// <summary>
/// 运行SQl语句返回第一条记录的数组。返回字符串数组
/// </summary>
/// <param name="SQL">查询语句。比如select top 1 * from tableName</param>
/// <returns></returns>
#region 函数实现 — — RunSqlStrings
public static string[] RunSqlStrings(string sql)
{
//传入查询语句,返回第一条记录的字符串数组
//设置command
DbCommand cm = Factory.CreateCommand(sql);
try
{
cm.Connection.Open();
DbDataReader r = cm.ExecuteReader(CommandBehavior.SingleRow);
if (r.Read())
{
int ArrLength = r.FieldCount;
string[] strValue = new string[ArrLength];
for (int i = ; i < ArrLength; i++)
strValue[i] = r.GetValue(i).ToString();
return strValue;
}
else
{
r.Close();
return null;
}
}
catch (Exception ex)
{
WriteLog.SetErrorMsg("RunSqlStrings", sql, ex.Message); //处理错误
return null;
}
finally
{
cm.Connection.Close();
cm.Connection.Dispose();
cm.Dispose();
}
}
#endregion
/// <summary>
/// 运行SQl语句返回每一条记录的第一个字段的数组。返回字符串数组
/// </summary>
/// <param name="SQL">查询语句。比如select myName from tableName</param>
/// <returns></returns>
#region 函数实现 — — RunSqlStringsByRow
public static string[] RunSqlStringsByRow(string sql)
{
//传入查询语句,每一条记录的第一个字段的数组。返回字符串数组
//设置command
DbCommand cm = Factory.CreateCommand(sql);
try
{
cm.Connection.Open();
DbDataReader r = cm.ExecuteReader();
System.Collections.Generic.List<string> list = new List<string>();
while (r.Read())
list.Add(r[].ToString());
return list.ToArray();
}
catch (Exception ex)
{
WriteLog.SetErrorMsg("RunSqlStringsByRow", sql, ex.Message); //处理错误
return null;
}
finally
{
cm.Connection.Close();
cm.Connection.Dispose();
cm.Dispose();
}
}
#endregion
/// <summary>
/// 运行SQl语句返回第一条记录的第一列的值。
/// </summary>
/// <param name="SQL">查询语句。比如select top 1 ID from tableName where userName='aa'。会返回ID的内容</param>
/// <returns></returns>
#region 函数实现 — — RunSqlGetID
public static string RunSqlGetID(string sql)
{
//设置command
DbCommand cm = Factory.CreateCommand(sql);
try
{
cm.Connection.Open();
DbDataReader r = cm.ExecuteReader(CommandBehavior.SingleRow);
if (r.Read())
return r.GetValue().ToString();
else
return null;
}
catch (Exception ex)
{
WriteLog.SetErrorMsg("RunSqlGetID", sql, ex.Message); //处理错误
return null;
}
finally
{
cm.Connection.Close();
cm.Connection.Dispose();
cm.Dispose();
}
}
#endregion
#endregion
#region 运行查询语句不返回记录集(无返回记录、检查持否存在指定的记录)
/// <summary>
/// 运行SQL查询语句,不返回记录集。用于添加、修改、删除等操作
/// </summary>
/// <param name="SQL">查询语句。比如insert into tableName 、update tableName</param>
/// <returns></returns>
#region 函数实现 — — RunSql
public static void RunSql(string sql)
{
//设置command
DbCommand cm = Factory.CreateCommand(sql);
try
{
cm.Connection.Open();
cm.ExecuteNonQuery();
}
catch (Exception ex)
{
WriteLog.SetErrorMsg("RunSql", sql, ex.Message); //处理错误
}
finally
{
cm.Connection.Close();
cm.Connection.Dispose();
cm.Dispose();
}
}
#endregion
/// <summary>
/// 执行一条SQL语句,看是否能查到记录 有:返回true;没有返回false,用于判断是否重名
/// </summary>
/// <param name="SQL">查询语句。比如select ID from tableName where userName='aa'</param>
/// <returns></returns>
#region 函数实现 — — RunSqlExists
public static bool RunSqlExists(string sql)
{
//设置command
DbCommand cm = Factory.CreateCommand(sql);
try
{
if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed)
cm.Connection.Open();
DbDataReader r = cm.ExecuteReader(CommandBehavior.CloseConnection);
if (r.HasRows)
return true;
else
return false;
}
catch (Exception ex)
{
WriteLog.SetErrorMsg("RunSqlDataSet", sql, ex.Message); //处理错误
return true;
}
finally
{
cm.Connection.Close();
cm.Connection.Dispose();
cm.Dispose();
}
}
#endregion
#endregion
#region 查询语句的方式添加、修改数据
/// <summary>
/// 添加记录。传入表名,字段数组,值数组,返回新生成记录的ID
/// </summary>
/// <param name="TableName">要添加记录的表的名称</param>
/// <param name="ziduan">字段名数组</param>
/// <param name="msg">字段对应的值的数组</param>
/// <returns></returns>
public static string InsertDataStr(string TableName, string[] columns, string[] msg)
{
//添加数据 返回新添加的ID
System.Text.StringBuilder SQL = new System.Text.StringBuilder();
SQL.Append("insert into "); //insert into
SQL.Append(TableName);
SQL.Append(" (");
int i;
for (i = ; i < columns.Length - ; i++) //字段
{
SQL.Append(columns[i]);
SQL.Append(",");
}
SQL.Append(columns[i]);
SQL.Append(") values ('");
for (i = ; i < columns.Length - ; i++)
{
SQL.Append(msg[i]);
SQL.Append("','");
}
SQL.Append(msg[i]);
if (WebConfig.DataBaseType() == )
SQL.Append("') select scope_identity() as a1");
else
SQL.Append("')");
string re = RunSqlGetID(SQL.ToString());
SQL.Length = ;
if (re == null)
return "-1";
else
return re;
}
/// <summary>
/// 修改记录。传入表名,字段数组,值数组
/// </summary>
/// <param name="TableName">要修改记录的表的名称</param>
/// <param name="ziduan">字段名数组</param>
/// <param name="msg">字段对应的值的数组</param>
/// <param name="tiaojian">条件 ,加在where 后面的语句</param>
/// <returns></returns>
public static bool UpdateData(string TableName, string[] cloumns, string[] msg, string myWhere)
{
System.Text.StringBuilder SQL = new System.Text.StringBuilder();
SQL.Append("update "); //update
SQL.Append(TableName);
SQL.Append(" set ");
int i;
for (i = ; i < cloumns.Length - ; i++)
{
SQL.Append(cloumns[i]); //update
SQL.Append("='");
SQL.Append(msg[i]);
SQL.Append("',");
}
SQL.Append(cloumns[i]); //update
SQL.Append("='");
SQL.Append(msg[i]);
SQL.Append("' where ");
SQL.Append(myWhere);
RunSql(SQL.ToString());
return true;
}
#endregion
}
}
存储过程的代码
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using JYK;
namespace JYK.DataAccessLibrary
{
public class DataAccessLayerSP
{
#region 属性
private static string errorMsg; //出错信息
private static bool isShowErrorSQL; //是否显示出错的查询语句(包括存储过程名程)
private DbCommand cm ; //建立Command对象
/// <summary>
/// 读取出错信息
/// </summary>
public string ErrorMsg
{
get{return errorMsg;}
}
/// <summary>
/// 修改连接字符串,在同时访问两个或两个以上的数据库的时候使用
/// </summary>
public string cnString
{
set{cm.Connection.ConnectionString = value;}
get{return cm.Connection.ConnectionString;}
}
/// <summary>
/// 释放资源~
/// </summary>
public void Dispose()
{
errorMsg = null;
cm.Parameters.Clear();
cm.Connection.Close();
cm.Connection.Dispose();
cm.Dispose();
}
#endregion
public DataAccessLayerSP() //构造函数
{
//获取连接字符串
cm = Factory.CreateCommand();
cm.CommandType = CommandType.StoredProcedure;
//初始化错误信息
errorMsg = "";
//本地运行,显示出错的查询语句(包括存储过程名程)
isShowErrorSQL = WebConfig.isShowErrorSQL();
}
//存储过程的参数部分
#region 存储过程的参数部分——清除和添加参数
#region 清除参数
/// <summary>
/// 清除Command的存储过程的参数。
/// </summary>
public void ClearParameter()
{cm.Parameters.Clear();}
#endregion
//int: tinyint、smallint
//bigint:
//bool: bit
//double: float、real
//string: char、nchar、varchar、nvarchar、uniqueidentifier、smalldatetime、datetime
//string: ntext、text
//decimal:从 -10^38 +1 到 10^38 –1 的固定精度和小数位的数字数据。
//numeric:功能上等同于 decimal。
//decimal: smallmoney、money
//二进制
// binary、varbinary、image
#region 输入型的参数 int 、double、decimal、nvarChar、、、
#region int
/// <summary>
/// 添加int型的参数。
/// </summary>
/// <param name="ParameterName">参数名称。比如 @UserName</param>
/// <param name="ParameterValue">参数值</param>
public void addNewParameter(string ParameterName,int ParameterValue)
{
((System.Data.SqlClient.SqlCommand)cm).Parameters.Add(ParameterName,SqlDbType.Int,); //添加存储过程的参数
cm.Parameters[ParameterName].Value=ParameterValue; //负值
//设置方向取默认值——输入
}
#endregion
#region double
/// <summary>
/// 添加小数参数,double
/// </summary>
/// <param name="ParameterName">参数名称。比如 @UserName</param>
/// <param name="ParameterValue">参数值</param>
public void addNewParameter(string ParameterName,double ParameterValue )
{
((System.Data.SqlClient.SqlCommand)cm).Parameters.Add(ParameterName, SqlDbType.Float); //添加存储过程的参数
cm.Parameters[ParameterName].Value=ParameterValue; //负值
}
#endregion
#region decimal
/// <summary>
/// 添加金额参数,方向是输入(input)。decimal
/// </summary>
/// <param name="ParameterName">参数名称。比如 @UserName</param>
/// <param name="ParameterValue">参数值</param>
public void addNewParameter(string ParameterName,decimal ParameterValue )
{
((System.Data.SqlClient.SqlCommand)cm).Parameters.Add(ParameterName, SqlDbType.Decimal); //添加存储过程的参数
cm.Parameters[ParameterName].Value=ParameterValue; //负值
}
#endregion
#region nvarChar
/// <summary>
/// 添加nvarChar型的参数。方向是输入(input)
/// </summary>
/// <param name="ParameterName">参数名称。比如 @UserName</param>
/// <param name="ParameterValue">参数值</param>
/// <param name="size">参数大小</param>
public void addNewParameter(string ParameterName,string ParameterValue,int size)
{
((System.Data.SqlClient.SqlCommand)cm).Parameters.Add(ParameterName, SqlDbType.NVarChar, size); //添加存储过程的参数
cm.Parameters[ParameterName].Value=ParameterValue; //负值
}
#endregion
#region nText
/// <summary>
/// 添加nText型的参数。方向是输入(input)
/// </summary>
/// <param name="ParameterName">参数名称。比如 @UserName</param>
/// <param name="ParameterValue">参数值</param>
public void addNewParameter(string ParameterName,string ParameterValue)
{
((System.Data.SqlClient.SqlCommand)cm).Parameters.Add(ParameterName, SqlDbType.NText); //添加存储过程的参数
cm.Parameters[ParameterName].Value=ParameterValue; //负值
}
#endregion
#region bit
/// <summary>
/// 添加bit型的参数。方向是输入(input)
/// </summary>
/// <param name="ParameterName">参数名称。比如 @UserName</param>
/// <param name="ParameterValue">参数值</param>
public void addNewParameter(string ParameterName,bool ParameterValue)
{
((System.Data.SqlClient.SqlCommand)cm).Parameters.Add(ParameterName, SqlDbType.Bit); //添加存储过程的参数
cm.Parameters[ParameterName].Value=ParameterValue; //负值
}
#endregion
#endregion
#region 输出型的参数
/// <summary>
/// 添加输出型的参数。只支持常用的几个参数类型,如果需要可以扩充。
/// </summary>
/// <param name="ParameterName">参数名称。比如 @UserName</param>
/// <param name="ParameterValue">参数的类型</param>
public void addNewParameter(string ParameterName,JYK.ParameterKind kind)
{
switch(kind)
{
case ParameterKind.Int :
((System.Data.SqlClient.SqlCommand)cm).Parameters.Add(ParameterName, SqlDbType.Int); //添加存储过程的参数
break;
case ParameterKind.Double:
((System.Data.SqlClient.SqlCommand)cm).Parameters.Add(ParameterName, SqlDbType.Float); //添加存储过程的参数
break;
case ParameterKind.Decimal :
((System.Data.SqlClient.SqlCommand)cm).Parameters.Add(ParameterName, SqlDbType.Decimal); //添加存储过程的参数
break;
case ParameterKind.NVarChar :
((System.Data.SqlClient.SqlCommand)cm).Parameters.Add(ParameterName, SqlDbType.NVarChar, ); //添加存储过程的参数
break;
case ParameterKind.Bit :
((System.Data.SqlClient.SqlCommand)cm).Parameters.Add(ParameterName, SqlDbType.Bit); //添加存储过程的参数
break;
}
cm.Parameters[ParameterName].Direction= ParameterDirection.Output; //设置方向
}
#endregion
#endregion
#region 存储过程的参数部分——取参数的返回值
/// <summary>
/// 按序号返回参数值,一般在执行完存储过程后使用
/// </summary>
/// <param name="ParameterIndex">序号</param>
/// <returns>返回参数的内容</returns>
public string this[int ParameterIndex]
{
get {return cm.Parameters[ParameterIndex].Value.ToString(); }
}
/// <summary>
/// 按名称返回参数值,一般在执行完存储过程后使用
/// </summary>
/// <param name="ParameterName">参数名称。比如 @UserName</param>
/// <returns>返回参数的内容</returns>
public string this[string ParameterName]
{
get {return cm.Parameters[ParameterName].Value.ToString(); }
}
#endregion
#region 存储过程的参数部分——修改参数值
/// <summary>
/// 按序号修改参数值,一般在一次添加多条记录时用。
/// </summary>
/// <param name="ParameterIndex">序号</param>
public void setParameter(int ParameterIndex,string parameterValue)
{ cm.Parameters[ParameterIndex].Value = parameterValue;}
/// <summary>
/// 按名称修改参数值,一般在一次添加多条记录时用
/// </summary>
/// <param name="ParameterName">参数名称。比如 @UserName</param>
public void setParameter(string ParameterName,string parameterValue)
{ cm.Parameters[ParameterName].Value = parameterValue;}
#endregion
//存储过程部分
#region 运行存储过程返回记录(DataSet、DataTable、不返回记录集)
/// <summary>
/// 运行存储过程返回DataSet。DataSet里面可以是多个表(DateTable)
/// </summary>
/// <param name="StoredProcedureName">存储过程名称</param>
/// <returns>返回DataSet</returns>
#region 函数实现 — — RunStoreDataSet
public DataSet RunStoreDataSet(string StoredProcedureName)
{
DbDataAdapter da = Factory.CreateDataAdapter(StoredProcedureName);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
try
{
DataSet DS = new DataSet();
da.Fill(DS);
return DS;
}
catch(Exception ex)
{
WriteLog.SetErrorMsg("RunStoreDataSet", StoredProcedureName, ex.Message); //处理错误
return null;
}
finally
{
da.Dispose();
}
}
#endregion
/// <summary>
/// 运行存储过程返回DataTable。
/// </summary>
/// <param name="StoredProcedureName">存储过程名称</param>
/// <returns>返回DataTable</returns>
#region 函数实现 — — RunStoreDataTable
public DataTable RunStoreDataTable(string StoredProcedureName)
{
DbDataAdapter da = Factory.CreateDataAdapter(StoredProcedureName);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
try
{
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
catch(Exception ex)
{
WriteLog.SetErrorMsg("RunStoreDataTable", StoredProcedureName, ex.Message); //处理错误
return null;
}
finally
{
da.Dispose();
}
}
#endregion
/// <summary>
/// 运行存储过程 不返回记录集,用于添加、修改、删除等操作
/// </summary>
/// <param name="ParameterName">存储过程名称</param>
/// <returns></returns>
public void RunStore(string StoredProcedureName)
{
DbCommand cm = Factory.CreateCommand(StoredProcedureName);
cm.CommandText = StoredProcedureName;//设置command
try
{
if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed )
cm.Connection.Open();
cm.ExecuteNonQuery();
}
catch(Exception ex)
{
WriteLog.SetErrorMsg("RunStore", StoredProcedureName, ex.Message); //处理错误
}
finally
{
cm.Connection.Close();
}
}
#endregion
}
}
这里没有使用静态函数的方式,目的是为了方便添加存储过程的参数。