△usingSystem; usingSy...... using System; using System.IO; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Collections; using System.Web; namespace Forum.Component { /// /// 存储过程的返回值纪录类 /// DataSet : 表示返回的表 /// Output : 存储过程的输出参数 /// Value : 存储过程的返回值 /// public class SqlResult { public int Value; public Hashtable Output; public DataSet dataSet; public SqlResult() { Value = 0; Output = new Hashtable(); dataSet = new DataSet(); } } /// /// 用于调用数据库中的存储过程,返回一个DataSet、Output、Value的SqlResult类 /// public class SqlProcedure { private string sp_name; private SqlConnection myConnection; private SqlCommand myCommand; private SqlParameter myParameter; public string ProcedureName { get{ return this.sp_name; } set{ this.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); } } } } } |
|小黑屋|最新主题|手机版|微赢网络技术论坛 ( 苏ICP备08020429号 )
GMT+8, 2024-9-30 01:41 , Processed in 0.270151 second(s), 12 queries , Gzip On, MemCache On.
Powered by Discuz! X3.5
© 2001-2023 Discuz! Team.