SQL: getting and storing data
-
Hi, Best way is to show you what I want: SQL Database:
SqlConnection connection = new SqlConnection(connectionString); SqlCommand getTableList = connection.CreateCommand(); string command = "Select name from sysobjects where type = 'U'"; getTableList.CommandText = command; connection.Open(); **string tables = getTableList.ExecuteScalar().ToString();** connection.Close(); MessageBox.Show("We got ..\n" + tables);How do I store the results of the SQL Command? The command appears to work OK but only the name of the first table is recorded in "tables". Ideally I would like to put the results of the SQL command into an array. TIA.Glen Harvy
-
Hi, Best way is to show you what I want: SQL Database:
SqlConnection connection = new SqlConnection(connectionString); SqlCommand getTableList = connection.CreateCommand(); string command = "Select name from sysobjects where type = 'U'"; getTableList.CommandText = command; connection.Open(); **string tables = getTableList.ExecuteScalar().ToString();** connection.Close(); MessageBox.Show("We got ..\n" + tables);How do I store the results of the SQL Command? The command appears to work OK but only the name of the first table is recorded in "tables". Ideally I would like to put the results of the SQL command into an array. TIA.Glen Harvy
Glen, You only get the first table because you're using the executeScalar method. To get what you want you need to use the executeReader. Your code would thus be smth like: SqlConnection connection = new SqlConnection(connectionString); string command = "Select name From sysobjects where type ='U'"; SqlCommand getTableList = new SqlCommand(command, connection); myConnection.Open(); SqlDataReader reader = getTableList.ExecuteReader(CommandBehavior.CloseConnection); List tables=new List(); while(reader.Read()) { tables.Add(reader.GetString(0)); } reader.Close(); //Implicitly closes the connection because CommandBehavior.CloseConnection was specified. This should get you the list. Greeting Kaine
-
Glen, You only get the first table because you're using the executeScalar method. To get what you want you need to use the executeReader. Your code would thus be smth like: SqlConnection connection = new SqlConnection(connectionString); string command = "Select name From sysobjects where type ='U'"; SqlCommand getTableList = new SqlCommand(command, connection); myConnection.Open(); SqlDataReader reader = getTableList.ExecuteReader(CommandBehavior.CloseConnection); List tables=new List(); while(reader.Read()) { tables.Add(reader.GetString(0)); } reader.Close(); //Implicitly closes the connection because CommandBehavior.CloseConnection was specified. This should get you the list. Greeting Kaine
I agree fully with Kaine. just an alternate method when using a reader.
SqlDataReader reader = getTableList.ExecuteReader();
and then continue to add to the tables, while using reader.Read().
Keshav Kamat :) India
-
Glen, You only get the first table because you're using the executeScalar method. To get what you want you need to use the executeReader. Your code would thus be smth like: SqlConnection connection = new SqlConnection(connectionString); string command = "Select name From sysobjects where type ='U'"; SqlCommand getTableList = new SqlCommand(command, connection); myConnection.Open(); SqlDataReader reader = getTableList.ExecuteReader(CommandBehavior.CloseConnection); List tables=new List(); while(reader.Read()) { tables.Add(reader.GetString(0)); } reader.Close(); //Implicitly closes the connection because CommandBehavior.CloseConnection was specified. This should get you the list. Greeting Kaine
Thansk for that - much appreciated.
Glen Harvy