using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Text;
using System.Collections;
namespace DBUtility
{
/// <summary>
/// ConnDB 的摘要说明。
/// </summary>
public class ConnDB
{
protected SqlConnection Connection;
public static readonly string ConnectionString= System.Configuration.ConfigurationManager.ConnectionStrings["SQLSever"].ToString();
/// <summary>
/// 默认构造函数
/// </summary>
public ConnDB()
{
Connection = new SqlConnection(ConnectionString);
}
#region "private method"
#region "完成SqlCommand对象的实例化"
/// <summary>
/// 完成SqlCommand对象的实例化
/// </summary>
/// <param name="storedProcName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
private SqlCommand BuildCommand(string storedProcName, CommandType cmdType, params IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand(storedProcName, Connection);
command.CommandType = cmdType;
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
return command;
}
#endregion
#endregion
#region "public method"
#region "执行SQL,无返回值"
/// <summary>
/// 执行SQL,无返回值
/// </summary>
/// <param name="storedProcName"></param>
/// <param name="cmdType"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public int ExecuteSQLNonQuery(CommandType cmdType, string cmdText, params IDataParameter[] parameters)
{
Connection.Open();
SqlCommand command;
command = BuildCommand(cmdText, cmdType, parameters);
int val=command.ExecuteNonQuery();
Connection.Close();
return val;
}
#endregion
#region "执行SQL语句，返回DataSet"
/// <summary>
/// 执行SQL语句，返回数据到DataSet中
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataSet ExecuteSQLDataSet(CommandType cmdType, string cmdText, params IDataParameter[] parameters)
{
DataSet dataSet = new DataSet();
Connection.Open();
SqlCommand command = BuildCommand(cmdText, cmdType, parameters);
SqlDataAdapter sqlDA = new SqlDataAdapter(command);
sqlDA.Fill(dataSet, "objDataSet");
Connection.Close();
return dataSet;
}
#endregion
#region "执行SQL语句，返回 DataReader"
/// <summary>
/// 执行SQL语句，返回 DataReader
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public SqlDataReader ExecuteSQLDataReader(CommandType cmdType, string cmdText, params IDataParameter[] parameters)
{
Connection.Open();
SqlCommand command = BuildCommand(cmdText, cmdType, parameters);
SqlDataReader dataReader = command.ExecuteReader();
return dataReader;
}
#endregion
#region "返回第一行第一列的值"
/// <summary>
/// 返回第一行第一列的值
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public object ExecuteSQLScalar(CommandType cmdType, string cmdText, params IDataParameter[] parameters)
{
SqlCommand command = BuildCommand(cmdText, cmdType, parameters);
object val = command.ExecuteScalar();
return val;
}
#endregion
#region "执行SQL语句（提供事务功能）"
/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public bool ExecuteSQLNonByTrans(CommandType cmdType, string cmdText, params IDataParameter[] parameters)
{
bool successState = false;
Connection.Open();
SqlTransaction myTrans = Connection.BeginTransaction();
SqlCommand command = new SqlCommand(cmdText, Connection, myTrans);
try
{
command.ExecuteNonQuery();
myTrans.Commit();
successState = true;
}
catch
{
myTrans.Rollback();
}
finally
{
Connection.Close();
}
return successState;
}
#endregion
#region "执行多条SQL语句（提供事务功能）"
/// <summary>
/// 执行多条SQL语句（提供事务功能）
/// </summary>
/// <param name="sqls"></param>
/// <returns></returns>
public bool ExecuteSQLsNonByTrans(CommandType cmdType, string[] sqls, params IDataParameter[] parameters)
{
bool successState = false;
Connection.Open();
SqlTransaction myTrans = Connection.BeginTransaction();
SqlCommand command;
try
{
foreach (string sql in sqls)
{
command = new SqlCommand(sql, Connection, myTrans);
command.ExecuteNonQuery();
}
myTrans.Commit();
successState = true;
}
catch
{
myTrans.Rollback();
}
finally
{
Connection.Close();
}
return successState;
}
#endregion
#region "执行存储过程，返回单个返回值"
/// <summary>
/// 执行存储过程，返回单个返回值
/// parameter.Direction=ParameterDirection.ReturnValue
/// </summary>
/// <param name="storedProcName"></param>
/// <param name="parameters"></param>
/// <param name="rowsAffected"></param>
/// <returns></returns>
public object ExecuteProcReturnValue(string storedProcName, CommandType cmdType, out int rowsAffected, params IDataParameter[] parameters)
{
object ReturnValue=null;
Connection.Open();
SqlCommand command = BuildCommand(storedProcName, cmdType, parameters);
rowsAffected = command.ExecuteNonQuery();
foreach (IDataParameter parameter in command.Parameters)
{
if (parameter.Direction == ParameterDirection.ReturnValue)
{
ReturnValue = command.Parameters[parameter.ParameterName].Value;
break;
}
}
Connection.Close();
return ReturnValue;
}
#endregion
#region "执行存储过程，返回多个返回值"
/// <summary>
/// 执行存储过程，返回多个返回值
/// parameter.Direction == ParameterDirection.ReturnValue
/// parameter.Direction == ParameterDirection.InputOutput
/// parameter.Direction == ParameterDirection.Output
/// </summary>
/// <param name="storedProcName"></param>
/// <param name="parameters"></param>
/// <param name="rowsAffected"></param>
/// <returns></returns>
public Hashtable ExecuteProcReturnValues(string storedProcName, CommandType cmdType, out int rowsAffected, params IDataParameter[] parameters)
{
Hashtable returnValue = new Hashtable();
Connection.Open();
SqlCommand command = BuildCommand(storedProcName, cmdType, parameters);
rowsAffected = command.ExecuteNonQuery();
foreach (IDataParameter parameter in command.Parameters)
{
if (parameter.Direction == ParameterDirection.ReturnValue &&
parameter.Direction == ParameterDirection.InputOutput &&
parameter.Direction == ParameterDirection.Output)
{
returnValue.Add(parameter.ParameterName,
command.Parameters[parameter.ParameterName].Value);
break;
}
}
Connection.Close();
return returnValue;
}
#endregion
#region "关闭数据库联接"
/// <summary>
/// 关闭数据库联接
/// </summary>
public void Close()
{
Connection.Close();
}
#endregion
#endregion
}
}
修正中...
