My scenario:
n number of records in table, threads trying
to access the table. One has to get first number and delete it, others
have to get the second, third, etc., one by one.
But the problem is some of the threads get the same number. How do I avoid this?
My code
private void Form1_Load(object sender, EventArgs e)
{
for (int j = 1; j >= 10; j++)
{
Thread.Sleep(1000);
ThreadStart StarterCon = delegate { this.Start_new(sno); };
Thread th = new Thread(StarterCon);
th.Start();
}
}
private void Start_new(int h)
{
try
{
for (; ; )
{
using (SqlConnection ObjConn = new SqlConnection(ConnectionString))
{
ObjConn.Open();
using (SqlDataAdapter ObjAda = new SqlDataAdapter("Select_BlockedNubmer", ObjConn))
{
ObjAda.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parm;
parm = ObjAda.SelectCommand.Parameters.Add("@id", SqlDbType.NVarChar);
parm.Value = h;
using (DataTable dtTable = new DataTable())
{
ObjAda.Fill(dtTable);
}
}
ObjConn.Close();
}
Thread.Sleep(500);
}
}
catch { }
}
my stored procedure is
Create procedure [dbo].[Select_BlockedNubmer]
@id varchar(max)
as
begin
set rowcount 1
select * from BlockedNumber
delete from BlockedNumber
set rowcount 0
end
I tried the following stored procedure. It works fine but reading number is very very slow what i do:
ALTER procedure [dbo].[Select_BlockedNubmer]
@id varchar(max)
as
begin
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin transaction
set rowcount 1
select * from BlockedNumber
delete from BlockedNumber
set rowcount 0
commit transaction
end