Getting a column or parameter's data type from the database [modified]

[JohnLBevan]

Hi Folks, I'm hoping someone can assist me with a (hopefully) simple question. Is there a way to query a database to get the type of the parameters expected by a stored proc, or the column types of a table. I know this can be done through SQL for most databases (e.g. see below, for SQL Server Column Types), but want a solution which works with any database.

SELECT name
from sys.types
where user_type_id in
(
select user_type_id
from sys.columns
WHERE object_id = OBJECT_ID('@schemaName.@tableName')
and name='@columnName'
)

Background For anyone wanting to know more about why I'd need this, here's a little more info on what I'm attempting. I'm writing a web service, which can act as a wrapper to any databases. The databases' connection strings will be included in the config file, using the MS Enterprise Library, so that when a call is made, the name of the DB can be included, which can then be used to get the appropriate connection. Reading information is pretty simple; given the database name and the table / (parameterless) stored proc name the code reads from that object, and outputs data in the format <tableOrProcName><row><fieldName>...</fieldName>...</row>...</tableOrProcName>. However, when performing inserts (and other operations where I'd need to be cognizant of data types), I'd like to query the database to get a list of expected parameter/column names and types, which I can then cross reference with the names of the XML 'Field' elements, to convert the values from strings to the correct types before populating the commandText / parameter. Once coded, I'll make the solution available on here for anyone looking to do the same. (For anyone wondering why we'd need a wrapper for databases, the answer is many programs these days come with simple methods for attaching to web service calls, but don't provide similar features for databases, so this is a simple & flexible workaround. Security is not an issue, since both the webservice and database are configured to only allow the service account access, so users can't use the service as a backdoor). Thanks in advance, JB

modified on Monday, June 14, 2010 7:50 AM

[PIEBALDconsult]

I get a DataReader* and use its GetSchemaTable method -- it works on all the databases I've used so far. * I use a query like "SELECT ... FROM ... WHERE 0=1" so the DataReader doesn't unnecessarily retrieve any data.

[JohnLBevan]

Ahh that's great - thanks for the info. It took me a bit of time to realise that columns were rows in the schema, so I've chucked some sample code below for anyone looking to do something similar:

        DbConnection con = new SqlConnection(connectionstring);
        DbCommand command = con.CreateCommand();
        command.CommandType = CommandType.Text;
        command.CommandText = "select \* from dbo.DemoTable where 0=1";
        command.Connection.Open();
        using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
        {
            DataTable table = reader.GetSchemaTable();
            //foreach (DataColumn column in table.Columns)
            foreach(DataRow row in table.Rows)
            {
                //Console.WriteLine(string.Format("{0}: {1}",column.ColumnName,column.DataType.ToString()));
                Console.WriteLine(string.Format("{0}: {1}", row.Field<string>("ColumnName"), row.Field<Type>("DataType").ToString()));
            }
        }
        command.Connection.Close();
        Console.WriteLine("Done");
        Console.ReadKey();

However, this won't work for getting the input parameters for a stored proc. Do you know of an equivelant to the following?

SELECT name
from sys.types
where user_type_id in
(
select user_type_id
from sys.parameters
WHERE object_id = OBJECT_ID('@schema.@storedProc')
and name='@paramName'
)

Thanks again for your help so far; it's very much appreciated. Kind regards, JB

[PIEBALDconsult]

Inverso1 wrote:

for getting the input parameters for a stored proc

That would depend on the database; not all of them support them.