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')
How to use DECLARE/SET in a query and get the result?
If you want to get the SqlDataReader object, you can use SqlCommand.ExecuteReader() method as shown in code below:
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();
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();
}
}
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();
}
}
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,
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,