How to Read Data From Database in C# Using Datareader
This post show you How to Read Excel file and Import data from Excel to SQL Server in C# .Internet Windows Forms Application using ExcelDataReader and Dapper ORM.
First off, y'all should create a new Windows Forms Application, then drag Label, TextBox, Button, Combobox and DataGridView from your Sixsual Studio Toolbox to your winform.
Next, You can design a unproblematic UI Winform that allows you lot to select the excel file, so read data from excel canvas to DataGridView and Import information from excel file to SQL Server in c# as shown below.
Correct click on your project, then select Manage Nuget Packages. Adjacent, You demand to installExcelDataReader, ExcelDataReader.DataSet, Z.Dapper to your project.
ExcelDataReader
Lightweight and fast library written in C# for reading Microsoft Excel files (two.0-2007). Using ExcelDataReader you can read excel file in c# without oledb.
ExcelDataReader.DataSet
ExcelDataReader extension for reading Microsoft Excel files into System.Data.DataSet.
Z.Dapper.Plus
It's a high efficient Bulk Deportment (Insert, Update, Delete, and Merge) for .NET Support: SQL Server, SQL Azure, SQL Compact, MySQL, SQLite and more..
To play the demo, you should create a new database, then run the sql script below to create the Clienttabular array.
CREATE Table [dbo].[Customers]( [CustomerID] [nchar](five) NOT NULL, [CompanyName] [nvarchar](40) NOT Goose egg, [ContactName] [nvarchar](30) Zilch, [ContactTitle] [nvarchar](30) NULL, [Address] [nvarchar](60) NULL, [City] [nvarchar](fifteen) Zippo, [Region] [nvarchar](15) NULL, [PostalCode] [nvarchar](x) Nada, [Land] [nvarchar](fifteen) NULL, [Telephone] [nvarchar](24) Nix, [Fax] [nvarchar](24) NULL, CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ( [CustomerID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Primary] ) ON [PRIMARY]
You should create a Client class with fields respective to the columns in the sql table. I'm using the Customers tabular array exported to the excel file from the Northwind database. Customer form helps you map data between client tables and customer objects.
public class Client { public string CustomerID { get; set; } public string CompanyName { get; fix; } public cord ContactTitle { get; set; } public string Accost { get; set; } public cord Metropolis { get; gear up; } public string Country { get; ready; } public string Phone { go; fix; } public string Fax { get; set; } public string PostalCode { get; set; } public string Region { get; set; } } Side by side, Open your form code behind, and then declare tables variable with the data type as shown beneath. This variable helps you store all data in excel. Each respective Sheet is a DataTable.
DataTableCollection tables;
How to Read Excel file in C# Windows Application?
Add together the click issue handler to the Browse button allows you to select an excel file.
private void btnBrowse_Click(object sender, EventArgs due east) { using (OpenFileDialog ofd = new OpenFileDialog() { Filter = "Excel 97-2003 Workbook|*.xls|Excel Workbook|*.xlsx" }) { if (ofd.ShowDialog() == DialogResult.OK) { txtPath.Text = ofd.FileName; using (var stream = File.Open up(ofd.FileName, FileMode.Open up, FileAccess.Read)) { using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream)) { DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration() { ConfigureDataTable = (_) => new ExcelDataTableConfiguration() { UseHeaderRow = truthful } }); tables = result.Tables; cboSheet.Items.Articulate(); foreach (DataTable tabular array in tables) cboSheet.Items.Add(table.TableName); } } } } } How do you read data of an Excel file using C#?
We will read all sheets in excel file, then read data from excel sheet using c#. You can add together sheet proper name to the Combobox. The resulting variable is aDataSetcontaining all theSheetsread from the excel file. Recall to clear all items of theCombobox earlier adding new item.
I'chiliad using theExcelDataReader library to read excel file (xls/xlsx). This is the best manner to read excel file in c#.
In other words, Import excel to datatable in c# windows application or c# read excel file into datatable without oledb.
Past default, ExcelDataReader supports c# read excel file into datatable. So, y'all should convert from DataTable to List if you want to use List.
Next, Add together the SelectedIndexChanged outcome handler to the Combobox that allows y'all to select a worksheet, and then read data from excel sheet to the DataGridView as the following c# code.
individual void cboSheet_SelectedIndexChanged(object sender, EventArgs e) { DataTable dt = tables[cboSheet.SelectedItem.ToString()]; if (dt != null) { List<Customer> listing = new Listing<Customer>(); for (int i = 0; i < dt.Rows.Count; i++) { Customer obj = new Client(); obj.CustomerID = dt.Rows[i]["CustomerID"].ToString(); obj.CompanyName = dt.Rows[i]["CompanyName"].ToString(); obj.ContactTitle = dt.Rows[i]["ContactTitle"].ToString(); obj.City = dt.Rows[i]["Urban center"].ToString(); obj.Accost = dt.Rows[i]["Address"].ToString(); obj.Country = dt.Rows[i]["Country"].ToString(); obj.Fax = dt.Rows[i]["Fax"].ToString(); obj.Phone = dt.Rows[i]["Phone"].ToString(); obj.PostalCode = dt.Rows[i]["PostalCode"].ToString(); obj.Region = dt.Rows[i]["Region"].ToString(); list.Add(obj); } customerBindingSource.DataSource = list; } } You need to add a BindingSource to the DataGridView.Next, Read information from excel sheet using c#, and then map the information from the DataTable to the list customer object.
Finally, Initialize the data source for DataGridView as the following c# lawmaking. As you see, you lot can easily import excel to DataGridView in C#. UsingExcelDataReader is the best /fastest style to read an Excel Sheet into a DataTable.
How to Import data from Excel to SQL Server using C#?
To import data from excel file to sql server database, you tin can utilise the BulkInsert method of Dapper Plus library.
public void Insert(List<Customer> listing) { DapperPlusManager.Entity<Customer>().Table("Customers"); using (IDbConnection db = new SqlConnection("Server=.;Database=dbtest;User Id=sa;[email protected];")) { db.BulkInsert(list); } } Using BulkInsert methodis the best way to import large amounts of information into sql server.BulkInsert merely execute the command once, so the execution speed will be many times faster than inserting each record into the database.
Import excel to Sql Server C# Winform
Adding a click issue handler to the Import push button allows you to import excel to sql server c# winform equally shown below.
private void btnImport_Click(object sender, EventArgs e) { try { Insert(customerBindingSource.DataSource equally List<Customer>); MessageBox.Show("Finished !"); } catch (Exception ex) { MessageBox.Show(ex.Message); } } Through this post, I hope you tin can find a solution on how to read excel file in c# windows application and import data from excel to sql server using c# windows application.
Y'all can do the same as above to import data from excel to sql server using asp.internet mvc or import information from excel to sql server using entity framework.
VIDEO TUTORIAL
Source: https://foxlearn.com/windows-forms/how-to-read-excel-file-and-import-data-from-excel-to-sql-server-in-csharp-393.html