In this article I’m going to explain how to import records from excel sheet to SQL Server with validations in ASP.NET using C#.

          I have already written an article for imports records from excel sheet to SQL Server.

Import records from excel sheet to SQL Server using ASP.NET and C#

Parse or read CSV file using TextFieldParser in ASP.NET 

Here I’ll show you how to do validations in Excel sheet while importing records. You could follow these steps 

1. Create a table for import the records to database

2. Upload excel sheet to the server folder

3. Fetch records from that excel sheet

4. Store records to DataTable

5. Validate data

6. Insert records to the table

7. Display the records to GridView

          So first we have to create a table for import the records to database. Table design could be like this.

Column Name

DataType

EmployeeID

varchar(10)

Name

varchar(50)

Designation

varchar(50)

DateOfBirth

varchar(50)

City

varchar(50)

 

Create Table script:

CREATE TABLE [dbo].[EmpImport](

          [EmployeeID] [varchar](10) NULL,

          [Name] [varchar](50) NULL,

          [Designation] [varchar](50) NULL,

          [DateOfBirth] [varchar](10) NULL,

          [City] [varchar](50) NULL

) ON [PRIMARY]

Sample excel sheet:

          This is sample excel file which we are going to import records from this file.

 

          Here we have to create a folder. Then we have to upload excel sheet to that folder which we have created.  

Sample code:

string FileName = FlUploadcsv.FileName;

string path = string.Concat(Server.MapPath("~/Document/" +

                                                                     FlUploadcsv.FileName));

 FlUploadcsv.PostedFile.SaveAs(path);

 

          Then we have to fetch and store records to DataTable from excel sheet which is uploaded to the server folder.

OleDbConnection OleDbcon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");               

               

                OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", OleDbcon);

                OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(cmd);

                ds = new DataSet();

                objAdapter1.Fill(ds);

                Dt = ds.Tables[0];

 

Validation:

          After store records to DataTable we have to validate data through for loop.

In this demo I’ve validated NULL values and Date format. You can do any validations like character length, allow only numbers, allow only alphabetic and etc.

Sample code:

Validate NULL values:

for (int i = 0; i < Dt.Rows.Count; i++)

            {               

                if (Dt.Rows[i][0].ToString() == "")

                {

                    int RowNo = i + 2;        

                    ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please enter Employee ID in row " + RowNo + "');", true);

                    return;

                }

            }

 

Validate Date format:

 

for (int i = 0; i < Dt.Rows.Count; i++)

            {

                string date= DateTime.Parse(Dt.Rows[i][3].ToString()).ToString("dd/MM/yyyy");

                if (!ValidateDate(date))

                {

                    int RowNo = i + 2;

                    ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Wrong Date format in row " + RowNo + "');", true);

                    return;

                }

            }

 private bool ValidateDate(string date)

    {

        try

        {

            string[] dateParts = date.Split('/');

            DateTime testDate = new DateTime(Convert.ToInt32(dateParts[2]), Convert.ToInt32(dateParts[1]), Convert.ToInt32(dateParts[0]));

            return true;

        }

        catch

        {

            return false;

        }

    }

 

Insert records to Database: 

Then we have to insert records to database though looping. 

private void InsertData()

    {

        for (int i = 0; i < Dt.Rows.Count; i++)

        {

            DataRow row = Dt.Rows[i];

            int columnCount = Dt.Columns.Count;

            string[] columns = new string[columnCount];

            for (int j = 0; j < columnCount; j++)

            {

                columns[j] = row[j].ToString();

            }

            conn.Open();

            string sql = "INSERT INTO EmpImport(EmployeeID,Name,Designation,DateOfBirth,City)";

            sql += "VALUES('" + columns[0] + "','" + columns[1] + "','" + columns[2] + "',Convert(varchar(10),'" + columns[3] + "',103),'" + columns[4] + "')";

            SqlCommand cmd = new SqlCommand(sql, conn);

 

            cmd.ExecuteNonQuery();

            conn.Close();

        }

    }

 

Designer source code:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="CSV" %>

<!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">

    <title>Excel sheet validation</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

  <asp:FileUpload ID="FlUploadcsv" runat="server" />

                    <asp:Button ID="btnIpload" runat="server" Text="Import" OnClick="btnIpload_Click" />

<br />

<asp:GridView ID="gvEmployee" runat="server" width="100%">                        <HeaderStyle BackColor="#89A0FE" />

</asp:GridView>

</div>

    </form>

</body>

</html>

 

Here we have to use following namespaces 

using System.IO;

using System.Data.OleDb;

 

Code behind:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.IO;

using System.Data.SqlClient;

using System.Data.OleDb; 

public partial class CSV : System.Web.UI.Page

     SqlConnection conn = new SqlConnection("Data Source=SPIDER;Initial Catalog=Demo;Integrated Security=True");

     DataSet ds;

     DataTable Dt;

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            BindGrid();

        }

    } 

    private void ImporttoDatatable()

    {

        try

        {

            if (FlUploadcsv.HasFile)

            {

                string FileName = FlUploadcsv.FileName;

                string path = string.Concat(Server.MapPath("~/Document/" + FlUploadcsv.FileName)); 

                FlUploadcsv.PostedFile.SaveAs(path); 

                OleDbConnection OleDbcon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");                              

                OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", OleDbcon);

                OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(cmd);

                ds = new DataSet();

                objAdapter1.Fill(ds);

                Dt = ds.Tables[0];

            }

        }

        catch (Exception ex)

        {

          

        }   

    }

     private void CheckData()

    {

        try

        {              

            for (int i = 0; i < Dt.Rows.Count; i++)

            {               

                if (Dt.Rows[i][0].ToString() == "")

                { 

                    int RowNo = i + 2;

                    ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please enter Employee ID in row " + RowNo + "');", true);

                    return;

                }

            }

 

            for (int i = 0; i < Dt.Rows.Count; i++)

            {          

                if (Dt.Rows[i][1].ToString() == "")

                {

                    int RowNo = i + 2;

                    ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please enter Name in row " + RowNo + "');", true);

                    return;

                }

            }

 

            for (int i = 0; i < Dt.Rows.Count; i++)

            {

                if (Dt.Rows[i][2].ToString() == "")

                {

                    int RowNo = i + 2;

                    ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please enter Designation in row " + RowNo + "');", true);

                    return;

                }

            }

 

            for (int i = 0; i < Dt.Rows.Count; i++)

            {

                string date= DateTime.Parse(Dt.Rows[i][3].ToString()).ToString("dd/MM/yyyy");          

                if (!ValidateDate(date))

                {

                    int RowNo = i + 2;

                    ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Wrong Date format in row " + RowNo + "');", true);

 

                    return;

                }

            }

           

            for (int i = 0; i < Dt.Rows.Count; i++)

            {

                if (Dt.Rows[i][4].ToString() == "")

                {

                    int RowNo = i + 2;

                    ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please Enter City in Row " + RowNo + "');", true);

                    return;

                }

            }

        }

        catch (Exception ex)

        { 

        }

    }

 

    private bool ValidateDate(string date)

    {

        try

        {

            string[] dateParts = date.Split('/');

            DateTime testDate = new DateTime(Convert.ToInt32(dateParts[2]), Convert.ToInt32(dateParts[1]), Convert.ToInt32(dateParts[0]));

            return true;

        }

        catch

        {

            return false;

        }

    }

 

    private void InsertData()

    {

        for (int i = 0; i < Dt.Rows.Count; i++)

        {

            DataRow row = Dt.Rows[i];

            int columnCount = Dt.Columns.Count;

            string[] columns = new string[columnCount];

            for (int j = 0; j < columnCount; j++)

            {

                columns[j] = row[j].ToString();

            }

            conn.Open();

            string sql = "INSERT INTO EmpImport(EmployeeID,Name,Designation,DateOfBirth,City)";

            sql += "VALUES('" + columns[0] + "','" + columns[1] + "','" + columns[2] + "',Convert(varchar(10),'" + columns[3] + "',103),'" + columns[4] + "')";

            SqlCommand cmd = new SqlCommand(sql, conn); 

            cmd.ExecuteNonQuery();

            conn.Close();

        }

    }

 

    protected void btnIpload_Click(object sender, EventArgs e)

    {

        ImporttoDatatable();

        CheckData();

        InsertData();

        BindGrid();

    }

 

    private void BindGrid()

    {

        DataSet ds = new DataSet();

        conn.Open();

        string cmdstr = "Select * from EmpImport";

        SqlDataAdapter adp = new SqlDataAdapter(cmdstr,conn);

        adp.Fill(ds);

        gvEmployee.DataSource = ds;

        gvEmployee.DataBind();

        ds.Dispose();

        conn.Close();

    }   

}

 

Note:

          While running this demo maybe you will get error something like

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

          So you could download and install 2007 Office System Driver: Data Connectivity Components. Download link is given below.

 

http://www.microsoft.com/en-us/download/confirmation.aspx?id=23734

cytotec abortion

an abortion pill buy abortion pills online
medical abortion pill online cytotec abortion abortion pill


View demo Download
http://www.dotnetfox.com/Document/1052/0photo1.jpg

If you enjoyed this article, get email updates (it's free).


Related Articles
  • Parse or read CSV file using TextFieldParser in ASP.NET CSV file contains multiple values which are separated by commas (,). In order to parse CSV file, we need to write some code. TextFieldParser class in the Microsoft.VisualBasic.FileIO namespace is used to parse CSV files. TextFieldParser reads in CSV files. With it, we specify a delimiter string, and then can read in the fields of every line in a loop. While parsing CSV file, it doesn’t fetch heade...
  • Import records from excel sheet to SQL Server using ASP.NET and C#In this article I’m going to explain how to import records from excel sheet to SQL Server using ASP.NET and C#. I got a requirement that I should import records from excel sheet to SQL Server. To achieve that I have created demo and you could follow the steps given below.
  • Import records from excel sheet to SQL Server with validations in ASP.NET using C#In this article I’m going to explain how to import records from excel sheet to SQL Server with validations in ASP.NET using C#. I have already written an article for imports records from excel sheet to SQL Server. Import records from excel sheet to SQL Server using ASP.NET and C#. Here I’ll show you how to do validations in Excel sheet while importing records.
  • Import records from HTML table and store to database in ASP.NET using C#In this article I’m going to explain how to import records from HTML and store to database. I got one requirement that I’ve to import records from simple HTML table and store those records to the database. It’s not complicated one but we should know about that whether that HTML page is well formatted. Here we need to make sure that how we can get content from simple HTML table. So only possibilit...

Comments
comments powered by Disqus

Thanks for downloading..!!

×
Subscribe via Email
    
message to display in fancybox