SQLHelper使用说明

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

 

     }

}