How to work correctly with a database?

Question

0.00/5 (No votes)

See more:

Hi,
I'm new, and I need beginner's help.
I have built a system that searches for words in a database, and if it does, it displays within a list.
I realized that the way I do it is very slow and cumbersome, I want to know how to do it more correctly.

Regards

What I have tried:

LblSrc.Text = src;

               List<Piska> allPiskaot = Blm.BLSrcCustomers(src);

bl-

public static List<Piska> BLSrcCustomers(string src)
       {
           List<Piska> EntityList = new List<Piska>();
           List<Piskaot> DBList = DAlManager.GetSrcPiska(src);
           foreach (var item in DBList)
           {
               EntityList.Add(ConvertPiskaDbToPiskaEntity(item));
           }
           return EntityList;
       }



       public static Piska ConvertPiskaDbToPiskaEntity(Piskaot piska)
       {
           return new Piska
           {
               Id = piska.Id,
               Tags = piska.Tags,
               PTitle = piska.Ptitle,
               Tohen = piska.Tohen,
               Edited = piska.edited,
               Book = piska.book,
               Chapter = piska.chapter
           };
       }

dal-

public static List<Piskaot> GetSrcPiska(string src)
{
    using (var context = new DBNafshenuEntities())
    {
        var result = context.Piskaot.ToList();
        List<Piskaot> returnList = new List<Piskaot>();
        foreach (var item in result)
        {

            if (item.Tohen.Contains(src))
            {

                returnList.Add(item);
            }

        }
        return returnList;
    }
}

Posted 4-Jul-20 11:30am

Member 14683862

Updated 6-Jul-20 3:45am

Add a Solution

Comments

PIEBALDconsult 5-Jul-20 1:20am

I see a number of issues.
The use of .ToList() is a red-flag for me, but more importantly is that you appear to be getting the full list of data items, converting them all to objects, and _then_ filtering then -- ideally the filtering should be done within the database.

Member 14683862 5-Jul-20 3:39am

Could you please tell me how to write code that can work directly on the database?

2 solutions

Solution 1

“More correctly” is a relative term. you should expect to be able to write better code until you’ve written a lot more beginner code. If your code meets the stated requirements, and runs without errors, it’s fine.

Posted 4-Jul-20 14:18pm

#realJSOP

Comments

Member 14683862 5-Jul-20 3:38am

Right now it's working fine, I'm scared about the time loading as the data meter grows

#realJSOP 5-Jul-20 10:11am

Well, it looks like you're using EntityFramework, so I can't comment because I refuse to use it. I thought it deserialized the data into entities for you, but I might be wrong...

Member 14683862 5-Jul-20 11:13am

If you use SQL, I want to know how to add SQL code in VISUALSTUDIO-

String connectionString = WebConfigurationManager.ConnectionStrings["DBNafshenuConnectionString"].ToString();
StringBuilder sb = new StringBuilder();

using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand commandPodesavanje = new SqlCommand("SELECT * FROM Users", connection))
{
SqlDataReader reader = commandPodesavanje.ExecuteReader();
while (reader.Read())
{

}
reader.Close();
}
}

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2020-07-06T03:45:00

Assuming you're using Entity Framework, you can pass the search into the database so that you're not loading more records than necessary.

DAL:

C#

public static List<Piskaot> GetSrcPiska(string src)
{
    using (var context = new DBNafshenuEntities())
    {
        return context.Piskaot.Where(item => item.Tohen.Contains(src)).ToList();
    }
}