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 }