C# Query : Failed to populate DataTable

[AmjiBhai]

The following lines have all of a suddan stopped bringing data into the dataTable [listProcDetails]

cmd.CommandText = "select name,object_id,Create_date,modify_date,@@ServerName from sys.procedures";
cmd.CommandType = CommandType.Text;
con.Open();
DataTable listProcDetails = new DataTable();
listProcDetails.Load(cmd.ExecuteReader());
con.Close();

Whereas when I try the same SQL "select name,object_id,Create_date,modify_date,@@ServerName from sys.procedures" in ssms...
it lists down all the procedures...

I guess I am missing something...Please help.

 

[Abraham Andres Luna]

I see some code is missing. Did you connect the con object with the cmd object? It seems as if you didn't connect them yet.

 

[AmjiBhai]

I see some code is missing. Did you connect the con object with the cmd object? It seems as if you didn't connect them yet.

No.... The code is preceeded by two using commands as follows

using (SqlConnection con = new SqlConnection(@SqlCon_Str))
{
using (SqlCommand cmd = con.CreateCommand())
{

It's was working fine in the morning...I tested it..... Something went wrong

 

[Abraham Andres Luna]

Okay, so if it worked before, maybe there is a permissions issue. What user are you connecting to the DB with?

 

[AmjiBhai]

Okay, so if it worked before, maybe there is a permissions issue. What user are you connecting to the DB with?

I have two servers which are located in the same office premises... I connect to them with user sa...being a super admin user Can't expect permission issue .

 

[Abraham Andres Luna]

Okay so both servers have the same database? And both have some stored procedures?

 

[AmjiBhai]

Okay so both servers have the same database? And both have some stored procedures?

Yes...I wanted to reconcile the two listings of stored procedures....The both database structures are identical.
I was lucky to have backup of my old source code which was successfully picking data....Short while ago I ran that code and glad to see its still picking the data from sql server and placing it in data table successfully....

The difference between the two is just that in the new script i am using the 'using' for SqlCon and Sql command where as in the old i am doing it without using the 'using'

Old script that works fine is as follows:

SqlConnection SqlCon = new SqlConnection(@SqlCon_Str);
var cmd = new SqlCommand("select name,object_id,Create_date,modify_date,@@ServerName from sys.procedures", SqlCon);
cmd.CommandType = System.Data.CommandType.Text ;
SqlCon.Open();
DataTable listJobDetails = new DataTable();

Whereas, in the new script I pretended to adopt a sober way (and failed!):

using (SqlConnection con = new SqlConnection(@SqlCon_Str))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{

cmd.CommandText = "MyList";
cmd.CommandType = CommandType.StoredProcedure;
DataTable listProcDetails = new DataTable();
listProcDetails.Load(cmd.ExecuteReader());

 

[Abraham Andres Luna]

I think your problem is opening the SqlCon before the using(SqlCommand) line. In the code that works you Open the SqlCon after you set the CommandType

 

[AmjiBhai]

I think your problem is opening the SqlCon before the using(SqlCommand) line. In the code that works you Open the SqlCon after you set the CommandType

You are absolutely right!