How to avoid duplication in sql connection and execute command

Question

I can use this loop to give me list of names:

string commandText = @"SELECT ....;";

string connectionString = ConfigurationSettings.AppSettings["connectionString"];

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(commandText, connection);
    try
    {
         connection.Open();                        
         using (SqlDataReader reader = command.ExecuteReader())
         {
             DataTable dt = new DataTable();
             dt.Load(reader);

             for (int i = dt.Rows.Count - 1; i >= 0; i--)
             {                               
                 SqlCommand addresscommand = new SqlCommand(address, connection);
                 addresscommand.Parameters.AddWithValue("@companyName", dt.Rows[i][0].ToString());
                 SqlDataReader addressreader = command.ExecuteReader();
                 string address = addressreader.GetString(0);
              }
          }
     }
     catch (Exception ex)
     {

     }
}

so the dt.Rows[i][0].ToString() is the name I need to add to all my different sql commands. So inside that for loop I will get each value from executing each sql command, one by one:

SqlCommand addresscommand = new SqlCommand(address, connection);
addresscommand.Parameters.AddWithValue("@companyName", dt.Rows[i][0].ToString());
SqlDataReader addressreader = addresscommand.ExecuteReader();
string comaddress = addressreader.GetString(0);
SqlCommand keyProcessescommand = new SqlCommand(keyProcesses, connection);
keyProcessescommand.Parameters.AddWithValue("@companyName", dt.Rows[i][0].ToString());
SqlDataReader keyProcessesreader = keyProcessescommand.ExecuteReader();
string comkeyProcesses = keyProcessesreader.GetString(0);
SqlCommand standardscommand = new SqlCommand(standards, connection);
standardscommand.Parameters.AddWithValue("@companyName", dt.Rows[i][0].ToString());
SqlDataReader standardsreader = standardscommand.ExecuteReader();
string comstandards = standardsreader.GetString(0);

Where the command string determined by:

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

 string keyProcesses = @" SELECT distinct STUFF((SELECT ', '+ cn.name  from WMCCMCategories cn 

           INNER JOIN CategorySets uc ON uc.categoryId = cn.categoryID 
           INNER JOIN KeyProcesses u ON u.categorySetId = uc.setId
           INNER JOIN Companies c ON c.companyId = u.companyId
           WHERE c.companyName = @companyName 
           ORDER BY cn.name
           FOR XML PATH('')), 1, 1, '') AS listStr
           FROM WMCCMCategories cnn Group by cnn.name";

 string standards = @" SELECT cn.name from WMCCMCategories cn 
               INNER JOIN CategorySets uc ON uc.categoryId = cn.categoryID 
               INNER JOIN Companies c ON c.standards = uc.setId
               WHERE c.companyName = @companyName";

Can I execute multiple sql commands like above? How is the best way to do that ?

Why I have the impression that this could be solved by a simple JOIN statement? — Steve
– Steve, Commented May 28, 2014 at 10:33
As you see, the address, keyProcesses can get from same sql query and join statemet (just what I guess, haven't try). But it looks like the standards is not able to be get from same sql, coz the two table "Companies and CategorySets link by standards, categorySetId and setId. So there is a conflict if use the same sql, i think. — user1314404
– user1314404, Commented May 28, 2014 at 15:26

codebased · Accepted Answer · 2014-05-28 10:53:39Z

0

One way you can solve this through JOIN in SQL. However, it may not be right thing to do if it is not representing same columns.

Now in terms of using multiple select in one command. Yes, you can use SqlDataReader with NextResult()

Please see this link:

http://csharp.net-informations.com/data-providers/csharp-multiple-resultsets.htm

answered May 28, 2014 at 10:53

codebased

7,07311 gold badges57 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1314404 Over a year ago

I see the definition of NextResult is suitable for my case, but in your example link, I do not see different sql queries. How can that nextresult be applied for my case? Thank you!

user1314404 Over a year ago

Because in my sql commands I need to transfer variable value into it later on, so it is quite different from your example. I cannot write like this: sql = @"SELECT cn.companyName from Companies cn INNER JOIN KeyProcesses uc ON uc.companyId = cn.companyId WHERE uc.description like '%" + ProcessInputClause + "%';"; @"SELECT address FROM Companies where companyName = @companyName";

user1314404 Over a year ago

How could I use my sql injection like: command.Parameters.AddWithValue("@companyName", dt.Rows[i][0].ToString()); with your nextresult() method ?

Collectives™ on Stack Overflow

How to avoid duplication in sql connection and execute command

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related