[.NET][C#][转载]调用存储过程的类

using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;

namespace Forum.Component
{
    
/// <summary>

    
///     存储过程的返回值纪录类
    
///     DataSet : 表示返回的表
    
///     Output  : 存储过程的输出参数
    
///     Value   : 存储过程的返回值
    
/// </summary>

    public class SqlResult
    
{
        
public int Value;
        
public Hashtable Output;
        
public DataSet dataSet;

        
public SqlResult()
        
{
            Value 
= 0;
            Output 
= new Hashtable();
            dataSet 
= new DataSet();
        }

    }


    
/// <summary>

    
///     用于调用数据库中的存储过程,返回一个DataSet、Output、Value的SqlResult类
    
/// </summary>

    public class SqlProcedure
    
{
        
private string sp_name;
        
private SqlConnection myConnection;
        
private SqlCommand myCommand;
        
private SqlParameter myParameter;

        
public string ProcedureName
        
{
            
getreturn this.sp_name; }
            
setthis.sp_name = value; }
        }


        
public SqlProcedure() : this("")
        
{
        }


        
public SqlProcedure(string sp_name)
        
{
            
this.ProcedureName = sp_name;
        }

        
        
public SqlResult Call(params object[] parameters)
        
{
            SqlResult result 
= new SqlResult();

            myConnection  
= new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);

            myCommand 
= new SqlCommand(this.ProcedureName, myConnection);
            myCommand.CommandType 
= CommandType.StoredProcedure;

            SqlDataAdapter myAdapter 
= new SqlDataAdapter(myCommand);

            
try
            
{
                myConnection.Open();

                GetProcedureParameter(parameters);

                myAdapter.Fill(result.dataSet, 
"Table");

                GetOutputValue(result);
            }

            
catch(Exception e)
            
{
                
throw e;
            }

            
finally
            
{
                myAdapter.Dispose();
                myCommand.Dispose();
                myConnection.Close();
                myConnection.Dispose();
            }


            
return result;
        }


        
private void GetProcedureParameter(params object[] parameters)
        
{
            SqlCommand myCommand2 
= new SqlCommand();

            myCommand2.Connection 
= this.myConnection;
            myCommand2.CommandText 
= "select * from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME='" +this.ProcedureName+ "' order by ORDINAL_POSITION";

            SqlDataReader reader 
= null;
            
try 
            
{
                reader 
= myCommand2.ExecuteReader();
                    myParameter 
= new SqlParameter();
                    myParameter.ParameterName 
= "@Value";
                    myParameter.SqlDbType 
= SqlDbType.Int;
                    myParameter.Direction 
= ParameterDirection.ReturnValue;

                    myCommand.Parameters.Add(myParameter);
                
int i = 0;
                
while(reader.Read())
                
{
                    myParameter 
= new SqlParameter();

                    myParameter.ParameterName 
= reader["PARAMETER_NAME"].ToString();
                    myParameter.Direction 
= reader["PARAMETER_MODE"].ToString()=="IN"?ParameterDirection.Input:ParameterDirection.Output;

                    
switch(reader["DATA_TYPE"].ToString())
                    
{
                        
case "bit" :
                            
if(myParameter.Direction == ParameterDirection.Input)
                                myParameter.Value 
= (bool)parameters[i];
                            myParameter.SqlDbType 
= SqlDbType.Bit;
                            
break;

                        
case "bigint" :
                            
if(myParameter.Direction == ParameterDirection.Input)
                                myParameter.Value 
= (int)parameters[i];
                            myParameter.SqlDbType 
= SqlDbType.BigInt;
                            
break;

                        
case "int" :
                            
if(myParameter.Direction == ParameterDirection.Input)
                                    myParameter.Value 
= (int)parameters[i];
                            myParameter.SqlDbType 
= SqlDbType.Int;
                            
break;
                            
                        
case "decimal" :
                            
if(myParameter.Direction == ParameterDirection.Input)
                                myParameter.Value 
= (double)parameters[i];
                            myParameter.SqlDbType 
= SqlDbType.Decimal;
                            myParameter.Precision 
= (byte)reader["NUMERIC_PRECISION"];
                            myParameter.Scale 
= (byte)reader["NUMERIC_SCALE"];
                            
break;

                        
case "nvarchar" :
                            
if(myParameter.Direction == ParameterDirection.Input)
                                myParameter.Value 
= (string)parameters[i];
                            myParameter.Size 
= (int)reader["CHARACTER_MAXIMUM_LENGTH"];
                            myParameter.SqlDbType 
= SqlDbType.NVarChar;
                            
break;

                        
case "varchar" :
                            
if(myParameter.Direction == ParameterDirection.Input)
                                myParameter.Value 
= (string)parameters[i];
                            myParameter.Size 
= (int)reader["CHARACTER_MAXIMUM_LENGTH"];
                            myParameter.SqlDbType 
= SqlDbType.VarChar;
                            
break;

                        
case "nchar" :
                            
if(myParameter.Direction == ParameterDirection.Input)
                                myParameter.Value 
= (string)parameters[i];
                            myParameter.Size 
= (int)reader["CHARACTER_MAXIMUM_LENGTH"];
                            myParameter.SqlDbType 
= SqlDbType.NChar;
                            
break;

                        
case "char" :
                            
if(myParameter.Direction == ParameterDirection.Input)
                                myParameter.Value 
= (string)parameters[i];
                            myParameter.Size 
= (int)reader["CHARACTER_MAXIMUM_LENGTH"];
                            myParameter.SqlDbType 
= SqlDbType.Char;
                            
break;

                        
case "ntext" :
                            
if(myParameter.Direction == ParameterDirection.Input)
                                myParameter.Value 
= (string)parameters[i];
                            myParameter.SqlDbType 
= SqlDbType.NText;
                            
break;

                        
case "text" :
                            
if(myParameter.Direction == ParameterDirection.Input)
                                myParameter.Value 
= (string)parameters[i];
                            myParameter.SqlDbType 
= SqlDbType.Text;
                            
break;

                        
case "datetime" :
                            
if(myParameter.Direction == ParameterDirection.Input)
                                myParameter.Value 
= (DateTime)parameters[i];
                            myParameter.SqlDbType 
= SqlDbType.DateTime;
                            
break;
                        
case "smalldatetime" :
                            
if(myParameter.Direction == ParameterDirection.Input)
                                myParameter.Value 
= (DateTime)parameters[i];
                            myParameter.SqlDbType 
= SqlDbType.DateTime;
                            
break;
                        
case "image" :
                            
if(myParameter.Direction == ParameterDirection.Input)
                            
{
                                HttpPostedFile PostedFile 
= (HttpPostedFile)parameters[i];

                                Byte[] FileByteArray 
= new Byte[PostedFile.ContentLength];
                                Stream StreamObject 
= PostedFile.InputStream;
                                StreamObject.Read(FileByteArray,
0,PostedFile.ContentLength);

                                myParameter.Value 
= FileByteArray;
                            }


                            myParameter.SqlDbType 
= SqlDbType.Image;
                            
break;

                        
case "uniqueidentifier" :
                            
//myParameter.Value = (string)parameters[i];
                            myParameter.SqlDbType = SqlDbType.UniqueIdentifier;
                            
break;

                        
default : break;
                    }

                    i
++;

                    myCommand.Parameters.Add(myParameter);
                }

            }

            
catch(Exception e)
            
{
                
throw e;
            
            }

            
finally
            
{
                
if(reader!=null) reader.Close();
                myCommand2.Dispose();
            }

        }



        
private void GetOutputValue(SqlResult result)
        
{
            result.Value 
= (int)myCommand.Parameters["@Value"].Value;

            
foreach(SqlParameter parameter in myCommand.Parameters)
            
{
                
if(parameter.Direction == ParameterDirection.Output)
                
{
                    result.Output.Add(parameter.ParameterName, parameter.Value);
                }

            }

        }

    }

}

 

调用方法

using Forum.Component
protected SqlProcedure procedure=new SqlProcedure();
public SqlResult result;

procedure.ProcedureName
="sp_name";
result
=procedure.Call(1,"1","2");

posted on 2007-07-04 23:22 ffan 阅读(1987) 评论(3)  编辑 收藏 引用 所属分类: .NET

评论

# re: [.NET][C#][转载]调用存储过程的类 2007-10-25 22:46 tset

有个问题
如果存储过程有返回值 如何取到?
yuwy.sh@163.com给个消息.
  回复  更多评论   

# re: [.NET][C#][转载]调用存储过程的类 2007-11-28 17:25 ffan

@tset

返回值在result 里

比如
public SqlResult result;

procedure.ProcedureName="sp_name";
result=procedure.Call(1,"1","2");
int abc = (int)result.Output["aaa"];  回复  更多评论   

# re: [.NET][C#][转载]调用存储过程的类 2008-10-08 02:01

貌似不行的  回复  更多评论   

只有注册用户登录后才能发表评论。
<2007年7月>
24252627282930
1234567
891011121314
15161718192021
22232425262728
2930311234

导航

统计

常用链接

留言簿(12)

随笔分类

随笔档案

相册

搜索

最新评论

阅读排行榜

评论排行榜