Insert Data into Data Base Table using Class File(Properties) and Stored Procedure

Step 1:CREATE A CLASS FILE:   Demo.cs

public class Demo
{

public int roll{get;set;}
        public int age{get;set;}
        public DateTime InfDate{get;set;}

}
Step 2:Create Stored Procedure:

Create procedure fms.KC_Test
(
  @roll int,
  @age int,
  @InfDate date
  )
as
begin

insert into FMS_Demo(roll,age,InfDate) values(@roll,@age,@InfDate)
end

Step 3:Create Table:
 command:
Create Table FMS_Demo(roll int,age int,InfDate DateTime)

Step 4: Coding Page:


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;

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

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        Demo dm = new Demo();
        dm.roll = int.Parse( TextBox1.Text);
        dm.age = int.Parse(TextBox2.Text);
        dm.InfDate = DateTime.Parse(Calendar1.SelectedDate.ToString());
        SqlConnection con = new SqlConnection(@"Data Source=  ABC   ;Initial Catalog=MYCampus;User ID=MY;pwd=My12554");
        con.Open();
        SqlCommand cmd = new SqlCommand("fms.KC_Test", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("roll", dm.roll);
        cmd.Parameters.AddWithValue("age", dm.age);
        cmd.Parameters.AddWithValue("InfDate", dm.InfDate);
        cmd.ExecuteNonQuery();
        con.Close();
        Response.Write("Successfully");
    }
}



Step 5 Design Page


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

<!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 runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
      RollNo  <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
      Age  <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
        <br />
        <br />
       Date <asp:Calendar ID="Calendar1" runat="server"></asp:Calendar>
        <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Button" />
    </div>
    </form>
</body>
</html>

Email and Website Validation Example

  <div>
            <asp:TextBox ID="TxtFax" runat="server" TabIndex="8" MaxLength="25" />
        </div>
        <div>
            <span class="EmptyMessage">
                <asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" ErrorMessage="Enter valid Email."
                    ControlToValidate="TxtEmail" ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"
                    SetFocusOnError="true">*</asp:RegularExpressionValidator></span> E-Mail
            :
        </div>
        <div>
            <asp:TextBox ID="TxtEmail" runat="server" TabIndex="9" MaxLength="50" Width="220" />
            <asp:Label ID="Label1" runat="server" Text="Ex: xyz@abc.com " Font-Bold="True" ForeColor="Red"></asp:Label>
        </div>
        <div>
            <asp:RegularExpressionValidator ID="RegularExpressionValidator2" runat="server" ErrorMessage="Enter valid URL"
                ControlToValidate="TxtWebSite" CssClass="EmptyMessage" SetFocusOnError="true"
                ValidationExpression="^(http(?:s)?\:\/\/[a-zA-Z0-9\-]+(?:\.[a-zA-Z0-9\-]+)*\.[a-zA-Z]{2,6}(?:\/?|(?:\/[\w\-]+)*)(?:\/?|\/\w+\.[a-zA-Z]{2,4}(?:\?[\w]+\=[\w\-]+)?)?(?:\&[\w]+\=[\w\-]+)*)$">*</asp:RegularExpressionValidator>
            Web Site :
        </div>
        <div>
            <asp:TextBox ID="TxtWebSite" runat="server" TabIndex="10" MaxLength="25" Width="220" />
            <asp:Label ID="Label2" runat="server" Text="Ex: http://www.abc.com " Font-Bold="True"
                ForeColor="Red" />
        </div>

(How to Set Tab Index)In Pressing tab key user go to next control

// Means Every control's we set a property which means TabIndex=""
                  <div>
            <asp:TextBox ID="TxtCompanyName" runat="server" TabIndex="1" MaxLength="100" />
        </div>
         <div>
            <asp:TextBox ID="TxtLegalName" runat="server" TabIndex="2" MaxLength="100" />
        </div>  

        <div>
            <asp:TextBox ID="TxtStreet" runat="server" TabIndex="3" MaxLength="100" />
        </div>
             <div>
            <asp:DropDownList ID="DdlCountry" runat="server" TabIndex="4" AutoPostBack="True"
                OnSelectedIndexChanged="DdlCountry_SelectedIndexChanged" />
        </div>   

        <div>
            <asp:DropDownList ID="DdlState" runat="server" TabIndex="5" AutoPostBack="True" OnSelectedIndexChanged="DdlState_SelectedIndexChanged" />
          <div>
            <asp:DropDownList ID="DdlCity" runat="server" TabIndex="6" />
        </div>      

        <div ">
            <asp:TextBox ID="TxtZip" runat="server" TabIndex="6" MaxLength="6" />
                 <div >
            <asp:TextBox ID="TxtPhone" runat="server" TabIndex="7" MaxLength="25" />
        </div>
                <div >
            <asp:TextBox ID="TxtFax" runat="server" TabIndex="8" MaxLength="25" />
        </div>
 

        <div >
            <asp:TextBox ID="TxtEmail" runat="server" TabIndex="9" MaxLength="50" Width="220" />
        </div>
              <div >
            <asp:TextBox ID="TxtWebSite" runat="server" TabIndex="10" MaxLength="25" Width="220" />
                        <div >
            <asp:TextBox ID="TxtSalesTax" runat="server" TabIndex="11" MaxLength="50" />
        </div>
              <div >
            <asp:TextBox ID="TxtTin" runat="server" TabIndex="12" MaxLength="50" />
        </div>
                <div>
            <asp:DropDownList ID="DdlBusinessType" runat="server" TabIndex="13" />
        </div>
   

Show Data In a Gridview and Edit , Update & Delete using Modal Popup

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

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>

<!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 runat="server">
    <title>
   
    </title>
    <style type="text/css">
    .ModalPopUpPanel
        {
            background-color: #FFFFFF;
            border-color: #C2D5EE;
            border-style: double;
            border-width: 15px;
        }
    
       
    </style>
</head>
<body>
    <form id="form1" runat="server">
  
    <asp:ScriptManager ID="ScriptManager1" runat="server">
   
    </asp:ScriptManager>
   
      <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
        onrowcommand="GridView1_RowCommand" BackColor="White"
        BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px" CellPadding="4" >
        <Columns>
       <asp:BoundField HeaderText="LedgerId" DataField="LedgerID" />
        <asp:BoundField HeaderText="LedgerName" DataField="LedgerName" />
        <asp:BoundField HeaderText="Company Id" DataField="CompanyID" />
        <asp:TemplateField >
        <ItemTemplate>
            <asp:LinkButton ID="linkView" runat="server" Text="View" CommandName="View" CommandArgument= '<%# Eval("LedgerID") %>' />
        </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField >
        <ItemTemplate>
            <asp:LinkButton ID="linkView1" runat="server" Text="ADD" CommandName="Add"  />
        </ItemTemplate>
        </asp:TemplateField>
         <asp:TemplateField >
        <ItemTemplate>
           <a href="DisplayPage.aspx?LID=<%# Eval("LedgerID") %>">Edit</a>
        </ItemTemplate>
        </asp:TemplateField>
        </Columns>
          <FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
          <HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
          <PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
          <RowStyle BackColor="White" ForeColor="#003399" />
          <SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
          <SortedAscendingCellStyle BackColor="#EDF6F6" />
          <SortedAscendingHeaderStyle BackColor="#0D4AC4" />
          <SortedDescendingCellStyle BackColor="#D6DFDF" />
          <SortedDescendingHeaderStyle BackColor="#002876" />
        </asp:GridView>
    <asp:Panel ID="PanelModalPopUp" runat="server"  Height="250px" Width="400px" CssClass="ModalPopUpPanel">
          <table width="100%">
             <tr>
              <td>
           <asp:Label ID="Label1" runat="server" Text="LedgerId" />
              </td>
                 <td>
                     <asp:TextBox ID="TextBox1" runat="server" />
                 </td>
             </tr>
             <tr>
              <td>
           <asp:Label ID="Label2" runat="server" Text="LedgerName" />
              </td>
                 <td>
                     <asp:TextBox ID="TextBox2" runat="server" />
                 </td>
             </tr>
             <tr>
              <td>
           <asp:Label ID="Label3" runat="server" Text="CompanyId" />
              </td>
                 <td>
                     <asp:TextBox ID="TextBox3" runat="server" />
                 </td>
             </tr>
             <tr>
             <td>
             <asp:Button ID="btnOk" runat="server" Text="OK" />

             <asp:Button ID="btnUpdate" runat="server" Text="Update" onclick="btnUpdate_Click" />
              <asp:Button ID="btndelete" runat="server" Text="Delete"
                     onclick="btndelete_Click" />
                     <asp:Button ID="Button2" runat="server" Text="Insert" onclick="Button2_Click" />
                       <asp:Button ID="btnreset" runat="server" Text="Reset"
                     onclick="btnreset_Click" />
                 <asp:Label ID="Label4" runat="server" Text="Label"></asp:Label>
             </td>
             </tr>
          </table>
    </asp:Panel>

   <asp:modalpopupextender ID="ModalPopupExtender1" runat="server" CancelControlID="btnOk" TargetControlID="Button1"
    PopupControlID="PanelModalPopUp" BackgroundCssClass="ModalBackgroundCSS"></asp:modalpopupextender>
    <asp:Button ID="Button1" runat="server" Text="Cancel" Style="display: none" />
    
  
    </form>
</body>
</html>

C# Coading
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;

public partial class Modalpopup : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection("server=Satya-DBS\\Satya;database=Linku;uid=li;pwd=li212");
    SqlDataAdapter da;
    SqlCommand com;
    DataSet ds;
    DataTable dt;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            fillGrid();
        }
        Label4.Visible = false;
       
    }
    public void fillGrid()
    {
       
        da = new SqlDataAdapter("Select * from Rakesh", con);
        if (con.State == ConnectionState.Open)
        {
            con.Close();
        }
        con.Open();
        dt = new DataTable();
        da.Fill(dt);
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
  
    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName == "View")
        {
            ModalPopupExtender1.Show();
           
            filldata(int.Parse(e.CommandArgument.ToString()));
        }
      else if (e.CommandName == "Add")
        {
            ModalPopupExtender1.Show();
        }


    }
    public void filldata(int ledgerid)
    {
        da = new SqlDataAdapter("Select * from Rakesh where LedgerID='"+ ledgerid +"'", con);
        if (con.State == ConnectionState.Open)
        {
            con.Close();
        }
        con.Open();
        dt = new DataTable();
        da.Fill(dt);
        TextBox1.Text = dt.Rows[0]["ledgerid"].ToString();
        TextBox2.Text = dt.Rows[0]["LedgerName"].ToString();
        TextBox3.Text = dt.Rows[0]["CompanyID"].ToString();
    }
    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        int x = 0;
        string up="update Rakesh set LedgerName='"+TextBox2.Text+"',CompanyId='"+ TextBox3.Text+"' where LedgerId="+TextBox1.Text+"";
        //da = new SqlDataAdapter("del",con);
        if (con.State == ConnectionState.Open)
        {
            con.Close();
        }
        con.Open();
        com = new SqlCommand(up,con);
       x= com.ExecuteNonQuery();
       if (x > 0)
       {
          Label4.Text="Data Update Successfully";
          fillGrid();
       }
       else
       {
            Label4.Text="Data Not Updated";
       }

    }
    protected void btndelete_Click(object sender, EventArgs e)
    {
        int x = 0;
        string de = "delete from Rakesh where LedgerId="+TextBox1.Text+"";
        if (con.State == ConnectionState.Open)
        {
            con.Close();
        }
        con.Open();
        com = new SqlCommand(de,con);
        x = com.ExecuteNonQuery();
        if (x > 0)
        {
            Label4.Text = "Data Deleted Successfully";
            fillGrid();
        }
        else
        {
            Label4.Text = "Data Not Deleted Successfully";
        }
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        int x = 0;
        string ins = "Insert into Rakesh values("+TextBox1.Text+",'"+TextBox2.Text+"',"+TextBox3.Text+")";
        if (con.State == ConnectionState.Open)
        {
            con.Close();
        }
        con.Open();
        com = new SqlCommand(ins,con);
        x = com.ExecuteNonQuery();
           if (x > 0)
        {
            Label4.Text = "Data Inserted Successfully";
            fillGrid();
        }
        else
        {
            Label4.Text = "Data Not Inserted Successfully";
        }
    }
    protected void btnreset_Click(object sender, EventArgs e)
    {
      
        TextBox1.Text = "";
        TextBox2.Text = "";
        TextBox3.Text = "";
    }
}
Database Table Design

Design
 


Run TimeDesign
when you click in the View Button Modal popup will be open You can edit when uyou want to add data you click in the Add Button OR you want to Edit Data Using Request Query String in Another Page though You Click in the Edit Button Go to Display Page Display Page Coding Are given Below

Display Page Source code

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

<!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 runat="server">
    <title></title>
    <style type="text/css">
        .style1
        {
            width: 100%;
        }
        .style2
        {
            width: 110px;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
   
    </div>
    <table class="style1">
        <tr>
            <td class="style2">
                <asp:Label ID="Label1" runat="server" Text="Ledger ID"></asp:Label>
            </td>
            <td>
                <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td class="style2">
                <asp:Label ID="Label2" runat="server" Text="LedgerName"></asp:Label>
            </td>
            <td>
                <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td class="style2">
                <asp:Label ID="Label3" runat="server" Text="Company ID"></asp:Label>
            </td>
            <td>
                <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td class="style2">
                &nbsp;</td>
            <td>
                <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Update" />
                <asp:Label ID="Label4" runat="server" Text="Label"></asp:Label>
            </td>
        </tr>
        <tr>
            <td class="style2">
                &nbsp;</td>
            <td>
                &nbsp;</td>
        </tr>
    </table>
    </form>
</body>
</html>
C# Coading

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;

public partial class DisplayPage : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection("server=Satya-DBS\\Satya;database=Linku;uid=li;pwd=li12011");
    SqlDataAdapter da;
    SqlCommand com;
    DataSet ds;
    DataTable dt;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            if (Request.QueryString["LID"] != null)
            {
                GetDataByID(int.Parse(Request.QueryString["LID"].ToString()));
            }
        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        int x = 0;
        string up = "update Rakesh set LedgerName='" + TextBox2.Text + "',CompanyId='" + TextBox3.Text + "' where LedgerId=" + TextBox1.Text + "";
        //da = new SqlDataAdapter("del",con);
        if (con.State == ConnectionState.Open)
        {
            con.Close();
        }
        con.Open();
        com = new SqlCommand(up, con);
        x = com.ExecuteNonQuery();
        if (x > 0)
        {
            Label4.Text = "Data Update Successfully";
          
        }
        else
        {
            Label4.Text = "Data Not Updated";
        }

    }
    public void GetDataByID(int Lid)
    {
        da = new SqlDataAdapter("Select * from Rakesh where LedgerId='"+Request.QueryString["LID"].ToString()+"'", con);
        if (con.State == ConnectionState.Open)
        {
            con.Close();
        }
        con.Open();
        dt = new DataTable();
        da.Fill(dt);
        TextBox1.Text = dt.Rows[0]["ledgerid"].ToString();
        TextBox2.Text = dt.Rows[0]["LedgerName"].ToString();
        TextBox3.Text = dt.Rows[0]["CompanyID"].ToString();
     
    }
}

Design

SQL CAST and CONVERT

It converts an expression from one data type to another.
CAST and CONVERT have similar functionality.

SQL CAST and CONVERT Syntax
Using CAST:
CAST ( expression AS data_type )

Using CONVERT:CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Example of SQL Cast and Convert
SQL Cast and Convert - StringSELECT SUBSTRING('CAST and CONVERT', 1, 3)
Return Value = CAS (it get from index 1 to 3)

SELECT CAST('CAST and CONVERT' AS char(3))
Return Value = CAS (it get 3 char only)


SQL Cast and Convert - Date Time
-Converting date time to character data(vachar)
-The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the Without century year(yy).
-Add 100 to a style value to get a four-place year that includes the century year(yyyy).
-Below is example for converting 1 format of date time to different format of date time, so that it can be use in various condition.

Value of current Date Time GETDATE()
SELECT (GETDATE()) = 2007-06-06 23:41:10.153

SELECT CONVERT(varchar,GETDATE(),0)
Return Value = Jun 6 2007 11:07PM
SELECT CONVERT(varchar,GETDATE(),100)
Return Value = Jun 6 2007 11:07PM


SELECT CONVERT(varchar,GETDATE(),1)
Return Value = 06/06/07
SELECT CONVERT(varchar,GETDATE(),101)
Return Value = 06/06/2007


SELECT CONVERT(varchar,GETDATE(),2)
Return Value = 07.06.06
SELECT CONVERT(varchar,GETDATE(),102)
Return Value = 2007.06.06

SELECT CONVERT(varchar,GETDATE(),3)
Return Value = 06/06/07
SELECT CONVERT(varchar,GETDATE(),103)
Return Value = 06/06/2007


SELECT CONVERT(varchar,GETDATE(),4)
Return Value = 06.06.07
SELECT CONVERT(varchar,GETDATE(),104)
Return Value = 06.06.2007

SELECT CONVERT(varchar,GETDATE(),5)
Return Value = 06-06-07
SELECT CONVERT(varchar,GETDATE(),105)
Return Value = 06-06-2007


SELECT CONVERT(varchar,GETDATE(),6)
Return Value = 06 Jun 07
SELECT CONVERT(varchar,GETDATE(),106)
Return Value = 06 Jun 2007


SELECT CONVERT(varchar,GETDATE(),7)
Return Value = Jun 06, 07
SELECT CONVERT(varchar,GETDATE(),107)
Return Value = Jun 06, 2007


SELECT CONVERT(varchar,GETDATE(),8)
Return Value = 23:38:49
SELECT CONVERT(varchar,GETDATE(),108)
Return Value = 23:38:49


SELECT CONVERT(varchar,GETDATE(),9)
Return Value = Jun 6 2007 11:39:17:060PM
SELECT CONVERT(varchar,GETDATE(),109)
Return Value = Jun 6 2007 11:39:17:060PM


SELECT CONVERT(varchar,GETDATE(),10)
Return Value = 06-06-07
SELECT CONVERT(varchar,GETDATE(),110)
Return Value = 06-06-2007


SELECT CONVERT(varchar,GETDATE(),11)
Return Value = 07/06/06
SELECT CONVERT(varchar,GETDATE(),111)
Return Value = 2007/06/06


SELECT CONVERT(varchar,GETDATE(),12)
Return Value = 070606
SELECT CONVERT(varchar,GETDATE(),112)
Return Value = 20070606


SELECT CONVERT(varchar,GETDATE(),13)
Return Value = 06 Jun 2007 23:40:14:577
SELECT CONVERT(varchar,GETDATE(),113)
Return Value = 06 Jun 2007 23:40:14:577


SELECT CONVERT(varchar,GETDATE(),14)
Return Value = 23:40:29:717
SELECT CONVERT(varchar,GETDATE(),114)
Return Value = 23:40:29:717


SELECT CONVERT(varchar,GETDATE(),20)
Return Value = 2007-06-06 23:40:51
SELECT CONVERT(varchar,GETDATE(),120)
Return Value = 2007-06-06 23:40:51


SELECT CONVERT(varchar,GETDATE(),21)
Return Value = 2007-06-06 23:41:10.153
SELECT CONVERT(varchar,GETDATE(),121)
Return Value = 2007-06-06 23:41:10.153


SELECT CONVERT(varchar,GETDATE(),126)
Return Value = 2007-06-06T23:41:10.153


SELECT CONVERT(varchar,GETDATE(),131)
Return Value = 21/05/1428 11:41:10:153PM