hi,
i am uploading excel sheet into database,when i uploading it,i want to create a table with excel sheet column headers in database,how it is possible,am new to .net can any one please help me to give an axample code please....
actually present i uploaded file into database,using selecting the table in which i want to import the excel sheet,the code is like this
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Drawing;
using System.Data.OleDb;
namespace pay_description
{
public partial class upload2 : System.Web.UI.Page
{
private string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
PopulateDatabaseTables();
}
}
private void PopulateDatabaseTables()
{
string tableName = string.Empty;
string sql = "SELECT *, name AS table_name " +
" FROM sys.tables WHERE Type = 'U' ORDER BY table_name";
using (SqlConnection conn = new SqlConnection(connStr))
{
using (DataTable table = new DataTable())
{
conn.Open();
using (SqlDataAdapter dAd = new SqlDataAdapter(sql, conn))
{
dAd.Fill(table);
}
ListBox1.DataSource = table;
ListBox1.DataBind();
}
}
}
protected void ImportNow_Click(object sender, EventArgs e)
{
if (ListBox1.SelectedValue == "")
{
lblMessage.ForeColor = Color.Red;
lblMessage.Text = "Please select table in which you want to import data from excel sheet";
}
else if ((fileuploadExcel.FileName != ""))
{
string extension = Path.GetExtension(fileuploadExcel.FileName); // fileuploadExcel.PostedFile.FileName
string excelConnectionString;
SqlConnection conn = new SqlConnection(connStr);
string tableName = ListBox1.SelectedValue;
string path = Server.MapPath("~/fileuploadExcel/" + fileuploadExcel.FileName);
fileuploadExcel.SaveAs(path);
//Create connection string to Excel work book
if (extension == ".xls")
{
excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Excel 8.0;HDR=Yes;IMEX=1";
}
else
{
excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;sData Source=" + path + ";Excel 12.0;HDR=Yes;IMEX=1";
}
//Create Connection to Excel work book
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
//Create OleDbCommand to fetch data from Excel
conn.Open();
SqlCommand comm = new SqlCommand("truncate table " + tableName, conn);
SqlCommand identityChange = conn.CreateCommand();
identityChange.CommandText = "SET IDENTITY_INSERT " + tableName + " ON";
OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$]", excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(connStr, SqlBulkCopyOptions.KeepIdentity & SqlBulkCopyOptions.KeepNulls);
//Give your Destination table name
sqlBulk.DestinationTableName = tableName;
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
conn.Close();
lblMessage.ForeColor = Color.Green;
lblMessage.Text = "Import into table <b>" + tableName + "</b> successful!<br />";
}
else
{
lblMessage.ForeColor = Color.Red;
lblMessage.Text = "Please first upload (Select) excel file.";
}
}
protected void viewdata_Click(object sender, EventArgs e)
{
BindData();
}
private void BindData()
{
try
{
if (ListBox1.SelectedValue == "")
{
lblMessage.ForeColor = Color.Red;
lblMessage.Text = "Please select table for which you want to view data in Gridview";
}
else
{
string tableName = ListBox1.SelectedValue;
SqlConnection conn = new SqlConnection(connStr);
SqlDataAdapter sda = new SqlDataAdapter("select * from " + tableName, conn);
DataSet ds = new DataSet();
sda.Fill(ds);
gvdetails.DataSource = ds;
gvdetails.DataBind();
}
}
catch (DataException de)
{
lblMessage.Text = de.Message;
lblMessage.ForeColor = System.Drawing.Color.Red;
}
}
protected void gvdetails_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gvdetails.PageIndex = e.NewPageIndex;
BindData();
}
}
}
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="upload2.aspx.cs" Inherits="pay_description.upload2" %>
<!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></title>
</head>
<body>
<form id="form1" runat="server">
<div align="center" class="bg_white">
<table cellpadding="2" cellspacing="1" border="1" style="border-collapse: collapse;"
width="100%">
<tr align="center">
<td align="center">
List of Tables
</td>
</tr>
<tr align="center">
<td align="center">
<asp:ListBox ID="ListBox1" Rows="10" runat="Server" DataTextField="table_name"
DataValueField="table_name" Width="200px" Height="18px"
> </asp:ListBox>
</td>
</tr>
<tr align="center">
<td align="center">
<asp:FileUpload ID="fileuploadExcel" runat="server" /> <span
onclick="return confirm('Are you sure to Import selected Excel?')">
<asp:Button ID="Button3"
runat="Server" Text="Import" OnClick="ImportNow_Click"/> </span>
</td>
</tr>
<tr>
<td align="center">
<span style="color: Red;"> *</span> Selecet table in list box for View datain gridview!
</td>
</tr>
<tr>
<td align="center">
<asp:GridView ID="gvdetails" runat="server" BackColor="White" EmptyDataText="THERE
IS NO DATA FOR VIEW...!" BorderColor="#E7E7FF" BorderStyle="None"
BorderWidth="1px" CellPadding="3" GridLines="Horizontal" EmptyDataRowStyle-
ForeColor="red" OnPageIndexChanging="gvdetails_PageIndexChanging"
EmptyDataRowStyle-Font-Size="Medium">
<AlternatingRowStyle BackColor="#F7F7F7" />
<FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" />
<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />
<PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" HorizontalAlign="Right" />
<RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="#F7F7F7" />
<SortedAscendingCellStyle BackColor="#F4F4FD" />
<SortedAscendingHeaderStyle BackColor="#5A4C9D" />
<SortedDescendingCellStyle BackColor="#D8D8F0" />
<SortedDescendingHeaderStyle BackColor="#3E3277" />
</asp:GridView>
</td>
</tr>
<tr>
<td align="center">
<asp:Button ID="viewdata" Text="View Data" runat="server" OnClick="viewdata_Click">
</asp:Button>
</td>
</tr>
<tr>
<td align="left">
<asp:Label ID="lblMessage" runat="Server" EnableViewState="False" ForeColor="Blue">
</asp:Label>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
but now i dont want to select the table,directly i want to create the table based on excel sheet columns,and want to insert the data to that table from excel sheet,and for every time when i uploading excel sheet,the columns will be changed,for that how to create the table and insert the data to table
