1. Sample text file Data
121211|23
121211|23
121211|23
121211|23
121211|23
121211|23
2. Write below code in a botton
string OpenPath, contents;
int tabSize = 4;
string[] arInfo;
string line;
//Create new DataTable.
System.Data.DataTable table = CreateTable();
DataRow row;
try
{
openFileDialog1.ShowDialog();
//DirectoryInfo fileListing = new DirectoryInfo(openFileDialog1.FileName.ToString());
// foreach (FileInfo file in fileListing.GetFiles("*.txt"))
// {
// EntryFileName = file.Name.ToString();
//listBox2.Items.Add(file.Name);
//lstfileName.Items.Add(file.Name + "\t-Not Exists");
// OpenPath = fileListing.ToString() + openFileDialog1.FileName.ToString();
string FILENAME = openFileDialog1.FileName.ToString();
//Get a StreamReader class that can be used to read the file
StreamReader objStreamReader;
objStreamReader = File.OpenText(FILENAME);
string[] lines = File.ReadAllLines(FILENAME);
for (int l = 0; l <>
{
line = lines[l];
//while ((line = objStreamReader.ReadLine()) != null)
//{
contents = line.Replace(("|").PadRight(tabSize, ' '), "|");
// define which character is seperating fields
char[] textdelimiter = { '|' };
arInfo = contents.Split(textdelimiter);
for (int i = 0; i <= arInfo.Length + 1; i++)
{
row = table.NewRow();
if (i <>
row["ItemID"] = arInfo[i].ToString().Replace("[", " ");
if (i + 1 <>
row["SalesPrice"] = arInfo[i + 1].ToString().Replace("[", " ");
if (i + 2 <>
{
row["MRP"] = arInfo[i + 2].ToString();
table.Rows.Add(row);
}
i = i + 2;
}
}
objStreamReader.Close();
//}
// Set to DataGrid.DataSource property to the table.
if (table.Rows.Count > 0)
{
btnUpdate.Enabled = true;
}
dataGridView1.DataSource = table;
int records = dataGridView1.RowCount - 1;
label1.Text = "Total Records :" + records.ToString();
//GridView1.DataBind();
//label1.Text = "Total Items : [" + table.Rows.Count.ToString() + "]";
}
catch (Exception ex)
{
// Response.Write(ex.Message.ToString());
//lblError.Text = ex.Message;
}
finally
{
// button1.Enabled = false;
}
#region Create DateTable
private System.Data.DataTable CreateTable()
{
try
{
// table.Reset();
if (table.Columns.Count > 0)
{
return table;
}
// Declare DataColumn and DataRow variables.
DataColumn column;
// Create new DataColumn, set DataType, ColumnName
// and add to DataTable.
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "ItemID";
table.Columns.Add(column);
// Create second column.
column = new DataColumn();
column.DataType = Type.GetType("System.String");
column.ColumnName = "SalesPrice";
table.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "MRP";
table.Columns.Add(column);
return table;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
#endregion
Database Connection Open and execute simple Update Query
/// Connection Open
/// here 1 for OLEDB Connection and 2 for SQLConnection
/// i am just using a condition to execute two type connection in a one function
/// strQuery is the SQL Query
1. Assign update query
string member = memberID + "/IAC";
string strSQL = " update bob_contacts ";
strSQL+= " set bob_contactexternalid = replace(bob_contactexternalid,'IAC','IND'), ";
strSQL+= " bob_modify_date_time = getdate(), ";
strSQL+= " bob_contactcategoryid = 8, ";
strSQL+= " bob_contact_name = left(bob_contact_name,8) + '/ACT' ";
strSQL+= " where bob_contacttypeid = 'customer' and bob_contactcategoryid = 11 ";
strSQL += " and bob_contact_name = '"+member+"' ";
2. execute this query
executeSQL(strSQL);
public void executeSQL(string strQuery)
{
if (command == 1)
{
OleDbConnection dbCon = GetOledbConnection();
if (dbCon.State != ConnectionState.Open)
{
dbCon.Open();
}
OleDbCommand oledbComm = new OleDbCommand(strQuery, dbCon);
oledbComm.ExecuteNonQuery();
dbCon.Close();
}
if (command == 2)//TODO : SqlServer Database
{
SqlConnection dbCon = GetSqlConnection();
if (dbCon.State != ConnectionState.Open)
{
dbCon.Open();
}
SqlCommand oledbComm = new SqlCommand(strQuery, dbCon);
oledbComm.ExecuteNonQuery();
dbCon.Close();
}
}
3. OPen Connection String
public OleDbConnection GetOledbConnection()
{
OleDbConnection dbCon = new OleDbConnection(m_ConnectionString);
try
{
if (dbCon.State != ConnectionState.Open)
{
dbCon.Open();
}
}
catch (Exception Ex)
{
throw Ex;
}
finally
{
dbCon.Close();
}
return dbCon;
}
/////// here m_ConnectionString is the connection string
MS Access connection
m_ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Payroll.mdb"
MSSQL Server
m_ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"
and
Oracle Connection
m_ConnectionString="Provider=msdaora; user id=xxx;password=xxx; data source=xxx"
public SqlConnection GetSqlConnection()
{
SqlConnection dbCon = new SqlConnection(m_ConnectionString);
try
{
if (dbCon.State != ConnectionState.Open)
{
dbCon.Open();
}
}
catch (Exception Ex)
{
throw Ex;
}
finally
{
dbCon.Close();
}
return dbCon;
}