none
pitfalls connected level ado.net

    Question

  • The unfavorable circumstances of the work with the database like mutexes should I consider in my code ? I have read a couple of books, but it seems the presentation is in the books is not enough deep. Thank you.
    Friday, August 23, 2013 6:13 PM

Answers

  • I'm not sure exactly what you are asking, but you mention mutexes so...

    I figure you're asking about SQLCLR (by the words "connected level" in the title). Multithreading is specifically not supported in SQLCLR (.NET stored procedures, etc. in SQL Server). Your assembly would have to be cataloged with CREATE ASSEMBLY...PERMISSION_SET UNSAFE in order to use it. Adam Machanic has a sample called QueryParallelizer here: http://sqlblog.com/files/folders/beta/entry29021.aspx.

    If you are interested in client-side ADO.NET multithreaded code (include SqlClient), a better place to post would be the MSDN forum for ADO.NET managed providers here: http://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetdataproviders

    Hope this helps, Bob

    • Marked as answer by _Alex9_ Saturday, August 24, 2013 6:47 AM
    Saturday, August 24, 2013 5:30 AM
    Moderator

All replies

  • I'm not sure exactly what you are asking, but you mention mutexes so...

    I figure you're asking about SQLCLR (by the words "connected level" in the title). Multithreading is specifically not supported in SQLCLR (.NET stored procedures, etc. in SQL Server). Your assembly would have to be cataloged with CREATE ASSEMBLY...PERMISSION_SET UNSAFE in order to use it. Adam Machanic has a sample called QueryParallelizer here: http://sqlblog.com/files/folders/beta/entry29021.aspx.

    If you are interested in client-side ADO.NET multithreaded code (include SqlClient), a better place to post would be the MSDN forum for ADO.NET managed providers here: http://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetdataproviders

    Hope this helps, Bob

    • Marked as answer by _Alex9_ Saturday, August 24, 2013 6:47 AM
    Saturday, August 24, 2013 5:30 AM
    Moderator
  • To clarify, I can not use a stored procedure calls ado.net means lack of support multithreading?
    Saturday, August 24, 2013 6:47 AM
  • If you are writing a stored procedure in .NET (that's part of what SQLCLR is, persisted modules written in .NET) asynchronous patterns and multitasking are not supported. You can do it (unsupported) if you declare your .NET assembly as unsafe.

    If you have a T-SQL stored procedure and you're using SqlClient, asynchronous patterns are supported (SqlConnection's OpenAsync method and SqlCommand's ExecuteReaderAsync as examples), but these do asynchronous operations on the client-side, they do not enable multi-threaded SQL Server operations. If you issue more than one ExecuteReaderAsync from the same connection for example, these do interleaved, rather than multithreaded, operations on the server. OpenAsync, etc are unsupported if you're writing a stored procedure in .NET.

    There's a different forum for asking questions about SqlClient client-side operations (ADO.NET managed providers, link in original reply), and if you're talking about the latter case, you might get more responses there.

    Hope this helps, and hope it was reasonably clear ;-), Cheers, Bob

    Saturday, August 24, 2013 4:55 PM
    Moderator