Title: Open Dataset from Text File - Load LIstView and GridView in .NET from TextFile Author: Greg Dubinovskiy Email: [email protected] 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
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");
}
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;
}
}
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.
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();
}
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();
}
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;
}
}
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;
}
}
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.