C#

Insert, Select and Delete Data With Stored Proc Using XSD (Typed Dataset)

Introduction

Insert Data using XSD

Question: What is Insert Data with Stored Procedure using XSD (Typed Dataset)?

In simple terms "It provides the flexibility to Insert data with a Stored Procedure using a dataset".

Step 1: Create a new WebForm project:

WebForm-Project.png

Step 2: The employee dataset looks like this:

SQL-Dataset.png

Step 3: The complete code of WebForm1.aspx looks like this:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="SPInsertXSDApp.WebForm1" %> 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <center>
            <table>
                <tr>
                    <td>
                        <asp:Label ID="Label1" runat="server" Text="Insert with Stored Proc using XSD" Font-Bold="true"></asp:Label>
                    </td>
                </tr>
                <tr>
                    <td align="center" colspan="2">
                        <asp:GridView ID="GridView1" runat="server" BackColor="#CCCCCC" BorderColor="#999999"
                            BorderStyle="Solid" BorderWidth="3px" CellPadding="4" CellSpacing="2" ForeColor="Black"
                            OnLoad="Page_Load">
                            <FooterStyle BackColor="#CCCCCC" />
                            <HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
                            <PagerStyle BackColor="#CCCCCC" ForeColor="Black" HorizontalAlign="Left" />
                            <RowStyle BackColor="White" />
                            <SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
                            <SortedAscendingCellStyle BackColor="#F1F1F1" />
                            <SortedAscendingHeaderStyle BackColor="#808080" />
                            <SortedDescendingCellStyle BackColor="#CAC9C9" />
                            <SortedDescendingHeaderStyle BackColor="#383838" />
                        </asp:GridView>
                    </td>
                </tr>
            </table>
            <br />
            <br />
            <table>
                <tr>
                    <td>
                        <asp:Label ID="Label2" runat="server" Text="Please Enter FirstName: " ForeColor="Brown"
                            Font-Bold="true" Font-Italic="true"></asp:Label>
                    </td>
                    <td>
                        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                        <asp:Label ID="Label3" runat="server" Text="Please Enter LastName: " ForeColor="Brown"
                            Font-Bold="true" Font-Italic="true"></asp:Label>
                    </td>
                    <td>
                        <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                        <asp:Label ID="Label4" runat="server" Text="Please Enter Age: " ForeColor="Brown"
                            Font-Bold="true" Font-Italic="true"></asp:Label>
                    </td>
                    <td>
                        <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td colspan="2" align="center">
                        <asp:Button ID="Button1" runat="server" Text="Insert Data" OnClick="Button1_Click"
                            BackColor="Orange" Font-Bold="true" /><br />
                        <br />
                    </td>
                </tr>
            </table>
            <br />
            <br />
            <table>
                <tr>
                    <td colspan="3">
                        <asp:Label ID="Label5" runat="server" Font-Bold="true"></asp:Label>
                    </td>
                </tr>
            </table>
        </center>
    </div>
    </form>
</body>
</html>

Step 4: The complete code of WebForm1.aspx.cs looks like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using SPInsertXSDApp.EmployeeDSTableAdapters;
namespace SPInsertXSDApp
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            TextBox1.Focus();
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(TextBox1.Text) || string.IsNullOrEmpty(TextBox2.Text) ||
                string.IsNullOrEmpty(TextBox3.Text))
            {
                Label5.Text = "Please Enter Some Values";
                Label5.ForeColor = System.Drawing.Color.Red;
            }
            else
            {
                objQueryAdapter.InsertData(TextBox1.Text, TextBox2.Text, int.Parse(TextBox3.Text));
                EmployeeDS.SelectDataDataTable objEmployeeTable = objAdapter.GetData();
                var query = from r in objEmployeeTable select new { FirstName = r.FirstName, LastName = r.LastName, Age = r.Age };
                GridView1.DataSource = query;
                GridView1.DataBind();
                Label5.Text = "Record Inserted Successfully";
                Label5.ForeColor = System.Drawing.Color.Green;
                TextBox1.Text = string.Empty;|
                TextBox2.Text = string.Empty;
                TextBox3.Text = string.Empty;
            }
        }
        #region Instance MembersQueriesTableAdapter objQueryAdapter = new QueriesTableAdapter();
        EmployeeDSTableAdapters.SelectDataTableAdapter objAdapter = new EmployeeDSTableAdapters.SelectDataTableAdapter();
        #endregion
    }
}

Step 5: The output of the application looks like this:

Insert-Data-using-XSD1.png

Step 6: The data inserted output of the application looks like this:

Insert-Data-using-XSD2.png

Select Data using XSD

Question: What is Select Data with Stored Procedure using XSD (Typed Dataset)?

In simple terms "It provides the flexibility to select data with a Stored Procedure using a dataset".

Step 1: Create a new WebForm project.

Step 2: The employee dataset looks like this:

SQL-Dataset1.png

Step 3: The complete code of WebForm1.aspx looks like this:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="SPSelectXSDApp.WebForm1" %> 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <style type="text/css">
        .grid
        {
            margin-top: 50px;
        }
    </style>
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <center>
        <div>
            <table>
                <tr>
                    <td colspan="2" align="center">
                        <asp:Label ID="Label1" runat="server" Text="Select Data with Stored Proc using XSD (DataSet)"
                            Font-Bold="true" Font-Size="Large" Font-Names="Verdana" ForeColor="Maroon"></asp:Label>
                    </td>
                </tr>
                <tr>
                    <td colspan="2" align="center">
                        <asp:Button ID="Button1" runat="server" Text="Select Data" Font-Names="Verdana" Width="213px"
                            BackColor="Orange" Font-Bold="True" OnClick="Button1_Click" />
                    </td>
                </tr>
                <tr>
                    <td colspan="2" align="center">
                        <asp:GridView ID="GridView1" runat="server" CssClass="grid" BackColor="LightGoldenrodYellow"
                            BorderColor="Tan" BorderWidth="1px" CellPadding="2" ForeColor="Black" GridLines="None">
                            <AlternatingRowStyle BackColor="PaleGoldenrod" />
                            <FooterStyle BackColor="Tan" />
                            <HeaderStyle BackColor="Tan" Font-Bold="True" />
                            <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" />
                            <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
                            <SortedAscendingCellStyle BackColor="#FAFAE7" />
                            <SortedAscendingHeaderStyle BackColor="#DAC09E" />
                            <SortedDescendingCellStyle BackColor="#E1DB9C" />
                            <SortedDescendingHeaderStyle BackColor="#C2A47B" />
                        </asp:GridView>
                    </td>
                </tr>
            </table>
        </div>
    </center>
    </form>
</body>
</html>

Step 4: The complete code of WebForm1.aspx.cs looks like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace SPSelectXSDApp
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            Employee.SelectDataDataTable objEmployeeSP = objAdapter.GetData();
            var query = from r in objEmployeeSP select new { FirstName = r.FirstName, LastName = r.LastName, Age = r.Age };
            GridView1.DataSource = query;
            GridView1.DataBind();
        }
        #region Instance MembersEmployeeTableAdapters.SelectDataTableAdapter objAdapter = new EmployeeTableAdapters.SelectDataTableAdapter();
        #endregion
    }
}

Step 5: The output of the application looks like this:

Select-Data-using-XSD1.png

Step 6: The data selected output of the application looks like this:

Select-Data-using-XSD2.png

Delete Data using XSD

Question: What is Delete Data with Stored Procedure using XSD (Typed Dataset)?

In simple terms "It provides flexibility to Delete data with a Stored Procedure using a dataset".

Step 1: Create a new WebForm project.

Step 2: The employee dataset looks like this:

SQL-Dataset2.png

Step 3: The complete code of WebForm1.aspx looks like this:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="SPDeleteXSDApp.WebForm1" %> 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <style type="text/css">
        .grid
        {
            margin-top: 50px;
        }
    </style>
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <center>
        <div>
            <table>
                <tr>
                    <td colspan="2" align="center">
                        <asp:Label ID="Label1" runat="server" Text="Delete Data with Stored Proc using XSD (DataSet)"
                            Font-Bold="true" Font-Size="Large" Font-Names="Verdana" ForeColor="Maroon"></asp:Label>
                    </td>
                </tr>
                <tr>
                    <td>
                        <asp:Label ID="Label2" runat="server" Text="Please Enter Employee Id" ForeColor="Brown"
                            Font-Bold="true" Font-Size="Medium" Font-Names="Verdana"></asp:Label>
                    </td>
                    <td>
                        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td colspan="2" align="center">
                        <asp:Button ID="Button1" runat="server" Text="Delete Data" Font-Names="Verdana" Width="213px"
                            BackColor="Orange" Font-Bold="True" OnClick="Button1_Click" />
                    </td>
                </tr>
                <tr>
                    <td colspan="2" align="center">
                        <asp:GridView ID="GridView1" runat="server" CssClass="grid" BackColor="LightGoldenrodYellow"
                            BorderColor="Tan" BorderWidth="1px" CellPadding="2" ForeColor="Black" GridLines="None">
                            <AlternatingRowStyle BackColor="PaleGoldenrod" />
                            <FooterStyle BackColor="Tan" />
                            <HeaderStyle BackColor="Tan" Font-Bold="True" />
                            <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" />
                            <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
                            <SortedAscendingCellStyle BackColor="#FAFAE7" />
                            <SortedAscendingHeaderStyle BackColor="#DAC09E" />
                            <SortedDescendingCellStyle BackColor="#E1DB9C" />
                            <SortedDescendingHeaderStyle BackColor="#C2A47B" />
                        </asp:GridView>
                    </td>
                </tr>
                <tr>
                    <td colspan="2" align="center">
                        <asp:Label ID="Label3" runat="server" Font-Bold="true" Font-Size="Medium" Font-Names="Verdana"></asp:Label>
                    </td>
                </tr>
            </table>
        </div>
    </center>
    </form>
</body>
</html>

Step 4: The complete code of WebForm1.aspx.cs looks like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using SPDeleteXSDApp.EmployeeDSTableAdapters;
namespace SPDeleteXSDApp
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            TextBox1.Focus();
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(TextBox1.Text))
            {
                Label3.Text = "Please Enter Id Value";
                Label3.ForeColor = System.Drawing.Color.Red;
            }
            else
            {
                objQueryAdapter.DeleteData(int.Parse(TextBox1.Text));
                EmployeeDS.SelectDataDataTable objEmployeeTable = objAdapter.GetData();
                var query = from r in objEmployeeTable select new
                { FirstName = r.FirstName, LastName = r.LastName, Age = r.Age };
                GridView1.DataSource = query;
                GridView1.DataBind();
                Label3.Text = "Record Deleted Successfully";
                Label3.ForeColor = System.Drawing.Color.Green;
                TextBox1.Text = string.Empty;
            }
        }
        #region Instance MembersQueriesTableAdapter objQueryAdapter = new QueriesTableAdapter();
        EmployeeDSTableAdapters.SelectDataTableAdapter objAdapter = new EmployeeDSTableAdapters.SelectDataTableAdapter();
        #endregion
    }
}

Step 5: The output of the application looks like this:

Delete-Data-using-XSD1.png

Step 6: The data deleted output of the application looks like this:

Delete-Data-using-XSD2.png

I hope this article is useful for you. I look forward for your comments and feedback. Thanks Vijay Prativadi

Up Next
    Ebook Download
    View all
    Diving Into OOP
    Read by 5.3k people
    Download Now!
    Learn
    View all
    MVC Corporation is consulting and IT services based company.
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs