100分求c#事务处理的数据访问层的封装类
如题 100求购，俺不够继续加，:-)，俺在WEB版赚了些拿来这里花花，^_^====
--------------------编程问答-------------------- sf --------------------编程问答-------------------- 怎么木人来解决呀 --------------------编程问答-------------------- up --------------------编程问答-------------------- 分有用吗？
这样的类估计这里60％的人都有 --------------------编程问答-------------------- using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace Production.DBUtility
{
/// <summary>
/// 数据访问抽象基础类
/// Copyright (C) 2004-2008 LiTianPing
/// All rights reserved
/// </summary>
public abstract class DbHelperSQL
{
//数据库连接字符串(保存在注册表中)
public static string connectionString = "链接字符串";
public DbHelperSQL()
{
}
#region 公用方法
public static int GetMaxID(string FieldName, string TableName)
{
string strsql = "select max(" + FieldName + ")+1 from " + TableName;
object obj = DbHelperSQL.GetSingle(strsql);
if (obj == null)
{
return 1;
}
else
{
return int.Parse(obj.ToString());
}
}
public static bool Exists(string strSql)
{
object obj = DbHelperSQL.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;
}
}
public static bool Exists(string strSql, params SqlParameter[] cmdParms)
{
object obj = DbHelperSQL.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="SQLString"></param>
/// <param name="Times"></param>
/// <returns></returns>
public static int ExecuteSqlByTime(string SQLString, int Times)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
cmd.CommandTimeout = Times;
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();
conn.Close();
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>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
public static object ExecuteSqlGet(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();
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)
{
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(使用该方法切记要手工关闭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();
return myReader;
}
catch (System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}
//finally //不能在此关闭，否则，返回的对象将无法使用
//{
// cmd.Dispose();
// connection.Close();
//}
}
/// <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)
{
connection.Close();
throw new Exception(ex.Message);
}
return ds;
}
}
/// <summary>
/// 用DataSet更新数据表
/// </summary>
/// <param name="ds"></param>
/// <param name="strTblName"></param>
/// <param name="strConnection"></param>
/// <returns></returns>
public static void UpdateByDataSet(DataSet ds, string strTblName)
{
//string strTblName = ds.Tables[0].TableName.Trim();
try
{
SqlConnection conn = new SqlConnection(connectionString);
SqlDataAdapter myAdapter = new SqlDataAdapter();
SqlCommand myCommand = new SqlCommand("select * from " + strTblName, conn);
myAdapter.SelectCommand = myCommand;
SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter);
myAdapter.Update(ds.Tables[0]);
}
catch(Exception x)
{
throw new Exception(x.Message);
}
}
/// <summary>
/// 执行查询语句，返回DataSet,设置命令的执行等待时间
/// </summary>
/// <param name="SQLString"></param>
/// <param name="Times"></param>
/// <returns></returns>
public static DataSet Query(string SQLString, int Times)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
command.SelectCommand.CommandTimeout = Times;
command.Fill(ds, "ds");
}
catch (System.Data.SqlClient.SqlException ex)
{
connection.Close();
throw new Exception(ex.Message);
}
return ds;
}
}
#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语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
public static void ExecuteSqlTran(Hashtable SQLStringList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
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);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
trans.Commit();
}
}
catch
{
trans.Rollback();
throw;
}
}
}
}
/// <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);
}
}
}
} --------------------编程问答-------------------- sqlhelper.cs
已经 相当的 经典 适用了
下载
http://msdn2.microsoft.com/en-us/library/ms954827.aspx --------------------编程问答-------------------- SQLHELPER我进常用，我翻出来看看 --------------------编程问答-------------------- 被你抢先一步
--------------------编程问答-------------------- jf --------------------编程问答-------------------- mark --------------------编程问答-------------------- 谢谢,学习了 --------------------编程问答-------------------- 学习了。谢谢 --------------------编程问答--------------------
--------------------编程问答--------------------
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace MyBookShopDAL
{
public abstract class DBHelper
{
private static readonly string connString =
ConfigurationManager.ConnectionStrings["S2_MyBookShopConnectionString"].ConnectionString;
private static SqlConnection cnn;
private static SqlConnection GetConnection()
{
if (cnn == null)
try
{
cnn = new SqlConnection(connString);
}
catch (Exception ex)
{
throw ex;
}
if (cnn.State == ConnectionState.Broken ||
cnn.State == ConnectionState.Open)
cnn.Close();
cnn.Open();
return cnn;
}
private static SqlCommand CreateCommand(string sql, CommandType type, params SqlParameter[] ps)
{
SqlCommand cmd = GetConnection().CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = type;
if (ps != null)
cmd.Parameters.AddRange(ps);
return cmd;
}
public static DataSet ExecuteDataSet(string sql)
{
SqlDataAdapter sda = new SqlDataAdapter(sql,connString);
DataSet ds = new DataSet();
sda.Fill(ds,"MySchoolShop");
return ds;
}
public static int ExecuteNonQuery(string sql, CommandType type, params SqlParameter[] ps)
{
try
{
SqlCommand comd = CreateCommand(sql, type, ps);
return comd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
cnn.Close();
}
}
/// <summary>
/// 本方法要求调用者得到SqlDataReader后，必须调用Close()关闭
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="type"></param>
/// <param name="ps"></param>
/// <returns></returns>
public static SqlDataReader ExecuteDataReader(string sql, CommandType type,params SqlParameter[] ps)
{
try
{
SqlCommand cmd = CreateCommand(sql,type,ps);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
cnn.Close();
throw ex;
}
}
public static object ExecuteScalar(string sql, CommandType type, params SqlParameter[] ps)
{
try
{
SqlCommand cmd = CreateCommand(sql,type,ps);
return cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
cnn.Close();
}
}
}
}
using System;--------------------编程问答--------------------
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;
namespace TopWinCMS.Common
{
/// <summary>
/// SQL Server数据访问层基类
/// 自动识别存储过程或者SQL查询语句
/// 支持长短数据库连接
/// </summary>
public class SQLBase : IDisposable
{
private SqlConnection conn;
private SqlTransaction trans = null;
#region 连接字符串
/// <summary>
/// 连接字符串
/// </summary>
private static string ConnectionString
{
get
{
string strConnection = MyCache.ReadCacheString("ConnectionSqlServer");
if (strConnection != null)
{
return strConnection;
}
strConnection = ConfigurationManager.AppSettings["ConnectionSqlServer"];
if (string.IsNullOrEmpty(strConnection))
{
strConnection = XML.GetConfig("ConnectionSqlServer");
}
MyCache.InsertCache("ConnectionSqlServer", strConnection, 3600);
return strConnection;
}
}
#endregion
#region 实例化数据库连接
/// <summary>
/// 选择是否使用长短数据库连接
/// </summary>
/// <param name="option"></param>
/// <remarks>LinkOption.Short 的时候是給dataReader用的</remarks>
public SQLBase(LinkOption option)
{
if (option != LinkOption.Short)
{
conn = new SqlConnection(ConnectionString);
conn.Open();
}
}
/// <summary>
/// 新建数据库连接,构造函数
/// </summary>
public SQLBase()
{
conn = new SqlConnection(ConnectionString);
conn.Open();
}
/// <summary>
/// 新建数据库连接,构造函数
/// </summary>
/// <param name="strConnection"></param>
public SQLBase(string strConnection)
{
conn = new SqlConnection(strConnection);
conn.Open();
}
#endregion
#region 释放资源
/// <summary>
/// 释放资源
/// </summary>
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(true);
}
/// <summary>
/// 释放资源
/// </summary>
/// <param name="disposing"></param>
protected virtual void Dispose(bool disposing)
{
if (!disposing)
{
return;
}
if (trans != null)
{
try
{
trans.Commit();
}
catch { }
}
this.conn.Close();
}
#endregion
#region 计算含有多少行数据
/// <summary>
/// 计算含有多少行数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int CountData(SQLModel s_model)
{
using (SqlCommand cmd = new SqlCommand("P_TopWinCMS_CountData", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@TableName", SqlDbType.NVarChar, 4000));
cmd.Parameters["@TableName"].Value = s_model.TableName;
cmd.Parameters.Add(new SqlParameter("@WHERE", SqlDbType.NVarChar, 4000));
cmd.Parameters["@WHERE"].Value = s_model.WHERE;
return (int)cmd.ExecuteScalar();
}
}
#endregion
#region 执行ExecuteNonQuery
/// <summary>
/// 执行查询语句
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <returns></returns>
public int ExecuteNonQuery(string strSQL)
{
return ExecuteNonQuery(strSQL, (SqlParameter[])null, CommandType.Text);
}
/// <summary>
/// 执行查询语句
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="CT"></param>
/// <returns></returns>
public int ExecuteNonQuery(string strSQL, CommandType CT)
{
return ExecuteNonQuery(strSQL, (SqlParameter[])null, CT);
}
/// <summary>
/// 执行查询语句或者存储过程
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="arrParameter"></param>
/// <returns></returns>
public int ExecuteNonQuery(string strSQL, SqlParameter[] arrParameter)
{
return ExecuteNonQuery(strSQL, arrParameter, CommandType.Text);
}
/// <summary>
/// 执行查询语句或者存储过程
/// </summary>
/// <param name="strSQL"></param>
/// <param name="arrParameter"></param>
/// <returns></returns>
public int ExecuteNonQuery(string strSQL, List<SqlParameter> arrParameter)
{
return ExecuteNonQuery(strSQL, arrParameter.ToArray(), CommandType.Text);
}
/// <summary>
/// 执行查询语句或者存储过程
/// </summary>
/// <param name="strSQL"></param>
/// <param name="arrParameter"></param>
/// <param name="CT"></param>
/// <returns></returns>
public int ExecuteNonQuery(string strSQL, List<SqlParameter> arrParameter, CommandType CT)
{
return ExecuteNonQuery(strSQL, arrParameter.ToArray(), CT);
}
/// <summary>
/// 执行查询语句
/// </summary>
/// <param name="strSQL"></param>
/// <param name="arrParameter"></param>
/// <param name="CT"></param>
/// <returns></returns>
public int ExecuteNonQuery(string strSQL, SqlParameter[] arrParameter, CommandType CT)
{
using (SqlCommand cmd = new SqlCommand(strSQL, conn))
{
if (trans == null && CT != CommandType.StoredProcedure)
{
trans = conn.BeginTransaction();
}
if (CT != CommandType.StoredProcedure)
{
cmd.Transaction = trans;
}
cmd.CommandType = CT;
if (arrParameter != null)
cmd.Parameters.AddRange(arrParameter);
try
{
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
catch (SqlException SE)
{
if (CT != CommandType.StoredProcedure)
{
trans.Rollback();
trans = null;
}
throw new Exception("执行" + strSQL + "发生错误，错误信息是：" + SE.Message);
}
}
}
/// <summary>
/// 执行查询语句
/// </summary>
/// <param name="strSQL"></param>
/// <param name="arrParameter"></param>
/// <param name="CT"></param>
/// <param name="objReturn"></param>
/// <returns></returns>
/// <remarks>只准用于存储过程，事务写于存储过程中。</remarks>
public int ExecuteNonQuery(string strSQL, SqlParameter[] arrParameter, out object objReturn)
{
using (SqlCommand cmd = new SqlCommand(strSQL, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter output = null;
foreach (SqlParameter sp in arrParameter)
{
if (sp.Direction == ParameterDirection.Output)
{
output = sp;
}
cmd.Parameters.Add(sp);
}
try
{
int val = cmd.ExecuteNonQuery();
objReturn = output.Value;
cmd.Parameters.Clear();
return val;
}
catch (SqlException SE)
{
throw new Exception("执行" + strSQL + "发生错误，错误信息是：" + SE.Message);
}
}
}
/// <summary>
/// 批量执行Sql
/// </summary>
/// <param name="sr"></param>
public void ExecuteNonQuery(System.IO.StreamReader sr)
{
ExecuteNonQuery(SqlText2SqlList(sr));
}
/// <summary>
/// 批量执行Sql
/// </summary>
/// <param name="SqlList"></param>
public void ExecuteNonQuery(List<string> SqlList)
{
foreach (string sql in SqlList)
{
ExecuteNonQuery(sql);
}
}
#endregion
--------------------编程问答--------------------
#region 执行ExecuteScalar
/// <summary>
/// 执行SQL命令和没有参数的存储过程，返回object
/// </summary>
/// <param name="strSQLpro">SQL语句</param>
/// <return></return>
public object ExecuteScalar(string strSQL)
{
return ExecuteScalar(strSQL, (SqlParameter[])null, CommandType.Text);
}
/// <summary>
/// 执行SQL命令和没有参数的存储过程，返回object
/// </summary>
/// <param name="strSQL"></param>
/// <param name="CT"></param>
/// <returns></returns>
public object ExecuteScalar(string strSQL, CommandType CT)
{
return ExecuteScalar(strSQL, (SqlParameter[])null, CT);
}
/// <summary>
/// 执行SQL命令和没有参数的存储过程，返回object
/// </summary>
/// <param name="strSQL"></param>
/// <param name="arrParameter"></param>
/// <returns></returns>
public object ExecuteScalar(string strSQL, SqlParameter[] arrParameter)
{
return ExecuteScalar(strSQL, arrParameter, CommandType.Text);
}
/// <summary>
/// 执行查询语句或者存储过程，返回object
/// </summary>
/// <param name="strSQLpro">SQL语句</param>
/// <param name="arrParameter"></param>
/// <param name="CT"></param>
public object ExecuteScalar(string strSQL, SqlParameter[] arrParameter, CommandType CT)
{
using (SqlCommand cmd = new SqlCommand(strSQL, conn))
{
if (trans == null && CT != CommandType.StoredProcedure)
{
trans = conn.BeginTransaction(); ;
}
if (CT != CommandType.StoredProcedure)
{
cmd.Transaction = trans;
}
cmd.CommandType = CT;
if (arrParameter != null)
cmd.Parameters.AddRange(arrParameter);
try
{
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
catch (Exception ex)
{
if (CT != CommandType.StoredProcedure)
{
trans.Rollback();
trans = null;
}
throw new Exception("执行" + strSQL + "发生错误，错误信息是：" + ex.Message);
}
}
}
#endregion
#region 填充DataSet
/// <summary>
/// 执行SQL命令和没有参数的存储过程，返回DataSet
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <return>DataSet</return>
public DataSet ReturnDataSet(string strSQL)
{
return ReturnDataSet(strSQL, (SqlParameter[])null, CommandType.Text);
}
/// <summary>
/// 执行SQL命令和没有参数的存储过程，返回DataSet
/// </summary>
/// <param name="strSQL"></param>
/// <param name="CT"></param>
/// <returns></returns>
public DataSet ReturnDataSet(string strSQL, CommandType CT)
{
return ReturnDataSet(strSQL, (SqlParameter[])null, CT);
}
/// <summary>
/// 执行SQL存储过程，返回DataSet
/// </summary>
/// <param name="strSQL"></param>
/// <param name="arrParameter"></param>
/// <returns></returns>
public DataSet ReturnDataSet(string strSQL, SqlParameter[] arrParameter)
{
return ReturnDataSet(strSQL, arrParameter, CommandType.Text);
}
/// <summary>
/// 执行SQL存储过程，返回DataSet
/// </summary>
/// <param name="strSQL">存储过程</param>
/// <param name="SqlParameter[]">存储过程参数</param>
/// <return>DataSet</return>
public DataSet ReturnDataSet(string strSQL, SqlParameter[] arrParameter, CommandType CT)
{
SqlCommand cmd = new SqlCommand(strSQL, conn);
cmd.CommandType = CT;
using (SqlDataAdapter ada = new SqlDataAdapter())
{
DataSet Mydataset = new DataSet();
if (arrParameter != null)
cmd.Parameters.AddRange(arrParameter);
try
{
ada.SelectCommand = cmd;
ada.Fill(Mydataset);
}
catch (Exception err)
{
throw new Exception(err.Message + "SQL：" + strSQL);
}
cmd.Parameters.Clear();
return Mydataset;
}
}
/// <summary>
/// 执行SQL存储过程，返回DataSet
/// </summary>
/// <param name="strSQL">存储过程</param>
/// <param name="SqlParameter[]">存储过程参数</param>
/// <return>DataSet</return>
public DataSet ReturnDataSet(string strSQL, SqlParameter[] arrParameter, ref object objReturn)
{
using (SqlCommand cmd = new SqlCommand(strSQL, conn))
{
if (MyValidate.IsSQL(strSQL) == false)//如果不是SQL查询语句说明就是存储过程了
{
cmd.CommandType = CommandType.StoredProcedure;
}
SqlParameter output = null;
using (SqlDataAdapter ada = new SqlDataAdapter())
{
DataSet Mydataset = new DataSet();
if (arrParameter != null)
{
foreach (SqlParameter sp in arrParameter)
{
if (sp.Direction == ParameterDirection.Output)
{
output = sp;
}
cmd.Parameters.Add(sp);
}
}
try
{
ada.SelectCommand = cmd;
ada.Fill(Mydataset);
objReturn = output.Value;
}
catch (Exception err)
{
throw new Exception(err.Message + "SQL：" + strSQL);
}
cmd.Parameters.Clear();
return Mydataset;
}
}
}
/// <summary>
/// 返回分页数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public DataSet ReturnDataSet(SQLModel model)
{
DataSet Mydataset = new DataSet();
using (SqlCommand cmd = new SqlCommand("P_TopWinCMS_PageList", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@TableName", SqlDbType.NVarChar));
cmd.Parameters["@TableName"].Value = model.TableName;
cmd.Parameters.Add(new SqlParameter("@Fields", SqlDbType.NVarChar));
cmd.Parameters["@Fields"].Value = model.Fields;
cmd.Parameters.Add(new SqlParameter("@FieldKey", SqlDbType.NVarChar));
cmd.Parameters["@FieldKey"].Value = model.FieldKey;
cmd.Parameters.Add(new SqlParameter("@WHERE", SqlDbType.NVarChar, 4000));
cmd.Parameters["@WHERE"].Value = model.WHERE;
cmd.Parameters.Add(new SqlParameter("@OrderField", SqlDbType.NVarChar));
cmd.Parameters["@OrderField"].Value = model.OrderField;
//cmd.Parameters.Add(new SqlParameter("@OrderType", SqlDbType.NVarChar));
//cmd.Parameters["@OrderType"].Value = model.OrderType;
cmd.Parameters.Add(new SqlParameter("@PageNo", SqlDbType.Int));
cmd.Parameters["@PageNo"].Value = model.PageNo;
cmd.Parameters.Add(new SqlParameter("@PageSize", SqlDbType.Int));
cmd.Parameters["@PageSize"].Value = model.PageSize;
using (SqlDataAdapter ada = new SqlDataAdapter())
{
ada.SelectCommand = cmd;
ada.Fill(Mydataset);
return Mydataset;
}
}
}
/// <summary>
/// 返回分页数据
/// </summary>
/// <param name="strSQL"></param>
/// <param name="s_model"></param>
/// <returns></returns>
public DataSet ReturnDataSet(string strSQL, SQLModel s_model)
{
DataSet ds = new DataSet();
using (SqlCommand cmd = new SqlCommand(strSQL, conn))
{
using (SqlDataAdapter ada = new SqlDataAdapter())
{
ada.SelectCommand = cmd;
ada.Fill(ds, s_model.PageSize * (s_model.PageNo - 1), s_model.PageSize, "table");
return ds;
}
}
}
/// <summary>
/// 返回分页数据
/// </summary>
/// <param name="strSQL"></param>
/// <param name="PageNo"></param>
/// <param name="PageSize"></param>
/// <returns></returns>
public DataSet ReturnDataSet(string strSQL, int PageNo, int PageSize)
{
DataSet ds = new DataSet();
using (SqlCommand cmd = new SqlCommand(strSQL, conn))
{
using (SqlDataAdapter ada = new SqlDataAdapter())
{
ada.SelectCommand = cmd;
ada.Fill(ds, PageSize * (PageNo - 1), PageSize, "table");
return ds;
}
}
}
#endregion}
#region 返回DataRow
/// <summary>
/// 执行SQL命令，返回DataRow
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <return>DataRow</return>
public DataRow ReturnDataRow(string strSQL)
{
return ReturnDataRow(strSQL, (SqlParameter[])null, CommandType.Text);
}
/// <summary>
/// 执行SQL命令和没有参数的存储过程，返回DataRow
/// </summary>
/// <param name="strSQL"></param>
/// <param name="CT"></param>
/// <returns></returns>
public DataRow ReturnDataRow(string strSQL, CommandType CT)
{
return ReturnDataRow(strSQL, (SqlParameter[])null, CT);
}
/// <summary>
/// 执行SQL存储过程，返回DataRow
/// </summary>
/// <param name="strSQL">存储过程</param>
/// <param name="SqlParameter[]">存储过程参数</param>
/// <return>DataRow</return>
public DataRow ReturnDataRow(string strSQL, SqlParameter[] arrParameter)
{
return ReturnDataRow(strSQL, arrParameter, CommandType.Text);
}
/// <summary>
/// 执行SQL存储过程，返回DataRow
/// </summary>
/// <param name="strSQL"></param>
/// <param name="arrParameter"></param>
/// <param name="CT"></param>
/// <returns></returns>
public DataRow ReturnDataRow(string strSQL, SqlParameter[] arrParameter, CommandType CT)
{
DataSet ds = ReturnDataSet(strSQL, arrParameter, CT);
if (ds.Tables[0].Rows.Count > 0)
return ds.Tables[0].Rows[0];
else
return null;
}
#endregion
#region 返回 SqlDataReader
/// <summary>
/// 执行SQL命令
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string strSQL)
{
return ExecuteReader(strSQL, (SqlParameter[])null, CommandType.Text);
}
/// <summary>
/// 执行SQL命令和没有参数的存储过程
/// </summary>
/// <param name="strSQL"></param>
/// <param name="CT"></param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string strSQL, CommandType CT)
{
return ExecuteReader(strSQL, (SqlParameter[])null, CT);
}
/// <summary>
/// 执行SQL命令
/// </summary>
/// <param name="strSQL"></param>
/// <param name="arrParameter"></param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string strSQL, SqlParameter[] arrParameter)
{
return ExecuteReader(strSQL, arrParameter, CommandType.Text);
}
/// <summary>
/// 执行SQL命令或没有参数的或者存储过程
/// </summary>
/// <param name="strSQL"></param>
/// <param name="arrParameter"></param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string strSQL, SqlParameter[] arrParameter, CommandType CT)
{
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand(strSQL, conn);
cmd.CommandType = CT;
if (arrParameter != null)
cmd.Parameters.AddRange(arrParameter);
conn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
/// <summary>
/// 执行SQL命令和没有参数的或者存储过程
/// </summary>
/// <param name="strSQL"></param>
/// <param name="arrParameter"></param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string strSQL, SqlParameter[] arrParameter, ref object objReturn)
{
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand(strSQL, conn);
if (MyValidate.IsSQL(strSQL) == false)
{
cmd.CommandType = CommandType.StoredProcedure;
}
SqlParameter output = null;
foreach (SqlParameter sp in arrParameter)
{
if (sp.Direction == ParameterDirection.Output)
{
output = sp;
}
cmd.Parameters.Add(sp);
}
conn.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
objReturn = output.Value;
return dr;
}
/// <summary>
/// 分页数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public SqlDataReader ExecuteReader(SQLModel model)
{
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand("P_TopWinCMS_PageList", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@TableName", SqlDbType.NVarChar));
cmd.Parameters["@TableName"].Value = model.TableName;
cmd.Parameters.Add(new SqlParameter("@Fields", SqlDbType.NVarChar));
cmd.Parameters["@Fields"].Value = model.Fields;
cmd.Parameters.Add(new SqlParameter("@FieldKey", SqlDbType.NVarChar));
cmd.Parameters["@FieldKey"].Value = model.FieldKey;
cmd.Parameters.Add(new SqlParameter("@WHERE", SqlDbType.NVarChar));
cmd.Parameters["@WHERE"].Value = model.WHERE;
cmd.Parameters.Add(new SqlParameter("@OrderField", SqlDbType.NVarChar));
cmd.Parameters["@OrderField"].Value = model.OrderField;
//cmd.Parameters.Add(new SqlParameter("@OrderType", SqlDbType.NVarChar));
//cmd.Parameters["@OrderType"].Value = model.OrderType;
cmd.Parameters.Add(new SqlParameter("@PageNo", SqlDbType.Int));
cmd.Parameters["@PageNo"].Value = model.PageNo;
cmd.Parameters.Add(new SqlParameter("@PageSize", SqlDbType.Int));
cmd.Parameters["@PageSize"].Value = model.PageSize;
conn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
#endregion
补充：.NET技术 , C#
