Creating Auto Generate Number Through SQL Server


Auto Generate Number from SQL SERVER

In this articles i'm going to show you how to create Auto Generate Number in Asp.net by using c# coding. Generally let we see how auto increment is done, auto increment is nothing but a adding + 1 to the previous number For Example : PreviousNumber + 1
PreviousNumber is the number of which was stored in SQL Server recently (or) Last Number (or) Max Number, and retrieve the Max number from Database and Add + 1 to that number and display in asp.net page.

Create AutoGenerateTable in SQL Server

this code simple copy and paste in sql query builder and click on execute button in SQL SERVER


CREATE TABLE [dbo].[autogeneratornumber](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [recordid] [nchar](5) NOT NULL,
 [recordname] [nvarchar](50) NULL
 CONSTRAINT [PK_autogeneratornumber] PRIMARY KEY CLUSTERED 
(
 [recordid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Create AutoGenerateTable Stored Procedure

to perform increment operation i'm using a stored procedure you just copy and paste in sql query windows and click on Execute button.


 create Procedure [dbo].[proc_AutoGenNumber]
  as begin
   Select MAX(recordid) from autogeneratornumber
  end
  GO

Max(recordid) is used to get highest values which was stored in recordid column in autoGeneratornumber Table.

CSS Code : Design

css code is used for font-end design

 #table1
      {
          border-collapse:collapse;
      }
      #table1 th, #table1 td, td1, td2, #table1 #td1, #td2
      {
          border:1px solid #dddddd;
      }
      #table1 th
      {
          padding:5px 7px 5px 7px;
          color:cyan;
          font-weight:bold;
          background:#000000;
      }
      #table1 td, td1, td2, #table1 #td1, #td2, #table1 #txtrecordname, #table1 #txtrecordname:hover
      {     
          padding:5px;
      }
      #table1 #td1, #td2
      {    
          font-weight:bold;
          background:#fafafa;
      }
      #table1 #btn_autogeneratenumber
      {
          padding:5px 7px 5px 7px;
          border-color:#2c2c2c;
      }
      #table1 #txtrecordname, #table1 #txtrecordname:hover
      {
          width:200px;
      }
      #table1 #txtrecordname:hover, #table1 #btn_autogeneratenumber
      {
          background:#2c2c2c;
          color:#ffffff;
      }
      #table1 #btn_autogeneratenumber:hover
      {
          background:#0000cc;
          padding:5px 7px 5px 7px;
          color:#fafafa;
          border-color:#0000cc;
      }

HTML Markup : Design


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

<!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>
    <link href="CSS/StyleSheet.css" rel="stylesheet" type="text/css" />
</head>
<body>
    <form id="form1" runat="server">
    <div>
     <table id="table1">
      <tr>
       <th colspan="3">Creating Auto Generate Number Through SQL SERVER</th>
      </tr>
      <tr>
       <td id="td1">Record ID : </td>
       <td colspan="2"><asp:Label ID="lblrecordid" runat="server"></asp:Label></td>
      </tr>
      <tr>
       <td id="td2">Record Name :</td>
       <td colspan="2"><asp:TextBox ID="txtrecordname" runat="server"></asp:TextBox></td>
      </tr> 
      <tr>
      <td colspan="3"><asp:Button ID="btn_autogeneratenumber" Text="Create Record" 
              runat="server" style="text-align:center;" 
              onclick="btn_autogeneratenumber_Click" /></td>
      </tr> 
     </table>
    </div>
    <asp:Label ID="lblerror" runat="server"></asp:Label>
    <br />
    <br />
    <br />
    <asp:HyperLink ID="LinkButton1" NavigateUrlrl="~/AutoGenerateNumberForm.aspx" 
        runat="server" NavigateUrl="~/AutoGenerateNumberForm.aspx">Create New Record </asp:HyperLink>
    </form>
</body>
</html>

C# Code

C# Coding : Namespace

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

C# Coding : Page Load


public partial class AutoGenerateNumberForm : System.Web.UI.Page
{
    int i = 0;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            generateautonumber();
        }
    }
}

C# Coding : AutoGenerate Number Method


    private void generateautonumber()
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbCon"].ToString());
        con.Open();
        SqlCommand cmd = new SqlCommand("proc_AutoGenNumber", con);
        cmd.CommandType = CommandType.StoredProcedure;
        string value = cmd.ExecuteScalar().ToString();
        i++;
        int rv = Int32.Parse(value) + 1;
        lblrecordid.Text = rv.ToString();
        
    }

C# Coding : Create Button Click Event


    protected void btn_autogeneratenumber_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbCon"].ToString());        
        con.Open();
        SqlCommand cmd = new SqlCommand("proc_InsertAutoGenNumber", con);        
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@recordid", SqlDbType.VarChar).Value = lblrecordid.Text;
        cmd.Parameters.Add("@recordname", SqlDbType.VarChar).Value = txtrecordname.Text;
        int i = cmd.ExecuteNonQuery();
        if (i > 0)
        {
            lblerror.Text = "Record Inserted Successfully...";
        }
        else
        {
            lblerror.Text = "Error in Record Inserting...";
        }
        con.Close();
    }
}
0 Komentar untuk "Creating Auto Generate Number Through SQL Server"

Back To Top