• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

MS SQL Server getting metadata information of tables

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
glottis
Asked:
glottis
1 Solution
 
Bob LearnedCommented:
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
 
glottisAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now