Given an SQLConnection object how can you get a schema for a single table?
I was trying this the other day and I seemed to be able to get the schema from a DataSet which I'd gotten from running a query, but all the schema info I could get from the connection seemed to be related to what tables were available and not the actual details on the tables.
I'm sure there is a simple way to do this.
This code will do what you want (obviously change the table name, server name etc):
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
namespace ConsoleApp
{
class Program
{
static void Main(string[] args)
{
string query = "SELECT * FROM t where 1=0";
string connectionString = "initial catalog=test;data source=localhost;Trusted_Connection=Yes";
DataTable tblSchema;
using (SqlConnection cnn = new SqlConnection(connectionString))
{
using (SqlCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = query;
cmd.CommandType = CommandType.Text;
cnn.Open();
using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.KeyInfo))
{
tblSchema = rdr.GetSchemaTable();
}
cnn.Close();
}
}
int numColumns = tblSchema.Columns.Count;
foreach (DataRow dr in tblSchema.Rows)
{
Console.WriteLine("{0}: {1}", dr["ColumnName"], dr["DataType"]);
}
Console.ReadLine();
}
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With