EliasMP EliasMP - 3 months ago 15
ASP.NET (C#) Question

How to Optimizing ADO.Net queries?

I need to optimize my Data Access through ADO.Net. For requeriments of the project I can´t use Linq or Entity...

So I´m launching 15 independents ADO.NET queries. I´m following the standards, Open the connection, launching the query, use the DataReader for getting the Data, storing them in classes, and finally I close the connection...

But I´m feeling like the project is not fast enough. So, Guys, Can you all teach me some tips for improve the speed of it?

My Database is SQL Server, and as far as I can, I´m lauching queries by entity.

EDITED:

private void Connect(string comando) {
try {
string cadena = "connection_data";
Cn = new SqlConnection(cadena);
Cn.Open();
SqlCommand Com = new SqlCommand(comando, Cn);
Datos = Com.ExecuteReader();
}
catch (Exception e)
{
//
}
}

private void Close()
{
try
{
Cn.Close();
Datos.Close();
}
catch (Exception e)
{
//
}
}

public List<class1> getClass1(double id)
{
Connect("SELECT TOP (5) CASE WHEN [table1].[attribute1] IN (7, 12, 13, 14, 15, 16, 17, 18, 19, 28, 29) THEN 'random_name' " +
"WHEN [table1].[attribute3] = NULL AND [table1].[attribute2] = NULL THEN 'random_name2' WHEN [table1].[attribute3] = NULL THEN [table1].[attribute3] ELSE "
// bla bla bla bla and so on and I have 15 massive queries like this one;

List<class1> res = new List<class1>();

if (Datos.HasRows == true)
{
while (Datos.Read())
{
class1obj = new class1();
obj.at1= class1.cont + 1;
obj.at2= class1.cont + 1;
class1.cont++;
obj.at3= "random_value";
obj.at4= Datos.GetValue(0);
obj.at5= Datos.GetValue(1);
res.Add(obj);
}
Close();
}
return res;
}

Answer

If you do have a function of creating for each class then what you can do is something like this:

Instead of having a function getClass1 Have an interface:

public interface IBuildClass<TClass>
{
    Task<IEnumerable<TClass>> BuildAsync(double id);
}

Add a base class for the interface that will request as a dependency a connection object to the database

public abstract class BuildClassBase<TClass> : IBuildClass<TClass>
{
    public BuildClassBase(SqlConnection connection)
    {
        Connection = connection;
    }

    public async Task<IEnumerable<TClass>> BuildAsync(double id)
    {
        //Execute query and pass results to InnerBuid
        return InnerBuildAsync(/*Pass DataTable*/);
    }

    public abstract async Task<IEnumerable<TClass>> InnerBuildAsync(DataTable data);

    //Ctor of each derived class will set the value
    public string Query { get; protected set; }
    public SqlConnection Connection {get; set; }
}

And then a specific implementation matching each class you want to create. You can also add a factory for them:

public interface IBuildClassFactory
{
    IBuildClass<TClass> GetBuilder<TClass>();
}

For different implementations of factory patterns ("normal"/flyweight/method/..) check SO Documentation

Then in your code where you currently execute all 15 methods you can create a Task for each class and then await buildClassFactory.GetBuilder<ClassA>().BuildAsync(id). Something like:

double id = 1;
Task createClassA = buildClassFactory.GetBuilder<ClassA>().BuildAsync(id);
Task createClassB = buildClassFactory.GetBuilder<ClassB>().BuildAsync(id);
Task createClassC = buildClassFactory.GetBuilder<ClassC>().BuildAsync(id);
....

Task.WaitAll(createClassA, createClassB, createClassC....);

//And then get result from tasks
Comments