c# display DB table structure

Posted by user3529643 on Programmers See other posts from Programmers or by user3529643
Published on 2014-05-28T00:43:21Z Indexed on 2014/05/28 4:00 UTC
Read the original article Hit count: 213

Filed under:
|
|

I have a question. My code is the following :

public partial class Form1 : Form
{
    public OleDbConnection datCon;
    public string MyDataFile;
    public ArrayList tblArray;
    public ArrayList fldArray;

    public Form1()
    {
        InitializeComponent();

        lvData.Clear();
        lvData.View = View.Details;
        lvData.LabelEdit = false;
        lvData.FullRowSelect = true;
        lvData.GridLines = true;
    }

    private void DataConnection()
    {
        MyDataFile = Application.StartupPath + @"\studenti.mdb";
        string MyCon = @"provider=microsoft.jet.oledb.4.0;data source=" + MyDataFile;

        try
        {
            datCon = new OleDbConnection(MyCon);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        FillTreeView();
    }

    private void GetTables(OleDbConnection cnn)
    {
        try 
        {
            cnn.Open();
            DataTable schTable = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" });
            tblArray = new ArrayList();

            foreach (DataRow datrow in schTable.Rows)
            {
                tblArray.Add(datrow["TABLE_NAME"].ToString());
            }
            cnn.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

    private void GetFields(OleDbConnection cnn, string tabNode)
    {
        string tabName;
        try
        {
            tabName = tabNode;
            cnn.Open();
            DataTable schTable = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
                new Object[] { null, null, tabName });
            fldArray = new ArrayList();
            foreach (DataRow datRow in schTable.Rows)
            {
                fldArray.Add(datRow["COLUMN_NAME"].ToString());
            }
            cnn.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

    private void FillTreeView()
    {
        tvData.Nodes.Clear();
        tvData.Nodes.Add("Database");

        tvData.Nodes[0].Tag = "RootDB";
        GetTables(datCon);

        // add table node
        for (int i = 0; i < tblArray.Count; i++)
        {
            tvData.Nodes[0].Nodes.Add(tblArray[i].ToString());
            tvData.Nodes[0].Nodes[i].Tag = "Tables";

        }
        // add field node
        for (int i = 0; i < tblArray.Count; i++)
        {
            GetFields(datCon, tblArray[i].ToString());
            for (int j = 0; j < fldArray.Count; j++)
            {
                tvData.Nodes[0].Nodes[i].Nodes.Add(fldArray[j].ToString());
                tvData.Nodes[0].Nodes[i].Nodes[j].Tag = "Fields";

            }
        }

        this.tvData.ContextMenuStrip = contextMenuStrip1;
        contextMenuStrip1.ItemClicked +=contextMenuStrip1_ItemClicked;
    }

    public void FillListView(OleDbConnection cnn, string tabName)
    {
        OleDbCommand cmdRead;
        OleDbDataReader datReader;
        string strField;

        lblTableName.Text = tabName;


        strField = "SELECT * FROM [" + tabName + "]";
        // Initi cmdRead obiect
        cmdRead = new OleDbCommand(strField, cnn);
        cnn.Open();
        datReader = cmdRead.ExecuteReader();
        // fill ListView
        while (datReader.Read())
        {
            ListViewItem objListItem = new ListViewItem(datReader.GetValue(0).ToString());
            for (int c = 1; c < datReader.FieldCount; c++)
            {
                objListItem.SubItems.Add(datReader.GetValue(c).ToString());
            }
             lvData.Items.Add(objListItem);
        }

        datReader.Close();
        cnn.Close();
    }

    private void ViewToolStripMenuItem_Click(object sender, EventArgs e)
    {
        DataConnection();
    }

    public void tvData_AfterExpand(object sender, System.Windows.Forms.TreeViewEventArgs e)
    {
        string tabName;
        int fldCount;

        if (e.Node.Tag.ToString() == "Tables")
        {
            fldCount = e.Node.GetNodeCount(false);
            //column headers.
            int n = lvData.Width;
            double wid = n / fldCount; // width columnn
            for (int c = 0; c < fldCount; c++)
            {
                lvData.Columns.Add(e.Node.Nodes[c].Text, (int)wid, HorizontalAlignment.Left);
            }
            // gett table name
            tabName = e.Node.Text;
            FillListView(datCon, tabName);
        }
    }

    public void button1_Click(object sender, EventArgs e)
    {
        //TO DO??
    }
}

I have a treeview populated with tables (nodes) from my database, and a listview which is populated with the data from my tables when I click on a table.

As you can see I have a button1 on my form. When I click it I want it to display to me the structure of the table I selected in my treeview (a treeview node). Not too many details, just the name of the columns in my table, type of columns, primary keys. I've tried to follow many tutorials but I can t seem to manage it.

© Programmers or respective owner

Related posts about c#

Related posts about database