image

Import csv file to ms sql server using ASP.NET C#

Import csv file to ms sql server using ASP.NET C#

Today I will be showing you how you can import CSV file data into ms sql databases using ASP.NET C#

image

First of all, define your connectionstring in web.config file
In Web.config file define your connectionstring :

<connectionStrings>
		<add name="myconnectionString" connectionString="Data Source=HITESH\SQLEXPRESS;Initial Catalog=MyDatabase;User ID=hitesh;Password=789"/>
	</connectionStrings>

Open your Default.aspx design page and place gridview and fileupload elements in this page
inside the form tag :

<form id="form1" runat="server">
 <asp:GridView runat="server" ID="datagridview" CssClass="mydatagrid" AllowPaging="true"
        PageSize="10" OnPageIndexChanging="datagridview_PageIndexChanging">
    </asp:GridView>
    <br />
    <asp:Button runat="server" ID="btncsvexport" Text="IMPORT CSV" BackColor="#1E67C1"
        Font-Bold="True" Font-Size="15pt" ForeColor="White" OnClick="btncsvimport_Click" />
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    <asp:FileUpload runat="server" ID="fileupload1" />
</form>

Now open the .cs page (coding page)
Now declare following :

public partial class _Default : System.Web.UI.Page 
{
    String myconnectionString = ConfigurationManager.ConnectionStrings["myconnectionstring"].ConnectionString;
    SqlConnection con;
    SqlDataAdapter adapter;
    DataSet ds;
    SqlCommand cmd;

The myconnectionString string is our connectionString.

Now to fill the gridview at runtime you will have to use following code in page load method:

    protected void Page_Load(object sender, EventArgs e)
    {
        fillgrid();
}

public void fillgrid()
{
        con = new SqlConnection(myconnectionString);
        cmd = new SqlCommand("select * from mytable", con);
        con.Open();
        adapter = new SqlDataAdapter(cmd);
        ds = new DataSet();
        adapter.Fill(ds, "mytable");
        datagridview.DataSource = ds.Tables[0];
        datagridview.DataBind();
        con.Close();
}

Now inside the button click event add following code


protected void btncsvimport_Click(object sender, EventArgs e)
    {
        DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[4] {new DataColumn("Id", typeof(string)), new DataColumn("Name", typeof(string)),
            new DataColumn("Founder", typeof(string)),
            new DataColumn("InYear",typeof(string)) });
 string uploadpath = Server.MapPath("~/Uploads/") + Path.GetFileName(fileupload1.PostedFile.FileName);
        fileupload1.SaveAs(uploadpath);
//Note :  You will have to make a folder with name Uploads in your project folder to copy to CSV file.

        string uploaddata= File.ReadAllText(uploadpath);
        foreach (string row in uploaddata.Split('\n'))
        {
            if (!string.IsNullOrEmpty(row))
            {
                dt.Rows.Add();
                int i = 0;
                foreach (string cell in row.Split(','))
                {
                    dt.Rows[dt.Rows.Count - 1][i] = cell;
                    i++;
                }
            }
        }


        using (SqlConnection con = new SqlConnection(myconnectionString))
        {
            using (SqlBulkCopy copy= new SqlBulkCopy(con))
            {
                copy.DestinationTableName = "dbo.mytable";
                con.Open();
                copy.WriteToServer(dt);
                con.Close();

            }
        }
fillgrid();
    }

Now run your project. 🙂

If you like my work, please click the like button and like my page on facebook. You can also comment below…I will try my best to help you out.

image

image



One thought to “Import csv file to ms sql server using ASP.NET C#”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.