Solved

MS SQL Server getting metadata information of tables

Posted on 2006-11-21
2
257 Views
Last Modified: 2008-01-09
Hi,

Can someone point me to an example of the following:

I want to create a simple page which will query a table 'mytable' like: select id, name, address from mytable
and create the a table of html elements (no data grid) in a way that for id field textfield is displayed, for name textfields is displayed, for address textarea is displayed.

What I want to know is that how can you get the MetaData information so that i can check that if a field size is greater that 10 then display textarea else textfield, secondly if the field type is int then add a javascript validation to it (that is not a problem to add a js valid.) third i want to know the name of the column...

any help would be appreciated,

thanks.
0
Comment
Question by:glottis
2 Comments
 
LVL 96

Accepted Solution

by:
Bob Learned earned 50 total points
ID: 17995101
You can get schema information with a data reader's GetSchemaTable method.

How To Retrieve Column Schema by Using the DataReader GetSchemaTable Method and Visual C# .NET
http://support.microsoft.com/kb/310107

C# example:

OleDbConnection cn = new OleDbConnection();
OleDbCommand cmd = new OleDbCommand();
DataTable schemaTable;
OleDbDataReader myReader;
                  
//Open a connection to the SQL Server Northwind database.
cn.ConnectionString = "Provider=SQLOLEDB;Data Source=server;User ID=login;
                       Password=password;Initial Catalog=Northwind";
cn.Open();

//Retrieve records from the Employees table into a DataReader.
cmd.Connection = cn;
cmd.CommandText = "SELECT * FROM Employees";
myReader = cmd.ExecuteReader(CommandBehavior.KeyInfo);

//Retrieve column schema into a DataTable.
schemaTable = myReader.GetSchemaTable();

//For each field in the table...
foreach (DataRow myField in schemaTable.Rows){
    //For each property of the field...
    foreach (DataColumn myProperty in schemaTable.Columns) {
      //Display the field name and value.
      Console.WriteLine(myProperty.ColumnName + " = " + myField[myProperty].ToString());
    }
    Console.WriteLine();

    //Pause.
    Console.ReadLine();
}

//Always close the DataReader and connection.
myReader.Close();
cn.Close();

Bob
0
 
LVL 2

Author Comment

by:glottis
ID: 18001119
Bob,

Thanks for that. I will look at this and try to implement it in a web based application, and if successfull will award you the points.

Thanks again,

Imran.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now