C# – Getting the schema for a table

ado.netc++schemasqlsql-server

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.

Best Solution

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();
        }
    }
}

This is the style that Microsoft tends to use in their examples.

It appears that the guidance in this area may have changed, as StyleCop now enforces the use of the C# specific aliases.

Sql – the difference between “INNER JOIN” and “OUTER JOIN”

Assuming you're joining on columns with no duplicates, which is a very common case:

An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Examples

Suppose you have two tables, with a single column each, and data as follows:

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join

A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right outer join

A right outer join will give all rows in B, plus any common rows in A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full outer join

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

Best Solution

Related Solutions

C# – the difference between String and string in C#

This is the style that Microsoft tends to use in their examples.

Sql – the difference between “INNER JOIN” and “OUTER JOIN”

Related Question