Autogenerate the field value in stored procedure

   Generally we auto generate a field to identify a uniqueness when we insert  a record into a table in our code(c#,vb) page and it is use full when it is used by a single user.But it creates a major problem when multiple users are using a program which has to auto generate a field in our code page. Because of  depending on user's efficiency at the time of data entry the auto generate code may mismatch or  through error. So it is advisable to generate a number in stores procedure at the time of insert and then should display to user  that your "Transaction number is:P0001" 

 Table Design :-

Stored Procedure :-

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[PROC_ADD_POLICY_MST]

(@P_POLICY_PLAN varchar(100),
@P_MONTHS decimal(10,2),
@P_MON_INSTL INT,
@P_CREDIT_VAL INT,
@P_INSTL_AMT INT,
@P_YEAR_PLAN INT,
@P_ACTIVE CHAR(1)=null,@P_PLAN_TYPE CHAR(1)=null,
@P_POLICY_CD varchar(20)=null,@P_RET_VALUE varchar(10) OUTPUT)
AS

declare @p_pcd varchar(20)
set @p_pcd=(select right('0000'+ convert(varchar(20),max(substring(policy_cd,2,4))+1),4)as policy_cd from policy_mst)
select @p_pcd='P'+ @p_pcd
set @P_RET_VALUE=@p_pcd
INSERT INTO POLICY_MST VALUES(@P_POLICY_PLAN,@P_MONTHS,@P_MON_INSTL,@P_CREDIT_VAL,@P_INSTL_AMT,@P_YEAR_PLAN,@P_ACTIVE,@P_PLAN_TYPE,@P_RET_VALUE)

return 

VB.Net Code

           con.Open()
            cmd = New SqlCommand
            cmd.Connection = con
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "PROC_ADD_POLICY_MST"
            With cmd.Parameters
                .AddWithValue("@P_POLICY_PLAN", Me.TxtPlan.Text)
                .AddWithValue("@P_MONTHS", Me.NmbMonths.Value)
                .AddWithValue("@P_MON_INSTL", Me.NmbMonInstl.Value)
                .AddWithValue("@P_CREDIT_VAL", Me.NmbCreVal.Value)
                .AddWithValue("@P_INSTL_AMT", Me.NmbInstlAmt.Value)
                .AddWithValue("@P_YEAR_PLAN", Me.numYearPlan.Value)
                .AddWithValue("@P_policy_cd", "")

                If ChkActive.Checked = True Then
                    .AddWithValue("@P_ACTIVE", "Y")
                Else
                    .AddWithValue("@P_ACTIVE", "N")
                End If
                If CmbPlanType.Text = "FD" Then
                    .AddWithValue("@P_PLAN_TYPE", "F")
                ElseIf CmbPlanType.Text = "RD" Then
                    .AddWithValue("@P_PLAN_TYPE", "R")
                Else
                    .AddWithValue("@P_PLAN_TYPE", "M")
                End If
            End With
            cmd.Parameters.Add("@P_RET_VALUE", SqlDbType.VarChar, 50).Direction = ParameterDirection.Output                  cmd.ExecuteNonQuery()  
             con.Close()
            MessageBox.Show("Record Saved and Your Transaction number is:" & Convert.ToString(cmd.Parameters("@p_ret_value").Value))

Getting selected row data from gridview in multiple way

 It is common requirement when we work in a single page means when our input parameters and display records from database in a gridview and we have to get data from selected row to manipulate.

 Table Design

Design View (Source code)

<%@ Page Title="" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeFile="FrmGetGridViewValue.aspx.cs" Inherits="FrmGetGridViewValue" %>

<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" Runat="Server">
    <style type="text/css">
    .style1
    {
        width: 100%;
    }
</style>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" Runat="Server">

    <table class="style1">
    <tr>
        <td>
            &nbsp;</td>
    </tr>
    <tr>
        <td>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
                onselectedindexchanged="GridView1_SelectedIndexChanged"
                onrowcommand="GridView1_RowCommand">
                <Columns>
               
                    <asp:BoundField DataField="id" HeaderText="ID" />
                    <asp:BoundField DataField="name" HeaderText="Name" />
                   <asp:CommandField ShowSelectButton="True" HeaderText="CommandField"/>
                    <asp:TemplateField HeaderText="LinkButton">
                        <ItemTemplate>
                            <asp:LinkButton ID="LnkSelect" runat="server" CommandName="link">Select</asp:LinkButton>
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
        </td>
    </tr>
     <tr>
        <td>
            <asp:Label ID="Label1" runat="server" Text="ID"></asp:Label>
            &nbsp;&nbsp;&nbsp;&nbsp;
            &nbsp;              <asp:TextBox ID="txtid" runat="server"></asp:TextBox>
         </td>
    </tr>
     <tr>
        <td>
            <asp:Label ID="Label2" runat="server" Text="Name"></asp:Label>
            &nbsp;<asp:TextBox ID="txtname" runat="server"></asp:TextBox>
         </td>
    </tr>
</table>

</asp:Content>

C# Code

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;

public partial class FrmGetGridViewValue : System.Web.UI.Page
{
    SqlConnection con;
    SqlDataAdapter da;
    DataSet ds;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack == false)
        {
            con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString);
            string x = "select * from st1";
            da = new SqlDataAdapter(x,con);
            ds = new DataSet();
            da.Fill(ds);
            GridView1.DataSource = ds;
            GridView1.DataBind();
        }
    }
    protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
    {
        txtid.Text = GridView1.SelectedRow.Cells[0].Text;
        txtname.Text = GridView1.SelectedRow.Cells[1].Text;
    }
  
    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName == "link")
        {

            GridViewRow val = (GridViewRow)((LinkButton)e.CommandSource).NamingContainer;
            int RowIndex = val.RowIndex;

            txtid.Text = GridView1.Rows[RowIndex].Cells[0].Text;
            txtname.Text = GridView1.Rows[RowIndex].Cells[1].Text;
        }
    }
}

Insert Multiple Records into Database from GridView

     This is a common concept,  which we use in our project maximum times. User stores records into gridview one after another and save all records from gridview into database at a single push.

Before starting the project we have to design the table according to our requirement

Table  Design

Column	DataType
StudentRoll(PK)	varchar(20)
StudentAge	int
StudentAddress	varchar(50)

Source Code (Design View)


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Multiple datainsert in database by gridview.aspx.cs" Inherits="Multiple_datainsert_in_database_by_gridview" %>

<!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%;
        }
    </style>
</head>
<body>
    <form id="form2" runat="server">
    <div>
 
        <table class="style1">
            <tr>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td style="text-align: right">
    <asp:Label ID="Label1" runat="server" Text="Roll"></asp:Label>
                </td>
                <td>
     
        <asp:TextBox ID="TextBox1" runat="server" ></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td width="40%" style="text-align: right">
    <asp:Label ID="Label2" runat="server" Text="Name" ></asp:Label>
                </td>
                <td width="60%">

 
        <asp:TextBox ID="TextBox2" runat="server" ></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td style="text-align: right">
    <asp:Label ID="Label3" runat="server" Text="Address"></asp:Label>
 
                </td>
                <td>
        <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td>
                  <asp:Button ID="Button1" runat="server" Text="Add" onclick="Button1_Click"/>
    <asp:Button ID="Button2" runat="server" Text="Save" onclick="Button2_Click" />
    <asp:Button ID="Button3" runat="server" Text="Clear" onclick="Button3_Click" />

                    &nbsp;</td>
            </tr>
        </table>

        &nbsp;

    <asp:GridView ID="GridView1" runat="server" >
    </asp:GridView>
    </div>
 
    </form>
</body>
</html>

 C# Code

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 Multiple_datainsert_in_database_by_gridview : System.Web.UI.Page

{

    SqlConnection con = new SqlConnection("Data source=.\\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\\Database.mdf;User Instance=true");

    SqlCommand cmd;

    static int flag = 0;

    static int rowno = 0;

    DataTable dt;

    # region Method

    protected void fillGrid()

    {

        dt = new DataTable();

        dt = (DataTable)ViewState["Datatable"];

        if (dt.Rows.Count > 0)

        {

            GridView1.DataSource = dt;

            GridView1.DataBind();

        }

        else

        {

            GridView1.Dispose();

            GridView1.DataBind();

        }

    }

    public DataTable Createtable()

    {

        dt = new DataTable();

        dt.Columns.Add("Roll", typeof(int));

        dt.Columns.Add("Name", typeof(string));

        dt.Columns.Add("Address", typeof(string));

        ViewState["Datatable"] = dt;

        return dt;

    }

    # endregion

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            Createtable();

        }

    }

    protected void Button1_Click(object sender, EventArgs e)

    {

        dt = new DataTable();

        dt = (DataTable)ViewState["Datatable"];

        DataRow dr;        

        dr = dt.NewRow();

        dr[0] = TextBox1.Text;

        dr[1] = TextBox2.Text;

        dr[2] = TextBox3.Text;

        dt.Rows.Add(dr);    

      

        ViewState["Datatable"] = dt;

        fillGrid();

    }

   

    protected void Button2_Click(object sender, EventArgs e)

    {

        foreach (GridViewRow r in GridView1.Rows)

        {

            int a = int.Parse(r.Cells[0].Text.Trim());

            string b = r.Cells[1].Text.Trim();

            string c = r.Cells[2].Text.Trim();

            SqlCommand cmd = new SqlCommand("Insert into Student values('" + a + "','" + b + "','" + c + "')", con);

            con.Open();

            cmd.ExecuteNonQuery();

            con.Close();

        }

    }

    protected void Button3_Click(object sender, EventArgs e)

    {

        ViewState["Datatable"] = null;       

        GridView1.DataSource = null;

        GridView1.DataBind();     

        Createtable();

    }

}

Working with Global.asax in ASP.net

  It is a Active Server Application file which contains the Application and Session events to keeping the information globally. It is a part of State Management in asp.net that provide to storing data at server side.A website can have only one Global.asax file.

   Step-1

    1. Add new Global.asax file if not added before
    2. Follow the Code below

 <%@ Application Language="C#" %>

<script runat="server">

    void Application_Start(object sender, EventArgs e)
    {
        // Code that runs on application startup
        Application["NoOfUser"] = 0;
        Application["OnLineUser"] = 0;

    }
   
    void Application_End(object sender, EventArgs e)
    {
        //  Code that runs on application shutdown

    }
       
    void Application_Error(object sender, EventArgs e)
    {
        // Code that runs when an unhandled error occurs

    }

    void Session_Start(object sender, EventArgs e)
    {
        // Code that runs when a new session is started
        Application.Lock();
        Application["NoOfUser"] = Convert.ToInt32(Application["NoOfUser"])+1;
        Application["OnLineUser"] = Convert.ToInt32(Application["OnLineUser"]) + 1;
        Application.UnLock();

    }

    void Session_End(object sender, EventArgs e)
    {
        // Code that runs when a session ends.
        // Note: The Session_End event is raised only when the sessionstate mode
        // is set to InProc in the Web.config file. If session mode is set to StateServer
        // or SQLServer, the event is not raised.
        Application.Lock();
        Application["OnLineUser"] = Convert.ToInt32(Application["OnLineUser"]) - 1;
        Application.UnLock();
    }
     
</script>

 Step-2

  

     1. Add new web form file if not added before

     2. Follow the Code below (I have taken it under a master page )
 
   Design page (Source code)

    <%@ Page Title="" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeFile="FrmGlobal.aspx.cs" Inherits="StateManagement_FrmGlobal" %>

<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" Runat="Server">
    <div align="center">
   <fieldset style="width:300px;height:200px">
   
       <table class="style1">
         
         
           <tr>
               <td>
                   &nbsp;</td>
               <td>
                  <asp:Label ID="Label2" runat="server" Text=""></asp:Label>
                  <br />
                  <asp:Label ID="Label1" runat="server" Text=""></asp:Label>
               </td>
           </tr>
         
       </table>
   
 </fieldset>
</div>
</asp:Content>


C# code



using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class StateManagement_FrmGlobal : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        Session.Timeout = 1;
        Label2.Text ="Total Number of users are :"+ Application["NoOfUser"].ToString();
        Label1.Text ="On Line users are :" + Application["OnLineUser"].ToString();
    }
}

Every call of the application we can see the result

 

     

Working with Cookies in ASP.net

   Cookies are the part of a state management in asp.net to store server information at client side. It has two types
      a. In-Memory Cookies
b. Persistent Cookies

 In-Memory Cookies
     cookies information stored as long as the current application is opened.
  Persistent Cookies
    cookies information stored as long as the current application is opened and leave the the application (both online and offline).It is stored temporarily based on date and time.

         Page Design (Source code) :-
     
             <%@ Page Title="" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeFile="FrmInMemoryCookies1.aspx.cs" Inherits="StateManagement_FrmInMemoryCookies1" %>

<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" Runat="Server">
    <style type="text/css">
        .style1
        {
            width: 100%;
        }
    </style>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" Runat="Server">
    <div align="center">
   <fieldset style="width:300px;height:200px">
 
       <table class="style1">
           <tr>
               <td>
                   &nbsp;</td>
               <td>
                   &nbsp;</td>
           </tr>
           <tr>
               <td>
                   &nbsp;</td>
               <td>
                   &nbsp;</td>
           </tr>
           <tr>
               <td style="text-align: right" width="30%">
                   <asp:Label ID="Label1" runat="server" Text="Enter Name"></asp:Label>
               </td>
               <td style="text-align: left" width="70%">
                   <asp:TextBox ID="TxtName" runat="server"></asp:TextBox>
               </td>
           </tr>
           <tr>
               <td>
                   &nbsp;</td>
               <td>
                   <asp:Button ID="BtnSubmit" runat="server" onclick="BtnSubmit_Click"
                       Text="In-MemoryCookies" />
                        <asp:Button ID="BtnPcookies" runat="server" Text="PersistentCookies"
                       onclick="BtnPcookies_Click" />
                   <asp:Button ID="BtnRefresh" runat="server" onclick="BtnRefresh_Click"
                       Text="ReStore" />
               </td>
           </tr>
           <tr>
               <td>
                   &nbsp;</td>
               <td>
                  <asp:Label ID="Label2" runat="server" Text=""></asp:Label>
               </td>
           </tr>
         
       </table>
 
 </fieldset>
</div>

</asp:Content>



C# Code :-



using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class StateManagement_FrmInMemoryCookies1 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
     
            if (Request.Cookies["name"] == null)
            {
            }
            else
            {
                TxtName.Text = Request.Cookies["name"].Value;
            }
     
    }
    protected void BtnSubmit_Click(object sender, EventArgs e)
    {    

            HttpCookie x = new HttpCookie("name");
            x.Value = TxtName.Text;
            Response.Cookies.Add(x);
            Label2.Text = "Information Stored in Cookies";
            TxtName.Text = "";
     
    }
    protected void BtnRefresh_Click(object sender, EventArgs e)
    {
        Label2.Text = "Cookies Information ReStored";
    }
    protected void BtnPcookies_Click(object sender, EventArgs e)
    {
        HttpCookie x = new HttpCookie("name");
        x.Value = TxtName.Text;
        x.Expires = DateTime.Now.AddMinutes(5);
        Response.Cookies.Add(x);
        Label2.Text = "Information Stored in Cookies";
        TxtName.Text = "";
    }
}