TechiesWeb.net

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 imformation 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 :….. at  :  ” + DateTime.Now.ToString() + “………………………”);

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 alll  you need. Try this. If you found / face any difficulty in following this, Please let me know it. Hapy yo HELP

Happy programming

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);