1

I have:

 string commandText = @"SELECT cn.companyName from Companies cn 
               INNER JOIN KeyProcesses uc ON uc.companyId = cn.companyId  
               WHERE uc.description like '%" + ProcessInputClause + "%';";

 string addr = @"SELECT address FROM Companies where companyName = @companyName";

To execute that I tried:

SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = commandText;
sqlCmd.Parameters.Clear();
string connectionString = ConfigurationSettings.AppSettings["connectionString"];
using (SqlConnection connection = new SqlConnection(connectionString))
{
   SqlCommand command = new SqlCommand(commandText, connection);
   try
   {
       connection.Open();
       sqlCmd = new SqlCommand(sqlCmd.CommandText, connection);
       SqlDataReader sqlReader = sqlCmd.ExecuteReader();
       DataTable dt = new DataTable();

       sqlReader.Read();
       dt.Load(sqlReader);
       Label1.Text = dt.Rows[0][0].ToString();
       sqlCmd.CommandText = addr;
       SqlCmd.Parameters.AddWithValue("@companyName", "namehere");
       SqlDataReader addressReader = sqlCmd.ExecuteReader();
       addressReader.Read();
       Label1.Text = Label1.Text + addressReader["address"].ToString() + addressReader.GetValue(1).ToString() + addressReader.GetString(0) + addressReader.GetString(1);

I can get only the first sql to execute, and get the companyName to Label1.text. But it looks like the second executeReade() gives me no result, although I tried the query alone successfully! I also tried the combine commandtext and use nextresult but no luck. Can you help me in how can I execute both command successfully ? Ps: added calling addressReader.Read(); I forget to copy this, tried with this but no result !

7
  • 7
    let's repeat the mantra: "do not concatenate values into SQL", "do not concatenate values into SQL", "do not concatenate values into SQL" Commented May 29, 2014 at 8:57
  • You never call addressReader.Read. In any case, you really need to clean up the code, separate the two executions and and not try to update the UI in the same method that calls the database. AND don't concatenate values, pass the entire %something% thing as a parameter Commented May 29, 2014 at 8:57
  • @i_Am_Suhaim calling a stored procedure is no different than sending a raw command if the code never reads the result Commented May 29, 2014 at 9:04
  • @i_Am_Suhaim "use stored procedure" is not actually useful advice for most people. There was a time in the late 90s and early 00s when that was genuinely a good idea, but for a long time now, all the RDBMS have had really good cache plan re-use for parameterized SQL send as command-text. The benefits of stored procedures are pretty minimal these days, outside of a few very niche scenarios. Yes, it is necessary to parameterize correctly, but that is a separate concern. Commented May 29, 2014 at 9:10
  • Two unrelated observations: your Read() above dt.Load() is actually throwing away the top row of data; and, your second batch of code accesses 2 columns - but your query only requests 1. Commented May 29, 2014 at 9:12

3 Answers 3

6

The first thing to do is to parameterize that first query ;p

After that, all you need to change here is to actually consume the second reader:

using(var addressReader = sqlCmd.ExecuteReader()) {
    if(addressReader.Read()) {
        Label1.Text = Label1.Text + addressReader["address"].ToString()
         + addressReader.GetValue(1).ToString() + addressReader.GetString(0)
         + addressReader.GetString(1);
    }
}

EDIT: removing this, as it doesn't apply since the two queries are hierarchical

You could perform both selects in a single sql operation (NextResult(), etc), but I'm not sure that dt.Load will work well with that; worth a try, though:

sqlCommand.CommandText = @"
    SELECT cn.companyName from Companies cn 
    INNER JOIN KeyProcesses uc ON uc.companyId = cn.companyId  
    WHERE uc.description like '%' + @description+ '%';

    SELECT address FROM Companies where companyName = @companyName;";

sqlCommand.Parameters.AddWithValue("description", ProcessInputClause);
sqlCommand.Parameters.AddWithValue("companyName", "namehere");
using(var reader = sqlCommand.ExecuteReader()) {
    dt.Load(reader);
    if(reader.NextResult() && reader.Read()) {
        Label1.Text = Label1.Text + reader["address"].ToString()
         + reader.GetValue(1).ToString() + reader.GetString(0)
         + reader.GetString(1);
    }
}

With a dependency between the queries, and the fact that the address is part of the company record, I would just do this:

@"SELECT cn.companyName, cn.Address from Companies cn 
           INNER JOIN KeyProcesses uc ON uc.companyId = cn.companyId  
           WHERE uc.description like '%" + @description + "%';";

a single query that fetches both values. You could use your existing dt.Load to access that, getting the name from the first column and the address from the second - but frankly I'm a huge fan of "dapper", so I'd do it this way:

class Company {
    public string CompanyName {get;set;}
    public string Address {get;set;}
}
...
var rows = conn.Query<Company>(
    @"SELECT cn.companyName, cn.Address from Companies cn 
      INNER JOIN KeyProcesses uc ON uc.companyId = cn.companyId  
      WHERE uc.description like '%" + @description + "%';",
    new { description = ProcessInputClause }).ToList();

then just iterate over rows:

foreach(var row in rows) {
    string name = row.CompanyName;
    string address = row.Address;
    // ...
}

Or if you don't want to declare the type:

var rows = conn.Query(
    @"SELECT cn.companyName, cn.Address from Companies cn 
      INNER JOIN KeyProcesses uc ON uc.companyId = cn.companyId  
      WHERE uc.description like '%" + @description + "%';",
    new { description = ProcessInputClause }).ToList();
foreach(var row in rows) {
    string name = row.companyName; // yes, this works
    string address = row.Address;
    // ...
}

In the scenario where multiple tables are involved, you can use a table-variable as the base for joins:

declare @ids table (CompanyId int not null)
insert @ids (CompanyId)
select companyId from Companies cn
INNER JOIN KeyProcesses uc ON uc.companyId = cn.companyId  
WHERE uc.description like '%" + @description + "%';

select cn.CompanyName, cn.Address
from @ids #i
inner join Companies cn on cn.CompanyId = #i.CompanyId

select /* other stuff */
from @ids #i
inner join /* other tables */

select /* yet more other stuff */
from @ids #i
inner join /* yet more other tables */
Sign up to request clarification or add additional context in comments.

13 Comments

can I use sqlCommand.Parameters.AddWithValue("companyName", "namehere"); inside the using(var reader = sqlCommand.ExecuteReader())? because I need to add like this: sqlCmd.Parameters.AddWithValue("@companyName", dt.Rows[0][0].ToString());
@user1314404 ah, right - then you need either two separate queries or a much more complicated single query - two separate queries is probably simpler. Let me rewrite a different way a moment...
@user1314404 question: how many companies, and how many addresses, are you expecting?
The query for CompanyName will return many names. So for each name I need to query different address. One company name will have only one address.
@user1314404 see the edit; if Address is a column on cn, this is actually really really simple; no need for two queries at all
|
0

You can put both commands on an string array and do foreach statement command in commands.

Like this:

string command1 = @"SELECT cn.companyName from Companies cn 
               INNER JOIN KeyProcesses uc ON uc.companyId = cn.companyId  
               WHERE uc.description like '%" + ProcessInputClause + "%';";
string command2 = @"SELECT address FROM Companies where companyName = @companyName";

string[] commands  = new string[2];
        commands.SetValue(command1 , 0);
        commands.SetValue(command2 , 1);

Then:

Foreach(string command in commands)
{
    SqlCommand c= new SqlCommand(command , connection);
    //do the rest
}

7 Comments

@chridam everyone seems to be getting one; sometimes, people are just grumpy
SetValue is to transfer value "0" and "1" to variable @companyName isn't it ?
@user1314404 Nope, commands.SetValue(command1 , 0); transfers the first string command to the first index of the string array! Applies to the second SetValue too.
@MarcGravell And quick to pull the trigger at times :-)
It is unclear to me why you would initialize the arrays that way (or indeed why you would use arrays at all here), but a simpler way would be : var commands = new[] {command1, command2};, which has the same result
|
0

Use DataSet instead of Datatable

DataSet ds = new DataSet();

SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);


SqlDataAdapter da = new SqlDataAdapter(qry, connection);

da.Fill(ds);

1 Comment

DataTable by itself is bad enough; what has the OP done to you to annoy you so much that you would wish DataSet upon them?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.