How to use DECLARE/SET in a query and get the result?

[shb]

Hi Greetings!

I am exploring SQLDirect.Net in VS2008. Following is my query against any database. How do I get the result set?
I am trying with:

a. SqlConnection
b. SqlDataTable
c. SqlCommand

Please note, I have DECLARE and SET statement in my SQL. What is the best way to get result set without removing DECLARE and SET keyword?

DECLARE @dbid int
SET @dbid = db_id()

SELECT object_name(i.object_id) TableNme,
i.name IndexName,
i.index_id indexID,
i.type_desc TypeDesc,
c.index_columns IndexedColumn
FROM sys.indexes i
LEFT OUTER JOIN sys.dm_db_index_usage_stats d
ON d.object_id = i.object_id
AND i.index_id = d.index_id
AND d.database_id = @dbid
LEFT OUTER JOIN (
SELECT DISTINCT object_id,
index_id,
stuff((SELECT ','+col_name(object_id,column_id ) AS 'data()'
FROM sys.index_columns t2
WHERE t1.object_id = t2.object_id
AND t1.index_id = t2.index_id
FOR XML PATH ('')),1,1,'') AS 'index_columns'
FROM sys.index_columns t1
) c
ON c.index_id = i.index_id
AND c.object_id = i.object_id
WHERE objectproperty(i.object_id, 'IsIndexable') = 1
AND d.index_id IS NULL
AND i.type_desc NOT IN ('heap', 'clustered')

[Shalex]

If you want to get the SqlDataReader object, you can use SqlCommand.ExecuteReader() method as shown in code below:

Code: Select all

      SqlConnection conn = new SqlConnection();
      conn.ConnectionString = @"Data Source=.\sqlexpress; Database = BaseName; user=UserName; password=YourPassword";
      conn.Open();
      try {
        
        SqlCommand command = new SqlCommand();
        command.Connection = conn;
        command.CommandText = @" ... ";   

        SqlDataTable table = new SqlDataTable();
        
        using (SqlDataReader reader = command.ExecuteReader()) {
          table.Load(reader);
          foreach (System.Data.DataRow myRow in table.Rows) {
            foreach (System.Data.DataColumn myCol in table.Columns) {
              Console.Write(myRow[myCol] + "\t");
            }
            Console.WriteLine();
          }
        }
      }

      catch (Exception ex) {
        Console.WriteLine("The error occurs: {0}", ex.Message);
      }

      finally {
        conn.Close();
        Console.ReadLine();
      }
    }

You can also use higher level objects such as SqlDataTable:

Code: Select all

      SqlConnection conn = new SqlConnection();
      conn.ConnectionString = @"Data Source=.\sqlexpress; Database = BaseName; user=UserName; password=YourPassword";
      conn.Open();
      try {
        
        SqlCommand command = new SqlCommand();
        command.Connection = conn;
        command.CommandText = @" ... ";   

        SqlDataTable table = new SqlDataTable();
        table.SelectCommand = command;
        table.Active = true;

        foreach (System.Data.DataRow myRow in table.Rows) {
          foreach (System.Data.DataColumn myCol in table.Columns) {
            Console.Write(myRow[myCol] + "\t");
          }
            Console.WriteLine();
        }
      }
      
      catch (Exception ex) {
        Console.WriteLine("The error occurs: {0}", ex.Message);
      }

      finally {
        conn.Close();
        Console.ReadLine();
      }
    }

[shb]

Thanks a lot!

Long Long time ago, I was a developer in Delphi and used CoreLab SDAC in one of our project. Right now I am planning to write a small database monitoring tool using VS2008, SQLDirect.Net and DevExpress. I believe that I have chosen the best tools combination!?

I appreciate your help and prompt support!

Regards,