Tuesday 21 August 2018

Import data from csv to sql server using asp.net c#

///////////////////// HTML

<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="Button2" Text="Upload" OnClick="Upload" runat="server" />




//////////////////////CS


    protected void Upload(object sender, EventArgs e)
    {
        Import_Csv
    }


    public void Import_Csv()
    {
        //Creating object of datatable 
        DataTable tblcsv = new DataTable();
        //creating columns 
        tblcsv.Columns.Add("Name");
        tblcsv.Columns.Add("City");
        tblcsv.Columns.Add("Address");
        tblcsv.Columns.Add("Designation");
        //getting full file path of Uploaded file 
        string CSVFilePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);
        //Reading All text 
        string ReadCSV = File.ReadAllText(CSVFilePath);
        //spliting row after new line 
        foreach (string csvRow in ReadCSV.Split('\n'))
        {
            if (!string.IsNullOrEmpty(csvRow))
            {
                //Adding each row into datatable 
                tblcsv.Rows.Add();
                int count = 0;
                foreach (string FileRec in csvRow.Split(','))
                {
                    tblcsv.Rows[tblcsv.Rows.Count - 1][count] = FileRec;
                    count++;
                }
            }


        }
        //Calling insert Functions 
        InsertCSVRecords(tblcsv);
    }
    //Function to Insert Records 
    private void InsertCSVRecords(DataTable csvdt)
    {
        string conn="";
        SqlConnection con = new SqlConnection(conn);
        //creating object of SqlBulkCopy   
        SqlBulkCopy objbulk = new SqlBulkCopy(con);
        //assigning Destination table name   
        objbulk.DestinationTableName = "Employee";
        //Mapping Table column   
        objbulk.ColumnMappings.Add("Name", "Name");
        objbulk.ColumnMappings.Add("City", "City");
        objbulk.ColumnMappings.Add("Address", "Address");
        objbulk.ColumnMappings.Add("Designation", "Designation");
        //inserting Datatable Records to DataBase   
        con.Open();
        objbulk.WriteToServer(csvdt);
        con.Close();


    }



No comments:

Post a Comment

Excel Sort values in ascending order using function TEXTJOIN

 Excel ::  Text ::  1,3,5,2,9,5,11 Result :: 1,2,3,5,5,9,11 Formula ::     TEXTJOIN ( ",",1,SORT(MID(SUBSTITUTE( A1 ,","...