Stored Procedures
--1: Example of a stored procedure returning a dataset:
C # code: generic class
public static int Singlestcd (DateTime starttime,datetime endtime,int smtp,string jl,string stdcd,string stcdstr. Out Data Set DS)
{
int ret=0;
Ds=null;
OleDbConnection constring=new OleDbConnection (APPRAISE.CONSTR);
OleDbCommand cmd=new OleDbCommand ("P_get_stcd_singleappraise_xunqi", constring);
Try
{
OleDbDataAdapter ada=new OleDbDataAdapter ();
Cmd.commandtype=commandtype.storedprocedure;
Cmd. Parameters.Add ("@StartTime", Oledbtype.dbtimestamp). Value=starttime;
Cmd. Parameters.Add ("@EndTime", Oledbtype.dbtimestamp). Value=endtime;
Cmd. Parameters.Add ("@SMTP", OleDbType.Integer). VALUE=SMTP;
Cmd. Parameters.Add ("@JL", OleDbType.VarChar, 50). VALUE=JL;
Cmd. Parameters.Add ("@STDCD", OleDbType.VarChar, 50). VALUE=STDCD; Evaluation criteria
Cmd. Parameters.Add ("@STCDSTR", OleDbType.VarChar, 8000). VALUE=STCDSTR;
System.Data.DataSet Data = new DataSet ();
Ada. SelectCommand = cmd;
Ada. Fill (Data, "table");
ds = data;
Ret=ds. Tables[0]. Rows.Count;
if (ret ==-1)
{
return- 1000; Countless
}
Else
{
return ret;
}
}
catch (Exception e)
{
Error occurred in database operation, error code returned
System.Diagnostics.Debug.WriteLine (E.message);
return-1001;
}
Finally
{
Constring.close ();
Cmd. Parameters.clear ();
}
VB.net call:
Private Sub Bindingdatagrid_query ()
Dim DS as System.Data.DataSet
Zehua.Water.Appraise.SingleSTCD ("1999-5-1", "1999-9-1", 1, "Ⅲ", "", " 50181300,50185050,50181350,50185100,50185150,50185200,50185250,50181200,50185150,51282300 ", DS)
Datagrid_query.datasource = ds
Datagrid_query.databind ()
End Sub
--1: Stored procedure returns an array string
REM executes the stored procedure, returning an array
Public Shared Function executesp_toarrary (ByVal sconnstring As String, ByVal Text_stcd as String, ByVal Text_time as Strin G, ByRef Tableinfo () as String)
Dim conn as OleDbConnection = New OleDbConnection (sconnstring)
ReDim Tableinfo (2)
Try
Conn. Open ()
Dim command as OleDbCommand = New OleDbCommand ("P_get_queryvalue", conn)
Command.commandtype = CommandType.StoredProcedure
Dim Sqlparams () as OleDbParameter = {new OleDbParameter ("@STCD", Text_stcd), New OleDbParameter ("@GETM", Text_time), new O Ledbparameter ("@ReturnValue", OleDbType.VarChar, 8000), New oledbparameter ("@ReturnColumnCode", OleDbType.VarChar, 8000), New oledbparameter ("@ReturnColumnName", OleDbType.VarChar, 8000)}
Sqlparams (2). Direction = ParameterDirection.Output
Sqlparams (3). Direction = ParameterDirection.Output
Sqlparams (4). Direction = ParameterDirection.Output
Dim i as Integer = 0
While I < Sqlparams.length
Command. Parameters.Add (Sqlparams (i))
i + 1
End While
Command. ExecuteNonQuery ()
If Sqlparams (2). Value.tostring (). Length > 0 Then
Tableinfo (0) = Sqlparams (2). Value.tostring ()
Else
End If
If Sqlparams (3). Value.tostring (). Length > 0 Then
Tableinfo (1) = Sqlparams (3). Value.tostring ()
Else
End If
If Sqlparams (4). Value.tostring (). Length > 0 Then
Tableinfo (2) = Sqlparams (4). Value.tostring ()
Else
End If
Catch ex as Exception
ZeHua.Log.exNoteBugs (ex, "siglestcdedit.aspx") ' SB. Append (ex. Message)
Finally
Conn. Close ()
End Try
End Function