A Class For Executing MSSql Store Procedure
生活随笔
收集整理的這篇文章主要介紹了
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 }
總結
以上是生活随笔為你收集整理的A Class For Executing MSSql Store Procedure的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 用Visual Studio .Net
- 下一篇： 忙~~~~~~~~~~~~~~~~~