This article has been
excerpted from 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 their properties of the selected table on list box.
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:
// Define variables
private string
dbName = "";
Listing 10-11: Reading a database schemas programmatically
private
void BrowseBtn_Click(object
sender, System.EventArgs e)
{
OpenFileDialog fdlg =
new OpenFileDialog();
fdlg.Title = "C# Corner Open File
Dialog";
fdlg.InitialDirectory = @"c:\";
fdlg.Filter = "All files (*.*)|*.mdb|"
+
"MS-Access Database files (*.mdb)|*.mdb";
fdlg.FilterIndex = 2;
fdlg.RestoreDirectory = true;
if (fdlg.ShowDialog() ==
DialogResult.OK)
{
textBox1.Text = fdlg.FileName;
dbName = fdlg.FileName;
}
}
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
private void
GetTablesBtn_Click(object sender, System.EventArgs
e)
{
// Connection string
string strDSN =
"Provider=Microsoft.Jet.OLEDB.4.0; " +
"Data Source=" + dbName;
try
{
// Create a
connection and open it
OleDbConnection conn = new
OleDbConnection(strDSN);
conn.Open();
// Call
GetOleDbSchemaTable to get the schema data table
DataTable dt =
conn.GetOleDbSchemaTable
(OleDbSchemaGuid.Tables, new
object[] { null,
null, null,
"TABLE" });
// Set
DataSource and Displaymember properties
// of the list
box control
listBox1.DataSource = dt.DefaultView;
listBox1.DisplayMember = "TABLE_NAME";
// Close the
connection
conn.Close();
}
catch (Exception
exp)
{
MessageBox.Show(exp.Message.ToString());
}
}
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
unique, column data type, column name, and so on.
Listing 10-13: Getting a database table schema
private void
GetSchemaBtn_Click(object sender, System.EventArgs
e)
{
// Get the selected
item text of list box
string selTable =
listBox1.GetItemText(listBox1.SelectedItem);
// Connection string
string strDSN =
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + dbName;
try
{
// Create and
open connection
OleDbConnection conn = new
OleDbConnection(strDSN);
conn.Open();
string strSQL =
"SELECT * FROM " + selTable;
//Create data
adapter
OleDbDataAdapter myCmd = new
OleDbDataAdapter(strSQL, conn);
// Create and
fill dataset
DataSet dtSet =
new DataSet();
myCmd.Fill(dtSet);
DataTable dt =
dtSet.Tables[0];
// Add items to
the list box control
listBox2.Items.Add("Column Name,
DataType, Unique," +
"AutoIncrement, AllowNull");
listBox2.Items.Add("=============");
foreach (DataColumn
dc in dt.Columns)
{
listBox2.Items.Add(dc.ColumnName +
" , " + dc.DataType +
" , " + dc.Unique +
" ," + dc.AutoIncrement +
" ," + dc.AllowDBNull);
}
// close
connection
conn.Close();
}
catch (Exception
exp)
{
MessageBox.Show(exp.Message.ToString());
}
}
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
private void
button1_Click(object sender, System.EventArgs
e)
{
// create a
connection object
string ConnectionString =
@"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=c:\\Northwind.mdb";
OleDbConnection conn = new
OleDbConnection(ConnectionString);
// Open the
connection
if (conn.State !=
ConnectionState.Open)
conn.Open();
string sql =
"SELECT DISTINCT(LastName)" +
"FROM Employees ORDER BY LastName";
OleDbDataAdapter da = new
OleDbDataAdapter(sql, conn);
DataSet ds =
new DataSet();
da.Fill(ds, "Employees");
dataGrid1.DataSource = ds.DefaultViewManager;
// Close the
connection
if (conn.State ==
ConnectionState.Open)
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 deeper
understanding of ADO.NET. |