Posted by: Cirilo Meggiolaro | 01/4/2009

Tip of the day #82 – Executing queries with Microsoft.SqlServer.Management.Smo namespace

Let’s check today how to execute queries against a database and retrieve a DataSet object with the Microsoft.SqlServer.Management.Smo namespace. I’ve written about this namespace and if you haven’t read anything about it yet, check the Tip#75 for a quick introduction.

How to…

The steps to perform the task of execute a T-SQL query statement against a database is pretty similar to everything we’ve seen on the latest posts:

  • Create an instance of the Server class;

Server svr = new Server(@”MYPC\SQLEXPRESS”);

  • Retrieve the database object instance passing the server object we created on the previous step and the database name;

Database db = new Database(svr, “SmoDb”);

  • Create a T-SQL statement;
  • Invoke one of the ExecuteWithResults method overloads;

public DataSet ExecuteWithResults(string sqlCommand);
public DataSet ExecuteWithResults(StringCollection sqlCommands);

The code must be similar to the following:

/// Creates a server class instance.
Server svr = new Server(@”MYPC\SQLEXPRESS”);

/// Creates the dataset that holds the query results.
DataSet ds = null;

try
{
    if (svr != null)
    {
        /// Retrieves the database from the server class instance.
        Database db = svr.Databases[“SmoDb”];

        if (db != null)
        {
            /// Creates a SQL statement.
            string sql = @”SELECT customer_id, customer_name, customer_dob,
                                             customer_active
                                    FROM Customer WITH(NOLOCK)”;

            /// Executes the query
            ds = db.ExecuteWithResults(sql);

            if ((ds != null) && (ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
            {
                /// Loops through all rows in the table and display the results.
                 foreach (DataRow row in ds.Tables[0].Rows)
                {
                     Console.WriteLine(“Customer ID: {0} – Name: {1} – Date of Birth: {2} – Is Active: {3}”,
row[“customer_id”], row[“customer_name”], row[“customer_dob”], Convert.ToBoolean(row[“customer_active”]));
                }
            }
        }
    }
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
}
finally
{
    /// Releases resources
    if (ds != null)
    {
        ds.Dispose();
        ds = null;
    }
}


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: