Display Database File Using TreeView And ListView With ADO.NET

This is a new article to display database file using TreeView and ListView with ADO.NET.

 

This article show how to,

• Use some Classes from System.Data.OleDb Namespace.
• Populate TreeView with Tables name and Fields name.
• Populate ListView with Records of selected Table.

I wrote the code using C# (2003).

 

The demonstration project has one Form, I add following controls to my Form (frmDataView),

• Two Labels (lblDatabase) to display the file name and (lblTableName) to display the table name.
• Two Buttons, one (btnLoadData) to connect with database file, other (btnExit) to end show.
• ImageList (ImageList1) to load some icons.
• TreeView (tvData) and ListView(lvData).

I have used two arrays as ArrayList to save tables name and fields name, tblArray to save tables name and fldArray to save fields name.

 

The code contains the following procedures,

• DataConnection() .. to connect with database file.
• GetTables() .. to fill tblArray with tables name.
• GetFields() .. to fill fldArray with fields name.
• FillTreeView() .. to fill TreeView control with tables name and fields name.
• FillListView() .. to fill ListView control with records of selected table.

After you expand the file (prjC.zip) you can view the full code in the Form (frmDataView).

 

I hope this article is useful and helps you to display tables, fields and records from database file to TreeView and ListView. Please tell me if you have any idea or if you find any problems. 

Important remark

Using [OleDbSchemaGuid] to get the name of fields return this fields are not in the same order as its table but the fields name are sorted (A-Z), therefore you can,

1. Create new SQL statement in the procedure [FillListView] wherein replace (*) with then fields name as ListView headers.
   
   or,
   
   
2. Use the following procedure [GetFields] instead of the procedure in my article because the following procedure uses [DataSet] to get fields name in order as fields in the table.

VB.NET

Private Sub GetFields(ByVal cnn As OleDbConnection, ByVal tabName As String)
Dim strSql As String = "SELECT * FROM " + tabName
Try
cnn.Open()
Dim cmdSelect As OleDbCommand = New OleDbCommand(strSql, datCon)
Dim datAdp As OleDbDataAdapter = New OleDbDataAdapter(cmdSelect)
Dim datSet As DataSet = New DataSet
datAdp.FillSchema(datSet, SchemaType.Source)
Dim columns As DataColumnCollection = datSet.Tables(0).Columns
fldArray = New ArrayList
For Each datColumn As DataColumn In columns
fldArray.Add(datColumn.ColumnName)
Next
cnn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub

C#

private void GetFields(OleDbConnection cnn, string tabName) {
    string strSql = "SELECT * FROM " + tabName;
    try {
        cnn.Open();
        OleDbCommand cmdSelect = new OleDbCommand(strSql, datCon);
        OleDbDataAdapter datAdp = new OleDbDataAdapter(cmdSelect);
        DataSet datSet = new DataSet();
        datAdp.FillSchema(datSet, SchemaType.Source);
        DataColumnCollection columns = datSet.Tables[0].Columns;
        fldArray = new ArrayList();
        foreach(DataColumn datColumn in columns) {
            fldArray.Add(datColumn.ColumnName);
        }
        cnn.Close();
    } catch (Exception ex) {
        MessageBox.Show(ex.Message);
    }
}