存储过程操作类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Collections;

namespace StoredProcedure
{
    
public class StoredProcedure
    {
        
#region 数据成员
        
private SqlConnection _SqlConnection = null;
        
private String _Procedure = String.Empty;
        
private SqlCommand _SqlCmd = new SqlCommand();
        
private Hashtable _InputTable = null// 保存input参数和值
        private String _LastError = String.Empty;
        
#endregion

        
#region 构造函数
        
public StoredProcedure()
        {
            _InputTable 
= new Hashtable();
            _SqlCmd.CommandType 
= CommandType.StoredProcedure;
        }

        
public StoredProcedure( String DBConnStr): this()
        {
            SqlConnection SqlConnection 
= new SqlConnection(DBConnStr);
            
this.SqlConnection = SqlConnection;
        }

        
public StoredProcedure(String Procedure, String DBConnStr): this()
        {
            SqlConnection SqlConnection 
= new SqlConnection(DBConnStr);
            
this.SqlConnection = SqlConnection;
            
this.Procedure = Procedure;
        }
        
#endregion

        
#region 属性
        
public String LastError
        {
            
get
            {
                
return this._LastError;
            }
        }

        
public Object ReturnValue
        {
            
get
            {
                
return _SqlCmd.Parameters["RetVal"].Value;
            }
        }

        
public SqlConnection SqlConnection
        {
            
set
            {
                
this._SqlConnection = value;
                _SqlCmd.Connection 
= this._SqlConnection;
            }   
        }

        
public String Procedure
        {
            
set
            {
                
this._Procedure = value;
                _SqlCmd.CommandText 
= this._Procedure;
            }

            
get
            {
                
return this._Procedure;
            }
        }
        
#endregion

        
#region 公共方法
        
/// <summary>
        
/// 执行存储过程,仅返回是否成功标志
        
/// </summary>
        
/// <param name="Procedure">存储过程名</param>
        
/// <returns>是否成功标志</returns>
        public Boolean ExecuteNonQuery(String Procedure)
        {
            
this.Procedure = Procedure;
            
return ExecuteNonQuery();
        }

        
/// <summary>
        
/// 执行存储过程,仅返回是否成功标志
        
/// </summary>
        
/// <returns>是否成功标志</returns>
        public Boolean ExecuteNonQuery()
        {
            Boolean RetValue 
= true;
            
// 绑定参数
            if (Bindings() == true)
            {
                
try
                {
                    
// 执行
                    _SqlCmd.ExecuteNonQuery();
                }
                
catch (Exception ex)
                {
                    _LastError 
= "execute command error: " + ex.Message;
                    RetValue 
= false;
                }
            }
            
else
            {
                RetValue 
= false;
            }

            _InputTable.Clear();

            
return RetValue;
        }

        
/// <summary>
        
/// 执行存储过程,返回SqlDataReader
        
/// </summary>
        
/// <param name="Procedure">存储过程名</param>
        
/// <returns>数据库读取行的只进流SqlDataReader</returns>
        public SqlDataReader ExecuteReader(String Procedure)
        {
            
this.Procedure = Procedure;
            
return ExecuteReader();
        }

        
/// <summary>
        
/// 执行存储过程,返回SqlDataReader
        
/// </summary>
        
/// <returns>数据库读取行的只进流SqlDataReader</returns>
        public SqlDataReader ExecuteReader()
        {
            SqlDataReader sqlReader 
= null;
            
// 绑定参数
            if (Bindings() == true)
            {
                
try
                {
                    
// 执行
                    sqlReader = _SqlCmd.ExecuteReader();
                }
                
catch (Exception ex)
                {
                    _LastError 
= "execute command error: " + ex.Message;
                }
            }

            _InputTable.Clear();
   
            
return sqlReader;
        }

        
/// <summary>
        
/// 执行存储过程,返回DataSet
        
/// </summary>
        
/// <param name="Procedure">存储过程名</param>
        
/// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(String Procedure)
        {
            
this.Procedure = Procedure;
            
return ExecuteDataSet();
        }

        
/// <summary>
        
/// 执行存储过程,返回DataSet
        
/// </summary>
        
/// <returns>DataSet</returns>
        public DataSet ExecuteDataSet()
        {
            DataSet ds 
= new DataSet();
            SqlDataAdapter sqlAdapter 
= null;

            
// 绑定参数
            if (Bindings() == true)
            {
                
try
                {
                    
// 执行
                    sqlAdapter = new SqlDataAdapter(_SqlCmd);
                }
                
catch (Exception ex)
                {
                    _LastError 
= "execute command error: " + ex.Message;
                }
            }

            _InputTable.Clear();
            sqlAdapter.Fill(ds, 
"table");
            
return ds;
        }

        
/// <summary>
        
/// 执行存储过程,返回SqlDataAdapter
        
/// </summary>
        
/// <param name="Procedure">存储过程名</param>
        
/// <returns>SqlDataAdapter</returns>
        public SqlDataAdapter ExecuteAdapter(String Procedure)
        {
            
this.Procedure = Procedure;
            
return ExecuteAdapter();
        }

        
/// <summary>
        
/// 执行存储过程,返回SqlDataAdapter
        
/// </summary>
        
/// <returns>SqlDataAdapter</returns>
        public SqlDataAdapter ExecuteAdapter()
        {
            SqlDataAdapter sqlAdapter 
= null;

            
// 绑定参数
            if (Bindings() == true)
            {
                
try
                {
                    
// 执行
                    sqlAdapter = new SqlDataAdapter(_SqlCmd);
                }
                
catch (Exception ex)
                {
                    _LastError 
= "execute command error: " + ex.Message;
                }
            }

            _InputTable.Clear();

            
return sqlAdapter;
        }

        
/// <summary>
        
/// 获取output的键值
        
/// </summary>
        
/// <param name="Output">output键名称</param>
        
/// <returns>output键值</returns>
        public Object GetOutputValue(String Output)
        {
            
//_SqlCmd.Parameters[Output].Direction = ParameterDirection.Output; 
            return _SqlCmd.Parameters[Output].Value;
        }

        
/// <summary>
        
/// 设置Input参数值
        
/// </summary>
        
/// <param name="Key">参数名</param>
        
/// <param name="Value">参数值</param>
        public void SetInputValue(String Key, Object Value)
        {
            
if (Key == null)
            {
                
return;
            }
            
if (!Key.StartsWith("@"))
            {
                Key 
= "@" + Key;
            }

            
if (_InputTable.ContainsKey(Key))
            {
                _InputTable[Key] 
= Value;
            }
            
else
            {
                _InputTable.Add(Key, Value);
            }
        }

        
/// <summary>
        
/// 获取已设置的Input参数值
        
/// 注:存储过程被成功执行后, Input参数被清空
        
/// </summary>
        
/// <param name="Key">参数名</param>
        
/// <returns>参数值</returns>
        public Object GetInputValue(String Key)
        {
            
if (Key == null)
            {
                
return null;
            }
            
if (!Key.StartsWith("@"))
            {
                Key 
= "@" + Key;
            }

            
if (_InputTable.ContainsKey(Key))
            {
                
return _InputTable[Key];
            }
            
else
            {
                
return null;
            }
        }
        
#endregion

        
#region 私有方法
        
/// <summary>
        
/// 给SqlCommand对象绑定参数
        
/// </summary>
        
/// <returns>是否成功标志</returns>
        private Boolean Bindings()
        {
            _SqlCmd.Parameters.Clear();
            XmlReader sqlXmlReader 
= GetParameters();
            
try
            {
                
while (sqlXmlReader.Read())
                {
                    
try
                    {
                        
if (Byte.Parse(sqlXmlReader["isoutparam"]) == 1)
                        {
                            
// 绑定output参数
                            _SqlCmd.Parameters.Add(sqlXmlReader["name"],
                            GetSqlDbType(sqlXmlReader[
"type"]),
                            Int32.Parse(sqlXmlReader[
"length"])).Direction = ParameterDirection.Output;
                        }
                        
else
                        {
                            
// 绑定input参数,并赋值
                            _SqlCmd.Parameters.Add(sqlXmlReader["name"],
                            GetSqlDbType(sqlXmlReader[
"type"]),
                            Int32.Parse(sqlXmlReader[
"length"])).Value = this.GetInputValue(sqlXmlReader["name"]);
                           
/*
                            * 不必担心赋值的ParametersValue类型问题,SqlParameter.Value是object类型,自动转换
                            
*/
                        }
                    }
                    
catch (Exception ex)
                    {
                        _LastError 
= sqlXmlReader["name"+ " parameter error: " + ex.Message;
                        
return false;
                    }
                }

                
// 绑定返回值
                _SqlCmd.Parameters.Add("RetVal", SqlDbType.Variant).Direction = ParameterDirection.ReturnValue;
            }
            
catch (Exception ex)
            {
                _LastError 
= "binding parameter error: " + ex.Message;
                
return false;
            }

            
return true;
        }

        
/// <summary>
        
/// 由存储过程名, 取包含参数的XmlReader
        
/// </summary>
        
/// <param name="Procedure">存储过程名</param>
        
/// <returns>包含参数的XmlReader</returns>
        private XmlReader GetParameters()
        {
            String sqlStr 
= "SELECT B.[name], C.[name] AS [type], B.length, B.isoutparam, B.isnullable";
            sqlStr 
+= " FROM sysobjects AS A INNER JOIN";
            sqlStr 
+= " syscolumns AS B ON A.id = B.id AND A.xtype = 'P' AND A.name = '" + _Procedure + "' INNER JOIN";
            sqlStr 
+= " systypes C ON B.xtype = C.xtype AND C.[name] <> 'sysname'";
            sqlStr 
+= " ORDER BY ROW_NUMBER() OVER (ORDER BY B.id), B.isoutparam";
            sqlStr 
+= " FOR XML RAW";
            SqlCommand sqlCmd 
= new SqlCommand(sqlStr, _SqlConnection);
            
// <row name="Action" type="varchar" length="50" isoutparam="0" isnullable="1" />
            XmlReader sqlXmlReader = null;
            
try
            {
                sqlXmlReader 
= sqlCmd.ExecuteXmlReader();
            }
            
catch (Exception ex)
            {
                
if (sqlXmlReader != null) sqlXmlReader.Close();
                sqlXmlReader 
= null;
                _LastError 
= "get parameters error: " + ex.Message;
            }
            
finally
            {
                sqlCmd.Dispose();
                sqlCmd 
= null;
            }
            
return sqlXmlReader;
        }

        
protected internal static SqlDbType GetSqlDbType(String TypeName)
        {
            
switch (TypeName)
            {
                
case "image":
                 
return SqlDbType.Image;
                
case "text":
                 
return SqlDbType.Text;
                
case "uniqueidentifier":
                 
return SqlDbType.UniqueIdentifier;
                
case "tinyint":
                 
return SqlDbType.TinyInt;
                
case "smallint":
                 
return SqlDbType.SmallInt;
                
case "int":
                 
return SqlDbType.Int;
                
case "smalldatetime":
                 
return SqlDbType.SmallDateTime;
                
case "real":
                 
return SqlDbType.Real;
                
case "money":
                 
return SqlDbType.Money;
                
case "datetime":
                 
return SqlDbType.DateTime;
                
case "float":
                 
return SqlDbType.Float;
                
case "sql_variant":
                 
return SqlDbType.Variant;
                
case "ntext":
                 
return SqlDbType.NText;
                
case "bit":
                 
return SqlDbType.Bit;
                
case "decimal":
                 
return SqlDbType.Decimal;
                
case "numeric":
                 
return SqlDbType.Decimal;
                
case "smallmoney":
                 
return SqlDbType.SmallMoney;
                
case "bigint":
                 
return SqlDbType.BigInt;
                
case "varbinary":
                 
return SqlDbType.VarBinary;
                
case "varchar":
                 
return SqlDbType.VarChar;
                
case "binary":
                 
return SqlDbType.Binary;
                
case "char":
                 
return SqlDbType.Char;
                
case "timestamp":
                 
return SqlDbType.Timestamp;
                
case "nvarchar":
                 
return SqlDbType.NVarChar;
                
case "nchar":
                 
return SqlDbType.NChar;
                
case "xml":
                 
return SqlDbType.Xml;
                
default:
                 
return SqlDbType.Variant;
            }
        }
        
#endregion  
 
    }
}
posted @ 2009-07-22 09:31 TONYBINLJ 阅读(...) 评论(...) 编辑 收藏