xCiqLrLz

1Checking a table for an existing record before a new record is inserted
2public void UpdateForm(Int64 requestid,
3                              Decimal empid,
4                              String leave,
5                              DateTime startdate,
6                              DateTime enddate,
7                              String starttime,
8                              String endtime,
9                              String standby,
10                              String status,
11                              String rsn,
12                              String remarks,
13                              String approver,
14                              String with,
15                              String reqleave,
16                              String FIRSTNAME,
17                              String LASTNAME)
18    {
19
20        var CurrUser = "a03       ";
21
22        Account.Login uusr = new Account.Login();
23        CurrUser = uusr.User.Identity.Name.ToString().ToUpper();
24
25        var sql = "update TIME.request set empid=@empid, leave=@leave, with=@with, startdate=@startdate, reqleave=@reqleave, enddate=@enddate, starttime=@starttime, endtime=@endtime, standby=@standby, status=@status, rsn=@rsn, remarks=@remarks, approver=@approver where requestid = @requestid";
26
27        using (iDB2Connection conn = new iDB2Connection(GetConnectionString()))
28        {
29            conn.Open();
30
31            using (iDB2Command cmd = new iDB2Command(sql, conn))
32            {
33                cmd.DeriveParameters();
34                cmd.Parameters["@requestid"].Value = requestid;
35                cmd.Parameters["@empid"].Value = empid;
36                cmd.Parameters["@leave"].Value = leave;
37                cmd.Parameters["@startdate"].Value = startdate;
38                cmd.Parameters["@enddate"].Value = enddate;
39                cmd.Parameters["@starttime"].Value = starttime;
40                cmd.Parameters["@endtime"].Value = endtime;
41                cmd.Parameters["@standby"].Value = standby;
42                cmd.Parameters["@status"].Value = status;
43                cmd.Parameters["@rsn"].Value = rsn;
44                cmd.Parameters["@remarks"].Value = remarks;
45                cmd.Parameters["@approver"].Value = approver;
46                cmd.Parameters["@reqleave"].Value = reqleave;
47                cmd.Parameters["@with"].Value = with;
48
49                cmd.ExecuteNonQuery();
50            }
51        }
52    }
53	
54ALTER TABLE <table_name>
55ADD CONSTRAINT <constraint name> UNIQUE (user,date)
56	
57ALTER TABLE TIME.request
58ADD CONSTRAINT uc_request UNIQUE (empid, startdate, enddate)
59	
60if exists(select 1 from table where user = @user and eventtime = @eventtime)
61  begin
62    set @output = 'event already exists'
63  end 
64else
65 begin
66    --Prevent DBNull output, see comments
67    set @output = ''
68   --Insert into table
69 end
70	
71var output = new SqlParameter() { Direction = ParameterDirection.Output, ParameterName = "@Output" };
72  var cmd = new SqlCommand("procname", connection)
73  cmd.Parameters.Add(output);
74  //Add other params
75  cmd.ExecuteNonQuery();
76  if (!string.IsNullOrEmpty(output.Value))
77    //Handle the error
78    throw new Exception("Already exists");
79	
80CREATE PROCEDURE [dbo].[Prefix_SomeProcName]
81
82--Parameters you need from the front end
83@User varchar(50),
84@EventTime datetime,
85@Output varchar(100) output
86
87--Created by: Your name
88--Created date: Todays date
89--Description: To do some stuff
90
91AS
92
93--Do your stuff here
94if exists(select 1 from table where user = @user and eventtime = @eventtime)
95  begin
96    set @output = 'event already exists'
97  end 
98else
99 begin
100    --Prevent DBNull output, see comments
101    set @output = ''
102   --Insert into table
103 end
104	
105using(SqlConnection con = New SqlConnection("ConnectionString")
106{
107   SqlCommand cmd = new SqlCommand("Prefix_SomeProcName", con);
108   cmd.CommandType = CommandType.StoredProcedure;
109   var output = new SqlParameter() { Direction = ParameterDirection.Output, ParameterName = "@Output" };
110   //Add your other parameters
111   SqlDataAdapter sda = new SqlDataAdapter(cmd);
112   DatatTable dt = New DataTable();
113   sda.Fill(dt);
114}
115	
116select ...
117where not exists (select 1 from myTable where....)