Cómo evitar la duplicación en la connection sql y ejecutar el command

Puedo usar este ciclo para darme una list de nombres:

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) { } } 

entonces el dt.Rows[i][0].ToString() es el nombre que necesito agregar a todos mis diferentes commands sql. Así que dentro de ese ciclo for obtendré que cada valor ejecute cada command sql, uno por uno:

 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); 

Donde la cadena de command está determinada por:

  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"; 

¿Puedo ejecutar múltiples commands sql como arriba? ¿Cómo es la mejor manera de hacer eso?

Una forma de resolverlo es a través de JOIN en SQL. Sin embargo, puede que no sea lo correcto si no representa las mismas columnas.

Ahora en términos de usar selección múltiple en un command. Sí, puedes usar SqlDataReader con NextResult ()

Por favor mira este enlace:

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