using System;
using System.Data.SqlClient;
using System.Collections;
using System.Data;
using System.Configuration;
namespace XNet
{
/// <summary>
/// 该类简化了与连结数据库的操作.用存储过程的
/// </summary>
public class SqlHelper
{
#region 成员
/// <summary>
/// 程序配置文件中的数据库连接字符串
/// </summary>
private const string XNetSqlHelperConnString = "XNetSqlHelperConnString";
/// <summary>
/// 执行数据语句
/// </summary>
private SqlCommand mySqlCommand = null ;
/// <summary>
/// 当前与数据库的连接
/// </summary>
private SqlConnection Conn = null;//
// private string connString;//数据连结字符串
/// <summary>
/// 执行存储时使用参数的类型 : 按顺序存储存储过程的参数的顺序存储值
/// </summary>
private ArrayList valueNoName = new ArrayList();
/// <summary>
/// 执行存储时使用参数的类型: 存储存储过程参数的名称和值
/// </summary>
private SortedList valueHaveName = new SortedList();
/// <summary>
/// 存储存储过程的返回值
/// </summary>
private SortedList outValue = new SortedList();
/// <summary>
/// 叛断执行存储时使用参数的类型是否使用顺序执行
/// </summary>
private Boolean useNoNmae = false;
/// <summary>
/// 判断是否有返回值
/// </summary>
private Boolean haveOutValue = false;
/// <summary>
/// 数据集,用于存储SqlHelper执行后的数据
/// </summary>
private DataSet DbSet = new DataSet();
/// <summary>
/// 当前执行的存储过程名称
/// </summary>
private string myProName = "";
/// <summary>
/// 执行搜索存储过程时,存储在DataSet中的表
/// </summary>
private DataTable dataTableRetunParameters = null;
#endregion
#region 构造函数
/// <summary>
/// 实例化类时提供数据连结字符串
/// </summary>
/// <param name="connString">数据连结字符串</param>
public SqlHelper( string connString)
{
Conn= new SqlConnection(connString);
}
/// <summary>
/// 实例化类时,由程序配置文件提供数据连结字符串(XNetSqlHelperConnString );
/// </summary>
public SqlHelper()
{
Conn = new SqlConnection(ConfigurationSettings.AppSettings[XNetSqlHelperConnString]);
}
#endregion
#region 属性
/// <summary>
/// 获取或设置数据连结字符串
/// </summary>
public string SqlConnectionString
{
get
{
return Conn.ConnectionString;
}
set
{
Conn.ConnectionString = value;
}
}
/// <summary>
/// 获取一个Boolean值,叛断所执行的存储过程是否有返回值
/// </summary>
public Boolean HaveOutValue
{
get
{
return haveOutValue;
}
}
/// <summary>
/// 获取存储参数值的数量
/// </summary>
public int ValueCount
{
get
{
return this.useNoNmae== true? this.valueNoName.Count:this.valueHaveName.Count;
}
}
/// <summary>
/// 获取或设置当前要运行的存储过程的名称
/// </summary>
public string ProcedureName
{
get
{
return myProName;
}
set
{
myProName = value;
}
}
/// <summary>
/// 获取储存过程返回值的数量
/// </summary>
public int OutValueCount
{
get
{
return this.outValue.Count;
}
}
#endregion
#region 添加存储过程值
/// <summary>
/// 在不知道存储过程参数的名称,但是知道参数的顺序可以使用此方法添加参数的值,必须按参数的顺序添加.
/// 用多字符串形式添加所执行的存储过程的参数的值
/// </summary>
/// <param name="values">参数的值.添加方法:AddValueByString("a","b","c")</param>
public void AddValueByString(params object[] values)
{
valueNoName.AddRange(values);
}
/// <summary>
/// 在不知道存储过程参数的名称,但是知道参数的顺序可以使用此方法添加参数的值,必须按参数的顺序添加
/// 不可与 "AddValueByName" 共用,可与 "InsertValueByIndex"、"AddValueByString" 共用
/// </summary>
/// <param name="values">存储过程的值</param>
public void AddValueByIndex(object values)
{
valueNoName.Add(values);
this.useNoNmae=true;
}
/// <summary>
/// 在知道存储过程名称的情况下,可以通过此方法来添加所运行的存储过程的参数值.
/// </summary>
/// <param name="name">存储过程的名称,格式:"@name"必须以"@"开头</param>
/// <param name="values">存储过程的值</param>
public void AddValueByName(string name,object values)
{
this.valueHaveName.Add(name,values);
this.useNoNmae= false;
}
/// <summary>
/// 在不知道存储过程参数的名称,但是知道参数的顺序可以使用此方法添加参数的值,必须按参数的顺序添加
/// 添加存储过程参数值到指的索引处,不可与 "AddValueByName" 共用,可与 "AddValueByIndex"、"AddValueByString" 共用
/// </summary>
/// <param name="index">在添加参数值的索引位置</param>
/// <param name="values">要添加的参数值</param>
public void InsertValueByIndex(int index,object values)
{
this.valueNoName.Insert(index,values);
this.useNoNmae = false;
}
#endregion
#region 清除当前执行的存储过程的值
/// <summary>
/// 清除传递给存储过程参数的值
/// </summary>
public void ClearValue()
{
this.valueNoName.Clear();
this.valueHaveName.Clear();
this.mySqlCommand.Parameters.Clear();
}
#endregion
#region 得到返回的值
/// <summary>
/// 通过索引得到返回参数值的名称
/// </summary>
/// <param name="index">参数索引</param>
/// <returns>返回 string 类型</returns>
public string GetOutValuesNameByIndex(int index)
{
return Convert.ToString(this.outValue.GetKey(index));
}
/// <summary>
/// 通过索引得到返回参数的值
/// </summary>
/// <param name="index">参数索引</param>
/// <returns>返回 object 类型</returns>
public object GetOutValuesValueByIndex(int index)
{
return this.outValue.GetByIndex(index);
}
/// <summary>
/// 通过参数的名称得到返回参数的值
/// </summary>
/// <param name="name">参数名称</param>
/// <returns>返回 object 类型</returns>
public object GetOutValuesValueByName(string name)
{
return this.outValue[name];
}
#endregion
#region 添加存储过程参数(保护方法)
/// <summary>
/// 添加存储过程参数
/// </summary>
/// <param name="proName">要值行的存储过程的名称</param>
private SqlCommand BeginAddParameters(string proName)
{
this.myProName= proName;
string selectProcInfo = "";
// ProcName = 存储过程名称
// Name = 参数名
// TypeName = 参数类型
// Colid = 参数在存储过程的顺序
// Isoutparam = 输入类型(输出或输入)
selectProcInfo = "select specific_name as ProcName,parameter_Name as Name,Data_Type as TypeName,ORDINAL_POSITION as Colid,parameter_Mode as Isoutparam from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME='" + proName + "' order by ORDINAL_POSITION";
//返回的SqlCommand
this.Conn.Open();
SqlCommand returnSqlCommand = new SqlCommand(selectProcInfo,this.Conn);
SqlDataAdapter myDP = new SqlDataAdapter(returnSqlCommand);
this.dataTableRetunParameters = new DataTable();
myDP.Fill(this.dataTableRetunParameters);//等到当前存储过程的信息:参数,参数的顺序, 参数的类型,参数的传递
returnSqlCommand.CommandText = proName;
returnSqlCommand.CommandType = CommandType.StoredProcedure;
if (this.dataTableRetunParameters.Rows.Count>0)
{
for(int i = 0;i <= this.dataTableRetunParameters.Rows.Count - 1;i++)
{
//当前参数的值类型
SqlDbType sqlDbType = SqlDbType.VarChar;
switch (Convert.ToString(this.dataTableRetunParameters.Rows[i]["TypeName"]))
{
case "image":
{
sqlDbType = SqlDbType.Image;
}
break;
case "text":
{
sqlDbType = SqlDbType.Text;
}
break;
case "uniqueidentifier":
{
sqlDbType = SqlDbType.UniqueIdentifier;
}
break;
case "tinyint":
{
sqlDbType = SqlDbType.TinyInt;
}
break;
case "smallint":
{
sqlDbType = SqlDbType.SmallInt;
}
break;
case "int":
{
sqlDbType = SqlDbType.Int;
}
break;
case "smalldatetime":
{
sqlDbType = SqlDbType.SmallDateTime;
}
break;
case "real":
{
sqlDbType= SqlDbType.Real;
}
break;
case "money":
{
sqlDbType = SqlDbType.Money;
}
break;
case "datetime":
{
sqlDbType = SqlDbType.DateTime;
}
break;
case "float":
{
sqlDbType = SqlDbType.Float;
}
break;
case "ntext":
{
sqlDbType = SqlDbType.NText;
}
break;
case "bit":
{
sqlDbType = SqlDbType.Bit;
}
break;
case "decimal":
{
sqlDbType = SqlDbType.Decimal;
}
break;
case "smallmoney":
{
sqlDbType = SqlDbType.SmallMoney;
}
break;
case "BigInt":
{
sqlDbType = SqlDbType.BigInt;
}
break;
case "varbinary":
{
sqlDbType = SqlDbType.VarBinary;
}
break;
case "varchar":
{
sqlDbType = SqlDbType.VarChar;
}
break;
case "binary":
{
sqlDbType = SqlDbType.Binary;
}
break;
case "char":
{
sqlDbType = SqlDbType.Char;
}
break;
case "timestamp":
{
sqlDbType = SqlDbType.Timestamp;
}
break;
case "nvarchar":
{
sqlDbType = SqlDbType.NVarChar;
}
break;
case "nchar":
{
sqlDbType = SqlDbType.NChar;
}
break;
default:
break;
}
//当前参数名
string parameterName = Convert.ToString(this.dataTableRetunParameters.Rows[i]["Name"]);
SqlParameter addParameter = new SqlParameter(parameterName,sqlDbType);
//输入输出类型
if(Convert.ToString(this.dataTableRetunParameters.Rows[i]["Isoutparam"]) == "INOUT")
{
addParameter.Direction = ParameterDirection.Output;
//当前存储过程有返回值
this.haveOutValue = true;
}
else
{
if (this.useNoNmae == true)
{
addParameter.Value = sqlDbType == SqlDbType.Bit?Convert.ToByte(this.valueNoName[i]):this.valueNoName[i];
}
else
{
addParameter.Value = sqlDbType == SqlDbType.Bit?Convert.ToByte(this.valueHaveName[parameterName]):this.valueHaveName[parameterName];
}
}
this.mySqlCommand.Parameters.Add(addParameter);
}
}
return returnSqlCommand;
}
/// <summary>
/// 返回存储过程中的返回值的值
/// </summary>
private void BackOutValue()
{
if (this.haveOutValue == true)
{
for(int i=0;i<=this.dataTableRetunParameters.Rows.Count - 1;i++)
{
if (Convert.ToString(this.dataTableRetunParameters.Rows[i]["Isoutparam"])=="INOUT")
{
this.outValue.Add(Convert.ToString(this.dataTableRetunParameters.Rows[i]["Name"]),this.mySqlCommand.Parameters[Convert.ToString(this.dataTableRetunParameters.Rows[i]["Name"])].Value);
}
}
}
this.Conn.Close();
this.dataTableRetunParameters = null;
}
#endregion
#region 公开方法
/// <summary>
/// 通过存储过程的名称执行存储过程,返回 DataSet。
/// </summary>
/// <param name="procedureName">存储过程的名称</param>
/// <returns>返回类型 DataSet </returns>
public DataSet RunProcReturnDataSet(string procedureName)
{
DataSet returnDataSet= new DataSet();
SqlDataAdapter ap = new SqlDataAdapter(this.BeginAddParameters(procedureName));
ap.Fill(returnDataSet);
this.BackOutValue();
return returnDataSet;
}
/// <summary>
/// 通过存储过程的名称执行存储过程,返回 DataSet。此方法必须先设置好属性 ProcedureName的值。
/// </summary>
/// <returns>返回类型 DataSet </returns>
public DataSet RunProcReturnDataSet()
{
return RunProcReturnDataSet(this.myProName);
}
/// <summary>
/// 通过存储过程的名称执行存储过程,返回 DataTable。
/// </summary>
/// <param name="procedureName">存储过程的名称</param>
/// <param name="tableName">指定所返回的DataTable的TableName</param>
/// <returns>返回类型 DataTable </returns>
public DataTable RunProcReturnDataTable(string procedureName,string tableName)
{
DataTable returnDataTable = new DataTable();
if(tableName!="")
{
returnDataTable.TableName = tableName;
}
SqlDataAdapter ap = new SqlDataAdapter(this.BeginAddParameters(procedureName));
ap.Fill(returnDataTable);
this.BackOutValue();
return returnDataTable;
}
/// <summary>
/// 通过存储过程的名称执行存储过程,返回 DataTable。
/// </summary>
/// <param name="procedureName">存储过程的名称</param>
/// <returns>返回类型 DataTable </returns>
public DataTable RunProcReturnDataTable(string procedureName)
{
return RunProcReturnDataTable(procedureName,"");
}
/// <summary>
/// 执行指定的存储过程。无返回类型。
/// </summary>
/// <param name="procedureName">存储过程名称</param>
public void RunProcedure(string procedureName)
{
this.BeginAddParameters(procedureName).ExecuteNonQuery();
this.BackOutValue();
}
/// <summary>
/// 执行存储过程,此方法必须先设置好属性 ProcedureName的值。无返回类型。
/// </summary>
public void RunProcedure()
{
RunProcedure(this.myProName);
}
/// <summary>
/// 执行SQL语句。返回 DataSet
/// </summary>
/// <param name="sql">所要执行的SQL语句</param>
/// <param name="tableName">返回的表名</param>
/// <returns>DataSet</returns>
public DataSet RunSqlReturnDataSet(string sql,string tableName)
{
SqlCommand sqlCommand= new SqlCommand(sql,this.Conn);
DataSet retrunDataSet = new DataSet();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
sqlDataAdapter.Fill(retrunDataSet,tableName);
return retrunDataSet;
}
/// <summary>
/// 执行SQL语句。返回 DataTable
/// </summary>
/// <param name="sql">所要执行的SQL语句</param>
/// <param name="tableName">返回的表名</param>
/// <returns>DataTable</returns>
public DataTable RunSqlReturnDataTable(string sql,string tableName)
{
SqlCommand sqlCommand = new SqlCommand(sql,this.Conn);
DataTable retrunDataTable = new DataTable();
retrunDataTable.TableName = tableName;
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
sqlDataAdapter.Fill(retrunDataTable);
return retrunDataTable;
}
/// <summary>
/// 执行SQL语句。返回Boolean类型
/// </summary>
/// <param name="sql">所要执行的SQL语句</param>
/// <returns>Boolean</returns>
public Boolean RunSql(string sql)
{
try
{
SqlCommand sqlCommand= new SqlCommand(sql,this.Conn);
this.Conn.Open();
sqlCommand.ExecuteNonQuery();
this.Conn.Close();
return true;
}
catch
{
return false;
}
}
#endregion
}
}