#region Copyright (c) 2005, By Sirius
/*================================================================
*
* Copyright (c) 2006, Sirius, All rights reserved.
*
* FileName : DBedit.cs
* Author : Sirius
* CreateDate : 2006/04/25
* ChangeDate :
*
=================================================================*/
#endregion
/*
* 重要说明：本类只适用于BS，结合ASP.NET使用
* 功能说明：
* 方法名称 功能 返回类型
* --------------------------------------------------------------------------------
* ExecuteString - 执行Transcat-SQL字符串 - void
* ExecuteNonQuery - 执行存储过程 - void
* ExecuteDataTalbe - 执行存储过程 - DatTable
* ExecuteSqlDataReader - 执行存储过程 - SqlDataReader
* DataTablePrepare - 填充数据表 - DataTable
* DataSetPrepare - 填充数据集 - DataSet
* CommandPrepare - 初始化SqlCommand对象 - void
* CommandDispose - 释放SqlCommand对象 - void
* --------------------------------------------------------------------------------
*/
namespace DBedit
{
using System;
using System.Data;
using System.Data.SqlClient;
/// <summary>
/// 名字：DBedit
/// 类型：继承类
/// 功能：数据库连接、操作
class DBedit : DBbase
{
private SqlConnection con;
private SqlCommand com;
/// <summary>
/// 名称：DBblog
/// 类型：析构
/// 功能：使用SQL连接字符串初始化
/// </summary>
public DBedit()
{
this.con = new SqlConnection();
this.com = new SqlCommand();
this.con.ConnectionString = this.connectionString;
}
/// <summary>
/// 名称：DBblog
/// 类型：析构
/// 功能：释放资源
/// </summary>
~ DBedit()
{
if(this.com != null)
{
this.com.Dispose();
}
if(this.con.State == ConnectionState.Open)
{
this.con.Close();
}
}
#region ----------- ExecuteString -----------
/// <summary>
/// 名称：ExecuteString
/// 类型：实例方法
/// 功能：执行SQL命令字符串
/// </summary>
/// <param name="cmdText">SQL命令字符串</param>
public void ExecuteStringA(string cmdText)
{
this.CommandPrepare(this.com, cmdText);
this.con.Open();
this.com.ExecuteNonQuery();
this.con.Close();
}
/// <summary>
/// 名称：ExecuteString
/// 类型：实例方法
/// 功能：执行SQL命令字符串，向指定数据表填充数据
/// </summary>
/// <param name="cmdText">SQL命令字符串</param>
/// <param name="dt">需要填充的数据表</param>
public void ExecuteStringA(string cmdText, DataTable dt)
{
dt = this.ExecuteStringB(cmdText);
}
/// <summary>
/// 名称：ExecuteString
/// 类型：实例方法
/// 功能：执行SQL命令字符串，向指定数据集填充数据
/// </summary>
/// <param name="cmdText">SQL命令字符串</param>
/// <param name="dt">需要填充的数据集</param>
public void ExecuteStringA(string cmdText, DataSet ds)
{
ds = this.ExecuteStringC(cmdText);
}
/// <summary>
/// 名称：ExecuteString
/// 类型：实例方法
/// 功能：执行SQL命令字符串，返回数据表
/// </summary>
/// <param name="cmdText">SQL命令字符串</param>
/// <returns>已填充数据的数据表</returns>
public DataTable ExecuteStringB(string cmdText)
{
return this.ExecuteDataTalbe(cmdText);
}
/// <summary>
/// 名称：ExecuteString
/// 类型：实例方法
/// 功能：执行SQL命令字符串，返回数据集
/// </summary>
/// <param name="cmdText">SQL命令字符串</param>
/// <returns>已填充数据的数据集</returns>
public DataSet ExecuteStringC(string cmdText)
{
return this.ExecuteDataSet(cmdText);
}
#endregion
#region ----------- ExecuteNonQuery -----------
/// <summary>
/// 名称：ExecuteNonQuery
/// 类型：实例方法
/// 功能：执行存储过程
/// </summary>
/// <param name="procedureName">存储过程名</param>
/// <param name="paras">存储过程参数数组</param>
public void ExecuteNonQuery(string procedureName, SqlParameter para)
{
SqlParameter[] paras = new SqlParameter[1] { para };
this.ExecuteNonQuery(procedureName, paras);
}
/// <summary>
/// 名称：ExecuteNonQuery
/// 类型：实例方法
/// 功能：执行存储过程
/// </summary>
/// <param name="procedureName">存储过程名</param>
/// <param name="paras">存储过程参数数组</param>
public void ExecuteNonQuery(string procedureName, SqlParameter[] paras)
{
this.CommandPrepare(this.com, procedureName, paras);
this.con.Open();
this.com.ExecuteNonQuery();
this.con.Close();
this.CommandDispose(this.com);
}
/// <summary>
/// 名称：ExecuteNonQuery
/// 类型：实例方法
/// 功能：执行SQL命令字符串
/// </summary>
/// <param name="ProcedureName">存储过程名</param>
/// <param name="Params">参数数组</param>
/// <param name="dt">预填充数据的数据表</param>
public void ExecuteNonQuery(string procedureName, SqlParameter[] paras, DataTable dt)
{
dt = this.ExecuteDataTalbe(procedureName, paras);
}
/// <summary>
/// 名称：ExecuteNonQuery
/// 类型：实例方法
/// 功能：执行SQL命令字符串
/// </summary>
/// <param name="ProcedureName">存储过程名</param>
/// <param name="Params">参数数组</param>
/// <param name="dt">预填充数据的数据集</param>
public void ExecuteNonQuery(string procedureName, SqlParameter[] paras, DataSet ds)
{
ds = this.ExecuteDataSet(procedureName, paras);
}
#endregion
#region ----------- ExecuteDataTalbe -----------
/// <summary>
/// 名称：ExecuteDataTalbe
/// 类型：实例方法
/// 功能：执行SQL命令字符串，返回数据表
/// </summary>
/// <param name="cmdText">SQL命令字符串</param>
/// <returns>数据表</returns>
public DataTable ExecuteDataTalbe(string cmdText)
{
this.CommandPrepare(this.com, cmdText);
return this.DataTablePrepare(this.com);
}
/// <summary>
/// 名称：ExecuteDataTalbe
/// 类型：实例方法
/// 功能：执行SQL命令字符串，返回数据表
/// </summary>
/// <param name="procedureName">存储过程名</param>
/// <param name="para">存储过程参数</param>
/// <returns>数据表</returns>
public DataTable ExecuteDataTalbe(string procedureName, SqlParameter para)
{
this.CommandPrepare(this.com, procedureName, para);
return this.DataTablePrepare(this.com);
}
/// <summary>
/// 名称：ExecuteDataTalbe
/// 类型：实例方法
/// 功能：执行SQL命令字符串，返回数据表
/// </summary>
/// <param name="procedureName">存储过程名</param>
/// <param name="para">存储过程参数数组</param>
/// <returns>数据表</returns>
public DataTable ExecuteDataTalbe(string procedureName, SqlParameter[] paras)
{
this.CommandPrepare(this.com, procedureName, paras);
return this.DataTablePrepare(this.com);
}
#endregion
#region ----------- ExecuteDataSet -----------
/// <summary>
/// 名称：TeamConstructExecB
/// 类型：实例方法
/// 功能：执行SQL命令字符串，返回数据表
/// </summary>
/// <param name="cmdText">SQL命令字符串</param>
/// <returns>数据表</returns>
public DataSet ExecuteDataSet(string cmdText)
{
this.CommandPrepare(this.com, cmdText);
return this.DataSetPrepare(this.com);
}
/// <summary>
/// 名称：TeamConstructExecD
/// 类型：实例方法
/// 功能：执行SQL命令字符串，返回数据
/// </summary>
/// <param name="cmdText">SQL命令字符串</param>
public DataSet ExecuteDataSet(string procedureName, SqlParameter para)
{
this.CommandPrepare(this.com, procedureName, para);
return this.DataSetPrepare(this.com);
}
/// <summary>
/// 名称：TeamConstructExecD
/// 类型：实例方法
/// 功能：执行SQL命令字符串，返回数据
/// </summary>
/// <param name="cmdText">SQL命令字符串</param>
public DataSet ExecuteDataSet(string procedureName, SqlParameter[] paras)
{
this.CommandPrepare(this.com, procedureName, paras);
return this.DataSetPrepare(this.com);
}
#endregion
#region ----------- ExecuteSqlDataReader -----------
/// <summary>
/// 名称：TeamConstructExecE
/// 类型：实例方法
/// 功能：执行SQL命令字符串
/// </summary>
/// <param name="ProcedureName">存储过程名</param>
/// <param name="Params">存储过程参数</param>
/// <returns></returns>
public SqlDataReader ExecuteSqlDataReader(string procedureName, SqlParameter para)
{
this.CommandPrepare(this.com, procedureName, para);
this.con.Open();
return this.com.ExecuteReader();
}
/// <summary>
/// 名称：TeamConstructExecE
/// 类型：实例方法
/// 功能：执行SQL命令字符串
/// </summary>
/// <param name="ProcedureName">存储过程名</param>
/// <param name="Params">存储过程参数</param>
/// <returns></returns>
public SqlDataReader ExecuteSqlDataReader(string procedureName, SqlParameter[] paras)
{
this.CommandPrepare(this.com, procedureName, paras);
this.con.Open();
return this.com.ExecuteReader();
}
#endregion
#region ----------- Data Prepare -----------
/// <summary>
/// 名称：DataTablePrepare
/// 类型：实例方法
/// 功能：填充数据表
/// </summary>
/// <param name="com">已初始化的SqlCommand对象</param>
/// <returns>填充过数据的数据表</returns>
public DataTable DataTablePrepare(SqlCommand com)
{
DataTable dt = new DataTable();
using (SqlDataAdapter da = new SqlDataAdapter(com))
{
da.Fill(dt);
}
return dt;
}
/// <summary>
/// 名称：DataSetPrepare
/// 类型：实例方法
/// 功能：填充数据集
/// </summary>
/// <param name="com">已初始化的SqlCommand对象</param>
/// <returns>填充过数据的数据集</returns>
public DataSet DataSetPrepare(SqlCommand com)
{
DataSet ds = new DataSet();
using (SqlDataAdapter da = new SqlDataAdapter(com))
{
da.Fill(ds);
}
return ds;
}
#endregion
#region ----------- Command Prepare -----------
/// <summary>
/// 名称：CommandPrepare
/// 类型：实例方法
/// 功能：设置SqlCommand的CommandType属性，赋予对象参数
/// </summary>
/// <param name="com">预设置SqlCommand对象</param>
/// <param name="cmdText">Transcat - SQL字符串</param>
public void CommandPrepare(SqlCommand com, string cmdText)
{
com.Connection = this.con;
com.CommandTimeout = 15;
com.CommandType = CommandType.Text;
com.CommandText = cmdText;
}
/// <summary>
/// 名称：ParameterPrepare
/// 类型：实例方法
/// 功能：设置SqlCommand的CommandType属性，赋予对象参数
/// </summary>
/// <param name="com">预设置SqlCommand对象</param>
/// <param name="paras">预赋予参数</param>
public void CommandPrepare(SqlCommand com, string procedureName, SqlParameter para)
{
com.Connection = this.con;
com.CommandTimeout = 15;
com.CommandType = CommandType.StoredProcedure;
com.CommandText = procedureName;
com.Parameters.Clear();
if(para != null)
{
com.Parameters.Add(para);
}
}
/// <summary>
/// 名称：ParameterPrepare
/// 类型：实例方法
/// 功能：设置SqlCommand的CommandType属性，赋予对象参数
/// </summary>
/// <param name="com">预赋予参数的SqlCommand对象</param>
/// <param name="paras">预赋予参数数组</param>
public void CommandPrepare(SqlCommand com, string procedureName, params SqlParameter[] paras)
{
com.Connection = this.con;
com.CommandTimeout = 15;
com.CommandType = CommandType.StoredProcedure;
com.CommandText = procedureName;
com.Parameters.Clear();
foreach(SqlParameter para in paras)
{
com.Parameters.Add(para);
}
}
/// <summary>
/// 名称：CommandDispose
/// 类型：实例方法
/// 功能：清空SqlCommand对象参数
/// </summary>
/// <param name="com">预清空参数的SqlCommand对象</param>
public void CommandDispose(SqlCommand com)
{
if(com.Parameters.Count > 0)
{
com.Parameters.Clear();
}
com.Dispose();
}
#endregion
}
/// <summary>
/// 名字：DBbase
/// 类型：基类
/// 功能：提供继承连接属性
/// </summary>
public class DBbase
{
/// <summary>
/// 名字：connectionString
/// 类型：属性
/// 功能：从Web.config文件获取连接数据库字符串
/// </summary>
public string connectionString
{
get
{
return System.Configuration.ConfigurationSettings.AppSettings["SiteSqlServer"];
}
}
}
}
