RSS

Get Maximum ID From Database after Insertion in storeprocedure

25 Aug

this is sp which return the maximum value of auto generated Primary key and return and used in other detail table for foreign key.

ALTER PROCEDURE [dbo].[SP_InsertUpdateRegistrationInfo]
@RegistrationID int,
@RegistrationCode varchar(100),
@Title varchar(25),
@FName varchar(25),
@LName varchar(25),
@MName varchar(25),
@FamilyName varchar(100),
@Gender varchar(10),
@DOB datetime,
@Address1 varchar(100),
@Address2 varchar(100),
@City varchar(50),
@State varchar(50),
@ZIPCode varchar(50),
@Country varchar(50),
@Phone varchar(50),
@Mobile varchar(50),
@ReferringSource varchar(50),
@RefereeName varchar(50),
@RefereeAddress varchar(50),
@CourseID int ,

@CampusID int ,
@Status int,
@RegistrationSource varbinary(50),
@Religion varchar(50),
@MaritalStaus varchar(50),
@PassportNo varchar(100),
@VisaNo varchar(50),
@VisaStartDate datetime,
@VisaEndDate datetime,
@Nationality varchar(50),
@PicturePath varchar(50),
@EnteredBy uniqueidentifier,
@LastModifiedBy uniqueidentifier,
@EnteredDate datetime,
@ShiftID int,
@ModifiedDate datetime,
@Email varchar(100)

AS

DECLARE @ReturnValue int

DECLARE @SetVisaStartDate datetime

IF (@RegistrationID IS NULL or @RegistrationID=0) — New Item

BEGIN

insert into tblRegistration
(RegistrationCode,
Title,
FName,
LName,
MName,
FamilyName,
Gender,
DOB,
Address1,
Address2,
City,
State,
ZIPCode,
Country,
Phone,
Mobile,
ReferringSource,
RefereeName,
RefereeAddress,
CourseID,

CampusID,
Status,
RegistrationSource,
Religion,
MaritalStaus,
PassportNo,
VisaNo,
VisaStartDate,
VisaEndDate,
Nationality,
PicturePath,
EnteredBy,
LastModifiedBy,
EnteredDate,
ShiftID,
ModifiedDate,
Email)

values(

@RegistrationCode,
@Title,
@FName,
@LName,
@MName,
@FamilyName,
@Gender,
@DOB,
@Address1,
@Address2,
@City,
@State,
@ZIPCode,
@Country,
@Phone,
@Mobile,
@ReferringSource,
@RefereeName,
@RefereeAddress,
@CourseID,

@CampusID,
@Status,
@RegistrationSource,
@Religion,
@MaritalStaus,
@PassportNo,
@VisaNo,
@VisaStartDate,
@VisaEndDate,
@Nationality,
@PicturePath,
@EnteredBy,
@LastModifiedBy,
@EnteredDate,
@ShiftID,
@ModifiedDate,
@Email

)
SELECT @ReturnValue = SCOPE_IDENTITY()
END
ELSE
BEGIN
Update tblRegistration set

Title=@Title,
FName=@FName,
LName=@LName,
MName=@MName,
FamilyName=@FamilyName,
Gender=@Gender,
DOB=@DOB,
Address1=@Address1,
Address2=@Address2,
City=@City,
State=@State,
ZIPCode=@ZIPCode,
Country=@Country,
Phone=@Phone,
Mobile=@Mobile,
ReferringSource=@ReferringSource,
RefereeName=@RefereeName,
RefereeAddress=@RefereeAddress,
CourseID=@CourseID,

CampusID=@CampusID,
Status=@Status,
RegistrationSource=@RegistrationSource,
Religion=@Religion,
MaritalStaus=@MaritalStaus,
PassportNo=@PassportNo,
VisaNo=@VisaNo,
VisaStartDate=@VisaStartDate,
VisaEndDate=@VisaEndDate,
Nationality=@Nationality,
PicturePath=@PicturePath,
EnteredBy=@EnteredBy,

ShiftID=@ShiftID,
ModifiedDate=@ModifiedDate,
Email=@Email

where RegistrationID=@RegistrationID

SELECT @ReturnValue = SCOPE_IDENTITY()
END

IF (@@ERROR != 0)
BEGIN
RETURN -1
END
ELSE
BEGIN
SELECT @ReturnValue
END

which return the Maximum ID and how to get in Asp.net Following here………..

which is Dal Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using System.Data.SqlTypes;
/// <summary>
/// Summary description for DALRegisrationOnline
/// </summary>
public class DALRegisrationOnline
{

#region “property”
public int RegistrationID { get; set; }
public  int RegistrationCode{get;set;}
public  string Title{get;set;}
public  string FName{get;set;}
public  string LName{get;set;}
public  string MName{get;set;}
public  string FamilyName{get;set;}
public  string Gender{get;set;}
public DateTime DOB{get;set;}
public  string Address1{get;set;}
public  string Address2{get;set;}
public  string City{get;set;}
public  string State{get;set;}
public  string ZIPCode{get;set;}
public  string Country{get;set;}
public string Phone{get;set;}
public string Mobile{get;set;}
public  string ReferringSource{get;set;}
public  string RefereeName{get;set;}
public  string RefereeAddress{get;set;}
public int CourseID{get;set;}
public int DepartmentID{get;set;}
public int CampusID{get;set;}
public  int Status{get;set;}
public  string RegistrationSource{get;set;}
public  string Religion{get;set;}
public  string MaritalStaus{get;set;}
public  string PassportNo{get;set;}
public  string VisaNo{get;set;}
public SqlDateTime VisaStartDate { get; set; }

public SqlDateTime VisaEndDate { get; set; }
public  string Nationality{get;set;}
public  string PicturePath{get;set;}
public  Guid EnteredBy{get;set;}
public  Guid LastModifiedBy{get;set;}
public DateTime EnteredDate{get;set;}
public int ShiftID{get;set;}
public DateTime ModifiedDate{get;set;}
public string Fax { get; set; }
public string EMail { get; set; }

//Enrolment of Student Properties
public DateTime EnrollmentStartDate { get; set; }
public DateTime EnrollmentEndDate { get; set; }
public string EnrollmentCode { get; set; }
public string Remarks { get; set; }
public int StudentStatus { get; set; }
public string Duration { get; set; }
//Payment of Enrollment Student
public int StudentID{get;set;}
public DateTime PaymentDate { get; set; }
public DateTime CreditCardExpiryDate { get; set; }
public string PaymentMode { get; set; }
public string CreditCardNumber { get; set; }

public string NameOnCard { get; set; }
public string CreditCardType{get;set;}
public int PaymentID{get;set;}
public string CreditCardCVV{get;set;}
public string CCSecurityNo { get; set; }
public string BankName{get;set;}
public string ChequePayOrder{get;set;}
public DateTime ChequePayOrderDate { get; set; }
public decimal Amount{get;set;}
public string TransactonCode{get;set;}
public int PaymentStatus{get;set;}
public decimal TotalFee { get; set; }
public decimal Discount { get; set; }
public decimal downPayment { get; set; }
public decimal Balance { get; set; }
public int TotalInstallment { get; set; }
public int NumberInstallment { get; set; }

#endregion

public static int InsertUpdateRegisrationInfo(DALRegisrationOnline RegInfo)
{
Database db = DatabaseFactory.CreateDatabase();
object[] parameters = new object[] { RegInfo.RegistrationID, RegInfo.RegistrationCode, RegInfo.Title, RegInfo.FName, RegInfo.LName, RegInfo.MName, RegInfo.FamilyName, RegInfo.Gender, RegInfo.DOB, RegInfo.Address1, RegInfo.Address2, RegInfo.City, RegInfo.State, RegInfo.ZIPCode, RegInfo.Country, RegInfo.Phone, RegInfo.Mobile, RegInfo.ReferringSource, RegInfo.RefereeName, RegInfo.RefereeAddress, RegInfo.CourseID, RegInfo.CampusID, RegInfo.Status, RegInfo.RegistrationSource, RegInfo.Religion, RegInfo.MaritalStaus, RegInfo.PassportNo, RegInfo.VisaNo, RegInfo.VisaStartDate, RegInfo.VisaEndDate, RegInfo.Nationality, RegInfo.PicturePath, RegInfo.EnteredBy, RegInfo.LastModifiedBy, DateTime.Now, RegInfo.ShiftID, DateTime.Now, RegInfo.EMail };
DbCommand cmd = db.GetStoredProcCommand(“SP_InsertUpdateRegistrationInfo”, parameters);
// return db.ExecuteNonQuery(cmd);
return (int)db.ExecuteScalar(cmd);

}

}

this code to return the last insert record in database

return (int)db.ExecuteScalar(cmd);

///

where we access this function like here………
//Registration Form Step1
DALRegisrationOnline reg = new DALRegisrationOnline();
reg.RegistrationID = 0;

reg.CampusID = Convert.ToInt32(ddlCampusName.SelectedValue);
reg.CourseID = Convert.ToInt32(ddlCurse.SelectedValue);
reg.DepartmentID = Convert.ToInt32(ddlCampusName.SelectedValue);
reg.Title = ddltitle.SelectedValue;
reg.FName = txtFirstName.Text;
reg.LName = txtLastName.Text;
reg.MName = txtMiddleName.Text;
reg.Gender = ddlGender.SelectedItem.Text;
reg.DOB = Convert.ToDateTime(txtDOB.Text);

//string dobDate = txtDOB.Text;

//string dd, mm, yy, dDate;
//yy = dobDate.Substring(0, 4);
//mm = dobDate.Substring(5, 2);
//dd = dobDate.Substring(8, 2);

//dDate = mm + “/” + dd + “/” + yy;
//reg.DOB =Convert.ToDateTime(dDate);

reg.Address1 = txtAddress1.Text;
reg.Address2 = “”;

reg.Country = ddlCountry.SelectedItem.Text;
reg.City = txtCity.Text;
reg.State = txtState.Text;
reg.ZIPCode = txtZipCode.Text;
reg.Religion = Convert.ToString(ddlReligion.SelectedValue);
reg.MaritalStaus = ddlMatrial.SelectedItem.Text;
reg.Nationality = txtNationality.Text;
reg.Mobile = txtMobile.Text;
reg.Phone = txtTelephone.Text;
reg.Fax = txtFax.Text;
reg.EMail = txtEmail.Text;
reg.PassportNo = txtPassportNo.Text;
reg.VisaNo = txtVisaNo.Text;

//Img Upload
string _ImgName = string.Empty;
string folderPath = ConfigurationManager.AppSettings.Get(“ImagePath”);

string ImgPathimg = Request.PhysicalApplicationPath + folderPath;
//if (fuPicture != null && fuPicture.HasFile)
//{
if (_ImgUrl != “”)
{

// _ImgName = System.IO.Path.GetFileName(fuPicture.PostedFile.FileName);
_ImgName = _ImgUrl;

string name = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + “_”;

string _imgrnd = name + _ImgName;
ImgPathimg = ImgPathimg + _imgrnd;
string name1 = _imgrnd;
fuPicture.SaveAs(ImgPathimg);
reg.PicturePath = _imgrnd;

}
else {
reg.PicturePath = “”;

}
System.Data.SqlTypes.SqlDateTime sqldatenull;
sqldatenull = SqlDateTime.Null;

///End Img Upload
if (txtVisaStDate.Text != “”)
{
reg.VisaStartDate = Convert.ToDateTime(txtVisaStDate.Text);
}
else
{

reg.VisaStartDate = sqldatenull;//DateTime.Now; //Convert.ToDateTime(“0000:00:00 00:00:00”);

}
if (txtVisaEndDate.Text != “”)
{
reg.VisaEndDate = Convert.ToDateTime(txtVisaEndDate.Text);
}
else
{

reg.VisaEndDate = sqldatenull;// DateTime.Now;// Convert.ToDateTime(“0000:00:00 00:00:00”);

}

reg.ReferringSource = txtReferringSource.Text;
reg.RefereeName = txtRefereeName.Text;
reg.RefereeAddress = txtRefereeAddress.Text;
reg.Status =0;
reg.EnteredBy = (Guid)Membership.GetUser().ProviderUserKey;

reg.LastModifiedBy = (Guid)Membership.GetUser().ProviderUserKey;

int _RegistrationID = DALRegisrationOnline.InsertUpdateRegisrationInfo(reg);

After Get Maximum Registration ID then use in other table

DALRegistrationQualifications regQul = new DALRegistrationQualifications();
regQul.RegistrationID = _RegistrationID;

Advertisements
 
Leave a comment

Posted by on August 25, 2010 in SQL Query

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: