Insert Delete Update Records In CSV File

In this post, I will explain how you can perform insert, update, and delete operations in a CSV file using ODBC. We will be working with CSV and text files, performing CRUD operations on them.

CSV and text files do not support insert, update, and delete operations using ODBC by default. Therefore, I have implemented logic to perform these operations on text and CSV files. Here's what you will learn in this post:

CRUD operations in CSV files and text files using C# ASP.NET
How to delete rows in a CSV file
How to delete a certain column from a .csv file
Connecting to CSV data files using Microsoft Data Access Components
Accessing text files using the ODBC data provider
How to delete a specific row inside a CSV file.

To begin, create a new website in Visual Studio by navigating to File > New > Web Site, then select "C#" on the left side and choose "Empty Web Site".

name it as you want.
now add a aspx page in your website.

now add a folder in your website name it Upload or anything as you want.

right click on it and your add csv file in it.

i have a csv file which contains information about person.
csv file

i have added a class file in my website.

now i have design my aspx page for performing CRUD Operations.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
           <table class="auto-style1">
            <tr>
                <td>Employeeid</td>
                <td class="auto-style3">
                    <asp:TextBox ID="txtemployeeid" runat="server"></asp:TextBox>
                </td>
                <td class="auto-style3">
                    <asp:Button ID="Btngetupdate" runat="server"  Text="GetPersonForUpdate" />
                </td>
                <td class="auto-style3">
                    <asp:Button ID="btnupdate" runat="server" Text="UpdatePerson"  />
                </td>
            </tr>
            <tr>
                <td>Fristname</td>
                <td class="auto-style3">
                    <asp:TextBox ID="txtfirstname" runat="server"></asp:TextBox>
                </td>
                <td class="auto-style3">
                    &nbsp;</td>
                <td class="auto-style3">
                    &nbsp;</td>
            </tr>
            <tr>
                <td>lastname</td>
                <td class="auto-style3">
                    <asp:TextBox ID="txtlastname" runat="server"></asp:TextBox>
                </td>
                <td class="auto-style3">
                    &nbsp;</td>
                <td class="auto-style3">
                    &nbsp;</td>
            </tr>
            <tr>
                <td>Emailid</td>
                <td class="auto-style3">
                    <asp:TextBox ID="txtemailid" runat="server"></asp:TextBox>
                </td>
                <td class="auto-style3">
                    &nbsp;</td>
                <td class="auto-style3">
                    &nbsp;</td>
            </tr>
            <tr>
                <td>Address</td>
                <td class="auto-style3">
                    <asp:TextBox ID="txtaddress" runat="server"></asp:TextBox>
                </td>
                <td class="auto-style3">
                    &nbsp;</td>
                <td class="auto-style3">
                    &nbsp;</td>
            </tr>
            <tr>
                <td>&nbsp;</td>
                <td class="auto-style3">
                    <asp:Button ID="btnadd" runat="server" Text="Addperson"  style="height: 26px" />
                </td>
                <td class="auto-style3">
                    &nbsp;</td>
                <td class="auto-style3">
                    &nbsp;</td>
            </tr>
            <tr>
                <td>&nbsp;</td>
                <td class="auto-style3">
                    &nbsp;</td>
                <td class="auto-style3">
                    &nbsp;</td>
                <td class="auto-style3">
                    &nbsp;</td>
            </tr>
            <tr>
                <td class="auto-style2">
                    &nbsp;</td>
                <td class="auto-style4">
                    <asp:Button ID="btnGet" runat="server" Text="GetPerson" />
                </td>
                <td class="auto-style4">
                    &nbsp;</td>
                <td class="auto-style4">
                    <asp:Button ID="btndelete" runat="server" Text="DeletePerson"  />
                </td>
            </tr>
        </table>
        <asp:GridView ID="GridView1" runat="server"></asp:GridView>
    </form>
</body>
</html>

Now, I have written the logic for CRUD operations. Please read through the code patiently. In this post, you will also learn the following points:

1. How to create a CSV file and save it automatically on the local/server, as well as how to save a CSV file in a folder.

2. How to add rows to a DataTable programmatically in C#.

3. How to compare and delete DataTable rows using C#, including deleting specific rows from a DataTable based on conditions.

4. How to find and update cells in a DataTable, including updating existing records in a Dataset, and editing a row in the DataTable.

5. Inserting, updating, and deleting records in a DataTable using C#.


using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.VisualBasic.FileIO;
using System.Data.Odbc;
using System.Text;
using System.IO;

public partial class Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    private void GetPerson()
    {
        string spath = System.Web.HttpContext.Current.Server.MapPath("~/Upload");
        //odbc connection to the csv file
        string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + spath.Trim() + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";
        OdbcConnection objCSV = new OdbcConnection(strConnString);
        OdbcCommand Cmd = new OdbcCommand("select * from Person.csv ", objCSV);
        objCSV.Open();
        OdbcDataAdapter adp = new OdbcDataAdapter(Cmd);
        DataSet ds = new DataSet();
        adp.Fill(ds);
        objCSV.Close();
        GridView1.DataSource = ds;
        GridView1.DataBind();
    }
    private Person GetPerson(string employeeid)
    {
        string spath = System.Web.HttpContext.Current.Server.MapPath("~/Upload");
        //odbc connection to the csv file
        string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + spath.Trim() + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";
        OdbcConnection objCSV = new OdbcConnection(strConnString);
        OdbcCommand Cmd = new OdbcCommand("select * from Person.csv ", objCSV);
        objCSV.Open();
        OdbcDataReader rdr = Cmd.ExecuteReader();
        Person _person = new Person();
        while (rdr.Read())
        {

            if (rdr["Employeeid"].ToString() == employeeid)
            {
                _person.Employeeid =Convert.ToInt32(rdr["Employeeid"]);
                _person.Fristname = rdr["Fristname"].ToString();
                _person.lastname = rdr["lastname"].ToString();
                _person.Emailid = rdr["Emailid"].ToString();
                _person.Address = rdr["Address"].ToString();
              

            }
        }
        objCSV.Close();
        return _person;

    }
    //adding new row to the datatable
    private bool AddPerson(Person obj)
    {
        try
        {
            string spath = System.Web.HttpContext.Current.Server.MapPath("~/Upload");
            //odbc connection to the csv file
            string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + spath.Trim() + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";
            OdbcConnection objCSV = new OdbcConnection(strConnString);
            OdbcCommand Cmd = new OdbcCommand("select * from Person.csv", objCSV);
            OdbcDataAdapter adp = new OdbcDataAdapter(Cmd);
            DataSet ds = new DataSet();
            adp.Fill(ds, "csv");
            DataTable dt = ds.Tables["csv"];
            //adding new row to the datatable
            DataRow newRow = dt.NewRow();
            newRow["Employeeid"] = obj.Employeeid;
            newRow["Fristname"] = obj.Fristname;
            newRow["lastname"] = obj.lastname;
            newRow["Emailid"] = obj.Emailid;
            newRow["Address"] = obj.Address;
            dt.Rows.Add(newRow);
            //this function for rewrite csv file
            Replacefile(ds.Tables["csv"]);
            return true;
        }
        catch
        {
            return false;
        }
    }
    //updating datatable with condition
    private bool UpdatePerson(string employeeid, Person obj)
    {
        try
        {
            string spath = System.Web.HttpContext.Current.Server.MapPath("~/Upload");
            //odbc connection to the csv file
            string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + spath.Trim() + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";
            OdbcConnection objCSV = new OdbcConnection(strConnString);
            OdbcCommand Cmd = new OdbcCommand("select * from Person.csv", objCSV);
            OdbcDataAdapter adp = new OdbcDataAdapter(Cmd);
            DataSet ds = new DataSet();
            adp.Fill(ds, "csv");
            DataTable dt = ds.Tables["csv"];
            foreach (DataRow rdr in dt.Rows)
            {
                if (rdr["Employeeid"].ToString() == employeeid)
                {
                    rdr["Employeeid"] = obj.Employeeid;
                    rdr["Fristname"] = obj.Fristname;
                    rdr["lastname"] = obj.lastname;
                    rdr["Emailid"] = obj.Emailid;
                    rdr["Address"] = obj.Address;
                  
                }
            }
            Replacefile(ds.Tables["csv"]);
            return true;
        }
        catch(Exception ex)
        {
            return false;
        }
    }
    //remove or delete row from datatable
    private int Delete(string employeeid)
    {
        try
        {
            string spath = System.Web.HttpContext.Current.Server.MapPath("~/Upload");
            //odbc connection to the csv file
            string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + spath.Trim() + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";
            OdbcConnection objCSV = new OdbcConnection(strConnString);
            objCSV.Open();
            OdbcCommand oCmd = new OdbcCommand("select * from Person.csv ", objCSV);
            OdbcDataAdapter adp = new OdbcDataAdapter(oCmd);
            DataSet ds = new DataSet();
            adp.Fill(ds, "csv");
            objCSV.Close();
            int nor = 0; // number of rows deleted
            DataTable dt = ds.Tables["csv"];
            DataRow[] toBeDeleted;
            toBeDeleted = dt.Select("employeeid=" + employeeid + "");

            if (toBeDeleted.Length > 0)
            {
                foreach (DataRow dr in toBeDeleted)
                {
                    dt.Rows.Remove(dr);//removing row
                    nor++;
                }
            }
            Replacefile(ds.Tables["csv"]);
            return nor;
        }
        catch(Exception ex)
        {
            return 0;
        }


    }
    //creating csv file and save it in a folder.
    private void Replacefile(DataTable datatable)
    {  // this method for creating  csv file
        char seperator = ',';
        string spath = System.Web.HttpContext.Current.Server.MapPath("~/Upload");
        string path = spath + "/Person.csv";
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < datatable.Columns.Count; i++)
        {
            sb.Append(datatable.Columns[i]);
            if (i < datatable.Columns.Count - 1)
                sb.Append(seperator);
        }
        sb.AppendLine();
        foreach (DataRow dr in datatable.Rows)
        {
            for (int i = 0; i < datatable.Columns.Count; i++)
            {
                sb.Append(dr[i].ToString());

                if (i < datatable.Columns.Count - 1)
                    sb.Append(seperator);
            }
            sb.AppendLine();
        }
        //write all text to csv file
        File.WriteAllText(path, sb.ToString());
    }



    protected void btnGet_Click(object sender, EventArgs e)
    {
        GetPerson();
    }

    protected void btnadd_Click(object sender, EventArgs e)
    {
        Person obj = new Person();
        obj.Employeeid =Convert.ToInt16(txtemployeeid.Text);
        obj.Fristname = txtfirstname.Text;
        obj.lastname = txtlastname.Text;
        obj.Emailid = txtemailid.Text;
        obj.Address = txtaddress.Text;
        bool status=AddPerson(obj);
        if (status)
        {
            Response.Write("<script>alert('insert successfully!')</script>");
            GetPerson();
        }
        else {
            Response.Write("<script>alert('Try again!')</script>");
            GetPerson();
        }

    }


    protected void Btngetupdate_Click(object sender, EventArgs e)
    {
        Person obj = GetPerson(txtemployeeid.Text);
        txtemployeeid.Text = obj.Employeeid.ToString();
        txtfirstname.Text = obj.Fristname;
        txtlastname.Text = obj.lastname;
        txtemailid.Text = obj.Emailid;
        txtaddress.Text = obj.Address;
    }

    protected void btnupdate_Click(object sender, EventArgs e)
    {
        Person obj = new Person();
        obj.Employeeid = Convert.ToInt16(txtemployeeid.Text);
        obj.Fristname = txtfirstname.Text;
        obj.lastname = txtlastname.Text;
        obj.Emailid = txtemailid.Text;
        obj.Address = txtaddress.Text;
        bool status = UpdatePerson(txtemployeeid.Text,obj);
        if (status)
        {
            Response.Write("<script>alert('updated successfully!')</script>");
            GetPerson();
        }
        else
        {
            Response.Write("<script>alert('Try again!')</script>");
            GetPerson();
        }
    }

    protected void btndelete_Click(object sender, EventArgs e)
    {
        int i = Delete(txtemployeeid.Text);
        if (i > 0)
        {
            Response.Write("<script>alert('deleted successfully!')</script>");
            GetPerson();
        }
        else
        {
            Response.Write("<script>alert('Try again!')</script>");
            GetPerson();
        }
    }
}

GetPerson Method:

This method retrieves data from the CSV file named "Person.csv" located in the "Upload" folder.
It establishes an ODBC connection to the CSV file, executes a select query, fills a DataSet, and binds the data to a GridView.

GetPerson (Overloaded) Method:

This method retrieves a specific person's data based on the provided employee ID.
It performs a similar operation as the GetPerson method but filters the data based on the employee ID.

AddPerson Method:

This method adds a new person's data to the CSV file.
It first retrieves the existing data, creates a new row in the DataTable, populates it with the provided person object's data, and then writes the changes back to the CSV file.

UpdatePerson Method:

This method updates an existing person's data in the CSV file based on the provided employee ID.
It retrieves the existing data, locates the row corresponding to the provided employee ID, updates the fields with the new values, and then writes the changes back to the CSV file.

Delete Method:

This method deletes a person's data from the CSV file based on the provided employee ID.
It retrieves the existing data, locates the row corresponding to the provided employee ID, removes the row, and then writes the changes back to the CSV file.

Replacefile Method:

This method creates or replaces the CSV file with the updated DataTable content.
It constructs the CSV content from the DataTable, writes it to a file, and saves it in the specified folder.

Button Click Event Handlers:

These event handlers are associated with buttons (btnGet, btnadd, Btngetupdate, btnupdate, btndelete) on the web page.
They trigger corresponding actions like retrieving data, adding a new record, retrieving data for updating, updating a record, and deleting a record, respectively.
Upon completion of each action, they call the GetPerson method to refresh the displayed data in the GridView.

Above code provides functionality to manage person data stored in a CSV file through a web interface. It demonstrates basic CRUD operations using ODBC connectivity with a CSV data source.

Insert Delete Update Records In CSV File - ASP.NET C#

Read Similar Articles