Title:       Open Dataset from Text File - Load LIstView and GridView in .NET from TextFile
Author:      Greg Dubinovskiy 
Email:       siccolo_mobile_management@yahoo.com
Environment: c# .NET
Keywords:    Open Dataset from Text File, Load ListView, Load GridView, TextFile, System.Data.OleDb Namespace, OledbConnection, OleDbCommand
Level:       Intermediate
Description: Open Dataset from Text File - Load LIstView and GridView in .NET from TextFile
Section      Miscellaneous
SubSection   General

Siccolo Development Articles - Open Dataset from Text File - Load LIstView and GridView in .NET from TextFile
Google

Open Dataset from Text File

The article presented bellow, shows how to open Dataset from a Text File (using schema.ini) and display Text File in GridView or in ListView:
(see Siccolo miscellaneous development tips - How to Open Dataset from Text File) Using OleDB namespace we can connect/open text file and retrieve data from a text file using Dataset:

				string errorInfo = String.Empty;
				//open text file into Dataset:
				string textFilePath = @"c:\test_to_load.txt";

				DataSet dataTextFile = new DataSet("textfile");
				if ( ! LoadTextFile ( textFilePath, dataTextFile, out errorInfo) )
				{
					MessageBox.Show("Failed to load text file:\n"  + errorInfo,
						"Load Text File");
					return;
				}
				else
				{
					MessageBox.Show("File Loaded:\nTables:"  + dataTextFile.Tables.Count.ToString() 
													+ "\nRows:" + dataTextFile.Tables[0].Rows.Count.ToString() ,
						"Load Text File");
					
				}
				

where LoadTextFile() :
				private bool LoadTextFile( string textFilePath, DataSet dataToLoad, out string  errorInfo )
				{
					errorInfo = String.Empty;

					try
					{
						string textFileFolder = ( new System.IO.FileInfo (textFilePath)).DirectoryName;
						string textConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
														"Data Source=" + textFileFolder + ";" +
														"Extended Properties=\"text;\";" ;
						//from using System.Data.OleDb:
						OleDbConnection textConnection = new OleDbConnection (textConnectionString);

						textConnection.Open();

						textFilePath = (new System.IO.FileInfo(textFilePath)).Name;
						string selectCommand = "select * from " + textFilePath;

						//open command:
						OleDbCommand textOpenCommand = new OleDbCommand(selectCommand);
						textOpenCommand.Connection = textConnection;

						OleDbDataAdapter textDataAdapter = new OleDbDataAdapter( textOpenCommand );

						int rows = 	textDataAdapter.Fill(dataToLoad);

						textConnection.Close();
						textConnection.Dispose();

						return true;
					}
					catch (Exception ex_load_text_file)
					{
						errorInfo = ex_load_text_file.Message;
						return false;
					}
				}
				



Side Note:
System.Data.OleDb Namespace - The System.Data.OleDb namespace is the.NET Framework Data Provider for OLE DB.
The.NET Framework Data Provider for OLE DB describes a collection of classes used to access an OLE DB data source in the managed space. Using the OleDbDataAdapter, you can fill a memory-resident DataSet that you can use to query and update the data source.

OleDbConnection class - Represents an open connection to a data source.

OleDbConnection class - Represents an SQL statement or stored procedure to execute against a data source.

OleDbConnection class - Represents an SQL statement or stored procedure to execute against a data source.

OleDbDataAdapter class - Represents a set of data commands and a database connection that are used to fill the DataSet and update the data source.

... then, loading GridView from a Text File:


GridView Design:

GridView Run-time:

To load a GridView from Dataset, at run-time:
				private void cmdLoadFileGrid_Click(object sender, System.EventArgs e)
				{
					string errorInfo = String.Empty;
					//open text file into Dataset:
					string textFilePath = @"c:\test_to_load.txt";

					DataSet dataTextFile = new DataSet("textfile");
					if ( ! LoadTextFile ( textFilePath, dataTextFile, out errorInfo) )
					{
						MessageBox.Show("Failed to load text file:\n"  + errorInfo,
							"Load Text File");
						return;
					}
					else
					{
						MessageBox.Show("File Loaded:\nTables:"  + dataTextFile.Tables.Count.ToString() 
							+ "\nRows:" + dataTextFile.Tables[0].Rows.Count.ToString() ,
							"Load Text File");
						
					}

					dgTextFile.DataSource = dataTextFile.Tables[0];

					dataTextFile.Dispose();	
				}
				

... or, to load ListView from a Text File:


ListView Design:

ListView Run-time:

To load a ListView from Dataset, at run-time - doing it a "hard-way":
				private void cmdLoadFileListView_Click(object sender, System.EventArgs e)
				{
					string errorInfo = String.Empty;
					//open text file into Dataset:
					string textFilePath = @"c:\test_to_load.txt";

					DataSet dataTextFile = new DataSet("textfile");
					if ( ! LoadTextFile ( textFilePath, dataTextFile, out errorInfo) )
					{
						MessageBox.Show("Failed to load text file:\n"  + errorInfo,
							"Load Text File");
						return;
					}
					else
					{
						MessageBox.Show("File Loaded:\nTables:"  + dataTextFile.Tables.Count.ToString() 
														+ "\nRows:" + dataTextFile.Tables[0].Rows.Count.ToString() ,
							"Load Text File");
						
					}

					//setup list view from columns from Dataset:
					SetupListView(dataTextFile);

					//show data in list view:
					if ( ! ShowDataInListView(dataTextFile, out errorInfo))
					{
						MessageBox.Show("Failed to display text file (listview) :\n"  + errorInfo,
							"Load Text File");
						return;
					}

					dataTextFile.Dispose();		
				}
				


where, setting up ListView Column headers based on DataColumns in Dataset - SetupListView():
				private bool SetupListView(DataSet dataToShow)
				{
					try
					{
						lvTextFile.Columns.Clear();
						lvTextFile.View = View.Details;
						lvTextFile.FullRowSelect = true;
						lvTextFile.GridLines = true;
						lvTextFile.MultiSelect = false;

						foreach (DataColumn textColumn in dataToShow.Tables[0].Columns)
						{
							ColumnHeader header = new ColumnHeader ();
							header.Text = textColumn.Caption;
							lvTextFile.Columns.Add(  header );
						}

						return true;
					}
					catch
					{
						return false;
					}
				}
				


and loading data into ListView from a TextFile, based on DataRow in Dataset - ShowDataInListView():
				private bool ShowDataInListView(DataSet dataToShow, out string errorInfo)
				{
					errorInfo = String.Empty;
					try
					{
						lvTextFile.Items.Clear();
						foreach ( DataRow textRow in dataToShow.Tables[0].Rows)
						{
							ListViewItem li = new ListViewItem( textRow[0].ToString());
							for (int i=1;i< dataToShow.Tables[0].Columns.Count;i++)
							{	li.SubItems.Add ( textRow[i].ToString() );	}

							lvTextFile.Items.Add(li);
						}

						return true;
					}
					catch (Exception ex_show_data)
					{
						errorInfo = ex_show_data.Message;
						return false;
					}
				}
				

Side Note:
DataRow class - Represents a row of data in a DataTable.
The DataRow and DataColumn objects are primary components of a DataTable. Use the DataRow object and its properties and methods to retrieve and evaluate; and insert, delete, and update the values in the DataTable.

DataColumn class - Represents the schema of a column in a DataTable.
The DataColumn is the fundamental building block for creating the schema of a DataTable. You build the schema by adding one or more DataColumn objects to the DataColumnCollection. Each DataColumn has a DataType property that determines the kind of data the DataColumn contains.



ListView ColumnHeader class - Displays a single column header in a ListView control. A column header is an item in a ListView control that contains heading text.





And that's it.
Article keywords: OleDB namespace, OleDbConnection, OleDbCommand, OleDbDataAdapter, OleDbDataAdapter.Fill(), DataSet, DataColumn, DataRow, ListView, ColumnHeader, GridView, DataSource

Back To Articles Page

Free Mobile Management For SQL Server(s!) - Siccolo - SQL Management ToolQuestions? Suggestions? Concerns? - email me to siccolo_mobile_management@yahoo.com    Greg Dubinovsky 2008
or share your thoughts at Siccolo Blog

Web being sponsor - Mid-Atlantic Processing. Well being sponsor - Clarity MediSpa. Hairless sponsor - Clarity MediSpa Laser Hair Removal.
Siccolo - SQL Server Management Tool For Mobile Devices is packed with built-in functionality and tools. Siccolo delivers a rich set of management tools for both DBAs and sys admins. SQL Server management has always been an area of DBA concern. The new Management Tool For Mobile Devices - Siccolo - has simple "Enterprise Manager" and the "Query Analyzer". Siccolo is a management tool for the MS SQL Server with administration capabilities and a database query tool. The administration features provide users the ability to browse database structures. An integrated query tool allows users to quickly create, edit and execute SQL queries and scripts. Siccolo also provides an export tool to allow users to easily save and email execution results. Siccolo helps database professionals save time and increase their productivity by utilizing a more efficient approach to database management - use their Windows Mobile empowered device while sipping margarita on the beach For increased security, Siccolo is configured to run under SSL with IIS authentication.