ASP.NET – Import CSV file to MS SQL Database

ASH

9 years ago

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#

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.

Share this: