Styjun

working with sql and .net, i had a situation to import bulk data with excel file and huge amount of data.

The table had also 20+ columns some of then are not mandatory.

the table structure is like below :

CREATE TABLE [dbo].[Employee] (
[EmployeeID] INT IDENTITY (1, 1) NOT NULL,
[EmpID] NVARCHAR (10) NULL,
[isActive] BIT DEFAULT ((0)) NOT NULL,
[Fname] NVARCHAR (50) NOT NULL,
[Mname] NVARCHAR (10) NULL,
[Lname] NVARCHAR (50) NULL,
[DOB] DATE NOT NULL,
[DOJ] DATE NOT NULL,
[gender] NVARCHAR (10) NOT NULL,
[M_Status] NVARCHAR (10) NULL,
[Father_Name] NVARCHAR (50) NULL,
[Mother_Name] NVARCHAR (50) NULL,
[Address1] NVARCHAR (20) NULL,
[Address2] NVARCHAR (20) NULL,
[Address3] NVARCHAR (20) NULL,
[Pin_Code] NUMERIC (6) NULL,
[StateID] INT NULL,
[DistrictID] INT NULL,
[Mobile1] NUMERIC (10) NOT NULL,
[mobile2] NUMERIC (10) NULL,
[email_address] NVARCHAR (50) NULL,
[IFSC_Code] NCHAR (11) NULL,
[bank_account] NCHAR (18) NULL,
[parmanent_account] NVARCHAR (10) NULL,
[aadhar_number] NVARCHAR (12) NOT NULL,
[ESIC] NVARCHAR (10) NULL,
[UAN] NVARCHAR (10) NULL,
[SalaryID] INT NOT NULL,
[DivisionID] INT NOT NULL,
[BranchID] INT NOT NULL,
[last_used] INT NOT NULL,
[access_time] DATETIME NOT NULL,
PRIMARY KEY CLUSTERED ([EmployeeID] ASC));

after that i like to import only not null fields and display the Excel data into gridview and then upload the data to table.

my gridview code is look like :

<div class="container blue">
 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CssClass="myGridClass" >
 <Columns>
 <asp:BoundField DataField="Emp_ID" HeaderText="Employee ID" />
 <asp:BoundField DataField="First_name" HeaderText="First Name" />
 <asp:BoundField DataField="Last_Name" HeaderText="Last Name" />
 <asp:BoundField DataField="Gender" HeaderText="Male/Female" />
 <asp:BoundField DataField="Date_Birth" HeaderText="Date of Birth" DataFormatString="0:dd/MM/yyyy" />
 <asp:BoundField DataField="Father_Name" HeaderText="Father Name" />
 <asp:BoundField DataField="Mobile" HeaderText="Mobile No." />
 <asp:BoundField DataField="AADHAR_Number" HeaderText="AADHAR No." />
 <asp:BoundField DataField="Date_Joining" HeaderText="Date of Joining" DataFormatString="0:dd/MM/yyyy" />
 <asp:BoundField DataField="UAN" HeaderText="UAN" />
 <asp:BoundField DataField="ESIC" HeaderText="ESIC" />
 <asp:TemplateField ConvertEmptyStringToNull="False" HeaderText="Salary">
 <EditItemTemplate>
 <asp:DynamicControl ID="DynamicControl1" runat="server" DataField="SalaryID" Mode="Edit" />
 </EditItemTemplate>
 <ItemTemplate>
 <asp:DropDownList ID="Salary_Select" runat="server" CssClass="dropbox" DataSourceID="Salary_List" DataTextField="Post_Name" DataValueField="SalaryID">
 </asp:DropDownList>
 <asp:SqlDataSource ID="Salary_List" runat="server" ConnectionString="<%$ ConnectionStrings:iPayConnectionString %>" SelectCommand="SELECT [Post_Name], [SalaryID] FROM [Salary]"></asp:SqlDataSource>
 </ItemTemplate>
 </asp:TemplateField>
 <asp:TemplateField ConvertEmptyStringToNull="False" HeaderText="Division">
 <EditItemTemplate>
 <asp:DynamicControl ID="DynamicControl1" runat="server" DataField="DivisionID" Mode="Edit" />
 </EditItemTemplate>
 <ItemTemplate>
 <asp:DropDownList ID="Division_Select" runat="server" CssClass="dropbox" DataSourceID="Division_List" DataTextField="Division_Name" DataValueField="DivisionID" AutoPostBack="True">
 </asp:DropDownList>
 <asp:SqlDataSource ID="Division_List" runat="server" ConnectionString="<%$ ConnectionStrings:iPayConnectionString %>" SelectCommand="SELECT [Division_Name], [Company_ID], [DivisionID] FROM [Division]"></asp:SqlDataSource>
 </ItemTemplate>
 </asp:TemplateField>
 <asp:TemplateField ConvertEmptyStringToNull="False" HeaderText="Branch">
 <EditItemTemplate>
 <asp:DynamicControl ID="DynamicControl1" runat="server" DataField="BranchID" Mode="Edit" />
 </EditItemTemplate>
 <ItemTemplate>
 <asp:DropDownList ID="Branch_Select" runat="server" CssClass="dropbox" DataSourceID="branch_List" DataTextField="Branch_Name" DataValueField="BranchID">
 </asp:DropDownList>
 <asp:SqlDataSource ID="branch_List" runat="server" ConnectionString="<%$ ConnectionStrings:iPayConnectionString %>" SelectCommand="SELECT [BranchID], [CompanyID], [DivisionID], [Branch_Name] FROM [Branch] WHERE ([DivisionID] = @DivisionID)">
 <SelectParameters>
 <asp:ControlParameter ControlID="Division_Select" Name="DivisionID" PropertyName="SelectedValue" Type="Int32" />
 </SelectParameters>
 </asp:SqlDataSource>
 </ItemTemplate>
 </asp:TemplateField>
 </Columns> 
 </asp:GridView> 
 <asp:SqlDataSource ID="salary_source" runat="server" ConnectionString="<%$ ConnectionStrings:iPayConnectionString %>" SelectCommand="SELECT [EmployeeID], [Fname], [Mname], [Lname], [DOB], [gender], [Father_Name], [Mobile1], [email_address], [IFSC_Code], [bank_account], [parmanent_account], [aadhar_number], [ESIC], [UAN], [EmpID], [Mother_Name], [DistrictID], [M_Status] FROM [Employee]"></asp:SqlDataSource>
</div>

now the import code come into effect.

Public Sub Import_Employees(ByVal FilePath As String, ByVal Extension As String, ByVal isHDR As String, gdview As GridView)
 Dim conStr As String = ""
 Select Case Extension
 Case ".xls"
 'Excel 97-03
 conStr = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString
 Exit Select
 Case ".xlsx"
 'Excel 07
 conStr = ConfigurationManager.ConnectionStrings("Excel07ConString").ConnectionString
 Exit Select
 End Select
 conStr = String.Format(conStr, FilePath, isHDR)
 Dim connExcel As New OleDbConnection(conStr)
 Dim cmdExcel As New OleDbCommand()
 Dim oda As New OleDbDataAdapter()
 Dim dt As New DataTable()
 cmdExcel.Connection = connExcel
 'Get the name of First Sheet
 connExcel.Open()
 Dim dtExcelSchema As DataTable
 dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
 Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
 connExcel.Close()
 'Read Data from First Sheet
 connExcel.Open()
 cmdExcel.CommandText = "SELECT * From [" & SheetName & "]"
 oda.SelectCommand = cmdExcel
 oda.Fill(dt)
 connExcel.Close()
 'Bind Data to GridView
 'GridView1.Caption = Path.GetFileName(FilePath)
 gdview.DataSource = dt
 gdview.DataBind()
End Sub

function is called on page like below :

If FileUpload1.HasFile Then
 Dim FileName As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
 Dim Extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
 Dim FolderPath As String = ConfigurationManager.AppSettings("FolderPath")
 Dim FilePath As String = Server.MapPath(FolderPath + FileName)
 FileUpload1.SaveAs(FilePath)
 cls.Import_Employees(FilePath, Extension, 0, GridView1)
 Upload.Visible = True
 'Import_To_Grid(FilePath, Extension, 0)
 End If

I tried some of link below not work for me.

https://www.aspsnippets.com/Articles/Import-Excel-data-to-SQL-Server-Database-using-Stored-Procedure-in-ASPNet.aspx

https://www.aspsnippets.com/Articles/Import-Excel-data-to-SQL-Server-Database-in-ASPNet-using-C-and-VBNet.aspx

https://www.aspsnippets.com/Articles/Insert-Save-Multiple-rows-from-DataGridView-to-Database-in-Windows-Forms-WinForms-Application-using-C-and-VBNet.aspx

Basically I am trying to import not null table data into table with excel file.