In this post I will explain you how you can perform insert,update,delete operation in csv file using odbc. In this post we are going to play with CSV and Text files.we are going to perform crud operation on it.
In csv and Text file you can not perform insert,update delete operation using odbc.So i have implemented logic for performing these operation in text and csv file.Description:you will learn following points in this post.
a.Crud Operations in CSV Files and Text Files Using C# ASP.NET
b.How to delete the rows in csv file
c.How do I delete certain column from .csv file
d.Connect to CSV Data Files using Microsoft Data Access Components
e.Accessing Text Files using ODBC Data Provider
f.How to deleting specific row inside CSVFile>New>Web Site then select “C#”(left side in visual studio)  “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 logic for Crud operation please read all code patiently.In this Post you can also learn these points.
Description:In this post you will also learn these point.
 1.Create CSV and save on local/server automatically,save csv file in folder in 
2.How to Add Rows to a DataTable ,How to add a new row to c# DataTable ,Add new rows to a DataTable pro grammatically C# 
3.How To Compare and delete datatable row using C#,c# – Deleting specific rows from DataTable ,
c# – Remove Row from DataTable Depending on Condition 
4.Find and Update cell in DataTable,How to update records inside datatable only in c#,Updating Existing Records in a Dataset,c# How to Edit a row in the datatable
Insert ,Update and Delete in 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();
        }
    }
}

The post Insert delete update records in CSV file -ASP.NET appeared first on Software Development | Programming Tutorials.



Read More Articles