Click here to Skip to main content
15,877,675 members
Please Sign up or sign in to vote.
4.00/5 (2 votes)
hi i created stored procedure to avoid duplicate entries but i dont knw how to give code in business class..can any one suggest me.

this my sp
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




ALTER PROCEDURE [dbo].[usp_InsertBuild]
@BuildNo varchar(50),
@StartDate varchar(50),
@EndDate varchar(50),
 @Success bit output, 
@msg varchar(50) output  


AS
BEGIN
if not exists(select * from tblBuild where BuildNo=@BuildNo) 
begin
set @Success=1
set @msg='Successfully Saved'      



insert into tblBuild(BuildNo,StartDate,EndDate)
 values(@BuildNo,@StartDate,@EndDate)
end
else  begin
set @Success=0           
set @msg='Build Number already exists, Cannot Save' 
END end


here is my aspx.cs code..
protected void imgbtnSave_Click(object sender, ImageClickEventArgs e)
   {
       InsertAllPL iaPL = new InsertAllPL();
       iaPL.BuildNo = txtBNo.Text;
       iaPL.StartDate = txtSDate.Text;
       iaPL.EndDate = txtEndDate.Text;

       InsertAllBLL iaBLL = new InsertAllBLL();
       iaBLL.InsertBuild(iaPL);

       //lblMessage.Text = "Record Added SuccessFully";
       divMessage.Visible = true;
       txtBNo.Text = "";
       txtSDate.Text = "";
       txtEndDate.Text = "";
   }
Posted
Comments
Sergey Alexandrovich Kryukov 9-Mar-12 0:58am    
What do you mean by duplicate, exactly? Totally identical record except primary key?
Database won't allow you the 100% exact duplicate...
--SA
ythisbug 9-Mar-12 1:15am    
ya identical.like i have build number for example 10.2.3 i dont want to repeat this in my table so .any suggestion
Sergey Alexandrovich Kryukov 9-Mar-12 1:18am    
You could make version a primary key, then the database won't allow to duplicate it. This would be the best way.
--SA
ythisbug 9-Mar-12 1:23am    
can u telmme how to do that.?
Sergey Alexandrovich Kryukov 9-Mar-12 1:33am    
To do what? Change database structure and code. My advice does not work in all cases though. Primary keys are designed to be unique by definition.
--SA

hi,
u just assign output parameter value to lblMessage using following code
C#
string msg =sqlCmd.Parameters["@Success"].Value;
    return msg;

check in btnSaveclick event
C#
if(msg=="1")
     lblMessage.Text="Successfully Saved";
   else
     lblMessage.Text="Build Number already exists, Cannot Save";

hope it will help
best luck
 
Share this answer
 
v2
You can use powerful LInQ query in business class. Here is the sample code to fetch the duplicate records before insert.

C#
var dbData = from x in db.tblBuild
                       where x.Sucess != 0
                       group x by x.BuildNo into g
                       let ct = g.Count()
                       orderby ct
                       where ct > 1
                       select new
                            {   
                                BuildNo = g.First().BuildNo,
                                StartDate = g.First().StartDate,
                                EndDate = g.First().EndDate,
                                Success = g.First().Success,
                                msg = g.First().msg,
                            };
 
Share this answer
 
Comments
ythisbug 9-Mar-12 1:24am    
hi ganesan is this vb code?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900