VB.net 调用带参数存储过程(转载)

 

--1:存储过程返回DataSet 的例子:

C# 代码 :通用类

  public static int SingleSTCD(DateTime StartTime,DateTime EndTime,int SMTP,string JL,string STDCD,string STCDSTR, out DataSet 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 ;  //评价标准
     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; //无数据
     }
     else
     {
      return ret;
     }

    }
    catch(Exception e)
    {
     //数据库操作发生错误,返回错误代码
     System.Diagnostics.Debug.WriteLine(e.Message);
     return -1001;
    }
    finally
    {
     Constring.Close();
     cmd.Parameters.Clear();
    }

VB.net 调用:

  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:存储过程返回数组字符串

  REM 执行存储过程,返回一数组
    Public Shared Function ExecuteSP_ToArrary(ByVal sConnString As String, ByVal Text_STCD As String, ByVal Text_TIME As String, 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 OleDbParameter("@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