hi I used below code to import execl file into database:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="CS.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Import Excel Data into Database</title>
</head>
<body>
<form id="form1" runat="server">
<asp:Panel ID="Panel1" runat="server">
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />
<br />
<asp:Label ID="lblMessage" runat="server" Text=""></asp:Label>
</asp:Panel>
<asp:Panel ID="Panel2" runat="server" Visible = "false" >
<asp:Label ID="Label5" runat="server" Text="File Name"/>
<asp:Label ID="lblFileName" runat="server" Text=""/>
<br />
<asp:Label ID="Label2" runat="server" Text="Select Sheet" />
<asp:DropDownList ID="ddlSheets" runat="server" AppendDataBoundItems = "true">
</asp:DropDownList>
<br />
<asp:Label ID="Label3" runat="server" Text="Enter Source Table Name"/>
<asp:TextBox ID="txtTable" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label1" runat="server" Text="Has Header Row?"></asp:Label>
<br />
<asp:RadioButtonList ID="rbHDR" runat="server">
<asp:ListItem Text = "Yes" Value = "Yes" Selected = "True" ></asp:ListItem>
<asp:ListItem Text = "No" Value = "No"></asp:ListItem>
</asp:RadioButtonList>
<br />
<asp:Button ID="btnSave" runat="server" Text="Save" OnClick="btnSave_Click" />
<asp:Button ID="btnCancel" runat="server" Text="Cancel" OnClick="btnCancel_Click" />
</asp:Panel>
</form>
</body>
</html>
and:
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);
GetExcelSheets(FilePath, Extension, "Yes");
}
}
private void GetExcelSheets(string FilePath, string Extension, string isHDR)
{
string conStr="";
switch (Extension)
{
case ".xls": //Excel 97-03
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
//Get the Sheets in Excel WorkBoo
conStr = String.Format(conStr, FilePath, isHDR);
OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
cmdExcel.Connection = connExcel;
connExcel.Open();
//Bind the Sheets to DropDownList
ddlSheets.Items.Clear();
ddlSheets.Items.Add(new ListItem("--Select Sheet--", ""));
ddlSheets.DataSource = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
ddlSheets.DataTextField = "TABLE_NAME";
ddlSheets.DataValueField = "TABLE_NAME";
ddlSheets.DataBind();
connExcel.Close();
txtTable.Text = "";
lblFileName.Text = Path.GetFileName(FilePath);
Panel2.Visible = true;
Panel1.Visible = false;
}
protected void btnSave_Click(object sender, EventArgs e)
{
string FileName = lblFileName.Text;
string Extension = Path.GetExtension(FileName);
string FolderPath = Server.MapPath (ConfigurationManager.AppSettings["FolderPath"]);
string CommandText = "";
switch (Extension)
{
case ".xls": //Excel 97-03
CommandText = "spx_ImportFromExcel03";
break;
case ".xlsx": //Excel 07
CommandText = "spx_ImportFromExcel07";
break;
}
//Read Excel Sheet using Stored Procedure
//And import the data into Database Table
//String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
string strConnString = ConfigurationManager.ConnectionStrings["behtopConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = CommandText;
cmd.Parameters.Add("@SheetName", SqlDbType.VarChar).Value = ddlSheets.SelectedItem.Text;
cmd.Parameters.Add("@FilePath", SqlDbType.VarChar).Value = FolderPath + FileName;
cmd.Parameters.Add("@HDR", SqlDbType.VarChar).Value = rbHDR.SelectedItem.Text;
cmd.Parameters.Add("@TableName", SqlDbType.VarChar).Value = txtTable.Text;
cmd.Connection = con;
try
{
con.Open();
object count = cmd.ExecuteNonQuery();
lblMessage.ForeColor = System.Drawing.Color.Green;
lblMessage.Text = count.ToString() + " records inserted.";
}
catch (Exception ex)
{
lblMessage.ForeColor = System.Drawing.Color.Red;
lblMessage.Text = ex.Message;
}
finally
{
con.Close();
con.Dispose();
Panel1.Visible = true;
Panel2.Visible = false;
}
}
protected void btnCancel_Click(object sender, EventArgs e)
{
Panel1.Visible = true;
Panel2.Visible = false;
}
and SP:
create PROCEDURE [dbo].[spx_ImportFromExcel03]
@SheetName varchar(20),
@FilePath varchar(100),
@HDR varchar(3),
@TableName varchar(50)
AS
BEGIN
DECLARE @SQL nvarchar(1000)
IF OBJECT_ID (@TableName,'U') IS NOT NULL
SET @SQL = 'INSERT INTO ' + @TableName + ' SELECT * FROM OPENDATASOURCE'
ELSE
SET @SQL = 'SELECT * INTO ' + @TableName + ' FROM OPENDATASOURCE'
SET @SQL = @SQL + '(''Microsoft.Jet.OLEDB.4.0'',''Data Source='
SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 8.0;HDR=' + @HDR + ''''''')...['
SET @SQL = @SQL + @SheetName + ']'
EXEC sp_executesql @SQL
END
but when I select file to upload this error happen:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
Best regards
Neda
