|
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
namespace StudyV.DBUtility
{
///
/// 调用存储过程工具类
///
public class DBSP: System.Object
{
///
/// 数据库连接列表
///
private SqlConnection m_SqlConnection ;
///
/// 屏蔽初始化
///
private DBSP()
{
}
///
/// 初始化时，输入存储过程名称
/// (string) 存储过程名称
///
public DBSP(string p_SPName)
{
FCommand.CommandType = CommandType.StoredProcedure;
FCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("ReturnValue",SqlDbType.BigInt,64,ParameterDirection.ReturnValue,false,0,0,string.Empty,DataRowVersion.Default,null));
FCommand.CommandText = p_SPName;
FCommand.Connection = GetSqlConnection();
}
public DBSP(string p_SPName,bool p_IsSqlText)
{
FCommand.CommandType = CommandType.Text;
FCommand.CommandText = p_SPName;
FCommand.Connection = GetSqlConnection();
}
///
/// 数据库操作命令
///
private SqlCommand FCommand = new SqlCommand();
///
/// 数据适配器
///
private SqlDataAdapter FDataAdapter = new SqlDataAdapter();
///
/// 调用后产生的数据集
///
private DataSet FDataSet = new DataSet();
///
/// 查询数据集
///
public DataView QueryData()
{
try
{
FDataSet.Clear();
FDataAdapter.SelectCommand = FCommand;
FDataAdapter.Fill(FDataSet, "SQLTable");
DisposeConnection();
return FDataSet.Tables["SQLTable"].DefaultView;
}
finally
{
}
}
public DataView ExecuteDataset(int pageIndex, int pageSize)
{
int firstPage = pageIndex * pageSize;
try
{
FDataSet.Clear();
FDataAdapter.SelectCommand = FCommand;
FDataAdapter.Fill(FDataSet, firstPage, pageSize, "SQLTable");
DisposeConnection();
return FDataSet.Tables["SQLTable"].DefaultView;
}
catch
{
throw;
}
finally
{
}
}
///
/// 查询后返回单个数据
///
public DBDataItem QuerySingleData()
{
DBDataItem DataItem = null;
object Data = null;
try
{
Data = FCommand.ExecuteScalar();
}
finally
{
DataItem = new DBDataItem(Data);
DisposeConnection();
}
return DataItem;
}
///
/// 查询后返回单列数据集
///
public ArrayList QueryDataArray()
{
ArrayList DataList = new ArrayList();
DataView dv = QueryData();
DisposeConnection();
for(int i = 0; i
/// 查询并返回哈希表
///
public Hashtable QueryDataHashtable()
{
Hashtable DataList = new Hashtable();
DataView dv = QueryData();
for(int i = 0; i
/// 执行存储过程，不返回数据
///
public void Execute()
{
try
{
FCommand.ExecuteNonQuery();
DisposeConnection();
}
finally
{
}
}
///
/// 得取存储过程返回值
///
///
public object GetReturnValue()
{
return this.FCommand.Parameters["ReturnValue"].Value;
}
///
/// 主动摧毁，减少内存消耗
///
public void Dispose()
{
DisposeConnection();
if (m_SqlConnection != null) m_SqlConnection.Dispose();
if (FDataSet!=null) FDataSet.Dispose();
if (FCommand!=null) FCommand.Dispose();
if (FDataAdapter!=null) FDataAdapter.Dispose();
}
///
/// 随机获取数据库连接
///
///
private SqlConnection GetSqlConnection()
{
if (m_SqlConnection == null)
{
m_SqlConnection = new SqlConnection();
m_SqlConnection.ConnectionString = GetConnectionString();
}
m_SqlConnection.Open();
return m_SqlConnection;
}
private void DisposeConnection()
{
if (this.m_SqlConnection.State == ConnectionState.Open)
this.m_SqlConnection.Close();
}
//private static SqlConnection GetSqlConnection()
//{
// int i = GetRandomInt();
// if (SqlConnectionList[i].State == ConnectionState.Closed)
// {
// SqlConnectionList[i].ConnectionString = GetConnectionString();
// SqlConnectionList[i].Open();
// }
// return SqlConnectionList[i];
//}
///
/// 获取连接字符串
///
private static string GetConnectionString()
{
return System.Configuration.ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
}
///
/// 获取随机数
///
///
private static int GetRandomInt()
{
Random ro = new Random();
return ro.Next(5);
}
///
/// 存储过程参数集(只读)
///
public SqlParameterCollection Paramers
{
get
{
return FCommand.Parameters;
}
}
}
}
|
