Home / ASP.Net / How to show Data, Edit Data, Update data and Delete data through DataSet in ASP.Net

Saturday, 13 May 2017

How to show Data, Edit Data, Update data and Delete data through DataSet in ASP.Net

Date - 

DataSet.aspx File
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DataSet.aspx.cs" Inherits="DemoClassExample.DataSet" %>
<head>
    <style type="text/css">
        .style1
        {
            width: 58%;
        }
    </style>
</head>
<form id="form1" runat="server">
<table class="style1">
    <tr>
        <td align="center" colspan="3">
            <asp:ScriptManager ID="ScriptManager1" runat="server">
            </asp:ScriptManager>
            <asp:Timer ID="Timer1" runat="server" Enabled="False" Interval="3000"
                ontick="Timer1_Tick">
            </asp:Timer>
        </td>
    </tr>
    <tr>
        <td align="center" colspan="3">
<table bgcolor="#FFFF66" class="style1">
    <tr>
        <td align="center" colspan="3"
            style="font-family: Georgia; font-size: small; font-weight: bold; font-style: normal; font-variant: normal; color: #800000">
            Registration</td>
    </tr>
    <tr>
        <td>
            RegistrationId</td>
        <td>
            <asp:TextBox ID="txtRegId" runat="server"></asp:TextBox>
        </td>
        <td align="center">
            <asp:Button ID="tnRegId" runat="server" BackColor="#CCCCCC" Font-Bold="True"
                ForeColor="Maroon" Text="RegID" onclick="tnRegId_Click" />
        </td>
    </tr>
    <tr>
        <td>
            &nbsp;</td>
        <td>
            &nbsp;</td>
        <td>
            &nbsp;</td>
    </tr>
    <tr>
        <td>
            Name</td>
        <td>
            <asp:TextBox ID="txtName" runat="server"></asp:TextBox>
        </td>
        <td>
            &nbsp;</td>
    </tr>
    <tr>
        <td>
            Address</td>
        <td>
            <asp:TextBox ID="txtAddress" runat="server" Height="31px" TextMode="MultiLine"
                Width="126px"></asp:TextBox>
        </td>
        <td>
            &nbsp;</td>
    </tr>
    <tr>
        <td>
            MobileNo</td>
        <td>
            <asp:TextBox ID="txtMobiNo" runat="server"></asp:TextBox>
        </td>
        <td>
            &nbsp;</td>
    </tr>
    <tr>
        <td>
            Course</td>
        <td>
            <asp:TextBox ID="txtCourse" runat="server"></asp:TextBox>
        </td>
        <td>
            &nbsp;</td>
    </tr>
    <tr>
        <td>
            Sex</td>
        <td>
            <asp:DropDownList ID="ddlGender" runat="server" Font-Bold="True" Height="20px"
                Width="126px">
                <asp:ListItem>----Select----</asp:ListItem>
                <asp:ListItem>Male</asp:ListItem>
                <asp:ListItem Value="Female">Female</asp:ListItem>
            </asp:DropDownList>
        </td>
        <td>
            &nbsp;</td>
    </tr>
    <tr>
        <td>
            EmailID</td>
        <td>
            <asp:TextBox ID="txtEmailId" runat="server"></asp:TextBox>
        </td>
        <td>
            &nbsp;</td>
    </tr>
    <tr>
        <td>
            DOB</td>
        <td>
            <asp:TextBox ID="txtDOB" runat="server" TextMode="Date"></asp:TextBox>
        </td>
        <td>
            &nbsp;</td>
    </tr>
    <tr>
        <td colspan="3">
            <asp:Label ID="lblMsg" runat="server" Font-Bold="True" ForeColor="Red"></asp:Label>
        </td>
    </tr>
    <tr>
        <td align="center">
            <asp:Button ID="btnSave" runat="server" BackColor="#CCCCCC" Font-Bold="True"
                ForeColor="Maroon" onclick="btnSave_Click" Text="Save" />
        </td>
        <td align="center">
            <asp:Button ID="btnUpdate" runat="server" BackColor="#CCCCCC" Font-Bold="True"
                ForeColor="Maroon" Text="Update" onclick="btnUpdate_Click" />
            <asp:Button ID="btnDelete" runat="server" BackColor="#CCCCCC" Font-Bold="True"
                ForeColor="Maroon" Text="Delete" onclick="btnDelete_Click" />
        </td>
        <td align="center">
            <asp:Button ID="btnReset" runat="server" BackColor="#CCCCCC" Font-Bold="True"
                ForeColor="Maroon" onclick="btnReset_Click" Text="Reset" />
        </td>
    </tr>
</table>
        </td>
    </tr>
    <tr>
        <td>
            <asp:Button ID="btnShow" runat="server" onclick="btnShow_Click" Text="ShowDataAdepter"
                BackColor="#CCCCCC" Font-Bold="True" ForeColor="Maroon" />
            <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333"
                GridLines="None">
                <AlternatingRowStyle BackColor="White" />
                <EditRowStyle BackColor="#2461BF" />
                <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
                <RowStyle BackColor="#EFF3FB" />
                <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
                <SortedAscendingCellStyle BackColor="#F5F7FB" />
                <SortedAscendingHeaderStyle BackColor="#6D95E1" />
                <SortedDescendingCellStyle BackColor="#E9EBEF" />
                <SortedDescendingHeaderStyle BackColor="#4870BE" />
            </asp:GridView>
        </td>
        <td>
            <asp:Button ID="btnDataSet" runat="server" BackColor="#CCCCCC" Font-Bold="True"
                ForeColor="Maroon" onclick="btnDataSet_Click" Text="ShowDataSet" />
            <asp:GridView ID="GridView2" runat="server" CellPadding="4" ForeColor="#333333"
                GridLines="None">
                <AlternatingRowStyle BackColor="White" />
                <EditRowStyle BackColor="#7C6F57" />
                <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
                <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
                <RowStyle BackColor="#E3EAEB" />
                <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
                <SortedAscendingCellStyle BackColor="#F8FAFA" />
                <SortedAscendingHeaderStyle BackColor="#246B61" />
                <SortedDescendingCellStyle BackColor="#D4DFE1" />
                <SortedDescendingHeaderStyle BackColor="#15524A" />
            </asp:GridView>
        </td>
        <td>
            <asp:Button ID="btnDataSet2" runat="server" BackColor="#CCCCCC"
                Font-Bold="True" ForeColor="Maroon" onclick="btnDataSet2_Click"
                Text="ShowDatSet2" />
            <asp:GridView ID="GridView3" runat="server" BackColor="White"
                BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px" CellPadding="4">
                <FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
                <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
                <PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />
                <RowStyle BackColor="White" ForeColor="#330099" />
                <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />
                <SortedAscendingCellStyle BackColor="#FEFCEB" />
                <SortedAscendingHeaderStyle BackColor="#AF0101" />
                <SortedDescendingCellStyle BackColor="#F6F0C0" />
                <SortedDescendingHeaderStyle BackColor="#7E0000" />
            </asp:GridView>
        </td>
    </tr>
</table>
</form>

DataSet.aspx.cs File
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DemoClassExample
{
    public partial class DataSet : System.Web.UI.Page
    {
        //string StrCon = "Server=PRADEEP-HP;Database=Decofloore2;User Id=User_006;password=user006";
        protected void Page_Load(object sender, EventArgs e)
        {
        }
        protected void tnRegId_Click(object sender, EventArgs e)
        {
            string StrCon = ConfigurationManager.AppSettings["ConnectionString"];
            SqlConnection Sqlcon = new SqlConnection(StrCon);
            string SqlQuery = "Select Name,Address,MobileNo,Course,Sex,EmailID,DOB from ShowOnePage where RegistrationId='" + txtRegId.Text + "'";
            SqlCommand Sqlcomm = new SqlCommand(SqlQuery, Sqlcon);
            Sqlcon.Open();
            SqlDataReader dr;
            dr = Sqlcomm.ExecuteReader(CommandBehavior.CloseConnection);
            if (dr.Read())
            //SqlDataAdapter da = new SqlDataAdapter(Sqlcomm);
            //DataTable dt = new DataTable("ShowOnePage");
            //DataSet ds = new DataSet();
            //if(dt.Rows.Count>0)
            {
                txtName.Text = dr["Name"].ToString();
                txtAddress.Text = dr["Address"].ToString();
                txtMobiNo.Text = dr["MobileNo"].ToString();
                txtCourse.Text = dr["Course"].ToString();
                ddlGender.SelectedItem.Text = dr["Sex"].ToString();
                txtEmailId.Text = dr["EmailID"].ToString();
            }
        }
        protected void btnSave_Click(object sender, EventArgs e)
        {
            string StrCon = ConfigurationManager.AppSettings["ConnectionString"];
            SqlConnection SqlCon = new SqlConnection(StrCon);
            string SqlQuery = null;
            SqlQuery = "Insert into ShowOnePage(RegistrationId,Name,Address,MobileNo,Course,Sex,EmailID,DOB) values"
                + "('" + txtRegId.Text.Trim() + "','" + txtName.Text.Trim() + "','" + txtAddress.Text.Trim() + "',"
            + "" + txtMobiNo.Text.Trim() + ",'" + txtCourse.Text.Trim() + "','" + ddlGender.SelectedItem.Text + "','" + txtEmailId.Text.Trim() + "','"+txtDOB.TextMode.ToString()+"')";
            //SqlQuery = "Insert into Employee(Name,Address,Age,DateOfBirth,RegistrationDate) values('" + txtName.Text + "','" + txtAddress.Text + "'," + txtAge.Text + ",'" + dateTimePicker1.Value.ToString() + "','" + dateTimePicker2.Value.ToString() + "')";
            //SqlQuery = "Insert into ShowOnePage(Name,Address,Age) values('" + txtName.Text + "','" + txtAddress.Text + "'," + txtAge.Text + ")";
            SqlCommand SqlComm = new SqlCommand(SqlQuery, SqlCon);
            SqlCon.Open();
            //int RecordInserted;
            //RecordInserted = SqlComm.ExecuteNonQuery();
            //if (RecordInserted > 0)
             SqlDataAdapter da = new SqlDataAdapter(SqlComm);
            DataTable dt = new DataTable("ShowOnePage");
            if(dt.Rows.Count>0)
            {
                Timer1.Enabled = true;
                lblMsg.Text = "Insert Record Successfully";
            }
            else
            {
                lblMsg.Text = "Records are not Inserted";
            }
        }
        protected void btnUpdate_Click(object sender, EventArgs e)
        {
            string StrCon = ConfigurationManager.AppSettings["ConnectionString"];
            SqlConnection SqlCon = new SqlConnection(StrCon);
            string StrSql = "Update ShowOnePage set Name='" + txtName.Text.Trim() + "',Address='" + txtAddress.Text + "',"
            +"MobileNo=" + txtMobiNo.Text + ",Course='" + txtCourse.Text + "',Sex='" + ddlGender.SelectedItem.Text + "',"
            +"EmailID='" + txtEmailId.Text + "',DOB='"+txtDOB.TextMode.ToString()+"' where RegistrationId='" + txtRegId.Text.Trim() + "'";
            SqlCommand SqlCom = new SqlCommand(StrSql, SqlCon);
            SqlCon.Open();
            //int RowAffected;
            //RowAffected = SqlCom.ExecuteNonQuery();
            //if (RowAffected > 0)
            SqlDataAdapter da = new SqlDataAdapter(SqlCom);
            DataTable dt = new DataTable("ShowOnePage");
            if (dt.Rows.Count > 0)
            {
                Timer1.Enabled = true;
                lblMsg.Text = "Update Record Successfully";
            }
            else
            {
                lblMsg.Text = "Record are not Updated";
            }
        }
        protected void btnDelete_Click(object sender, EventArgs e)
        {
            string StrCon = ConfigurationManager.AppSettings["ConnectionString"];
            SqlConnection SqlCon = new SqlConnection(StrCon);
            string StrSql = "Delete from ShowOnePage where RegistrationId='" + txtRegId.Text.Trim() + "'";
            SqlCommand SqlCom = new SqlCommand(StrSql, SqlCon);
            SqlCon.Open();
            //int RowAffected;
            //RowAffected = SqlCom.ExecuteNonQuery();
            //if (RowAffected > 0)
            SqlDataAdapter da = new SqlDataAdapter(SqlCom);
            DataTable dt = new DataTable("ShowOnePage");
            if (dt.Rows.Count > 0)
            {
                lblMsg.Text = "Delete Record Successfully";
            }
            else
            {
                lblMsg.Text = "Record are not Deleted";
            }
        }
        protected void btnReset_Click(object sender, EventArgs e)
        {
            txtCourse.Text = "";
            txtAddress.Text = "";
            txtEmailId.Text = "";
            txtMobiNo.Text = "";
            txtName.Text = "";
            txtRegId.Text = "";
            ddlGender.SelectedItem.Text = "";
            lblMsg.Text = "";
        }
        protected void btnShow_Click(object sender, EventArgs e)
        {
            string StrCon = ConfigurationManager.AppSettings["ConnectionString"];
            SqlConnection SqlCon = new SqlConnection(StrCon);
            string SqlQuery = "Select * from ShowOnePage";
            SqlCommand Sqlcomm = new SqlCommand(SqlQuery, SqlCon);
            SqlCon.Open();
            SqlDataAdapter da = new SqlDataAdapter(Sqlcomm);
            //da.SelectCommand = new SqlCommand(SqlQuery, StrCon);
            DataTable dt = new DataTable("ShowOnePage");
            da.Fill(dt);
            SqlCon.Close();
            GridView1.DataSource = dt;
            GridView1.DataBind();
            dt.Dispose();
            Sqlcomm.Dispose();
            SqlCon.Dispose();
        }
        protected void btnDataSet_Click(object sender, EventArgs e)
        {
            string StrCon = ConfigurationManager.AppSettings["ConnectionString"];
            SqlConnection SqlCon = new SqlConnection(StrCon);
            string SqlQuery = "Select * from ShowOnePage";
            SqlDataAdapter da = new SqlDataAdapter(SqlQuery, SqlCon);
            //DataSet ds = new DataSet();
            DataTable dt = new DataTable("ShowOnePage");
            da.Fill(dt);
            GridView2.DataSource = dt;
            GridView2.DataBind();

            dt.Dispose();
            SqlCon.Dispose();
        }
        protected void btnDataSet2_Click(object sender, EventArgs e)
        {
            string StrCon = ConfigurationManager.AppSettings["ConnectionString"];
            SqlConnection SqlCon = new SqlConnection(StrCon);
            string SqlQuery = "Select * from ShowOnePage";
            SqlCommand SqlComm = new SqlCommand(SqlQuery, SqlCon);
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(SqlComm);
            SqlCon.Open();
            //DataTable dt = new DataTable("ShowOnePage");
            //da.Fill();
            GridView2.DataSource = ds;
            GridView2.DataBind();

            //dt.Dispose();
            SqlCon.Dispose();
        }
        protected void Timer1_Tick(object sender, EventArgs e)
        {
            Timer1.Enabled = false;
            Response.Redirect("DataSet.aspx");
        }
    }
}

web.config File
<configuration>
<appSettings>
    <add key="ConnectionString" value="Server=PRADEEP-HP;Database=Decofloore2;User Id=User_006;password=user006"/>
  </appSettings>

Share this

0 comments

Post a Comment

Subscribe to: Post Comments (Atom)