Using system;
Using system.io;
Using system.data;
Using system.data.sqlclient;
Using system.configuration;
Using system.collections;
Using system.Web;
Namespace forum.component
{
///
/// Storage process return value record class
/// DataSet: Represents the returned table
/// Output: Output parameters of stored procedures
/// value: Return value for stored procedures
///
Public Class SqlResult
{
Public int value;
Public HashTable Output;
Public DataSet DataSet;
Public SqlResult ()
{
Value = 0;
Output = new hashtable ();
DataSet = new dataset ();
}
}
///
// Used to call the stored procedure in the database, return a SQLRESULT class for Dataset, Output, Value
///
Public Class Sqlprocedure
{
PRIVATE STRING SP_NAME;
Private sqlconnection myconnection;
Private sqlcommand mycommand;
Private sqlparameter myparameter;
Public String ProcedureName, PUBLIC STRING
{
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 (ConfigurationSttings.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 (parameters) {parameters) {PARAMES 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.executeRead ();
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);
}
}
}
}
}
