Master in ASP.Net And Sql: January 2012

Tuesday, 31 January 2012

Date Validation in Asp.Net to Retrive Data from Database

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

<%@ 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>
</head>
<body>
    <form id="form1" runat="server">
    <div>

        <asp:Label ID="Label1" runat="server" Text="Date"></asp:Label>
        <asp:TextBox ID="txtdate1" runat="server"></asp:TextBox>
        <asp:CalendarExtender ID="txtdate1_CalendarExtender" runat="server"
            Enabled="True" TargetControlID="txtdate1">
        </asp:CalendarExtender>
        <br />
        <asp:Label ID="Label2" runat="server" Text="Sale"></asp:Label>
        <asp:TextBox ID="txtdate2" runat="server"></asp:TextBox>


        <br />
        <br />
        <asp:Button ID="btn_Insert" runat="server" onclick="btn_Insert_Click"
            Text="Insert" />

        <asp:Button ID="btn_grid" runat="server" Text="Gridfill"
            onclick="btn_grid_Click" />
        <br />
        <br />

        <br />
        <asp:GridView ID="GridView1" runat="server" BackColor="White"
            BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px" CellPadding="4"
            onrowcommand="GridView1_RowCommand" onrowediting="GridView1_RowEditing">
            <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" />

            <Columns>
            <asp:TemplateField>
            <ItemTemplate>
                <asp:LinkButton ID="LinkButton1" runat="server" CommandName="Edit" Text='<%#Eval("ID")%>' CommandArgument='<%#Eval("ID")%>' />
            </ItemTemplate>
            </asp:TemplateField>
            </Columns>
        </asp:GridView>
        <br />
        <br />
        <asp:Label ID="Label3" runat="server" Text="From"></asp:Label>
        <asp:TextBox ID="TxtFromDate" runat="server"></asp:TextBox>

        <asp:CalendarExtender ID="TxtFromDate_CalendarExtender" runat="server"
            Enabled="True" TargetControlID="TxtFromDate">
        </asp:CalendarExtender>

        <asp:Label ID="Label4" runat="server" Text="To"></asp:Label>
        <asp:TextBox ID="Txttodate" runat="server"></asp:TextBox>

        <asp:CalendarExtender ID="Txttodate_CalendarExtender" runat="server"
            Enabled="True" TargetControlID="Txttodate">
        </asp:CalendarExtender>
        <asp:Button ID="btn_Retrive" runat="server" Text="Show"
            onclick="btn_Retrive_Click" />

        <br />
        <asp:GridView ID="GridView2" runat="server" BackColor="White"
            BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3">
            <FooterStyle BackColor="White" ForeColor="#000066" />
            <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
            <RowStyle ForeColor="#000066" />
            <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
            <SortedAscendingCellStyle BackColor="#F1F1F1" />
            <SortedAscendingHeaderStyle BackColor="#007DBB" />
            <SortedDescendingCellStyle BackColor="#CAC9C9" />
            <SortedDescendingHeaderStyle BackColor="#00547E" />
        </asp:GridView>
        <br />
        <asp:Label ID="Label5" runat="server" Text="Label"></asp:Label>
    </div>
    <asp:ScriptManager ID="ScriptManager1" runat="server">
    </asp:ScriptManager>
    </form>
</body>
</html>
Design :-

RunTime Design

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;
using System.Collections;
using System.Globalization;

public partial class DateDifference : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection("server=SWASH-DBS\\SWASHSQLINT;database=kenCampus;uid=ken;pwd=kc@2011");
    SqlDataAdapter da;
    SqlCommand cmd;
    DataTable dt;
    string fromdate = "";
    string todate = "";
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Page.IsPostBack == false)
        {
            con.Open();
        }
    }
    protected void btn_Insert_Click(object sender, EventArgs e)
    {
        string ins = "Insert into Demo1 values('"+txtdate1.Text+"','"+txtdate2.Text+"')";
        if (con.State == ConnectionState.Open)
        {
            con.Close();
        }
        con.Open();
        cmd = new SqlCommand(ins, con);
        cmd.ExecuteNonQuery();
    }
    protected void btn_grid_Click(object sender, EventArgs e)
    {
        string sel = "select ID,convert(varchar(20),Date1,106) as Date,Sale from Demo1";//Example date retrive [12/Jan/2012]
        if (con.State == ConnectionState.Open)
        {
            con.Close();
        }
        con.Open();
        SqlDataAdapter da = new SqlDataAdapter(sel, con);
        dt = new DataTable();
        da.Fill(dt);
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName == "Edit")
        {
            string se = "Select Date1,Sale from Demo1 where ID='"+int.Parse(e.CommandArgument.ToString())+"'";
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
            con.Open();
            SqlDataAdapter da = new SqlDataAdapter(se, con);
            dt = new DataTable();
            da.Fill(dt);
            txtdate1.Text = dt.Rows[0]["Date1"].ToString();
            txtdate2.Text = dt.Rows[0]["Sale"].ToString();
        }

    }
    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {

    }
    private bool ValidateDate()
    {

        if (TxtFromDate.Text != "")
        {
            fromdate = string.Format("{0:yyyy/MM/dd}", TxtFromDate.Text);
        }
        if (Txttodate.Text == "")
        {
            DateTime dt = DateTime.Now.Date;
            //todate = dt.ToString("dd/MM/yyyy");
            todate = dt.ToString("yyyy/MM/dd");
        }
        else
        {
            todate = string.Format("{0:yyyy/MM/dd}", Txttodate.Text);
        }


        return true;

    }
    protected void btn_Retrive_Click(object sender, EventArgs e)
    {
        if (ValidateDate() == true)
        {
            string sel = "select convert(varchar(20),Date1,106) as Date,Sale from Demo1 where date1 >= '" + fromdate + "' and date1 <= '" + todate + "'";//Example date retrive [12/Jan/2012]
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
            con.Open();
            SqlDataAdapter da = new SqlDataAdapter(sel, con);
            dt = new DataTable();
            da.Fill(dt);
            if (dt.Rows.Count > 0)
            {

                GridView2.DataSource = dt;
                GridView2.DataBind();
            }
            else
            {
                Label5.Text = "From Date Should Not be less than todate";
            }
        }
    }
}

Table Design

Monday, 30 January 2012

insert Date Using spilt() Function and retrive Date in different Date,Month and Year Format

Source Code
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Dateand spilt.aspx.cs" Inherits="Dateand_spilt" %>
<!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>
        <asp:Label ID="Label1" runat="server" Text="Roll"></asp:Label>
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />
        <asp:Label ID="Label2" runat="server" Text="Name"></asp:Label>
        <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br />
        <asp:Label ID="Label3" runat="server" Text="DOB"></asp:Label>
        <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>

        <br />
        <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Insert" />
        <asp:Button ID="Button2" runat="server" Text="Year/Month/Date"
            onclick="Button2_Click" />
        <asp:Button ID="Button3" runat="server" onclick="Button3_Click"
            Text="Date/Month/Year" />
        <asp:Button ID="Button4" runat="server" onclick="Button4_Click"
            Text="Month/Day/Year/Time" />
        <br />
        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
        <br />

    </div>

    </form>
</body>
</html>

Page Design

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;
using System.Collections;

public partial class Dateand_spilt : System.Web.UI.Page
{
    Class1 obj;
    SqlConnection con = new SqlConnection("server=Satya\\12;database=Linku;uid=li;pwd=satya2011");
    SqlDataAdapter da;
    SqlCommand cmd;
    DataTable dt;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Page.IsPostBack == false)
        {
            con.Open();
        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        int x = 0;
        string[] date = TextBox3.Text.Split(new char[] { '/' });//Sending date after Split
        string Newdate = date[1] + "/" + date[0] + "/" + date[2];
        string query = "Insert into Demo1 values('"+TextBox1.Text+"','"+TextBox2.Text+"','"+Newdate+"')";
        if (con.State == ConnectionState.Open)
        {
            con.Close();
        }
        con.Open();
        cmd = new SqlCommand(query,con);
        cmd.ExecuteNonQuery();

        if (x < 0)
        {
            Response.Write("Data inserted Successfully");
        }
        else
        {
            Response.Write("Sorry Not Successfully");
        }

    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        string sel = "select Roll,Name,CONVERT(varchar(20), DOB, 111) AS VoucherDate from Demo1";//Example date retrive [Year/Month/Date]
        if (con.State == ConnectionState.Open)
        {
            con.Close();
        }
        con.Open();
        SqlDataAdapter da = new SqlDataAdapter(sel,con);
        dt = new DataTable();
        da.Fill(dt);
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
    protected void Button3_Click(object sender, EventArgs e)
    {
        string sel = "select Roll,Name,CONVERT(varchar(20), DOB, 103) AS VoucherDate from Demo1";//Example date retrive [Day/Month/Year]
        if (con.State == ConnectionState.Open)
        {
            con.Close();
        }
        con.Open();
        SqlDataAdapter da = new SqlDataAdapter(sel, con);
        dt = new DataTable();
        da.Fill(dt);
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
    protected void Button4_Click(object sender, EventArgs e)
    {
        string sel = "select * from Demo1";//Example date retrive [Month/Day/Year/Time]
        if (con.State == ConnectionState.Open)
        {
            con.Close();
        }
        con.Open();
        SqlDataAdapter da = new SqlDataAdapter(sel, con);
        dt = new DataTable();
        da.Fill(dt);
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
}

Table Design

Tuesday, 17 January 2012

At a time insert Data in two table with the help of Transction and Class File Methods

SourceFile Code

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

<!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>

        <asp:Label ID="Label1" runat="server"
            style="z-index: 1; left: 53px; top: 54px; position: absolute" Text="Roll"></asp:Label>
        <asp:Label ID="Label2" runat="server"
            style="z-index: 1; left: 44px; top: 83px; position: absolute" Text="Name"></asp:Label>
        <asp:Label ID="Label3" runat="server"
            style="z-index: 1; left: 40px; top: 120px; position: absolute"
            Text="Address"></asp:Label>

    <asp:TextBox ID="txtname" runat="server"
        style="z-index: 1; left: 105px; top: 97px; position: absolute"></asp:TextBox>
    <asp:TextBox ID="txtroll" runat="server"
        style="z-index: 1; left: 98px; top: 55px; position: absolute; right: 1069px;"></asp:TextBox>

    </div>
    <asp:TextBox ID="txtaddress" runat="server"
        style="z-index: 1; left: 99px; top: 118px; position: absolute"></asp:TextBox>
    <asp:Button ID="btnsave" runat="server"
        style="z-index: 1; left: 106px; top: 167px; position: absolute"
        Text="Save" onclick="btnsave_Click" />
    <p>

    </p>
    </form>
</body>
</html>

ClassFileCoading

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

/// <summary>
/// Summary description for Class1
/// </summary>
public class Class1
{
    private SqlConnection con;
    public Class1()
    {
        con = new SqlConnection(@"server=Satya\12;database=linku;uid=li;pwd=satya1");
    }
    public void ExecuteQuery(string sp,Hashtable ht)
    {
        try
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
            con.Open();
            SqlCommand cmd = new SqlCommand(sp, con);
            cmd.CommandType = CommandType.StoredProcedure;
            foreach (DictionaryEntry p in ht)
            {
                cmd.Parameters.Add("@" + (string)p.Key, p.Value);
            }
            cmd.ExecuteNonQuery();
            con.Close();
            cmd.Dispose();
        }
        catch (Exception ex)
        {
            string errMsg = ex.Message;
        }
        finally
        {

            if (this.con.State == ConnectionState.Open)
                con.Close();
            if (this.con != null) this.con = null;
            if (this.con != null) this.con = null;
        }
    }
}

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.Collections;

public partial class Transction : System.Web.UI.Page
{
    Class1 obj = new Class1();
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnsave_Click(object sender, EventArgs e)
    {
        obj = new Class1();
        Hashtable ht = new Hashtable();
        ht.Add("roll", txtroll.Text);
        ht.Add("Name ", txtname.Text);
        ht.Add("address", txtaddress.Text);

       obj.ExecuteQuery("TrDemo", ht);
    }
}

Table Design

Demo1 :-

Demo2 :-

Database StoreProcedure

Createprocedure TrDemo
(@roll int,
@name varchar(50),
@address varchar(50)
)
as
BEGIN
BEGIN TRY
BEGIN TRANSACTION
Insert into Demo1 Values(@roll,@name)
Insert into Demo3 Values(@roll,@name,@address)
print 'success'
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT 'ERROR'
END CATCH
END
Design

Sql TRANSACTION (Two Table Here how we Insert Data With the help of Transction and commit and RollBack)

ALTER procedure TrDemo

BEGIN

BEGIN TRY

BEGIN TRANSACTION

Insert into Demo1 Values(12,'RAMA')

Insert into Demo3 Values(12,'RAMA','SAHID NAGAR')

print 'success'

COMMIT TRANSACTION

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION

PRINT 'ERROR'

END CATCH

END

Table Demo1

Table Demo3