///
版权归江西微软技术中心
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Net.Mail;
using System.Data.Common;
using System.Security.Principal;
using System.IO;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Collections;
using Microsoft.Practices.EnterpriseLibrary;
/// <summary>
/// Copyright (C) 2004-2008 LiTianPing
/// 数据访问基础类(基于SQLServer)
/// 用户可以修改满足自己项目的需要。
/// </summary>
public abstract class DbHelperSQL
{
public DbHelperSQL()
{
}
public static Database GetDatabase()
{
return DatabaseFactory.CreateDatabase();
}
#region membership中的用户及邮件相关函数
/// <summary>
/// 发送邮件
/// </summary>
/// <param name="mailFrom"> 发件人 </param>
/// <param name="mailTo"> 收件人 </param>
/// <param name="mailSubject"> 邮件标题 </param>
/// <param name="mailBody"> 邮件正文 </param>
public static void SendMail( string mailFrom, string mailTo, string mailSubject, string mailBody)
{
try
{
MailMessage message = new MailMessage();
if (mailFrom.Length > 0 )
{
message.From = new MailAddress(mailFrom);
}
message.IsBodyHtml = true ;
message.To.Add( new MailAddress(mailTo));
message.Subject = mailSubject;
message.Body = mailBody;
SmtpClient client = new SmtpClient();
client.Send(message);
}
catch
{
}
}
/// <summary>
/// 发送文本邮件
/// </summary>
/// <param name="mailFrom"> 发件人 </param>
/// <param name="mailTo"> 收件人 </param>
/// <param name="mailSubject"> 邮件标题 </param>
/// <param name="mailBody"> 邮件正文 </param>
public static void SendTextMail( string mailFrom, string mailTo, string mailSubject, string mailBody)
{
try
{
MailMessage message = new MailMessage();
if (mailFrom.Length > 0 )
{
message.From = new MailAddress(mailFrom);
}
message.IsBodyHtml = false ;
message.To.Add( new MailAddress(mailTo));
message.Subject = mailSubject;
message.Body = mailBody;
SmtpClient client = new SmtpClient();
client.Send(message);
}
catch
{
}
}
// 自动发送邮件给系统管理员
public static void SendMailToAdmin( string mailFrom, string mailBody)
{
SendMail(mailFrom, ConfigurationManager.AppSettings[ " AdminMail " ], " 系统自动发送 " , mailBody);
}
/// <summary>
/// 查找用户名对应的membership的用户id
/// </summary>
/// <param name="UserName"> 要查找UserId的用户名称 </param>
public static string MemberShipUserId( string UserName)
{
Database db = GetDatabase();
string select = " select UserId from aspnet_Users where UserName=' " + UserName + " ' " ;
string sResult = db.ExecuteScalar(CommandType.Text, select).ToString();
return sResult;
}
/// <summary>
/// 查找用户唯一标识对应的membership的用户名
/// </summary>
/// <param name="UserID"> 用户的唯一标识ID </param>
public static string MemberShipUserName( string UserID)
{
Database db = GetDatabase();
string select = " select UserName from aspnet_Users where UserId=' " + UserID + " ' " ;
return db.ExecuteScalar(CommandType.Text, select).ToString();
}
#endregion
#region 一次执行多条存储过程,同时使用事务的方法 ExecuteTransaction
/// <summary>
/// 再次封装Entlib,用于一次执行多条存储过程,同时使用事务的方法
/// 使用时请先建立Database,再把需要执行的语句及参数保存到DbCommand对象中,
/// 最后调用本方法,并判断返回结果即可
///
/// 使用方法如下:
/// Database db = GetDatabase();
/// DbCommand[] cmd = new DbCommand[你需要的大小];
/// 给每个cmd赋值
/// ExecuteTransaction(db, cmd);
/// </summary>
/// <param name="db"> 从DatabaseFactory获得的Database对象 </param>
/// <param name="cmd"> 要执行的DbCommand对象数组 </param>
/// <returns> 事务执行成功返回True,失败返回False并自动回滚 </returns>
public static bool ExecuteTransaction(ArrayList cmd)
{
Database db = GetDatabase();
bool result = false ;
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
for ( int i = 0 ; i < cmd.Count; i ++ )
{
db.ExecuteNonQuery(((DbCommand)cmd[i]), transaction);
}
transaction.Commit();
result = true ;
}
catch (Exception ee)
{
transaction.Rollback();
HttpContext.Current.Trace.Warn(ee.Message);
}
connection.Close();
return result;
}
}
#endregion
#region 分页方法调用 AuthorizeUserOnPageRule
/// <summary>
/// 获取总条数记录
/// </summary>
/// <param name="table"> 数据表名称 </param>
public static int SelectCount( string table, string where )
{
int result;
Database db = GetDatabase();
string sql = " select count(*) from " + table;
if ( ! string .IsNullOrEmpty( where .Trim()))
{
sql += " where " + where ;
}
DbCommand dbc = db.GetSqlStringCommand(sql);
result = ( int )db.ExecuteScalar(dbc);
return result;
}
/// <summary>
/// 获取当前页的数据,返回DataSet
/// 程序编写:姚国标
/// 完成日期:2006.05.13
/// </summary>
/// <param name="anp"> 分页控件 </param>
/// <param name="table"> 数据表名称 </param>
/// <param name="PK"> 主键 例如:ID </param>
/// <param name="sort"> 排序字段 例如:ID desc </param>
/// <param name="fields"> 查询字段 例如:* </param>
/// <param name="filter"> where条件 例如:id>100 </param>
/// <param name="group"> group条件 </param>
/// <param name="lblDGMessage"> label控件,显示 多少条记录,有多少页等 </param>
/// <returns> 返回Dataset数据 </returns>
public static DataSet GetPagenumberData(Wuqi.Webdiyer.AspNetPager anp, string table, string pk, string sort, string fields, string filter, string group, Label lblDGMessage)
{
Database db = GetDatabase();
DbCommand dbc = db.GetStoredProcCommand( " Paging_RowCount " );
lblDGMessage.Text = " 共有信息记录 " + anp.RecordCount.ToString() + " 条, 当前第 " + anp.CurrentPageIndex.ToString() + " 页, 共 " + anp.PageCount.ToString() + " 页. " ;
db.AddInParameter(dbc, " tables " , DbType.String, table);
db.AddInParameter(dbc, " PK " , DbType.String, pk);
db.AddInParameter(dbc, " sort " , DbType.String, sort);
db.AddInParameter(dbc, " PageNumber " , DbType.Int32, anp.CurrentPageIndex);
db.AddInParameter(dbc, " PageSize " , DbType.String, anp.PageSize);
db.AddInParameter(dbc, " Fields " , DbType.String, fields);
db.AddInParameter(dbc, " Filter " , DbType.String, filter);
db.AddInParameter(dbc, " Group " , DbType.String, group);
DataSet ds = db.ExecuteDataSet(dbc);
return ds;
}
#endregion
#region 公用方法 GetMaxID,Exists
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());
}
}
public static bool Exists( string strSql)
{
object obj = GetSingle(strSql);
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语句 ExecuteSql 执行存储过程 ExecuteProcedure
/// <summary>
/// 执行SQL语句,返回影响的记录数
/// </summary>
/// <param name="SQLString"> SQL语句 </param>
/// <returns> 影响的记录数,-1表示操作错误 </returns>
public static int ExecuteSql( string SQLString)
{
Database db = GetDatabase();
using (DbConnection connection = db.CreateConnection())
{
try
{
connection.Open();
int iRows = db.ExecuteNonQuery(CommandType.Text, SQLString);
connection.Close();
return iRows;
}
catch (Exception ee)
{
connection.Close();
HttpContext.Current.Trace.Warn(ee.Message);
return - 1 ;
}
}
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="ProcName"> 存储过程名 </param>
/// <param name="ParaValue"> 存储过程参数 </param>
/// <returns> 影响的记录数,-1表示操作失败 </returns>
public static int ExecuteProcedure( string ProcName, params object [] ParaValue)
{
Database db = GetDatabase();
using (DbConnection connection = db.CreateConnection())
{
try
{
connection.Open();
int iRows = db.ExecuteNonQuery(ProcName, ParaValue);
connection.Close();
return iRows;
}
catch (Exception ee)
{
connection.Close();
HttpContext.Current.Trace.Warn(ee.Message);
return - 1 ;
}
}
}
/// <summary>
/// 执行查询语句,返回SqlDataReader
/// </summary>
/// <param name="strSQL"> 查询语句 </param>
/// <returns> SqlDataReader,错误返回null </returns>
public static SqlDataReader ExecuteReader( string strSQL)
{
Database db = GetDatabase();
using (DbConnection connection = db.CreateConnection())
{
try
{
connection.Open();
SqlDataReader idr = (SqlDataReader)db.ExecuteReader(CommandType.Text, strSQL);
connection.Close();
return idr;
}
catch (Exception ee)
{
connection.Close();
HttpContext.Current.Trace.Warn(ee.Message);
return null ;
}
}
}
/// <summary>
/// 执行查询语句,返回DataSet
/// </summary>
/// <param name="strSQL"> 查询语句 </param>
/// <returns> DataSet,错误返回null </returns>
public static DataSet ExecuteDataSet( string strSQL)
{
Database db = GetDatabase();
using (DbConnection connection = db.CreateConnection())
{
try
{
connection.Open();
DataSet ds = db.ExecuteDataSet(CommandType.Text, strSQL);
connection.Close();
return ds;
}
catch (Exception ee)
{
connection.Close();
HttpContext.Current.Trace.Warn(ee.Message);
return null ;
}
}
}
/// <summary>
/// 执行一条计算查询结果语句,返回查询结果(object)。
/// </summary>
/// <param name="SQLString"> 计算查询结果语句 </param>
/// <returns> 查询结果(object) </returns>
public static object GetSingle( string SQLString)
{
try
{
Database db = GetDatabase();
object obj = db.ExecuteScalar(CommandType.Text,SQLString);
if ((Object.Equals(obj, null )) || (Object.Equals(obj, System.DBNull.Value)))
{
return null ;
}
else
{
return obj;
}
}
catch (Exception ex)
{
HttpContext.Current.Trace.Warn(ex.Message);
throw new Exception(ex.Message);
}
}
#endregion
#region 存储过程操作
/// <summary>
/// 执行存储过程,返回结果集
/// </summary>
/// <param name="storedProcName"> 存储过程名 </param>
/// <param name="parameters"> 存储过程参数 </param>
/// <returns> SqlDataReader </returns>
public static SqlDataReader RunProcedure( string storedProcName, IDataParameter[] parameters)
{
Database db = GetDatabase();
// 用以下方法设置存储过程的参数
DbCommand dbCommand = db.GetStoredProcCommand(storedProcName);
for ( int i = 0 ; i < parameters.Length; i ++ )
{
db.AddParameter(dbCommand, parameters[i].ParameterName, parameters[i].DbType, parameters[i].Direction,
parameters[i].SourceColumn, parameters[i].SourceVersion, parameters[i].Value);
}
// 执行存储过程
try
{
return (SqlDataReader)db.ExecuteReader(dbCommand);
}
catch (Exception ex)
{
HttpContext.Current.Trace.Warn(ex.Message);
return null ;
}
}
/// <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)
{
Database db = GetDatabase();
// 用以下方法设置存储过程的参数
DbCommand dbCommand = db.GetStoredProcCommand(storedProcName);
for ( int i = 0 ; i < parameters.Length; i ++ )
{
db.AddParameter(dbCommand, parameters[i].ParameterName, parameters[i].DbType, parameters[i].Direction,
parameters[i].SourceColumn, parameters[i].SourceVersion, parameters[i].Value);
}
// 执行存储过程
try
{
rowsAffected = db.ExecuteNonQuery(dbCommand);
}
catch (Exception ex)
{
HttpContext.Current.Trace.Warn(ex.Message);
rowsAffected = - 1 ;
}
// 如果有返回参数
if (dbCommand.Parameters[ 0 ].Direction == ParameterDirection.Output)
{
try
{
return Convert.ToInt32(dbCommand.Parameters[ 0 ].Value);
}
catch
{
return - 1 ;
}
}
else
{
return rowsAffected;
}
}
/// <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 ds)
{
Database db = GetDatabase();
// 用以下方法设置存储过程的参数
DbCommand dbCommand = db.GetStoredProcCommand(storedProcName);
for ( int i = 0 ; i < parameters.Length; i ++ )
{
db.AddParameter(dbCommand,parameters[i].ParameterName,parameters[i].DbType,parameters[i].Direction,
parameters[i].SourceColumn,parameters[i].SourceVersion,parameters[i].Value);
}
// 执行存储过程
try
{
return db.ExecuteDataSet(dbCommand);
}
catch (Exception ex)
{
HttpContext.Current.Trace.Warn(ex.Message);
return null ;
}
}
#endregion
}
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Net.Mail;
using System.Data.Common;
using System.Security.Principal;
using System.IO;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Collections;
using Microsoft.Practices.EnterpriseLibrary;
/// <summary>
/// Copyright (C) 2004-2008 LiTianPing
/// 数据访问基础类(基于SQLServer)
/// 用户可以修改满足自己项目的需要。
/// </summary>
public abstract class DbHelperSQL
{
public DbHelperSQL()
{
}
public static Database GetDatabase()
{
return DatabaseFactory.CreateDatabase();
}
#region membership中的用户及邮件相关函数
/// <summary>
/// 发送邮件
/// </summary>
/// <param name="mailFrom"> 发件人 </param>
/// <param name="mailTo"> 收件人 </param>
/// <param name="mailSubject"> 邮件标题 </param>
/// <param name="mailBody"> 邮件正文 </param>
public static void SendMail( string mailFrom, string mailTo, string mailSubject, string mailBody)
{
try
{
MailMessage message = new MailMessage();
if (mailFrom.Length > 0 )
{
message.From = new MailAddress(mailFrom);
}
message.IsBodyHtml = true ;
message.To.Add( new MailAddress(mailTo));
message.Subject = mailSubject;
message.Body = mailBody;
SmtpClient client = new SmtpClient();
client.Send(message);
}
catch
{
}
}
/// <summary>
/// 发送文本邮件
/// </summary>
/// <param name="mailFrom"> 发件人 </param>
/// <param name="mailTo"> 收件人 </param>
/// <param name="mailSubject"> 邮件标题 </param>
/// <param name="mailBody"> 邮件正文 </param>
public static void SendTextMail( string mailFrom, string mailTo, string mailSubject, string mailBody)
{
try
{
MailMessage message = new MailMessage();
if (mailFrom.Length > 0 )
{
message.From = new MailAddress(mailFrom);
}
message.IsBodyHtml = false ;
message.To.Add( new MailAddress(mailTo));
message.Subject = mailSubject;
message.Body = mailBody;
SmtpClient client = new SmtpClient();
client.Send(message);
}
catch
{
}
}
// 自动发送邮件给系统管理员
public static void SendMailToAdmin( string mailFrom, string mailBody)
{
SendMail(mailFrom, ConfigurationManager.AppSettings[ " AdminMail " ], " 系统自动发送 " , mailBody);
}
/// <summary>
/// 查找用户名对应的membership的用户id
/// </summary>
/// <param name="UserName"> 要查找UserId的用户名称 </param>
public static string MemberShipUserId( string UserName)
{
Database db = GetDatabase();
string select = " select UserId from aspnet_Users where UserName=' " + UserName + " ' " ;
string sResult = db.ExecuteScalar(CommandType.Text, select).ToString();
return sResult;
}
/// <summary>
/// 查找用户唯一标识对应的membership的用户名
/// </summary>
/// <param name="UserID"> 用户的唯一标识ID </param>
public static string MemberShipUserName( string UserID)
{
Database db = GetDatabase();
string select = " select UserName from aspnet_Users where UserId=' " + UserID + " ' " ;
return db.ExecuteScalar(CommandType.Text, select).ToString();
}
#endregion
#region 一次执行多条存储过程,同时使用事务的方法 ExecuteTransaction
/// <summary>
/// 再次封装Entlib,用于一次执行多条存储过程,同时使用事务的方法
/// 使用时请先建立Database,再把需要执行的语句及参数保存到DbCommand对象中,
/// 最后调用本方法,并判断返回结果即可
///
/// 使用方法如下:
/// Database db = GetDatabase();
/// DbCommand[] cmd = new DbCommand[你需要的大小];
/// 给每个cmd赋值
/// ExecuteTransaction(db, cmd);
/// </summary>
/// <param name="db"> 从DatabaseFactory获得的Database对象 </param>
/// <param name="cmd"> 要执行的DbCommand对象数组 </param>
/// <returns> 事务执行成功返回True,失败返回False并自动回滚 </returns>
public static bool ExecuteTransaction(ArrayList cmd)
{
Database db = GetDatabase();
bool result = false ;
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
for ( int i = 0 ; i < cmd.Count; i ++ )
{
db.ExecuteNonQuery(((DbCommand)cmd[i]), transaction);
}
transaction.Commit();
result = true ;
}
catch (Exception ee)
{
transaction.Rollback();
HttpContext.Current.Trace.Warn(ee.Message);
}
connection.Close();
return result;
}
}
#endregion
#region 分页方法调用 AuthorizeUserOnPageRule
/// <summary>
/// 获取总条数记录
/// </summary>
/// <param name="table"> 数据表名称 </param>
public static int SelectCount( string table, string where )
{
int result;
Database db = GetDatabase();
string sql = " select count(*) from " + table;
if ( ! string .IsNullOrEmpty( where .Trim()))
{
sql += " where " + where ;
}
DbCommand dbc = db.GetSqlStringCommand(sql);
result = ( int )db.ExecuteScalar(dbc);
return result;
}
/// <summary>
/// 获取当前页的数据,返回DataSet
/// 程序编写:姚国标
/// 完成日期:2006.05.13
/// </summary>
/// <param name="anp"> 分页控件 </param>
/// <param name="table"> 数据表名称 </param>
/// <param name="PK"> 主键 例如:ID </param>
/// <param name="sort"> 排序字段 例如:ID desc </param>
/// <param name="fields"> 查询字段 例如:* </param>
/// <param name="filter"> where条件 例如:id>100 </param>
/// <param name="group"> group条件 </param>
/// <param name="lblDGMessage"> label控件,显示 多少条记录,有多少页等 </param>
/// <returns> 返回Dataset数据 </returns>
public static DataSet GetPagenumberData(Wuqi.Webdiyer.AspNetPager anp, string table, string pk, string sort, string fields, string filter, string group, Label lblDGMessage)
{
Database db = GetDatabase();
DbCommand dbc = db.GetStoredProcCommand( " Paging_RowCount " );
lblDGMessage.Text = " 共有信息记录 " + anp.RecordCount.ToString() + " 条, 当前第 " + anp.CurrentPageIndex.ToString() + " 页, 共 " + anp.PageCount.ToString() + " 页. " ;
db.AddInParameter(dbc, " tables " , DbType.String, table);
db.AddInParameter(dbc, " PK " , DbType.String, pk);
db.AddInParameter(dbc, " sort " , DbType.String, sort);
db.AddInParameter(dbc, " PageNumber " , DbType.Int32, anp.CurrentPageIndex);
db.AddInParameter(dbc, " PageSize " , DbType.String, anp.PageSize);
db.AddInParameter(dbc, " Fields " , DbType.String, fields);
db.AddInParameter(dbc, " Filter " , DbType.String, filter);
db.AddInParameter(dbc, " Group " , DbType.String, group);
DataSet ds = db.ExecuteDataSet(dbc);
return ds;
}
#endregion
#region 公用方法 GetMaxID,Exists
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());
}
}
public static bool Exists( string strSql)
{
object obj = GetSingle(strSql);
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语句 ExecuteSql 执行存储过程 ExecuteProcedure
/// <summary>
/// 执行SQL语句,返回影响的记录数
/// </summary>
/// <param name="SQLString"> SQL语句 </param>
/// <returns> 影响的记录数,-1表示操作错误 </returns>
public static int ExecuteSql( string SQLString)
{
Database db = GetDatabase();
using (DbConnection connection = db.CreateConnection())
{
try
{
connection.Open();
int iRows = db.ExecuteNonQuery(CommandType.Text, SQLString);
connection.Close();
return iRows;
}
catch (Exception ee)
{
connection.Close();
HttpContext.Current.Trace.Warn(ee.Message);
return - 1 ;
}
}
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="ProcName"> 存储过程名 </param>
/// <param name="ParaValue"> 存储过程参数 </param>
/// <returns> 影响的记录数,-1表示操作失败 </returns>
public static int ExecuteProcedure( string ProcName, params object [] ParaValue)
{
Database db = GetDatabase();
using (DbConnection connection = db.CreateConnection())
{
try
{
connection.Open();
int iRows = db.ExecuteNonQuery(ProcName, ParaValue);
connection.Close();
return iRows;
}
catch (Exception ee)
{
connection.Close();
HttpContext.Current.Trace.Warn(ee.Message);
return - 1 ;
}
}
}
/// <summary>
/// 执行查询语句,返回SqlDataReader
/// </summary>
/// <param name="strSQL"> 查询语句 </param>
/// <returns> SqlDataReader,错误返回null </returns>
public static SqlDataReader ExecuteReader( string strSQL)
{
Database db = GetDatabase();
using (DbConnection connection = db.CreateConnection())
{
try
{
connection.Open();
SqlDataReader idr = (SqlDataReader)db.ExecuteReader(CommandType.Text, strSQL);
connection.Close();
return idr;
}
catch (Exception ee)
{
connection.Close();
HttpContext.Current.Trace.Warn(ee.Message);
return null ;
}
}
}
/// <summary>
/// 执行查询语句,返回DataSet
/// </summary>
/// <param name="strSQL"> 查询语句 </param>
/// <returns> DataSet,错误返回null </returns>
public static DataSet ExecuteDataSet( string strSQL)
{
Database db = GetDatabase();
using (DbConnection connection = db.CreateConnection())
{
try
{
connection.Open();
DataSet ds = db.ExecuteDataSet(CommandType.Text, strSQL);
connection.Close();
return ds;
}
catch (Exception ee)
{
connection.Close();
HttpContext.Current.Trace.Warn(ee.Message);
return null ;
}
}
}
/// <summary>
/// 执行一条计算查询结果语句,返回查询结果(object)。
/// </summary>
/// <param name="SQLString"> 计算查询结果语句 </param>
/// <returns> 查询结果(object) </returns>
public static object GetSingle( string SQLString)
{
try
{
Database db = GetDatabase();
object obj = db.ExecuteScalar(CommandType.Text,SQLString);
if ((Object.Equals(obj, null )) || (Object.Equals(obj, System.DBNull.Value)))
{
return null ;
}
else
{
return obj;
}
}
catch (Exception ex)
{
HttpContext.Current.Trace.Warn(ex.Message);
throw new Exception(ex.Message);
}
}
#endregion
#region 存储过程操作
/// <summary>
/// 执行存储过程,返回结果集
/// </summary>
/// <param name="storedProcName"> 存储过程名 </param>
/// <param name="parameters"> 存储过程参数 </param>
/// <returns> SqlDataReader </returns>
public static SqlDataReader RunProcedure( string storedProcName, IDataParameter[] parameters)
{
Database db = GetDatabase();
// 用以下方法设置存储过程的参数
DbCommand dbCommand = db.GetStoredProcCommand(storedProcName);
for ( int i = 0 ; i < parameters.Length; i ++ )
{
db.AddParameter(dbCommand, parameters[i].ParameterName, parameters[i].DbType, parameters[i].Direction,
parameters[i].SourceColumn, parameters[i].SourceVersion, parameters[i].Value);
}
// 执行存储过程
try
{
return (SqlDataReader)db.ExecuteReader(dbCommand);
}
catch (Exception ex)
{
HttpContext.Current.Trace.Warn(ex.Message);
return null ;
}
}
/// <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)
{
Database db = GetDatabase();
// 用以下方法设置存储过程的参数
DbCommand dbCommand = db.GetStoredProcCommand(storedProcName);
for ( int i = 0 ; i < parameters.Length; i ++ )
{
db.AddParameter(dbCommand, parameters[i].ParameterName, parameters[i].DbType, parameters[i].Direction,
parameters[i].SourceColumn, parameters[i].SourceVersion, parameters[i].Value);
}
// 执行存储过程
try
{
rowsAffected = db.ExecuteNonQuery(dbCommand);
}
catch (Exception ex)
{
HttpContext.Current.Trace.Warn(ex.Message);
rowsAffected = - 1 ;
}
// 如果有返回参数
if (dbCommand.Parameters[ 0 ].Direction == ParameterDirection.Output)
{
try
{
return Convert.ToInt32(dbCommand.Parameters[ 0 ].Value);
}
catch
{
return - 1 ;
}
}
else
{
return rowsAffected;
}
}
/// <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 ds)
{
Database db = GetDatabase();
// 用以下方法设置存储过程的参数
DbCommand dbCommand = db.GetStoredProcCommand(storedProcName);
for ( int i = 0 ; i < parameters.Length; i ++ )
{
db.AddParameter(dbCommand,parameters[i].ParameterName,parameters[i].DbType,parameters[i].Direction,
parameters[i].SourceColumn,parameters[i].SourceVersion,parameters[i].Value);
}
// 执行存储过程
try
{
return db.ExecuteDataSet(dbCommand);
}
catch (Exception ex)
{
HttpContext.Current.Trace.Warn(ex.Message);
return null ;
}
}
#endregion
}