ADO.NET Provider for Microsoft Excel

Build 23.0.8839

Procedure Parameters

The ProcedureParameters schema collection describes the stored procedure parameters.

Retrieving Stored Procedure Parameter Metadata

The ProcedureParameters schema collection contains information about the parameters of stored procedures.

To retrieve the ProcedureParameters schema collection, call the GetSchema method of the ExcelConnection class. Access the metadata in the DataTable object returned. The following example retrieves parameter information for all stored procedures:

C#

String connectionString = "URI=C:\MyExcelWorkbooks\SampleWorkbook.xlsx;";

using (ExcelConnection conn = new ExcelConnection(connectionString)) {
  conn.Open();
  DataTable table = conn.GetSchema("ProcedureParameters");
  foreach (DataRow row in table.Rows) {
    foreach (DataColumn col in table.Columns) {
      Console.WriteLine(col.ColumnName + "=" + row[col]);
    }
  }
}

VB.NET

Dim connectionString As [String] = "User=URI=C:\MyExcelWorkbooks\SampleWorkbook.xlsx;"

Using conn As New ExcelConnection(connectionString)
  conn.Open()
  Dim table As DataTable = conn.GetSchema("ProcedureParameters")
  For Each row As DataRow In table.Rows
    For Each col As DataColumn In table.Columns
      Console.WriteLine(col.ColumnName + "=" + row(col))
    Next
  Next
End Using

Columns Returned

The columns of the schema collection are the following:

Column NameData TypeDescription
SPECIFIC_CATALOGSystem.StringThe name of the database containing the stored procedure.
SPECIFIC_SCHEMASystem.StringThe schema that contains the stored procedure.
SPECIFIC_NAMESystem.StringThe name of the stored procedure containing the parameter.
PARAMETER_NAMESystem.StringThe name of the parameter.
PARAMETER_MODESystem.StringReturns IN for an input parameter, OUT for an output parameter, or INOUT for parameters that can be both input and output parameters.
ORDINAL_POSITIONSystem.Int32The sequence number of the parameter.
DATA_TYPESystem.StringThe data type name.
CHARACTER_MAXIMUM_LENGTHSystem.Int32The maximum length in characters.
CHARACTER_SET_NAMESystem.StringThe name of the character set for a column with character data.
NUMERIC_PRECISIONSystem.Int32The maximum number of digits in numeric data.
NUMERIC_SCALESystem.Int32The column scale or number of digits to the right of the decimal point.
DATETIME_PRECISIONSystem.Int32The precision in fractional seconds if the parameter type is datetime or smalldatetime. Otherwise, returns NULL.
PROCEDURE_DESCRIPTIONSystem.StringA brief description of the procedure.
PROVIDER_TYPESystem.TypeIndicates the appropriate data type dependent on the language you are executing in.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 23.0.8839