Home>

Imagine this situation. There is a database and some application (web or desktop) that receives information from this database. It looks like this

alt text

That is, some kind of table that is generated from the database under several conditions. I had such a question. How to get data from the database using the model correctly? Do I need to write for this one large method with a bunch of parameters, some of which will often be default parameters, or do I need to write many, many methods for each case and thus duplicate the same logic for obtaining data in different methods?

That is, in the first case, I get something like this

public IEnuerable <Someclass>GetData (int firstFilter, string secondFilter,
                                     int thirdFilter= -1,
                                     DateTime fourthFilter= default (DateTime)
                                     ....
                                     T nthFilter= defaualt (T))
{
      string sql= @ "select *
                     from Table
                     where firstCol= @first and @secondCol= @second ";
     if (thirdFilter!= -1)
         sql += "AND thirdCol= @third";
     if (fourthFilter!= default (DateTime))
         sql += "AND forthCol= @fourth";
     //and so on for all parameters
     //logic for receiving data
     ......
     return someData;
}

this option is bad with a too bloated method and a bunch of conditions and default parameters

in the second version I get this

public IEnuerable <Someclass>GetDataFirst (int filter)
{
    string sql= @ "select *
                   from Table
                   where firstCol= @filter ";
     //logic for receiving data
     ......
     return someData;
}
public IEnuerable <Someclass>GetDataSecond (int filter)
{
    string sql= @ "select *
                   from Table
                   where secondCol= @filter ";
     //logic for receiving data
     ......
     return someData;
}
///and a bunch of similar methods

In general, I'm at a loss in guesses how best to organize data retrieval without duplicating code and without inflating the size of these methods. Thanks in advance for your help

First option. The second option does not fit into any gate due to the fact that instead of one query to the database there will be four queries, which is significantly slower.

ReinRaus2021-02-23 00:35:24

I do not mean that all 4 requests should be done simultaneously. Each method is needed for a specific situation. For example, one to get users by a specific surname, the other to by age, and so on.

JuniorTwo2021-02-23 00:35:24

use the method that creates the least load on the DBMS, as well as code that is easy to read and understand. Nobody bothers to use overloaded methods ...

en20122021-02-23 00:35:24

So after all, you still need to select data by some parameters, even if you use stored procedures or Linq. And there can be many parameters

JuniorTwo2021-02-23 00:35:24

What's the problem with passing many parameters?

uzumaxy2021-02-23 00:35:24
  • Answer # 1

    I would advise to abandon the listed approaches altogether. In fact, you are now making a bicycle -your own query builder. There are a great variety of ready-made solutions. Stored procedures can be created in the DBMS and called directly. LINQ can be used. You can use CommandBuilder. The capabilities of ADO.NET and modern DBMSs allow this.

    For simplicity of explanation, we focus on one of the options I have proposed. Stored procedures. After designing the application, implementing the database and data manager, you should have a more or less fixed set of stored procedures.

    Let's say you need to remove a photo from the gallery. To do this, you have created a separate stored procedure that takes a parameter -a unique identifier for the photo. You call it, it works, everyone is happy. You also need to delete all photos of a particular user. To do this, you do not use the above stored procedure, but create a new one that takes a unique user ID as a parameter.

    >Why? Let's say I need to get from the>user table all>male users aged>20 to 40 with last names beginning>with the letter B. How can I do this with a stored procedure>without these functions? >I have to set these conditions somewhere. In addition, the code>must not be in c#, therefore linq is not>a very universal solution Create a stored procedure with a selection by age and last name. Call the stored procedure where the data is needed, what it returns. LINQ can be used in any .NET programming language.

    uzumaxy2021-02-23 00:35:24

    1) we are generally talking about any languages ​​-at least c# at least php at least java 2) how without specifying parameters to get data about these users from 20 to 40 years old male with a surname starting with the letter B? How?

    JuniorTwo2021-02-23 00:35:24

    >How to get data about these users without specifying parameters>from 20 to 40 years old males with a surname starting with the letter B? How? Why without specifying parameters? The answer contains an example. Parameters are passed to the stored procedure. Why create functions? We called a specific stored procedue in the right place with the necessary parameters, took the result, displayed it, PROFIT! >speech in general about any languages ​​-at least c# at least php at least java You are asking how to get data from the database using the model. So, .NET has its own model and methods, in JAVA everything is a little different, PHP is already another song.

    uzumaxy2021-02-23 00:35:24

    Different technologies and /or PL have different connectors to the DBMS. Own drivers for working with DBMS. Working with them is specific. There is no universal solution. There are only attempts to implement the latter: PDO, ADO.NET ...

    uzumaxy2021-02-23 00:35:24

    damn well, I'm not talking about specific technologies! Understand finally -we are talking about the fact that you need to obtain data on numerous parameters, many of which may either be absent or present. I have already given an example -in one request I need to get male users from 20 to 40 years old with a surname starting with the letter B. In another, for example, all users under 30 years old living in Moscow, and so on. I do not need specific data access technologies in specific languages. I need an elegant solution that will allow selecting data by multiple variable query parameters.

    JuniorTwo2021-02-23 00:35:24