Juan Pelotas

Juan Pelotas

  • NA
  • 15
  • 1.8k

Parallel SqlDataReader and reader.Read()

Sep 9 2015 8:13 PM

I want to do a consult in Parallel mode, or which is the better way to do the consult to SQL.

The consult spends 3 minutes doing 7 cycles from 7 stocks, but I need that spend less time, maybe by doing the 7 consults at the same time and return the result.

My problem with the code is that reader.Read() maybie shock between the consults.

 <code>
List<List<clsExistenciaA>> ListaExistencia = new List<List<clsExistenciaA>>(); 
SqlConnection objConexion = new SqlConnection();//conexion a sql
try {
objConexion
.ConnectionString =
@"Database=DataDB;Data Source=local;User
Id=sa;Password=20011$;MultipleActiveResultSets=True";
objConexion
.Open(); } catch { MessageBox.Show("No se puede conectar al servidor");
 } try { string almacen; int i = 0; foreach(clsExistenciaA A in AlmaceneS)
{ //Parallel.ForEach(AlmaceneS, clsExistenciaA => //{ //
lock (AlmaceneS) // { //
almacen = clsExistenciaA.CodigoAlm;
almacen
= A.CodigoAlm; SqlCommand comm = new SqlCommand();//crea command
comm
.Connection = objConexion;// se agrega la conexion al comando

comm
.CommandType = CommandType.Text;// se define el tipo de comando string
sql = ""; sql = "select c_codigo_alm, v_nombre_alm from invalmacen
(nolock) where c_pedido_alm='almacen'"; comm.CommandTimeout = 1000;
comm
.CommandText = sql;// se agrega la consulta a el comando
SqlDataReader reader = comm.ExecuteReader();
// se ejecuta el comando en un data reader

ListaExistencia.Add(new List<clsExistenciaA>());
string piezaN = "0";
string kiloN = "0";

while(reader.Read()) { if (reader["pieza"].ToString() != "") {
piezaN
= reader["pieza"].ToString(); }
if (reader["kilos"].ToString() != "") {
kiloN
= reader["kilos"].ToString(); }
ListaExistencia[i].Add(new clsExistenciaA() {
pro
= reader["pro"].ToString(),
nompro
= reader["v_nombre_pro"].ToString(),
pieza
= Math.Round(double.Parse(piezaN), 2),
kilos
= Math.Round(double.Parse(kiloN), 2),
CodigoAlm = almacen, }); // se agregan los datos a un Ilist }

reader
.Close(); } ////} //});

return ListaExistencia; }</code>

Answers (13)