Csharp/C Sharp/Database ADO.net/Table Schema

Get all table names

<source lang="csharp"> using System; using System.Data; using System.Data.OleDb; public class DatabaseInfo {

public static void Main () { 
  String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.\\Employee.mdb";
  OleDbConnection con = new OleDbConnection(connect);
  con.Open();  
  Console.WriteLine("Made the connection to the database");
  Console.WriteLine("Information for each table contains:");
  DataTable tables = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[]{null,null,null,"TABLE"});
  Console.WriteLine("The tables are:");
  foreach(DataRow row in tables.Rows) 
    Console.Write("  {0}", row[2]);

  con.Close();
}

}

      </source>

Get Column data type and name from DataColumn

<source lang="csharp"> using System; using System.Data; using System.Data.OleDb; public class DatabaseInfo {

public static void Main () { 
  String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.\\Employee.mdb";
  OleDbConnection con = new OleDbConnection(connect);
  con.Open();  
  Console.WriteLine("Made the connection to the database");
  String cmd = "SELECT * FROM Employee"; 
  OleDbDataAdapter adapter = new OleDbDataAdapter();
  adapter.SelectCommand = new OleDbCommand(cmd, con);
  DataSet ds = new DataSet();
  adapter.Fill(ds, "Employee");
  DataTable item = ds.Tables[0];
  Console.WriteLine("Table name: {0}", item.TableName);
  Console.WriteLine("Its columns are:");
  foreach (DataColumn col in item.Columns)
    Console.WriteLine("{0}\t{1}", col.ColumnName, col.DataType);
  con.Close();
}

}

      </source>

Get specified column data type and column name from OleDbSchemaTable

<source lang="csharp"> using System; using System.Data; using System.Data.OleDb; public class DatabaseInfo {

public static void Main () { 
  String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.\\Employee.mdb";
  OleDbConnection con = new OleDbConnection(connect);
  con.Open();  
  Console.WriteLine("Made the connection to the database");
  Console.WriteLine("Information for each table contains:");
  DataTable tables = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[]{null,null,null,"TABLE"});
  DataTable cols = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
        new object[]{null,null,"Employee",null});
  Console.WriteLine("The columns in the Customer table are:");

  foreach(DataRow row in cols.Rows) 
    Console.WriteLine("  {0}\t{1}", row[3],(OleDbType)row[11]);
  con.Close();
}

}

      </source>

Get table Schema

<source lang="csharp"> using System; using System.Data; using System.Data.SqlClient;

  class SchemaTable
  {
     static void Main(string[] args)
     {
        string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
        string sql = @"select * from employee";
        SqlConnection conn = new SqlConnection(connString);
        try {
           conn.Open();
           SqlCommand cmd = new SqlCommand(sql, conn);
           SqlDataReader reader = cmd.ExecuteReader();
           DataTable schema = reader.GetSchemaTable();
           foreach (DataRow row in schema.Rows)
           { 
              foreach (DataColumn col in schema.Columns){
                 Console.WriteLine(col.ColumnName + " = " + row[col]);
                 Console.WriteLine("Null value allowed: " + col.AllowDBNull);
              }
           }
           reader.Close();
        } catch(Exception e) {
           Console.WriteLine("Error Occurred: " + e);
        } finally {
           conn.Close();
        }
     }  
  }

      </source>

How to read a table schema

<source lang="csharp"> using System; using System.Data; using System.Data.SqlClient; class SchemaOnlyCommandBehavior {

 public static void Main()
 {
   SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
   SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
   mySqlCommand.rumandText ="SELECT ID, FirstName, LastName FROM Employee WHERE ID = 8";
   mySqlConnection.Open();
   SqlDataReader productsSqlDataReader =mySqlCommand.ExecuteReader(CommandBehavior.SchemaOnly);
   DataTable myDataTable = productsSqlDataReader.GetSchemaTable();
   foreach (DataRow myDataRow in myDataTable.Rows)
   {
     Console.WriteLine("\nNew column details follow:");
     foreach (DataColumn myDataColumn in myDataTable.Columns)
     {
       Console.WriteLine(myDataColumn + "= " +
         myDataRow[myDataColumn]);
       if (myDataColumn.ToString() == "ProviderType")
       {
         Console.WriteLine(myDataColumn + "= " +
           ((System.Data.SqlDbType) myDataRow[myDataColumn]));
       }
     }
   }
   productsSqlDataReader.Close();
   mySqlConnection.Close();
 }

}

      </source>

Read schema information using the FillSchema() method of a DataAdapter object

<source lang="csharp"> using System; using System.Data; using System.Data.SqlClient; class FillSchema {

 public static void Main()
 {
   SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
   SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
   mySqlCommand.rumandText =
     "SELECT ProductID, ProductName " +
     "FROM Products;" +
     "SELECT OrderID " +
     "FROM Orders;" +
     "SELECT OrderID, ProductID, UnitPrice " +
     "FROM [Order Details];";
   SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
   mySqlDataAdapter.SelectCommand = mySqlCommand;
   DataSet myDataSet = new DataSet();
   mySqlConnection.Open();
   mySqlDataAdapter.FillSchema(myDataSet, SchemaType.Mapped);
   mySqlConnection.Close();
   myDataSet.Tables["Table"].TableName = "Products";
   myDataSet.Tables["Table1"].TableName = "Orders";
   myDataSet.Tables["Table2"].TableName = "Order Details";
   foreach (DataTable myDataTable in myDataSet.Tables)
   {
     Console.WriteLine("\n\nReading from the " +
       myDataTable + "DataTable:\n");
     foreach (DataColumn myPrimaryKey in myDataTable.PrimaryKey)
     {
       Console.WriteLine("myPrimaryKey = " + myPrimaryKey);
     }
     foreach (Constraint myConstraint in myDataTable.Constraints)
     {
       Console.WriteLine("myConstraint.IsPrimaryKey = " + ((UniqueConstraint) myConstraint).IsPrimaryKey);
       foreach (DataColumn myDataColumn in ((UniqueConstraint) myConstraint).Columns)
       {
         Console.WriteLine("myDataColumn.ColumnName = " + myDataColumn.ColumnName);
       }
     }
     foreach (DataColumn myDataColumn in myDataTable.Columns)
     {
       Console.WriteLine("\nmyDataColumn.ColumnName = " + myDataColumn.ColumnName);
       Console.WriteLine("myDataColumn.DataType = " + myDataColumn.DataType);
       Console.WriteLine("myDataColumn.AllowDBNull = " + myDataColumn.AllowDBNull);
       Console.WriteLine("myDataColumn.AutoIncrement = " + myDataColumn.AutoIncrement);
       Console.WriteLine("myDataColumn.AutoIncrementSeed = " + myDataColumn.AutoIncrementSeed);
       Console.WriteLine("myDataColumn.AutoIncrementStep = " + myDataColumn.AutoIncrementStep);
       Console.WriteLine("myDataColumn.MaxLength = " + myDataColumn.MaxLength);
       Console.WriteLine("myDataColumn.ReadOnly = " + myDataColumn.ReadOnly);
       Console.WriteLine("myDataColumn.Unique = " + myDataColumn.Unique);
     }
   }
 }

}

      </source>