Upload Data from CSV file to Sql Server

1.FileUploader1 is a fileUploadControl

2.name,fathername,address are the columns which same as csv file name and sql server database 

3.G:\\FileName is your local computer path where CSV file save.

           

FileUploader1.SaveAs("G:\\FileName\\" + FileUploader1.FileName);

                // You can get connected to driver either by using DSN or connection string

                // Create a connection string as below, if you want to use DSN less connection.

The DBQ attribute sets the path of directory which contains CSV files

                string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + "G:\\FileName\\" + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";

                string Query;

                DataSet ds = new DataSet();

                System.Data.Odbc.OdbcConnection conn;

                //Create connection to CSV file

                conn = new System.Data.Odbc.OdbcConnection(strConnString.Trim());

                // For creating a connection using DSN, use following line

                //Open the connection

                conn.Open();

                //Fetch records from CSV

                Query = "select * from [" + FileUploader1.FileName.ToString() + "]";

                System.Data.Odbc.OdbcDataAdapter oledaObj = new System.Data.Odbc.OdbcDataAdapter(Query, conn);

                //Fill dataset with the records from CSV file

                oledaObj.Fill(ds);

                int j = 0;

                if (ds.Tables[0].Rows.Count > 0)

                {

                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)

                    {

                        try

                        {

                            DataSet ds2 = new DataSet();

                            SqlDataAdapter adap = new SqlDataAdapter("INSERT INTO TableDetails (name,fathername,address)  VALUES('" + ds.Tables[0].Rows[i]["name"].ToString() + "','" + ds.Tables[0].Rows[i]["fathername"].ToString() + "','" + ds.Tables[0].Rows[i]["address"].ToString() + "')", ConfigurationManager.ConnectionStrings["Conn"].ConnectionString.ToString());

                            adap.Fill(ds);

                            j++;

                        }

                        catch (Exception ex)

                        {

                        }

                    }

                    LblMsg.Text = "File Uploaded Successfully, No. of record save - " + System.Convert.ToString(j);

                    LblMsg.Visible = true;

                }

                conn.Close();