Import CSV Data Into Database Using C# Windows Form Application

Here I will show you how to import CSV data into SQL Server database using C#.Net Windows Application.

Here my goal to import .csv file data into SQL Server database using Windows Forms Application with C#.Net.

 

It's an easy way to add multiple records into the database instead of creating records one by one. It saves you lots of time. So it's a good idea to import data using csv or excel file instead of creating (Adding) record one by one.

 

I hope this sample code overview will help you to achieve your goal.

 

Let me explain, what I did here?

 

I have created a Windows Forms Application and I used C# as a language in this tutorial. In this application, I created a form called frmImportItem.

So in this, I will import item(product) data into the database for my POS (Point Of Sale) Application.

 

You can have any data like Student, Employee, and patient, etc. create your file that you want to import into the database.

 

I have a sample file (CSV file) for the import that I will import into the SQL Server database.

 

Step 1

 

Create A design to import data, take a button that will open a dialog box to select CSV file and one textbox to show the selected file name.

 

Step 2

 

Also, take one DataGridView to show all the items, selected CSV file item will bind into that DataGridView and show to the user.

 

Step 3

 

Create two Button one for Save Item and Another one is Close form.

 

Step 4

 

Here I will write code for the Select file (.csv) and read data and bind to the database. To select the file and Read CSV file data and bind to a DataGridView. Check the btnSelectFile_Click ()

private void btnSelectFile_Click(object sender, EventArgs e) {
    try {
        OpenFileDialog dialog = new OpenFileDialog();
        dialog.ShowDialog();
        int ImportedRecord = 0, inValidItem = 0;
        string SourceURl = "";
        if (dialog.FileName != "") {
            if (dialog.FileName.EndsWith(".csv")) {
                DataTable dtNew = new DataTable();
                dtNew = GetDataTabletFromCSVFile(dialog.FileName);
                if (Convert.ToString(dtNew.Columns[0]).ToLower() != "lookupcode") {
                    MessageBox.Show("Invalid Items File");
                    btnSave.Enabled = false;
                    return;
                }
                txtFile.Text = dialog.SafeFileName;
                SourceURl = dialog.FileName;
                if (dtNew.Rows != null && dtNew.Rows.ToString() != String.Empty) {
                    dgItems.DataSource = dtNew;
                }
                foreach(DataGridViewRow row in dgItems.Rows) {
                    if (Convert.ToString(row.Cells["LookupCode"].Value) == "" || row.Cells["LookupCode"].Value == null || Convert.ToString(row.Cells["ItemName"].Value) == "" || row.Cells["ItemName"].Value == null || Convert.ToString(row.Cells["DeptId"].Value) == "" || row.Cells["DeptId"].Value == null || Convert.ToString(row.Cells["Price"].Value) == "" || row.Cells["Price"].Value == null) {
                        row.DefaultCellStyle.BackColor = Color.Red;
                        inValidItem += 1;
                    } else {
                        ImportedRecord += 1;
                    }
                }
                if (dgItems.Rows.Count == 0) {
                    btnSave.Enabled = false;
                    MessageBox.Show("There is no data in this file", "GAUTAM POS", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
            } else {
                MessageBox.Show("Selected File is Invalid, Please Select valid csv file.", "GAUTAM POS", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }
    } catch (Exception ex) {
        MessageBox.Show("Exception " + ex);
    }
}
public static DataTable GetDataTabletFromCSVFile(string csv_file_path) {
    DataTable csvData = new DataTable();
    try {
        if (csv_file_path.EndsWith(".csv")) {
            using(Microsoft.VisualBasic.FileIO.TextFieldParser csvReader = new Microsoft.VisualBasic.FileIO.TextFieldParser(csv_file_path)) {
                csvReader.SetDelimiters(new string[] {
                    ","
                });
                csvReader.HasFieldsEnclosedInQuotes = true;
                //read column
                string[] colFields = csvReader.ReadFields();
                foreach(string column in colFields) {
                    DataColumn datecolumn = new DataColumn(column);
                    datecolumn.AllowDBNull = true;
                    csvData.Columns.Add(datecolumn);
                }
                while (!csvReader.EndOfData) {
                    string[] fieldData = csvReader.ReadFields();
                    for (int i = 0; i < fieldData.Length; i++) {
                        if (fieldData[i] == "") {
                            fieldData[i] = null;
                        }
                    }
                    csvData.Rows.Add(fieldData);
                }
            }
        }
    } catch (Exception ex) {
        MessageBox.Show("Exce " + ex);
    }
    return csvData;
}

Step 5

 

On the Save button, I have written code to save data into the database.

 

For this check the button click event as btnSave_Click().

private void btnSave_Click(object sender, EventArgs e) {
    try {
        DataTable dtItem = (DataTable)(dgItems.DataSource);
        string Lookup, description, dept, UnitPrice;
        string InsertItemQry = "";
        int count = 0;
        foreach(DataRow dr in dtItem.Rows) {
            Lookup = Convert.ToString(dr["LookupCode"]);
            description = Convert.ToString(dr["ItemName"]);
            dept = Convert.ToString(dr["DeptId"]);
            UnitPrice = Convert.ToString(dr["Price"]);
            if (Lookup != "" && description != "" && dept != "" && UnitPrice != "") {
                InsertItemQry += "Insert into tbItem(LookupCode,ItemName,DeptId,CateId,Cost,Price, Quantity, UOM, Weight, TaxID, IsDiscountItem,EntryDate)Values('" + Lookup + "','" + description + "','" + dept + "','" + dr["CateId"] + "','" + dr["Cost"] + "','" + UnitPrice + "'," + dr["Quantity"] + ",'" + dr["UOM"] + "','" + dr["Weight"] + "','" + dr["TaxID"] + "','" + dr["IsDiscountItem"] + "',GETDATE()); ";
                count++;
            }
        }
        if (InsertItemQry.Length > 0) {
            bool isSuccess = DBAccess.ExecuteQuery(InsertItemQry);
            if (isSuccess) {
                MessageBox.Show("Item Imported Successfully, Total Imported Records : " + count + "", "GAUTAM POS", MessageBoxButtons.OK, MessageBoxIcon.Information);
                dgItems.DataSource = null;
            }
        }
    } catch (Exception ex) {
        MessageBox.Show("Exception " + ex);
    }
}

DBAccess Class Code

------------------------------

class DBAccess {
    private static SqlConnection objConnection;
    private static SqlDataAdapter objDataAdapter;
    public static string ConnectionString = @ "Data Source=GAUTAM\SQLEXPRESS; Initial Catalog=GautamPOS; User Id=sa; Password=123456";
    private static void OpenConnection() {
        try {
            if (objConnection == null) {
                objConnection = new SqlConnection(ConnectionString);
                objConnection.Open();
            } else {
                if (objConnection.State != ConnectionState.Open) {
                    objConnection = new SqlConnection(ConnectionString);
                    objConnection.Open();
                }
            }
        } catch (Exception ex) {}
    }
    private static void CloseConnection() {
        try {
            if (!(objConnection == null)) {
                if (objConnection.State == System.Data.ConnectionState.Open) {
                    objConnection.Close();
                    objConnection.Dispose();
                }
            }
        } catch {}
    }
    public static bool ExecuteQuery(string query) {
        try {
            using(SqlConnection connection = new SqlConnection(ConnectionString)) {
                using(SqlCommand cmd = new SqlCommand(query, connection)) {
                    connection.Open();
                    cmd.ExecuteNonQuery();
                    connection.Close();
                    return true;
                }
            }
        } catch (Exception ex) {
            return false;
        }
    }
}

Check the video for your reference.