Sunday, 24 February 2013

excel to sql

c#code :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;


public class dbcon
{
    public static SqlConnection cn = null;
    public dbcon()
    {
       
    }
    string s = string.Empty;
    public void con(string sn,string dbn,string uid,string pwd)
    {
        try
        {
            s = "server=" + sn + ";database=" + dbn + ";uid=" + uid + ";pwd=" + pwd;
            cn = new SqlConnection(s);
            cn.Open();
        }
        catch
        {
            throw;
        }
    }
}
connetion page coding :
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Index.aspx.cs" Inherits="Test" %>

<!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">
<style type="text/css">
.a
{
    width:940px;
}
.b
{
    width:560px;
    list-style:none;
    padding-top:3px;
    float:left;
    text-align:right;
}
.c
{
    width:20px;
    list-style:none;
    padding-top:3px;
    float:left;
    font-weight:bolder;
    text-align:center;
}
.d
{
    width:360px;
    list-style:none;
    padding-top:3px;
    float:left;
    text-align:left;
}
</style>
    <script src="script/jquery-1.4.1.js" type="text/javascript"></script>
    <script language="javascript" type="text/javascript">
        $(document).ready
    (
    function () {
        $('#btn').click
    (
    function () {
        if ($('#sn').val() == "") {
            alert('Server name should not blank.');
            $('#sn').focus();
            return false;
        }
        else if ($('#dbn').val() == "") {
            alert('Database Name should not blank.');
            $('#dbn').focus();
            return false;
        }
        else if ($('#uid').val() == "") {
            alert('User Id should not blank.');
            $('#uid').focus();
            return false;
        }
        else if ($('#pwd').val() == "") {
            alert('Password should not blank.');
            $('#pwd').focus();
            return false;
        }
   
    }
    )
    }
    )


    </script>
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
 
    <div>
     <ul class="a">
    <li class="b">&nbsp;</li>
    <li class="c">&nbsp;</li>
     <li class="d">CONNETION INFORMATION</li>
    </ul>
    <ul class="a">
    <li class="b">Server Name</li>
    <li class="c">:</li>
     <li class="d"><input type="text" name="sn" id="sn" runat="server" style="width:200px" /></li>
    </ul>
     <ul class="a">
    <li class="b">Database Name</li>
    <li class="c">:</li>
     <li class="d"><input type="text" name="dbn" id="dbn" runat="server" style="width:200px" /></li>
    </ul>
     <ul class="a">
    <li class="b">User ID</li>
    <li class="c">:</li>
     <li class="d"><input type="text" name="uid" id="uid" runat="server" style="width:200px" /></li>
    </ul>
     <ul class="a">
    <li class="b">Password</li>
    <li class="c">:</li>
     <li class="d"><input type="password" name="pwd" id="pwd" runat="server" style="width:200px" /></li>
    </ul>
     <ul class="a">
    <li class="b">&nbsp;</li>
    <li class="c">&nbsp;</li>
     <li class="d"><asp:Button ID="btn" runat="server" Text="Submit" Width="80"
             onclick="btn_Click" />&nbsp; <asp:Button ID="btn1" runat="server" Text="Reset" Width="80" /></li>
    </ul>
    </div>
    </form>
</body>
</html>
C# coding for connetion page :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Test : System.Web.UI.Page
{
    dbcon p = new dbcon();
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btn_Click(object sender, EventArgs e)
    {
        p.con(sn.Value.ToString(), dbn.Value.ToString(), uid.Value.ToString(), pwd.Value.ToString());
        Response.Redirect("Default.aspx");
    }
}
page looks like :
 Default page coding:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.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">
    <style type="text/css">
        .A
        {
            width: 940px;
            list-style: none;
        }
        .B
        {
            width: 360px;
            list-style: none;
            text-align: right;
            float: left;
            padding-top: 3px;
        }
        .C
        {
            width: 20px;
            list-style: none;
            text-align: center;
            float: left;
            padding-top: 3px;
            font-weight: 900;
        }
        .D
        {
            width: 560px;
            list-style: none;
            text-align: left;
            float: left;
            padding-top: 3px;
        }
        .E
        {
            width: 556px;
            list-style: none;
            border-right: 1px solid #000000;
            border-bottom: 1px solid #000000;
            border-top: 1px solid #000000;
            border-left: 1px solid #000000;
        }
        .F
        {
            width: 146px;
            list-style: none;
            text-align: right;
            float: left;
            padding-top: 3px;
        }
        .G
        {
            width: 386px;
            list-style: none;
            text-align: left;
            float: left;
            padding-top: 3px;
        }
        .H
        {
            width: 16px;
            list-style: none;
            text-align: center;
            float: left;
            padding-top: 3px;
            font-weight: 900;
        }
    </style>
    <script src="script/jquery-1.4.1.js" type="text/javascript"></script>
    <script src="script/jquery-1.4.1.min.js" type="text/javascript"></script>
    <script src="script/jquery-1.4.1-vsdoc.js" type="text/javascript"></script>
    <script language="javascript" type="text/javascript">
        $(document).ready
    (
    function () {
        $('#<%=btn.ClientID %>').click
    (
    function () {
        if ($('#<%=fu.ClientID %>').val() == "") {
            alert('Upload excel file should not blank.');
            return false;
        }
    }
    )
        function () {
            $('#<%=btn1.ClientID %>').click(
        function () {

        }
        )
        }
    }
    )
    </script>
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <asp:ScriptManager ID="sm" runat="server">
    </asp:ScriptManager>
    <center>
        <div>
            <asp:UpdatePanel ID="up" runat="server">
                <ContentTemplate>
                    <table width="100%">
                        <tr>
                            <td align="right">
                                Select A Table
                            </td>
                            <td>
                                :
                            </td>
                            <td align="left">
                                <div style="overflow: auto; height: 200px; width: 400px">
                                    <asp:GridView ID="gvtb" runat="server" Width="100%">
                                        <Columns>
                                            <asp:TemplateField>
                                                <ItemTemplate>
                                                    <asp:CheckBox ID="cb" runat="server" AutoPostBack="True" OnCheckedChanged="cb_CheckedChanged" />
                                                </ItemTemplate>
                                            </asp:TemplateField>
                                        </Columns>
                                    </asp:GridView>
                                </div>
                            </td>
                        </tr>
                        <tr>
                            <td align="right">
                                Upload Excel File
                            </td>
                            <td>
                                :
                            </td>
                            <td align="left">
                                <asp:FileUpload ID="fu" runat="server" />&nbsp;<asp:Button ID="btn" runat="server" Text="Display" Width="80" OnClick="btn_Click" />
                            </td>
                        </tr>
                        <tr>
                            <td align="right">
                            <asp:Label ID="lbex" runat="server"></asp:Label>
                            </td>
                            <td>
                             <asp:Label ID="lbcol" runat="server"></asp:Label>
                            </td>
                            <td align="left">
                                <asp:Label ID="lb" runat="server" Font-Bold="True"></asp:Label>
                            </td>
                        </tr>
                        <tr>
                            <td colspan="3">
                                <div style="overflow: auto; height: 300px; width: 1350px">
                                    <asp:GridView ID="gv" runat="server" Width="100%">
                                    </asp:GridView>
                                </div>
                            </td>
                        </tr>
                        <tr>
                            <td>
                                &nbsp;
                            </td>
                            <td>
                                &nbsp;
                            </td>
                            <td align="left">
                                <div>
                                    <asp:DataList ID="dl" runat="server" Width="100%">
                                        <HeaderTemplate>
                                            <table cellpadding="0" cellspacing="0" border="1">
                                                <tr>
                                                    <th>
                                                        DATA BASE COLUMN NAME
                                                    </th>
                                                    <th>
                                                        EXCEL COLUMN NAME
                                                    </th>
                                                </tr>
                                        </HeaderTemplate>
                                        <ItemTemplate>
                                            <tr>
                                                <td>
                                                    <%#Eval("COLUMN_NAME") %>
                                                </td>
                                                <td>
                                                    <asp:DropDownList ID="ddl" runat="server" Width="200">
                                                    </asp:DropDownList>
                                                </td>
                                            </tr>
                                        </ItemTemplate>
                                        <FooterTemplate>
                                            </table>
                                        </FooterTemplate>
                                    </asp:DataList>
                                </div>
                            </td>
                        </tr>
                        <tr>
                            <td>
                                &nbsp;
                            </td>
                            <td>
                                &nbsp;
                            </td>
                            <td align="left">
                                <asp:Button ID="btn1" runat="server" Text="Save" Width="80"
                                    OnClick="btn1_Click" Visible="False" />
                            </td>
                        </tr>
                        <tr>
                            <td align="right">
                           <asp:Label ID="lbdb" runat="server"></asp:Label>
                            </td>
                            <td>
                              <asp:Label ID="lbcol1" runat="server"></asp:Label>
                            </td>
                            <td align="left">
                                <asp:Label ID="lb1" runat="server" Font-Bold="True"></asp:Label>
                            </td>
                        </tr>
                        <tr>
                            <td colspan="3">
                                <div style="overflow: auto; height: 300px;">
                                    <asp:GridView ID="gv1" runat="server" Width="100%">
                                    </asp:GridView>
                                </div>
                            </td>
                        </tr>
                    </table>
                </ContentTemplate>
                <Triggers>
                    <asp:PostBackTrigger ControlID="btn" />
                </Triggers>
            </asp:UpdatePanel>
        </div>
    </center>
    </form>
</body>
</html>
C# coding :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

public partial class _Default : System.Web.UI.Page
{
    #region
    OleDbCommand cm = new OleDbCommand();
    OleDbConnection cn = new OleDbConnection();
    OleDbDataAdapter da = new OleDbDataAdapter();
    SqlCommand cms = new SqlCommand();
    SqlDataAdapter das = new SqlDataAdapter();
    string sss = string.Empty;
    string sss1 = string.Empty;
    DataTable dt = new DataTable();
    DataTable dt1 = new DataTable();
    DataTable dt2 = new DataTable();
    DataTable dt3 = new DataTable();
    int i, j;
    #endregion
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            fill1();
        }
    }
    void fill1()
    {
        das = new SqlDataAdapter("SELECT TABLE_NAME,TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES", dbcon.cn);
        dt3.Clear();
        dt3.Reset();
        das.Fill(dt3);
        gvtb.DataSource = dt3;
        gvtb.DataBind();
    }
    protected void btn_Click(object sender, EventArgs e)
    {
        try
        {
            if (fu.HasFile)
            {
                string[] s = fu.PostedFile.ContentType.Split('/');
                if (s[0] == "application")
                {
                    string[] ss = fu.FileName.Split('.');
                    if (ss[1].ToLower().Trim().Substring(0, 2) == "xl".Trim())
                    {
                        sss = "Test" + "." + ss[1].ToString();
                        fu.SaveAs(Server.MapPath("~/file/" + sss));
                        sss1 = Server.MapPath("~/file/" + sss);
                        if (ss[1].ToString().Trim() == "xlsx".Trim())
                            cn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sss1 + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"");
                        else if (ss[1].ToString().Trim() == "xls".Trim())
                            cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sss1 + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"");
                        da = new OleDbDataAdapter("select * from [Sheet1$]", cn);
                        dt.Clear();
                        dt.Reset();
                        da.Fill(dt);
                        gv.DataSource = dt;
                        gv.DataBind();
                        lbex.Text = "Total Number Of Record(s) In Excel File";
                        lbcol.Text = ":";
                        lb.Text = dt.Rows.Count.ToString();
                        for (j = 0; j < dl.Items.Count; j++)
                        {
                            ((DropDownList)dl.Items[j].FindControl("ddl")).Items.Clear();
                            for (i = 0; i < gv.HeaderRow.Cells.Count; i++)
                            {
                                ListItem li = new ListItem();
                                li.Text = gv.HeaderRow.Cells[i].Text;
                                li.Value = (i + 1).ToString().Trim();
                                ((DropDownList)dl.Items[j].FindControl("ddl")).Items.Add(li);
                            }
                            ((DropDownList)dl.Items[j].FindControl("ddl")).Items.Insert(0, "Select");
                        }
                    }
                    else
                    {
                        Response.Write("<script>alert('You can only upload excel file.')</script>");
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Response.Write("<script>alert('" + ex.Message.ToString() + "')</script>");
        }
    }
    void fill()
    {
        gv1.DataSource = null;
        gv1.DataBind();
        das = new SqlDataAdapter("select * from " + "" + ViewState["sn"].ToString() + "." + ViewState["tn"].ToString(), dbcon.cn);
        dt1.Clear();
        dt1.Reset();
        das.Fill(dt1);
        gv1.DataSource = dt1;
        gv1.DataBind();
        lbdb.Text = "Total Number Of Record(s) In Data Base";
        lbcol1.Text = ":";
        lb1.Text = dt1.Rows.Count.ToString();
        dl.DataSource = null;
        dl.DataBind();
        das = new SqlDataAdapter("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + ViewState["tn"].ToString() + "'", dbcon.cn);
        dt2.Clear();
        dt2.Reset();
        das.Fill(dt2);
        dl.DataSource = dt2;
        dl.DataBind();
    }
    protected void btn1_Click(object sender, EventArgs e)
    {
        string l;
        try
        {
            for (int j = 0; j < dl.Items.Count; j++)
            {
                if (((DropDownList)dl.Items[j].FindControl("ddl")).SelectedValue == "Select")
                {
                    l = "alert('Please select excel coulumn name.')";
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "popup", l, true);
                    return;
                }
            }

            for (int i = 0; i < gv.Rows.Count; i++)
            {
                string st = string.Empty;
                for (int j = 0; j < dl.Items.Count; j++)
                {
                    if (gv.Rows[i].Cells[Convert.ToInt32(((DropDownList)dl.Items[j].FindControl("ddl")).SelectedValue) - 1].Text != "&nbsp;")
                        st = st + "'" + gv.Rows[i].Cells[Convert.ToInt32(((DropDownList)dl.Items[j].FindControl("ddl")).SelectedValue) - 1].Text + "'" + ",";
                    else
                        st = st + "''" + ",";
                }
                st = st.Substring(0, st.LastIndexOf(','));
                cms = new SqlCommand("insert into " + "" + ViewState["sn"].ToString() + "" + "." + "" + ViewState["tn"].ToString() + "" + " values(" + st + ")", dbcon.cn);
                cms.ExecuteNonQuery();
            }
            fill();
            l = "alert('Data saved.')";
            ScriptManager.RegisterStartupScript(this, this.GetType(), "alertScript", l, true);

        }
        catch (Exception ex)
        {
            Response.Write("<script>alert('" + ex.Message.ToString() + "')</script>");
        }
    }


    protected void cb_CheckedChanged(object sender, EventArgs e)
    {
        lbex.Text = "";
        lbcol.Text = "";
        lb.Text = "";
        lbdb.Text = "";
        lbcol1.Text = "";
        lb1.Text = "";
        foreach (GridViewRow gr in gvtb.Rows)
            ((CheckBox)gr.FindControl("cb")).Checked = false;
        CheckBox cb = (CheckBox)sender;
        cb.Checked = true;
        if (cb.Checked)
        {
            GridViewRow gvr = (GridViewRow)cb.NamingContainer;
            ViewState["sn"] = gvr.Cells[2].Text;
            ViewState["tn"] = gvr.Cells[1].Text;
            fill();
            btn1.Visible = true;
        }
        else
        {
            gv1.DataSource = null;
            gv1.DataBind();
            btn1.Visible = false;
        }
        gv.DataSource = null;
        gv.DataBind();
    }
}
Page looks like :

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)