This blog will explain to you how to create a website and performing CRUD (Create, Read, Update and Delete) operations with PostgreSQL database using asp.net and c# language. 

Create an empty website and Install PostgreSQL NuGet Package

Step 1. Create an empty website from Visual Studio. (Open Visual Studio then select File-> New Web Site -> ASP.NET Empty Web Site OR File-> New-> Web Site -> ASP.NET Empty Web Site)

Step 2. Create a database in PostgreSQL with one table "tbluser" with required columns ie. UserID, FullName, EmailAddress, Password, Gender, Hobbies, ProfilePicture. Note: Use small alphabets while creating table name and its column name. Otherwise, it will give error while performing crud operations.

Step 3. Now to work with PostgreSQL database classes we need to install PostgreSQL NuGet Package. Right click on the website in Solution Explorer and select Manage NuGet Package…

Step 4. Above step will open Manage NuGet Package window. Write Npgsql in a search box. It will show you Npgsql for.Net. Click on Install button and it will add PostgreSQL reference in our website.

After installing it, you will see Npgsql.dll has been added to your website in Bin folder.

Note: If you get any error while installing NuGet Package, you can try the following way to install it,

1. See this link to install Npgsql -> Install Npgsql NuGet Package

2. Download Npgsql.dll and add a reference to our website. 

Steps to add dll reference,

  • Right click on website -> Add Reference…
  • Click on the Browse button
  • Select path of Npgsql.dll
  • Click on Add button and then OK button

Insert Operation

Step 1. Create a page Default.aspx and take fields according to our database. As ID in autoincrement, do not take it in insert form. Also, create a folder with name "profilepic" in Solution Explorer. Write the following code to design page and to insert data into the PostgreSQL database.

Namespaces: You have to use the following namespaces to connect with PostgreSQL database. Add these namespaces at the top of the page.

using Npgsql;

using System.Data;

Connection String: Use the following connection string to connect our website with a database. All property remains the same, only Database and Password will be changed according to your database.

string strCon = "Server=localhost;Port=5432;User Id=postgres;Password=Your-PostgreSQL-Password;Database=dbCRUD;"

Default.aspx

<%@ 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>Insert using PostgreSQL</title>

    <style type="text/css">

        h2 {

            display: table;

            margin: 40px auto;

        }

  

        table {

            display: table;

            margin: 40px auto 0px;

        }

  

            table tr td {

                padding: 0px 10px 10px 0px;

            }

  

        .clsmargin {

            margin: 0 auto 15px;

            display: table;

        }

    </style>

</head>

<body>

    <form id="form1" runat="server">

        <div>

            <h2>Insert Form</h2>

            <table>

                <tr>

                    <td>Full Name

                    </td>

                    <td>

                        <asp:TextBox ID="txtFullName" runat="server"></asp:TextBox>

                    </td>

                </tr>

                <tr>

                    <td>Email Address

                    </td>

                    <td>

                        <asp:TextBox ID="txtEmailAddress" runat="server"></asp:TextBox>

                    </td>

                </tr>

                <tr>

                    <td>Password

                    </td>

                    <td>

                        <asp:TextBox ID="txtPassword" TextMode="Password" runat="server"></asp:TextBox>

                    </td>

                </tr>

                <tr>

                    <td>Gender

                    </td>

                    <td>

                        <asp:RadioButton ID="rdoMale" Text="Male" GroupName="Gender" runat="server" />

                        <asp:RadioButton ID="rdoFemale" Text="Female" GroupName="Gender" runat="server" />

                    </td>

                </tr>

                <tr>

                    <td>Hobbies

                    </td>

                    <td>

                        <asp:CheckBox ID="chkCricket" Text="Cricket" runat="server" />

                        <asp:CheckBox ID="chkFootball" Text="Football" runat="server" />

                    </td>

                </tr>

                <tr>

                    <td>Profile Picture

                    </td>

                    <td>

                        <asp:FileUpload ID="fileProfilePic" runat="server" />

                    </td>

                </tr>

                <tr>

                    <td></td>

                    <td colspan="2" style="padding-top: 20px">

                        <asp:Button Text="Click here to Insert" ID="btnInsert" runat="server" OnClick="btnInsert_Click" />

                    </td>

                </tr>

            </table>

            <h4 class="clsmargin">

                <asp:Label Text="" ID="lblMsg" runat="server" Style="color: green" />

            </h4>

        </div>

    </form>

</body>

</html>

Default.aspx.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using Npgsql; /* Add this Namespace */

  

public partial class _Default : System.Web.UI.Page

{

    /* Connection String */

    string strCon = "Server=localhost;Port=5432;User Id=postgres;Password=Your-PostgreSQL-Password;Database=dbCRUD;";

  

    protected void Page_Load(object sender, EventArgs e)

    {

    }

  

    /* Insert Code */

    protected void btnInsert_Click(object sender, EventArgs e)

    {

        string Gender = "", Hobbies = "", FileName = "", Path = "", FullPath = "";

        if (rdoMale.Checked == true)

        {

            Gender = rdoMale.Text;

        }

        if (rdoFemale.Checked == true)

        {

            Gender = rdoFemale.Text;

        }

        if (chkCricket.Checked == true)

        {

            if (Hobbies == "")

            {

                Hobbies = chkCricket.Text;

            }

            else

            {

                Hobbies = Hobbies + "," + chkCricket.Text;

            }

        }

        if (chkFootball.Checked == true)

        {

            if (Hobbies == "")

            {

                Hobbies = chkFootball.Text;

            }

            else

            {

                Hobbies = Hobbies + "," + chkFootball.Text;

            }

        }

        if (fileProfilePic.HasFile)

        {

            FileName = fileProfilePic.FileName;

            Path = Server.MapPath("profilepic");

            FullPath = Path + "//" + FileName;

            fileProfilePic.SaveAs(FullPath);

        }

  

        NpgsqlConnection con = new NpgsqlConnection(strCon);

        NpgsqlCommand cmd = new NpgsqlCommand("insert into tbluser(fullname,emailaddress,password,gender,hobbies,profilepicture) values('" + txtFullName.Text + "','" + txtEmailAddress.Text + "','" + txtPassword.Text + "','" + Gender + "','" + Hobbies + "','" + FileName + "')", con);

        con.Open();

        int isInserted = cmd.ExecuteNonQuery();

        con.Close();

        if (isInserted > 0)

        {

            lblMsg.Text = "Inserted Successfully..!!";

        }

        else

        {

            lblMsg.Text = "Erro Inserting Data..!!";

        }

    }

}

Step 2. Run the Default.aspx page. Enter data to all fields and click Insert button. Data will be inserted to tale and "Inserted Successfully..!!" message will display.

Select operation

Step 1. Now add new page ShowData.aspx to make GridView and perform Select operation. Add page, drag and drop GridView and see given code to design it. You will find GridView in Data tab of Toolbox.

ShowData.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ShowData.aspx.cs" Inherits="ShowData" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title>Show data</title>

    <style type="text/css">

        .clsmargin {

            margin: 0 auto 15px;

            display: table;

        }

    </style>

</head>

<body>

    <form id="form1" runat="server">

        <div class="clsmargin" style="margin-top: 5%">

            <h2 class="clsmargin">Show Data Form</h2>

            <asp:GridView ID="grdView" runat="server" AutoGenerateColumns="false" OnRowCommand="grdView_RowCommand">

                <Columns>

                    <asp:TemplateField HeaderText="ID">

                        <ItemTemplate>

                            <asp:Label ID="lblUserID" runat="server" Text='<%#Eval("userid") %>'></asp:Label>

                        </ItemTemplate>

                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Full Name">

                        <ItemTemplate>

                            <asp:Label ID="lblFullName" runat="server" Text='<%#Eval("fullname") %>'></asp:Label>

                        </ItemTemplate>

                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Email Address">

                        <ItemTemplate>

                            <asp:Label ID="lblEmailAddress" runat="server" Text='<%#Eval("emailaddress") %>'></asp:Label>

                        </ItemTemplate>

                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Password">

                        <ItemTemplate>

                            <asp:Label ID="lblPassword" runat="server" Text='<%#Eval("password") %>'></asp:Label>

                        </ItemTemplate>

                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Gender">

                        <ItemTemplate>

                            <asp:Label ID="lblGender" runat="server" Text='<%#Eval("gender") %>'></asp:Label>

                        </ItemTemplate>

                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Hobbies">

                        <ItemTemplate>

                            <asp:Label ID="lblHobbies" runat="server" Text='<%#Eval("hobbies") %>'></asp:Label>

                        </ItemTemplate>

                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Profile Picture">

                        <ItemTemplate>

                            <asp:Image ID="imgProfilePicture" runat="server" ImageUrl='<%# "profilepic/" + Eval("profilepicture").ToString().Trim() %>' Style="height: 50px; width: 50px"></asp:Image>

                        </ItemTemplate>

                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Actions">

                        <ItemTemplate>

                            <asp:LinkButton ID="lnkbtnEdit" CommandName="lnkbtnEdit" CommandArgument='<%#Eval("userid") %>' runat="server">Edit</asp:LinkButton>

                            <asp:LinkButton ID="lnkbtnDelete" CommandName="lnkbtnDelete" CommandArgument='<%#Eval("userid") %>' runat="server">Delete</asp:LinkButton>

                        </ItemTemplate>

                    </asp:TemplateField>

                </Columns>

            </asp:GridView>

        </div>

    </form>

</body>

</html>

ShowData.aspx.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using Npgsql; /* Add this Namespace */

using System.Data; /* Add this Namespace */

  

public partial class ShowData : System.Web.UI.Page

{

    /* Connection String */

    string strCon = "Server=localhost;Port=5432;User Id=postgres;Password=Your-PostgreSQL-Password;Database=dbCRUD;";

  

    /* Page load event */

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            getData();

        }

    }

  

    /* Bind GridView */

    public void getData()

    {

        NpgsqlConnection con = new NpgsqlConnection(strCon);

        NpgsqlDataAdapter da = new NpgsqlDataAdapter("select * from tbluser", con);

        DataTable dt = new DataTable();

        da.Fill(dt);

        grdView.DataSource = dt;

        grdView.DataBind();

    }

  

    /* Row command event - calling when user click on edit or delete button */

    protected void grdView_RowCommand(object sender, GridViewCommandEventArgs e)

    {

        /* This condition work when click on edit button */

        if (e.CommandName == "lnkbtnEdit")

        {

            int userid = int.Parse(e.CommandArgument.ToString());

            Response.Redirect("UpdateData.aspx?userid=" + userid); /* Pass userid in querystring for updating record */

        }

  

        /* This condition work when click on delete button */

        if (e.CommandName == "lnkbtnDelete")

        {

            int userid = int.Parse(e.CommandArgument.ToString());

            NpgsqlConnection con = new NpgsqlConnection(strCon);

            NpgsqlCommand cmd = new NpgsqlCommand("delete from tbluser where userid='" + userid + "'", con);

            con.Open();

            cmd.ExecuteNonQuery();

            con.Close();

            getData(); /* Reload gridview */

        }

    }

}

In above code getData() function is used to bind gridview with database records. grdView_RowCommand() event is called when the user clicks on Edit or Delete button. The event is Edit or Delete that is recognized by e.CommandName. And e.CommandArgument is used to get userid of the row on which the user has clicked. When the user clicks on Edit link button, it will be redirected to update the page with Query String to update clicked record and click of Delete button will delete the record and rebind gridview.

Step 2. Run the ShowData.aspx page and you may see output like the following image.

Update operation

Step 1. Create new page UpdateData.aspx. This page will look same as insert form but the difference is the controls should be prefilled with user data on which the user has clicked to edit in gridview. Data will be fetched on load event using userid sent from ShoData.aspx page in URL using Query String.

UpdateData.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="UpdateData.aspx.cs" Inherits="UpdateData" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title>Update data</title>

    <style type="text/css">

        h2 {

            display: table;

            margin: 40px auto;

        }

  

        table {

            display: table;

            margin: 40px auto 0px;

        }

  

            table tr td {

                padding: 0px 10px 10px 0px;

            }

  

        .clsmargin {

            margin: 0 auto 15px;

            display: table;

        }

    </style>

</head>

<body>

    <form id="form1" runat="server">

        <div>

            <h2>Update Form</h2>

            <table>

                <tr>

                    <td>Full Name

                    </td>

                    <td>

                        <asp:TextBox ID="txtFullName" runat="server"></asp:TextBox>

                    </td>

                </tr>

                <tr>

                    <td>Email Address

                    </td>

                    <td>

                        <asp:TextBox ID="txtEmailAddress" runat="server"></asp:TextBox>

                    </td>

                </tr>

                <tr>

                    <td>Gender

                    </td>

                    <td>

                        <asp:RadioButton ID="rdoMale" Text="Male" GroupName="Gender" runat="server" />

                        <asp:RadioButton ID="rdoFemale" Text="Female" GroupName="Gender" runat="server" />

                    </td>

                </tr>

                <tr>

                    <td>Hobbies

                    </td>

                    <td>

                        <asp:CheckBox ID="chkCricket" Text="Cricket" runat="server" />

                        <asp:CheckBox ID="chkFootball" Text="Football" runat="server" />

                    </td>

                </tr>

                <tr>

                    <td>Profile Picture

                    </td>

                    <td>

                        <asp:Image ID="imgUser" runat="server" Style="height: 50px; width: 50px" />

                        <br />

                        <asp:FileUpload ID="fileProfilePic" runat="server" />

                    </td>

                </tr>

                <tr>

                    <td align="right" style="padding-top: 20px">

                        <asp:Button Text="Update" ID="btnUpdate" runat="server" OnClick="btnUpdate_Click" />

                    </td>

                    <td align="left" style="padding-top: 20px">

                        <asp:Button Text="Cancel" ID="btnCancel" runat="server" OnClick="btnCancel_Click" />

                    </td>

                </tr>

            </table>

            <h4 class="clsmargin">

                <asp:Label Text="" ID="lblMsg" runat="server" Style="color: green" />

            </h4>

            <asp:HiddenField ID="hdImageName" runat="server" />

        </div>

    </form>

</body>

</html>

UpdateData.aspx.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using Npgsql; /* Add this Namespace */

using System.Data; /* Add this Namespace */

  

public partial class UpdateData : System.Web.UI.Page

{

    /* Connection String */

    string strCon = "Server=localhost;Port=5432;User Id=postgres;Password=Your-PostgreSQL-Password;Database=dbCRUD;";

  

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            if (Request.QueryString["userid"] != null)

            {

                getUserById();

            }

            else

            {

                Response.Redirect("ShowData.aspx");

            }

        }

    }

  

    /* Get user data by userid */

    public void getUserById()

    {

        int userid = int.Parse(Request.QueryString["userid"].ToString());

        NpgsqlConnection con = new NpgsqlConnection(strCon);

        NpgsqlDataAdapter da = new NpgsqlDataAdapter("select * from tbluser where userid='" + userid + "'", con);

        DataTable dt = new DataTable();

        da.Fill(dt);

  

        txtFullName.Text = dt.Rows[0]["fullname"].ToString().Trim();

        txtEmailAddress.Text = dt.Rows[0]["emailaddress"].ToString().Trim();

        if (dt.Rows[0]["gender"].ToString().Trim() == "Male")

        {

            rdoMale.Checked = true;

        }

        if (dt.Rows[0]["gender"].ToString().Trim() == "Female")

        {

            rdoFemale.Checked = true;

        }

        string[] Hobbies = dt.Rows[0]["hobbies"].ToString().Trim().Split(',');

        for (int i = 0; i < Hobbies.Length; i++)

        {

            if (Hobbies[i] == "Cricket")

            {

                chkCricket.Checked = true;

            }

            if (Hobbies[i] == "Football")

            {

                chkFootball.Checked = true;

            }

        }

        imgUser.ImageUrl = "profilepic/" + dt.Rows[0]["profilepicture"].ToString().Trim();

        hdImageName.Value = dt.Rows[0]["profilepicture"].ToString().Trim();

    }

  

    /* Update Code */

    protected void btnUpdate_Click(object sender, EventArgs e)

    {

        int userid = int.Parse(Request.QueryString["userid"].ToString());

        string Gender = "", Hobbies = "", FileName = "", Path = "", FullPath = "";

        if (rdoMale.Checked == true)

        {

            Gender = rdoMale.Text;

        }

        if (rdoFemale.Checked == true)

        {

            Gender = rdoFemale.Text;

        }

        if (chkCricket.Checked == true)

        {

            if (Hobbies == "")

            {

                Hobbies = chkCricket.Text;

            }

            else

            {

                Hobbies = Hobbies + "," + chkCricket.Text;

            }

        }

        if (chkFootball.Checked == true)

        {

            if (Hobbies == "")

            {

                Hobbies = chkFootball.Text;

            }

            else

            {

                Hobbies = Hobbies + "," + chkFootball.Text;

            }

        }

        if (fileProfilePic.HasFile)

        {

            FileName = fileProfilePic.FileName;

            Path = Server.MapPath("profilepic");

            FullPath = Path + "//" + FileName;

            fileProfilePic.SaveAs(FullPath);

        }

        else

        {

            FileName = hdImageName.Value;

        }

  

        NpgsqlConnection con = new NpgsqlConnection(strCon);

        NpgsqlCommand cmd = new NpgsqlCommand("update tbluser set fullname='" + txtFullName.Text + "',emailaddress='" + txtEmailAddress.Text + "',gender='" + Gender + "',hobbies='" + Hobbies + "',profilepicture='" + FileName + "' where userid='" + userid + "'", con);

        con.Open();

        int isUpdated = cmd.ExecuteNonQuery();

        con.Close();

        if (isUpdated > 0)

        {

            Response.Redirect("ShowData.aspx");

        }

        else

        {

            lblMsg.Text = "Erro Updating Data..!!";

        }

    }

  

    /* Cancel Code */

    protected void btnCancel_Click(object sender, EventArgs e)

    {

        Response.Redirect("ShowData.aspx");

    }

}

Step 2. Run the page and you will see the page like below. Update data and check it will reflect in gridview.

Delete operation

To perform delete operation we do not have to create new page because it is already done in ShowData.aspx page on Delete button click event. Please see my ShoData.aspx.cs page for delete button code, it is written in grdView_RowCommand() event.

/* This condition work when click on delete button */

if (e.CommandName == "lnkbtnDelete")

{

    int userid = int.Parse(e.CommandArgument.ToString());

    NpgsqlConnection con = new NpgsqlConnection(strCon);

    NpgsqlCommand cmd = new NpgsqlCommand("delete from tbluser where userid='" + userid + "'", con);

    con.Open();

    cmd.ExecuteNonQuery();

    con.Close();

    getData(); /* Reload gridview */

}

Hope you will like this blog. Feel free to ask a question. Thank You… :)