User-1647172364 posted
hello i want to show excel data in the gridview in this work i got success
but my requirement is gridview data is save in the database
suddenly error occur connection string property has not been initialized
so how could deal this error
here is my code
Aspx
<%@ Page Title="" Language="C#" MasterPageFile="~/Site1.Master" AutoEventWireup="true" CodeBehind="Dept Desg.aspx.cs" Inherits="WebApplication14.Dept_Desg" %>
<asp:Content ID="Content1" ContentPlaceHolderID="title" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="head" runat="server">
</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderID="body" runat="server">
<div style="margin-top:30px; margin-left:20px">
<table>
<tr><td>
<asp:FileUpload ID="FileUpload1" runat="server" /></td>
<td>
<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" BackColor="#3366CC" BorderColor="#3366CC" ForeColor="White" />
</td>
<br />
<br />
</tr>
<asp:GridView ID="Gv8" runat="server" OnPageIndexChanging = "PageIndexChanging" AllowPaging = "true" CellPadding="4" ForeColor="#333333" GridLines="Both">
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<EditRowStyle BackColor="#999999" />
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#E9E7E2" />
<SortedAscendingHeaderStyle BackColor="#506C8C" />
<SortedDescendingCellStyle BackColor="#FFFDF8" />
<SortedDescendingHeaderStyle BackColor="#6F8DAE" />
</asp:GridView>
<tr> <td>
<asp:Button ID="Button1" runat="server" Text="Submit" OnClick="btnUpload1_Click" BackColor="#3366CC" BorderColor="#3366CC" ForeColor="White" />
</td></tr>
</table>
</div>
</asp:Content>
C#
using System;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;
namespace WebApplication14
{
public partial class Dept_Desg : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
}
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
string FilePath = Server.MapPath(FolderPath + FileName);
FileUpload1.SaveAs(FilePath);
Import_To_Grid(FilePath, Extension);
}
}
private void Import_To_Grid(string FilePath, string Extension)
{
string conStr = "";
switch (Extension)
{
case ".xls": //Excel 97-03
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
}
conStr = String.Format(conStr, FilePath, 1);
OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
DataTable dt = new DataTable();
cmdExcel.Connection = connExcel;
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
//Read Data from First Sheet
connExcel.Open();
cmdExcel.CommandText = "SELECT * FROM [" + SheetName + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dt);
connExcel.Close();
Gv8.DataSource = dt;
Gv8.DataBind();
}
protected void PageIndexChanging(object sender, GridViewPageEventArgs e)
{
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
string FileName = Gv8.Caption;
string Extension = Path.GetExtension(FileName);
string FilePath = Server.MapPath(FolderPath + FileName);
Import_To_Grid(FilePath, Extension);
Gv8.PageIndex = e.NewPageIndex;
Gv8.DataBind();
}
protected void btnUpload1_Click(object sender, EventArgs e)
{
string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(excelPath);
string conString = string.Empty;
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
switch (extension)
{
case ".xls": //Excel 97-03
conString = ConfigurationManager.ConnectionStrings["Excel03"].ConnectionString;
break;
}
conString = string.Format(conString, excelPath);
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
DataTable dtExcelData = new DataTable();
//[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
dtExcelData.Columns.AddRange(new DataColumn[3] { new DataColumn("tbl_id", typeof(decimal)),
new DataColumn("temp1", typeof(string)),
new DataColumn("temp2", typeof(string)) });
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "tbl_dept_desg";
//[OPTIONAL]: Map the Excel columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("Country", "temp1");
sqlBulkCopy.ColumnMappings.Add("University Type", "temp2");
sqlBulkCopy.ColumnMappings.Add("University Name", "temp3");
sqlBulkCopy.ColumnMappings.Add("Date of Contract", "temp4");
sqlBulkCopy.ColumnMappings.Add("Expiry Date", "temp5");
sqlBulkCopy.ColumnMappings.Add("Contact Person", "temp6");
sqlBulkCopy.ColumnMappings.Add("Contact No.", "temp7");
sqlBulkCopy.ColumnMappings.Add("Email-Id", "temp8");
con.Open();
sqlBulkCopy.WriteToServer(dtExcelData);
con.Close();
}
}
}
}
}
}
