Hi,
Here i am trying to load a Excel file to a grid view without saving to the database.
I need to perform DML operations on the grid that is saved in a DataSet / DataTable.
I dont have any idea of performing operations on a dataset and here i am saving the whole excel sheet in a ViewState and assiging it to gridview.
Any one have idea of performing DML operations on a DataSet/DataTable. If So, helpme out in this aspect.
Thank You.
This is the process i am following
First:
Loading excel to gridview
DataTable _dtTable = new DataTable();
DataSet _objDs = new DataSet();
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
OleDbConnection connection = null;
string excelPath = ConfigurationManager.AppSettings["ExcelPath"].ToString();
excelPath = excelPath + FileUpload1.PostedFile.FileName;
ViewState["ExcelSheetName"] = FileUpload1.PostedFile.FileName;
FileUpload1.SaveAs(excelPath);
string FileType = FileUpload1.PostedFile.FileName.Substring(FileUpload1.PostedFile.FileName.LastIndexOf("."));
if (FileType == ".xlsx")
{
connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelPath + ";" + "Extended Properties=Excel 12.0 Xml;");
}
else if (FileType == ".xls")
{
connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelPath + ";" + "Extended Properties=Excel 8.0;");
}
connection.Open();
OleDbCommand ocmd = new OleDbCommand("SELECT * FROM [Sheet1$]", connection);
OleDbDataAdapter da = new OleDbDataAdapter(ocmd);
da.Fill(_objDs);
_dtTable = _objDs.Tables[0];
Label1.Text = "Total No.Records:";
lblcount.Text = _dtTable.Rows.Count.ToString();
ViewState["PinsTable"] = _dtTable;
GridView1.DataSource = _dtTable;
GridView1.DataBind();
}
Second:
protected void btnSubmit_Click(object sender, EventArgs e)
{
string connectionString = "";
DataTable _dt = (DataTable)ViewState["PinsTable"];
I have idea of grid editing but here it is different scenario.
Here i have to do some DML operations on the datatable and this has to be saved to the database not the Excel sheet data only the gridview data.