0

I created a test application with Thread and call stored procedure. If I call stored procedure from console application stored procedure executes, but does not insert values into test table. I'm using a DbContext. If I call procedure in SQL Server Management Studio then value adds to test table. Where is error?

My console application for thread call stored procedure:

 static void Main(string[] args)
            {
             ModelContext context = new ModelContext();
             Thread t = new Thread(() => ThreadTestProcedure(context));
             t.Start();
    }

Thread call method:

 private static void ThreadTestProcedure(ModelContext сontext)
            {
                try
                {                
                        context.RunTestProcedure(1000, "Test")
                }
                catch (Exception ex)
                {

                }

                Console.WriteLine();
                Console.ReadKey();
            }

My stored procedure for insert values to test table:

CREATE PROCEDURE TestProcedure
   @Int1 INT,
   @SrtingValue nvarchar(250)
AS        
   IF NOT EXISTS (SELECT * FROM sys.objects 
                   WHERE object_id = OBJECT_ID(N'[dbo].[ASSIGNMENTS_DELETED]') 
                   AND type in (N'U'))
   BEGIN
      CREATE TABLE ASSIGNMENTS_DELETED
             (ID INT PRIMARY KEY (ID) identity(1,1),
              Assigment int, 
              StringValue nvarchar(250)
             )   
   END 

   WHILE 1=1 
   BEGIN
      BEGIN TRY
      BEGIN TRANSACTION
          insert into ASSIGNMENTS_DELETED
             select @Int1, @SrtingValue            

          COMMIT TRANSACTION
     END TRY
     BEGIN CATCH
          IF XACT_STATE() <> 0 
              ROLLBACK TRANSACTION
          RAISERROR ('it broke', 16, 1)
     END CATCH
 END
2
  • Hope it helps msdn.microsoft.com/en-us/data/gg699321.aspx Commented Dec 24, 2013 at 8:57
  • I'm also Map Stored Procedures and call stored procedure with: context.Database.ExecuteSqlCommand(string.Format("exec TestProcedure @id, @value", id, value), new SqlParameter("id", id), new SqlParameter("value", value)); But also not insert values Commented Dec 24, 2013 at 9:08

2 Answers 2

3

Your stored procedure has an infinite loop WHILE 1=1 ??? remove that.

Sign up to request clarification or add additional context in comments.

2 Comments

I need long time execute stored procedure
It's going to be a real long time.... Why not use a loop instead and you can adjust how long it runs WHILE (SELECT COUNT(ID) FROM [dbo].[ASSIGNMENTS_DELETED]) < 1000
2

Remove WHILE 1=1 from your stored procedure. It goes in infinite loop.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.