c# display DB table structure
- by user3529643
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.
