How To Retrieve Database Schema?

[Axel Dahmen]

Hi,

I'm trying to retrieve the schema of some database tables from my database. In MSDN it says I need to call SqlDataAdapter.Fill() to retrieve the schema of a couple of tables at once. However, even though I've set the SqlDataAdapter.MissingSchemaAction to AddWithKey, the Fill() function retrieves not only the Schema but all the data as well.

How can I just retrieve the schema of some tables at once?

Any help is appreciated.
www.axeldahmen.de
Axel Dahmen


----
Here's the code I'm currently using:

{
SqlDataAdapter adp = new SqlDataAdapter("SELECT * FROM dbo.orgunit "
+ "SELECT * FROM dbo.employee "
+ "SELECT * FROM dbo.customer "
, ConnectionString);

DataSet schemaInfo = new DataSet();
adp.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adp.Fill(schemaInfo);
}

 

[Gregory A. Beamer]

If you have a strongly typed dataset, you already have schema for any tables
on the surface. You can then create rows in tables, etc.

I am probably missing something here, can you post back exactly what you are
trying to do (meaning the bigger picture).

--
Gregory A. Beamer
MVP: MCP: +I, SE, SD, DBA

Blog:
http://feeds.feedburner.com/GregoryBeamer

********************************************
| Think Outside the Box! |
********************************************
Hi,

I'm trying to retrieve the schema of some database tables from my database.
In MSDN it says I need to call SqlDataAdapter.Fill() to retrieve the schema
of a couple of tables at once. However, even though I've set the
SqlDataAdapter.MissingSchemaAction to AddWithKey, the Fill() function
retrieves not only the Schema but all the data as well.

How can I just retrieve the schema of some tables at once?

Any help is appreciated.
www.axeldahmen.de
Axel Dahmen


----
Here's the code I'm currently using:

{
SqlDataAdapter adp = new SqlDataAdapter("SELECT * FROM dbo.orgunit "
+ "SELECT * FROM dbo.employee "
+ "SELECT * FROM dbo.customer "
, ConnectionString);

DataSet schemaInfo = new DataSet();
adp.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adp.Fill(schemaInfo);
}

 

[Axel Dahmen]

Hi Gregory,

I guess I got trapped by an error in the MSDN Library. There it reads:

"Note

When handling batch SQL statements that return multiple results, the implementation of FillSchema for the .NET Framework Data Provider for OLE DB retrieves schema information for only the first result. To retrieve schema information for multiple results, use Fill with the MissingSchemaAction set to AddWithKey."

(http://msdn.microsoft.com/en-us/library/ms135707(VS.80).aspx)


It should read "... use ***FillSchema*** with the MissingSchemaAction set to AddWithKey..."

After simply trying that variation it worked...


I want to achieve to get the DataColumns of all database columns to provide a comfortable mechanism to automatically set SqlParameter structures with appropriate types and sizes. This is just step #1... In step #2 I want to get Stored Procedure parameter types and sizes but I'm not sure yet how to retrieve them using ADO.NET yet.

Thanks for trying to help, Gregory
Axel

 

[Zhi-Xin Ye [MSFT]]

Hi Axel,

Yes, it should be "FileSchema" method instead of "Fill" method in the
document.

For retrieving parameter information from the stored procedure, you can use
the SqlCommandBuilder.DeriveParameters() method.

====== Sample Code For Your Information ========

string connstr = "Persist Security Info=False;Integrated
Security=SSPI;Initial Catalog=Northwind;server=localhost";
SqlConnection conn = new SqlConnection(connstr);

// Create Command
SqlCommand command = conn.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "SalesByCategory"; //stored procedure name

conn.Open();

// Discover Parameters for Stored Procedure
// Populate command.Parameters Collection.
SqlCommandBuilder.DeriveParameters(command);

// Populate the Input Parameters With Values Provided
foreach (SqlParameter parameter in command.Parameters)
{
Console.WriteLine(parameter.ParameterName);
Console.WriteLine(parameter.SqlDbType.ToString());
Console.WriteLine(parameter.Size);
}

========================================

For more information about the SqlCommandBuilder.DeriveParameters() method,
you can refer to these documents:

SqlCommandBuilder.DeriveParameters Method
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuil
der.deriveparameters.aspx

Configuring Parameters and Parameter Data Types (ADO.NET)
http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx

Please try my suggestion, and feel free to let me know if you have any
questions or concerns, I will be happy to be of assistance.

Have a great day!

Sincerely,
Zhi-Xin Ye
Microsoft Managed Newsgroup Support Team

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Axel Dahmen]

Hi, Zhi-Xin,

thanks a lot for pointing me to this information. Great! I'll use this in my base class.

Best regards
www.axeldahmen.de
Axel Dahmen