Display Database using TreeView and ListView with ADO.NET

[vincezed]

Hi all.... i have found code that displays treeview and listview from database but it is done for microsoft access ... i am try this code for sql sever but end up only in exception,..... can any one modify this code to access the sql database and display the listview..... thanks..... for further details about this see the link........

thanks in advance
vince

http://www.codeproject.com/KB/list/TreeView_ListView.aspx?msg=3848850#xx3848850xx

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

			try
			{
				//Initialize datCon object
				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();
			// set root node of TreeView.
			tvData.Nodes.Add("Database");
			tvData.Nodes[0].ImageIndex = 0;
			tvData.Nodes[0].SelectedImageIndex = 0;
			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";
				tvData.Nodes[0].Nodes[i].ImageIndex = 2;
				tvData.Nodes[0].Nodes[i].SelectedImageIndex = 2;
			}

			// 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";
					tvData.Nodes[0].Nodes[i].Nodes[j].ImageIndex = 4;
					tvData.Nodes[0].Nodes[i].Nodes[j].SelectedImageIndex = 4;
				}
			}
		}

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

			lblTableName.Text = tabName;

			strField = "SELECT * FROM [" + tabName + "]";
			// Initialize cmdRead object
			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());
			}
			objListItem.ImageIndex = 5;
			lvData.Items.Add(objListItem);
		}
		
		datReader.Close();
		cnn.Close();
		}

		private void btnLoadData_Click(object sender, System.EventArgs e)
		{
			DataConnection();
			lblDatabase.Text = MyDataFile;
		}

		private void tvData_AfterCollapse(object sender, System.Windows.Forms.TreeViewEventArgs e)
		{
			if (e.Node.Tag.ToString() == "RootDB")
			{
				e.Node.ImageIndex = 0;
				e.Node.SelectedImageIndex = 0;
			}
			else if (e.Node.Tag.ToString() == "Tables")
			{
				e.Node.ImageIndex = 2;
				e.Node.SelectedImageIndex = 2;
				lvData.Clear();
			}
		}

		private void tvData_BeforeExpand(object sender, System.Windows.Forms.TreeViewCancelEventArgs e)
		{
			lvData.Clear();
			// We want only one node expands
			for (int i = 0; i < tvData.Nodes[0].GetNodeCount(false); i++)
			{
				tvData.Nodes[0].Nodes[i].Collapse();
			}
		}

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

			if (e.Node.Tag.ToString() == "RootDB")
			{
				e.Node.ImageIndex = 1;
				e.Node.SelectedImageIndex = 1;
			}
			else if (e.Node.Tag.ToString() == "Tables")
			{
				e.Node.ImageIndex = 3;
				e.Node.SelectedImageIndex = 3;
				fldCount = e.Node.GetNodeCount(false);
				// create the column headers.
				int n = lvData.Width;
				double wid = n / fldCount; // width of any column
				for (int c = 0; c < fldCount; c++)
				{
					lvData.Columns.Add(e.Node.Nodes[c].Text, (int)wid, HorizontalAlignment.Left);
				}
				// get table name
				tabName = e.Node.Text;
				FillListView(datCon, tabName);
			}
		}

		private void frmDataView_Load(object sender, System.EventArgs e)
		{
			// init TreeView
			tvData.ImageList = imageList1;
			// init ListView
			lvData.SmallImageList = imageList1;
			lvData.Clear();
			lvData.View = View.Details;
			lvData.LabelEdit = false;
			lvData.FullRowSelect = true;
			lvData.GridLines = true;
		}