A Class For Executing MSSql Store Procedure

1using System;
2using System.IO;
3using System.Data;
4using System.Data.SqlClient;
5using System.Configuration;
6using System.Collections;
7using System.Web;
8
9namespace Forum.Component
10{
11 /// 

12 /// 存储过程的返回值纪录类
13 /// DataSet : 表示返回的表
14 /// Output  : 存储过程的输出参数
15 /// Value   : 存储过程的返回值
16 /// 
17 public class SqlResult
18 {
19  public int Value;
20  public Hashtable Output;
21  public DataSet dataSet;
22
23  public SqlResult()
24  {
25   Value = 0;
26   Output = new Hashtable();
27   dataSet = new DataSet();
28  }
29 }
30
31 /// 

32 /// 用于调用数据库中的存储过程，返回一个DataSet、Output、Value的SqlResult类
33 /// 
34 public class SqlProcedure
35 {
36  private string sp_name;
37  private SqlConnection myConnection;
38  private SqlCommand myCommand;
39  private SqlParameter myParameter;
40
41  public string ProcedureName
42  {
43   get{ return this.sp_name; }
44   set{ this.sp_name = value; }
45  }
46
47  public SqlProcedure() : this("")
48  {
49  }
50
51  public SqlProcedure(string sp_name)
52  {
53   this.ProcedureName = sp_name;
54  }
55  
56  public SqlResult Call(params object[] parameters)
57  {
58   SqlResult result = new SqlResult();
59
60   myConnection  = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
61
62   myCommand = new SqlCommand(this.ProcedureName, myConnection);
63   myCommand.CommandType = CommandType.StoredProcedure;
64
65   SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
66
67   try
68   {
69    myConnection.Open();
70
71    GetProcedureParameter(parameters);
72
73    myAdapter.Fill(result.dataSet, "Table");
74
75    GetOutputValue(result);
76   }
77   catch(Exception e)
78   {
79    throw e;
80   }
81   finally
82   {
83    myAdapter.Dispose();
84    myCommand.Dispose();
85    myConnection.Close();
86    myConnection.Dispose();
87   }
88
89   return result;
90  }
91
92  private void GetProcedureParameter(params object[] parameters)
93  {
94   SqlCommand myCommand2 = new SqlCommand();
95
96   myCommand2.Connection = this.myConnection;
97   myCommand2.CommandText = "select * from INFORMATION_SCHEMA.PARAMETERS"
98        + " where SPECIFIC_NAME='" +this.ProcedureName+ "' order by ORDINAL_POSITION";
99
100   SqlDataReader reader = null;
101   try 
102   {
103    reader = myCommand2.ExecuteReader();
104     myParameter = new SqlParameter();
105     myParameter.ParameterName = "@Value";
106     myParameter.SqlDbType = SqlDbType.Int;
107     myParameter.Direction = ParameterDirection.ReturnValue;
108
109     myCommand.Parameters.Add(myParameter);
110    int i = 0;
111    while(reader.Read())
112    {
113     myParameter = new SqlParameter();
114
115     myParameter.ParameterName = reader["PARAMETER_NAME"].ToString();
116     myParameter.Direction = reader["PARAMETER_MODE"].ToString()=="IN"?ParameterDirection.Input:ParameterDirection.Output;
117
118     switch(reader["DATA_TYPE"].ToString())
119     {
120      case "bit" :
121       if(myParameter.Direction == ParameterDirection.Input)
122        myParameter.Value = (bool)parameters[i];
123       myParameter.SqlDbType = SqlDbType.Bit;
124       break;
125
126      case "bigint" :
127       if(myParameter.Direction == ParameterDirection.Input)
128        myParameter.Value = (int)parameters[i];
129       myParameter.SqlDbType = SqlDbType.BigInt;
130       break;
131
132      case "int" :
133       if(myParameter.Direction == ParameterDirection.Input)
134         myParameter.Value = (int)parameters[i];
135       myParameter.SqlDbType = SqlDbType.Int;
136       break;
137       
138      case "decimal" :
139       if(myParameter.Direction == ParameterDirection.Input)
140        myParameter.Value = (double)parameters[i];
141       myParameter.SqlDbType = SqlDbType.Decimal;
142       myParameter.Precision = (byte)reader["NUMERIC_PRECISION"];
143       myParameter.Scale = (byte)reader["NUMERIC_SCALE"];
144       break;
145
146      case "nvarchar" :
147       if(myParameter.Direction == ParameterDirection.Input)
148        myParameter.Value = (string)parameters[i];
149       myParameter.Size = (int)reader["CHARACTER_MAXIMUM_LENGTH"];
150       myParameter.SqlDbType = SqlDbType.NVarChar;
151       break;
152
153      case "varchar" :
154       if(myParameter.Direction == ParameterDirection.Input)
155        myParameter.Value = (string)parameters[i];
156       myParameter.Size = (int)reader["CHARACTER_MAXIMUM_LENGTH"];
157       myParameter.SqlDbType = SqlDbType.VarChar;
158       break;
159
160      case "nchar" :
161       if(myParameter.Direction == ParameterDirection.Input)
162        myParameter.Value = (string)parameters[i];
163       myParameter.Size = (int)reader["CHARACTER_MAXIMUM_LENGTH"];
164       myParameter.SqlDbType = SqlDbType.NChar;
165       break;
166
167      case "char" :
168       if(myParameter.Direction == ParameterDirection.Input)
169        myParameter.Value = (string)parameters[i];
170       myParameter.Size = (int)reader["CHARACTER_MAXIMUM_LENGTH"];
171       myParameter.SqlDbType = SqlDbType.Char;
172       break;
173
174      case "ntext" :
175       if(myParameter.Direction == ParameterDirection.Input)
176        myParameter.Value = (string)parameters[i];
177       myParameter.SqlDbType = SqlDbType.NText;
178       break;
179
180      case "text" :
181       if(myParameter.Direction == ParameterDirection.Input)
182        myParameter.Value = (string)parameters[i];
183       myParameter.SqlDbType = SqlDbType.Text;
184       break;
185
186      case "datetime" :
187       if(myParameter.Direction == ParameterDirection.Input)
188        myParameter.Value = (DateTime)parameters[i];
189       myParameter.SqlDbType = SqlDbType.DateTime;
190       break;
191      case "smalldatetime" :
192       if(myParameter.Direction == ParameterDirection.Input)
193        myParameter.Value = (DateTime)parameters[i];
194       myParameter.SqlDbType = SqlDbType.DateTime;
195       break;
196      case "image" :
197       if(myParameter.Direction == ParameterDirection.Input)
198       {
199        HttpPostedFile PostedFile = (HttpPostedFile)parameters[i];
200
201        Byte[] FileByteArray = new Byte[PostedFile.ContentLength];
202        Stream StreamObject = PostedFile.InputStream;
203        StreamObject.Read(FileByteArray,0,PostedFile.ContentLength);
204
205        myParameter.Value = FileByteArray;
206       }
207
208       myParameter.SqlDbType = SqlDbType.Image;
209       break;
210
211      case "uniqueidentifier" :
212       //myParameter.Value = (string)parameters[i];
213       myParameter.SqlDbType = SqlDbType.UniqueIdentifier;
214       break;
215
216      default : break;
217     }
218     i++;
219
220     myCommand.Parameters.Add(myParameter);
221    }
222   }
223   catch(Exception e)
224   {
225    throw e;
226   
227   }
228   finally
229   {
230    if(reader!=null) reader.Close();
231    myCommand2.Dispose();
232   }
233  }
234
235
236  private void GetOutputValue(SqlResult result)
237  {
238   result.Value = (int)myCommand.Parameters["@Value"].Value;
239
240   foreach(SqlParameter parameter in myCommand.Parameters)
241   {
242    if(parameter.Direction == ParameterDirection.Output)
243    {
244     result.Output.Add(parameter.ParameterName, parameter.Value);
245    }
246   }
247  }
248 }
249}

调用方法：

1using Forum.Component
2private void Submit1_ServerClick(object sender, System.EventArgs e)
3{
4     protected SqlProcedure procedure=new SqlProcedure();
5     public SqlResult result;
6     HttpPostedFile picture_PostedFile =picture.PostedFile;
7     HttpPostedFile taskpic1_PostedFile = taskpic1.PostedFile;
8     HttpPostedFile taskpic2_PostedFile = taskpic2.PostedFile;
9  
10     procedure.ProcedureName="Teacher_insert";
11     result=procedure.Call(name.Text,name.Text,gender.SelectedItem.Value,
12         Convert.ToDateTime(birthday.Value),headship.Text,departmentname.SelectedItem.Value.ToString(),
13         picture_PostedFile,instruction.Value,task.Value,workplace.Text,telephone.Text,email.Text,
14         taskpic1_PostedFile,taskpic2_PostedFile,degree.Text,job.Text,other.Value,false);
15     if (result.Value==1)
16     {
17         message.Text="添加成功！";
18     }
19     else
20     {
21         message.Text="添加失败！";
22     }
23 }