Getting a Database Schema

This article has been excerpted from the book "A Programmer's Guide to ADO.NET in C#".
 
Usually, when you work with databases, you already known the database schema such as database tables, table columns, add the column properties. What if you don't know the database schema, and you need to know database tables, their columns, and column properties programmatically?
 
In this sample, I'll show you how to access a database schema programmatically. As you can see from figure 10-36, I created a Windows application with one text box, three buttons, and two list boxes. The Browser button lets you browse .mdb database on your machine. The GetTables button then reads the database tables and adds them to the first list box. The Get Table Schema button returns table columns and the properties of the selected table on the list box.
 
Figure-10.36.gif
 
Figure 10-36: Getting a database schema programmatically
 
Listing 10-11 shows the source code for this application. As you can see, the BrowseBtn_Click handler browsers Access database on the machine fills the selected database name to the text box, and sets dbName as the database name, which is a string type of variable defined as follows:
  1. // Define variables   
  2. private string dbName = ""
Listing 10-11: Reading a database schemas programmatically
  1. private void BrowseBtn_Click(object sender, System.EventArgs e) {  
  2.     OpenFileDialog fdlg = new OpenFileDialog();  
  3.     fdlg.Title = "C# Corner Open File Dialog";  
  4.     fdlg.InitialDirectory = @ "c:\";  
  5.     fdlg.Filter = "All files (*.*)|*.mdb|" + "MS-Access Database files (*.mdb)|*.mdb";  
  6.     fdlg.FilterIndex = 2;  
  7.     fdlg.RestoreDirectory = true;  
  8.     if (fdlg.ShowDialog() == DialogResult.OK) {  
  9.         textBox1.Text = fdlg.FileName;  
  10.         dbName = fdlg.FileName;  
  11.     }  
The GetOleDbSchemaTable method of OleDbConnection returns a data table object containing database tables. As you can see from the GetTableBtn_Click handler in Listing 10-12, I set the dataset to the left list box with the DisplayMember property as TABLE_NAME. I set DesplayMember because I want to show only one column of the data table in the list box. 
 
Listing 10-12: Getting database tables from a SQL Server database 
  1. private void GetTablesBtn_Click(object sender, System.EventArgs e) {  
  2.    // Connection string  
  3.    string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0; " + "Data Source=" + dbName;  
  4.    try {  
  5.        // Create a connection and open it  
  6.        OleDbConnection conn = new OleDbConnection(strDSN);  
  7.        conn.Open();  
  8.        // Call GetOleDbSchemaTable to get the schema data table  
  9.        DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { nullnullnull"TABLE" });  
  10.        // Set DataSource and Displaymember properties  
  11.        // of the list box control  
  12.        listBox1.DataSource = dt.DefaultView;  
  13.        listBox1.DisplayMember = "TABLE_NAME";  
  14.        // Close the connection  
  15.        conn.Close();  
  16.    } catch (Exception exp) {  
  17.        MessageBox.Show(exp.Message.ToString());  
  18.    }  
GetSchemaBn_Click listed in Listing 10-13 is the event handler that returns the columns and their properties of a database table. You read the database table using SELECT * and use DataTable to get columns. The DataColumn class defines a unique, column data type, column name, and so on.
 
Listing 10-13: Getting a database table schema 
  1. private void GetSchemaBtn_Click(object sender, System.EventArgs e) {  
  2.     // Get the selected item text of list box  
  3.     string selTable = listBox1.GetItemText(listBox1.SelectedItem);  
  4.     // Connection string  
  5.     string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;" +  
  6.         "Data Source=" + dbName;  
  7.     try {  
  8.         // Create and open connection  
  9.         OleDbConnection conn = new OleDbConnection(strDSN);  
  10.         conn.Open();  
  11.         string strSQL = "SELECT * FROM " + selTable;  
  12.         //Create data adapter  
  13.         OleDbDataAdapter myCmd = new OleDbDataAdapter(strSQL, conn);  
  14.         // Create and fill dataset  
  15.         DataSet dtSet = new DataSet();  
  16.         myCmd.Fill(dtSet);  
  17.         DataTable dt = dtSet.Tables[0];  
  18.         // Add items to the list box control  
  19.         listBox2.Items.Add("Column Name, DataType, Unique," +  
  20.             "AutoIncrement, AllowNull");  
  21.         listBox2.Items.Add("=============");  
  22.         foreach(DataColumn dc in dt.Columns) {  
  23.             listBox2.Items.Add(dc.ColumnName + " , " + dc.DataType +  
  24.                 " , " + dc.Unique + " ," + dc.AutoIncrement + " ," + dc.AllowDBNull);  
  25.         }  
  26.         // close connection  
  27.         conn.Close();  
  28.     } catch (Exception exp) {  
  29.         MessageBox.Show(exp.Message.ToString());  
  30.     }  
Selecting Distinct Records
 
It isn't hard to select unique rows from a database table using SELECT DISTINCT, but sometimes people get stuck on this. So, it's not a bad idea to talk about the procedure.
 
You can use the SELECT DISTINCT SQL statement to select distinct records from a database. This is useful when you want to return only one record corresponding to a criterion. Listing 10-14 returns distinct records from the Employees table ordered by the LastName.
 
Listing 10-14: Selecting distinct rows from a database table
  1. private void button1_Click(object sender, System.EventArgs e) {  
  2.     // create a connection object  
  3.     string ConnectionString = @ "Provider=Microsoft.Jet.OLEDB.4.0;" +  
  4.         "Data Source=c:\\Northwind.mdb";  
  5.     OleDbConnection conn = new OleDbConnection(ConnectionString);  
  6.     // Open the connection  
  7.     if (conn.State != ConnectionState.Open)  
  8.         conn.Open();  
  9.     string sql = "SELECT DISTINCT(LastName)" +  
  10.         "FROM Employees ORDER BY LastName";  
  11.     OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);  
  12.     DataSet ds = new DataSet();  
  13.     da.Fill(ds, "Employees");  
  14.     dataGrid1.DataSource = ds.DefaultViewManager;  
  15.     // Close the connection  
  16.     if (conn.State == ConnectionState.Open)  
  17.         conn.Close();  

Conclusion

 
Hope this article would have helped you in understanding getting a Database Schema. See other articles on the website also for further reference.
 
adobook.jpg
 
This essential guide to Microsoft's ADO.NET overviews C# then leads you toward a deeper understanding of ADO.NET.


Similar Articles
Mindcracker
Founded in 2003, Mindcracker is the authority in custom software development and innovation. We put best practices into action. We deliver solutions based on consumer and industry analysis.