How to optimize access to the Database?

Asked

Viewed 70 times

0

I’m doing a project where I often need to access the database to do things like:

  • Load User Data (Example: Balance, Settings);
  • Upload images;
  • Insert/Update data.

It’s a pretty boring process to have to do the same code over and over and just change the SQL, or the Table.

I decided to make a Connection class, but within this class I only keep the Connectionstring, ends up not changing much.

public class DBConnection
{
     public const string ConnectionString = @"....";   
}

I inherit this class in all classes I need to access the Database, with this code below I update the User Balance.

using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
using (SqlCommand sqlCommand = new SqlCommand("UPDATE tblClientes SET Saldo += @Saldo WHERE Usuário = @Usuário", sqlConnection))
{
    sqlCommand.Parameters.Add("@Saldo", SqlDbType.Real).Value = Saldo;
    sqlCommand.Parameters.Add("@Usuário", SqlDbType.NVarChar, 50).Value = Usuario;

    sqlConnection.Open();
    sqlCommand.ExecuteNonQuery();
}
  • How can I optimize this?
  • So I don’t have to repeat this same code over and over again in my project?
  • 3

    There are several approaches you can follow: you can look for some design pattern that helps you abstract this whole part as DAO, or a repository standard. You can also search for an ORM like the Entity Framework, the Dapper or the Nhibernate.

  • All you need is an ORM like the Entity Framework.

  • I’ll look into it, thank you!!

  • Following @LINQ’s advice focus on the repository pattern and try to really understand how it works and how to implement it, this is the basis for the other tips he gave. And you don’t necessarily need a ORM, because much of the optimization will still involve questions in the database like queries review and index creation.

  • A framework helps to standardize, of course for this environment is what has already "semi-ready", but a simple thing that can be applied anywhere, would be to create a method that takes as first parameter a string, this string would be the query, and within the method has the connection and command process. Okay, I wouldn’t have to keep repeating code like that... I totally understand wanting to teach frameworks and ready-made systems, but something basic and even trivial like avoiding code repetition with a simple method is something that should be learned before going for the most glamorous

  • I had thought of creating methods for each function to run in the Database, such as Select, Update, Delete, etc. In the constructor of the class I would receive the Query and in the methods I would receive an array of parameters, which in case are the ones that would be in the Query, thinks this is a good idea @Guilhermenascimento?

  • @Henriqueferreira I think q only a method solves, it is no use to embellish much, even more if you have "compound" queries (type sub-query or use of JOIN). You can play everything in the string, don’t think q will create this to use forever, have to create to solve the now, but of course, well done and simple, the rest comes with the time, practice and experience that will be acquired, so always support the simple, instead of pushing complex frameworks to people, the good is starting from the basics :)

  • @Guilhermenascimento I understand, depending on what I want to execute the code looks different, so whether I want to or not I will need more than one method. Thank you so much for your help!

  • 1

    I didn’t see Inheritance (Eu herdo essa classe em todas as classes que preciso acessar o Banco de Dados, com esse código abaixo eu atualizo o Saldo do usuário.) by your comment, it will depend a lot, in your case that is a Windows Forms, I would use singleton in the connection class and create classes DAL( Data Access Layer) to perform operations in the Database. Or else, Dapper and Entity Framework, where the micro Orm (DAPPER) for complex SQL will require performance and Entity Framework (ORM) for insertion, removal and various updates. Remember that the scenario not exposed by you counts a lot

  • @Virgilionovic didn’t see why I didn’t show this in the Balance class, but basically what I get is just the Dbconnection class to use the Connectionstring.

  • @Henriqueferreira even maybe that’s a problem! cannot inherit so, have to parse well, I would make a Singleton connection class not only with the conection string, but, yes with a command and other structures already prepared to use, or else goes from Dapper.

Show 6 more comments
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.