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
}