TechiesWeb

Most of the application which handles with bulk amount of data needs an import facility.Recently i also came across the same situation where i had to read data from an excel file and insert ito to the backend – Database table.
It s quite simple.Lets look at the solution.
Lets start.. Initially I am uploading the excel file which user submitted to somewhere in the server

string tempExcelFileUploadPath=”";
try {
tempExcelFileUploadPath = “Excelstore/TempFiles/”;
tempExcelFileUploadPath = Server.MapPath(tempExcelFileUploadPath);
if (!Directory.Exists(tempExcelFileUploadPath))� // If directory not exist, create a new one
{
Directory.CreateDirectory(tempExcelFileUploadPath);
}
tempFilePath = tempExcelFileUploadPath + “\\tempExcelFile.xls”;
excelUploader.PostedFile.SaveAs(tempFilePath);
Response.Write(“<br><font face=’Verdana’ > Excel file Uploaded …</font>”);
}
catch (Exception er)
{

Response.Write(“<br><font face=’Verdana’ > Error in excel file upload ! </font>”); Response.Write(“<br>” + er.ToString());
}

Now we have uploaded the excel file to server.The file is located in the folder Excelstore/TempFiles with the name as tempExcelFile.xls. Now we wanto read the data from this file .for this we are using an Oledb connection (Import the

System.Data.OleDb to our program)

Ex : using System.Data.OleDb; OleDbConnection con = new OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + tempFilePath + “;Extended Properties=Excel 8.0″);
// Create an object of OleDBConnection class
con.Open();// Open the connection
try { //Create Dataset and fill with information from the Excel Spreadsheet for easier reference
DataSet myDataSet = new DataSet();
SqlCommand objCmd = new SqlCommand();
OleDbDataAdapter myCommand = new OleDbDataAdapter(“SELECT * from [Sheet1$]“, con);

myCommand.Fill(myDataSet);
con.Close();
DataTable dTblManu;
dTblManu = new DataTable();
DataTable dt = myDataSet.Tables[0];
Response.Write(” Reading data from Excel file :……….”);
foreach (DataRow dr in dt.Rows)
{
if (dr[0].ToString() != “”)
{
name=d r[0].ToString();
age=d r[1].ToString(); // Now the variable ‘ name’ is holding the value of the first record’s name column

// Code to insert this data to DB (Build an inser query here)
// Ex : string sqlInsert=”insert into students (name,age) values

(‘”+name+”‘,”+age+”)”; //execute the query
}

}

catch (Exception ex1)

{

//Write excpetion handling code heree

}

Thats all you need. Try this. If you  face any difficulty in following this,
Please let me know it. Hapy yo HELP.

Some time we need to store connection strings in web.config file .The below is the code to add connection string to web.config.file

<configuration> <appSettings> <add key=”ConnectionString” value=”server=localhost;uid=sa;pwd=;database=testdatabase” /> </appSettings> </configuration>

You can change the connection string according to your requirement.here i am specifying a connectionstring for accessing data from the same server (therefor server : localhost) and the database name is testdatabase.

How do we access this from our ASP.NET pages ?     This way it is

We need to include System.Configuration namespace
using System.Configuration;
string connectionString = (string )ConfigurationSettings.AppSettings["ConnectionString"];

When you are using response.write in your server side code behind page (C# or vb) for an ASP.NET page,The styles of page /CSS seems to be disappeared.Have you ever come across this problem ? Here is the solution to get rid of the probelm .

Instead of simply writing Response.Write, Use ” Page.ClientScript.RegisterStartupScript”

Ex : string strRenderImg=@”<img src=’company_logo1.gif’ align=’center’ />”;

Page.ClientScript.RegisterStartupScript(this.GetType(), “strRenderImg”, strRenderImg);

In C#, Creating a DateTime object from a Date String is as follows.

string strStartDate=”11/11/2009″;

DateTime objStartDate = DateTime.Parse(strStartDate);

The objStartDate object,which is an object of DateTime class,  is now holding the date and u can use this object for all date manipulation functions

Always use DateTime wherever handling the dates instead of using String.DateTime gives u flexibility to work more on that like performing date manipulation