I'm working on application which needs to connect to another database to get some data, and to do that, I decided to use SqlConnection, reader etc..
And I need to execute few queries, for example first I need to get CARD ID for some user, after that I need to get some data by that CARD ID..
Here is my code:
#region Connection to another Database
SqlConnection sqlConnection1 = new SqlConnection("Data Source=ComputerOne; Initial Catalog=TestDatabase;Integrated Security=False; User ID=test; Password=test123;");
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
cmd.CommandText = "Select * From Users Where CardID=" + "'" + user.CardID + "'";
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
reader = cmd.ExecuteReader();
string cardID = "";
string quantity="";
while (reader.Read())
{
cardID = reader["CardID"].ToString();
}
//HOW COULD I WRITE ANOTHER QUERY NOW, FOR EXAMPLE, OK I GOT CARDID NOW GIVE ME SOME OTHER THINGS FROM THAT DATABASE BY THAT cardID
//here I tried to change CommandText and to keep working with reader.. but its not working like this because its throwing me exception mention in question title.
cmd.CommandText = "Select T1.CardID, T2.Title, Sum(T1.Quantity) as Quantity From CardTransactions as T1 JOIN Adds as T2 ON T1.AddsID = T2.AddsID Where T1.CardID =" + cardID + "AND T1.Type = 1 Group By T1.CardID, T2.Title";
reader = cmd.ExecuteReader();
while (reader.Read())
{
quantity = reader["Quantity"].ToString();
}
// Data is accessible through the DataReader object here.
sqlConnection1.Close();
#endregion
So guys how could I execute few queries statemens using this example.
Thanks a lot! Cheers
Your problem is that you are not disposing the objects you are using. For that purpose is better to always use using
structure, since it will guarantee you that everithing is gonna be disposed. Try the code below:
SqlConnection sqlConnection1 = new SqlConnection("Data Source=ComputerOne; Initial Catalog=TestDatabase;Integrated Security=False; User ID=test; Password=test123;");
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
string cardID = "";
string quantity="";
using(sqlConnection1 = new SqlConnection("Data Source=ComputerOne; Initial Catalog=TestDatabase;Integrated Security=False; User ID=test; Password=test123;"))
{
sqlConnection1.Open();
using(cmd = new SqlCommand())
{
cmd.CommandText = "Select * From Users Where CardID=" + "'" + user.CardID + "'";
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;
using(reader = cmd.ExecuteReader())
{
while (reader.Read())
{
cardID = reader["CardID"].ToString();
}
} //reader gets disposed right here
} //cmd gets disposed right here
using(cmd = new SqlCommand())
{
cmd.CommandText = "Select T1.CardID, T2.Title, Sum(T1.Quantity) as Quantity From CardTransactions as T1 JOIN Adds as T2 ON T1.AddsID = T2.AddsID Where T1.CardID =" + cardID + "AND T1.Type = 1 Group By T1.CardID, T2.Title";
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;
using(reader = cmd.ExecuteReader())
{
while (reader.Read())
{
quantity = reader["Quantity"].ToString();
}
} //reader gets disposed right here
} //cmd gets disposed right here
sqlConnection1.Close();
} //sqlConnection1 gets disposed right here