none
execuating database procedures. RRS feed

  • Question

  • I am updating a database with a list of database procedures in C#.   After each database procedure execuated  I would like  to catch a message from database which would tell me if the query has execuated successfully.  How can I do that?

     

    Thank you!

    Friday, September 7, 2007 3:11 AM

Answers

  • you can add the return value at the end of each sp.

    and you can catch the value in your DB access code with ParameterDirection.ReturnValue.

     

     

    for more code example:http://msdn2.microsoft.com/en-us/library/59x02y99(VS.71).aspx

    Friday, September 7, 2007 3:18 AM
  • Alteratively, you could also do a SELECT SCOPE_IDENTITY() on the stored procedure after completing your insertion, then use the ExecuteScalar() method on the SqlCommand object, which returns the result of the first row and first column only, in which case you should get the ID of the record returned back, if you implemented the above code.

     

    However correctly, you should be able to use the ReturnValue/Output value, which your SPROC will set for you, and you can retrieve the value of that parameter after executing the operation.

    To do this, aside from setting the parameter to the output type of ReturnValue, you also need to set this parameter in the SPROC as an output/return value.

     

    aside from this, the ExecuteNonQuery() will return an int on how many rows/records were effected by the transaction executed, so you don't need to do a SCOPE_IDENTITY() or even a returnvalue - again, depends on how your SPROC is designed.

    Friday, September 7, 2007 3:43 AM

All replies

  • you can add the return value at the end of each sp.

    and you can catch the value in your DB access code with ParameterDirection.ReturnValue.

     

     

    for more code example:http://msdn2.microsoft.com/en-us/library/59x02y99(VS.71).aspx

    Friday, September 7, 2007 3:18 AM
  • Alteratively, you could also do a SELECT SCOPE_IDENTITY() on the stored procedure after completing your insertion, then use the ExecuteScalar() method on the SqlCommand object, which returns the result of the first row and first column only, in which case you should get the ID of the record returned back, if you implemented the above code.

     

    However correctly, you should be able to use the ReturnValue/Output value, which your SPROC will set for you, and you can retrieve the value of that parameter after executing the operation.

    To do this, aside from setting the parameter to the output type of ReturnValue, you also need to set this parameter in the SPROC as an output/return value.

     

    aside from this, the ExecuteNonQuery() will return an int on how many rows/records were effected by the transaction executed, so you don't need to do a SCOPE_IDENTITY() or even a returnvalue - again, depends on how your SPROC is designed.

    Friday, September 7, 2007 3:43 AM
  • Thank you for the suggestions! The thing is I did not create all those sps nor I have control over them;  I can only read them.  The following is the code I got.  I am reading all those sp names from a directory and then executing sps one at the time.  I would like to receive the message  which would nodify me the status of  each execuation.

     

    DirectoryInfo Dir = new DirectoryInfo(@"C:\******\SQL Update");

    SqlConnection MyConn = new SqlConnection("user id" + "=" + username + ";"

    + "password" + "=" + password + ";"

    + "server" + "=" + serverurl + ";"

    + "Trusted_Connection=yes;"

    + "database" + "=" + database + ";"

    + "Connection timeout=30"+";"

    + "Initial Catalog={0}"+";"

    + "Pooling=False"+";"

    + "Asynchronous Processing=true");

     

    if (Dir.Exists)

    {

    FileInfo[] sqlfiles = Dir.GetFiles("*.sql");

    FileSize = sqlfiles.Length;

    foreach (FileInfo f in sqlfiles)

    {

    string script = f.OpenText().ReadToEnd();

    Server server = new Server(new ServerConnection(MyConn));

    server.ConnectionContext.ExecuteNonQuery(script);

    }

    Friday, September 7, 2007 8:13 PM
  • foreach (FileInfo f in sqlfiles)

    {

    string script = f.OpenText().ReadToEnd();

    //add try-catch clause here to catch exception

    // when each sp run successfully, print a message or log it in your file.

    //also you need to use transaction here to protec your data when occur exception

    Server server = new Server(new ServerConnection(MyConn));

    server.ConnectionContext.ExecuteNonQuery(script);

    }

     

     

     

    sorry for i can't provider u detail code bcoz i dont have compiler on my hand now.

     

     

    Friday, September 7, 2007 11:50 PM
  • Eric,

     

    Thank you very much for your help!  I will give that a try.

     

    Liang

    Monday, September 10, 2007 1:53 PM