Hello Friends,
I have created a project name ExcelChart. I have an excel file (Sample.xls) which has been inserted in a dbo table(ExcelTable.dbo) in asp.net (VS2010) with the help of the following code:
using System.IO;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
protected void BtnImport_Click(object sender, EventArgs e)
{
String strConnection = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ExcelChart.mdf;Integrated Security=True;User Instance=True";
//file upload path
string path = FileUploadexcel.PostedFile.FileName;
string fileBasePath = Server.MapPath("~/Excel/");
string fileName = Path.GetFileName(this.FileUploadexcel.FileName);
string fullFilePath = fileBasePath + fileName;
//Create connection string to Excel work book
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fullFilePath + ";Extended Properties=Excel 12.0;Persist Security Info=False";
//Create Connection to Excel work book
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand("Select [Carats],[Value],[Month] from [Sheet1$]", excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "ExcelTable";
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
}
After the data is been inserted in the dbo table, the data is shown on the webpage via a GridView control the same details are shown by a Graph (as per the details in ExcelTable.dbo) with the help of the below code:
private void BindData()
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["CnStrExcelChart"].ConnectionString);
string CmdString = "SELECT Carats, Value, Month from ExcelTable";
SqlDataAdapter sda = new SqlDataAdapter(CmdString, con);
DataSet ds = new DataSet();
sda.Fill(ds);
Chart1.DataBind();
}
protected void BtnGraph_Click(object sender, EventArgs e)
{
Chart1.Visible = true;
BindData();
}
As the Month column in ExcelTable.dbo has been marked as Primary Key i face issue in inserting duplicate data. So thought of erasing the values from the dbo table when ever the page is refreshed and inserting back the details again.
I am trying to build a code where in after postback the data from the dbo table should be erased. When i click on Import button the data should be inserted again in the dbo table and the details should be shown in the graph. Again when i refresh the page the data from the dbo should be erased.
The best i can think of is if..else clause but need help in building the code. Please help.
