Can GetSchema method work asynchronously?

Question

How does GetSchema method of SqlConnection class work? Does it run queries? Can it be called asynchronously?

Answer 1

Assuming you want the c# answer since it is in your tag see: Use SqlConnection.GetSchema to get Tables Only (No Views)

    using System.Data.SqlClient;

and

    SqlConnection.GetSchema("Tables");

or

    SQLCon.Open();
    DataTable tables = SQLCon.GetSchema("Tables");
    SQLCon.Close();

I would guess what this is actually doing is running a query to either the sys tables or the information_schema.tables in SQL when you open the connection. something like:

   SELECT * FROM information_schema.tables 

or

    SELECT * FROM [database].sys.tables

And the equivalent calls for the other methods within the class.

For Async calls you can use

    Asynchronous Processing=True; 

in your connection string

    string connectionString = "Data Source=yourDataSource;Initial Catalog=yourCat;Integrated Security=true;Asynchronous Processing=True;";

Did this answer your question?

Answer 2

I guess it fetch data from sys tables. Look at http://msdn.microsoft.com/en-us/library/ms188348.aspx

There is no async implementation of GetSchema. If you need concrete details of the method try to use ILSPY.

Answer 3

Can you delegate the method that calls "GetSchema"? Because SQL is transactional system, the parallel tasks we work with in c# are very different, possible even non existent in sql. The highest ranked answer on this post does a good job explaining this.

SQL Server (TSQL) - Is it possible to EXEC statements in parallel?

If you cannot run the GetSchema method asynchronously, because that method just returns a data table, you could alternatively execute a query against the database that returns the same thing.

This post shows the getSchema returns a data table: http://msdn.microsoft.com/en-us/library/ms136365(v=vs.110).aspx

Read the remarks on this post: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.getschema(v=vs.110).aspx

If you call the getSchema more than once, the last call could potentially overwrite the first response.

If you are able to, you could potentially create a service queue in SQL to handle the "Asynchronous execution" but it's very complex: http://rusanu.com/2009/08/05/asynchronous-procedure-execution/

Hope this helps!