C# Async SQL query

I am a big fan of entity framework. If it was me I would create a view from the query map it to an entity and use dbContext. and you can use it's async methods.
 
I won't like the generic one I'm using, might be a bit confusing. This is one I'm using for one of my clients, works well. While it's processing 2500 invoices I have a progress bar running on the UI so it doesn't freeze up.

Code: 
public async Task AutoInvoiceCreate()
        {
            await Task.Run(() =>
            {
                try
                {
                    using (SqlConnection conn = new SqlConnection(base.ConnectionString))
                    {
                        conn.Open();

                        using (SqlCommand cmd = new SqlCommand("autoInvoiceCreation", conn))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
                catch (Exception ex)
                {
                    ErrorLogger.LogMessage("InvoicePreview", "PreviewDAL", "AutoInvoiceCreate()", ex.Message);
                }
            });
        }

Code: 
await db.AutoInvoiceCreate();
 
Last edited:
Test
C#: 
public async Task AutoInvoiceCreate()
        {
            await Task.Run(() =>
            {
                try
                {
                    using (SqlConnection conn = new SqlConnection(base.ConnectionString))
                    {
                        conn.Open();

                        using (SqlCommand cmd = new SqlCommand("autoInvoiceCreation", conn))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
                catch (Exception ex)
                {
                    ErrorLogger.LogMessage("InvoicePreview", "PreviewDAL", "AutoInvoiceCreate()", ex.Message);
                }
            });
        }
C#: 
await db.AutoInvoiceCreate();

Sad, doesn't seem to support C++ or C#.
It does, using Prism https://prismjs.com/#languages-list

Use code=csharp for correct syntax highlighter. Above has list to all supported languages.
 
Last edited:
I just use one sqdb class with all the necessary methods. I also only use stored procedures for all my sql statements. Most of my sql calls are then done like this in code:


List<SqlParameter> sqlParameters = new List<SqlParameter>();
sqlParameters.Add(new SqlParameter("@CUST_ID", user.Cust_ID));
DataSet dsCustomers = sqlData.getDataSet(CommandType.StoredProcedure, "SP_READ_CUSTOMER_CUSTOMERS", sqlParameters);
if (dsCustomers.Tables[0].Rows.Count > 0)
{
foreach (DataRow drCustomer in dsCustomers.Tables[0].Rows)
customers.Add(getCustomerDetails(drCustomer, false, false, false, true, USER_ID));
}
else
{
customers.Clear();
customers.Add(new Customer() { Error = "No customers found" });
}
 
Have you thought of using EF or EFCore with DbContext? (Depending on your target platform) then use Dapper for CRUD operations. Just my thoughts though. It would be a lot cleaner to maintain in a long run :)
 
Mentalchaoz said:
Have you thought of using EF or EFCore with DbContext? (Depending on your target platform) then use Dapper for CRUD operations. Just my thoughts though. It would be a lot cleaner to maintain in a long run :)
Click to expand...
I don't find Dapper or ORMs useful; far easier to build a simple API without this dependency.
 
[)roi(] said:
I don't find Dapper or ORMs useful; far easier to build a simple API without this dependency.
Click to expand...

Have you used Dapper at all? Forget EF. Dapper specifically.

I find it's quite useful in getting work done, and I don't have to write a single "using" statement or open / close database connections etc. It maps my objects correctly and even if the object and/or stored procedure changes, it automatically adapts and there is no ORM logic I have to change....

Post your simple API version here so we can learn from you. i love working without silly little dependencies
 
envo said:
Have you used Dapper at all? Forget EF. Dapper specifically.

I find it's quite useful in getting work done, and I don't have to write a single "using" statement or open / close database connections etc. It maps my objects correctly and even if the object and/or stored procedure changes, it automatically adapts and there is no ORM logic I have to change....

Post your simple API version here so we can learn from you. i love working without silly little dependencies
Click to expand...
Agreed. You would abstract the persistence with an interface (API) anyway to loosely couple it to whatever implementation you choose
 
I am a fan of Dapper, but its magic is in its extension methods to `IDbConnection`. you still have to manage that connection, and as such probably need some form of `using`
 
envo said:
Have you used Dapper at all? Forget EF. Dapper specifically.

I find it's quite useful in getting work done, and I don't have to write a single "using" statement or open / close database connections etc. It maps my objects correctly and even if the object and/or stored procedure changes, it automatically adapts and there is no ORM logic I have to change....

Post your simple API version here so we can learn from you. i love working without silly little dependencies
Click to expand...
Yes, and I've also removed a lot of Dapper and other ORMs from codebases. Why is another debate entirely.
As for building simple, concise and stable APIs, I have already shared some of the basics in my functional threads; the last thread focused on a few examples of exception handling, decoupling data validation code from constructors and building a single API for SqlClient and SQLite.

If you like very terse APIs, it easy to compose this down to a single extension method, that's still flexible I.e. a higher order function like those use by Linq.
 
Last edited:
[)roi(] said:
Yes, and I've also removed a lot of Dapper and other ORMs from codebases. Why is another debate entirely.
As for building simple, concise and stable APIs, I have already shared some of the basics in my functional threads; the last thread focused on a few examples of exception handling, decoupling data validation code from constructors and building a single API for SqlClient and SQLite.

If you like very terse APIs, it easy to compose this down to a single extension method, that's still flexible I.e. a higher order function like those use by Linq.
Click to expand...

Thanks for sharing. I'm not always on here, will be helpful in future projects definitely.
 
Company I'm with wants to move to EF. My first question was "why?"

As yet I've not received a straight forward answer.

:(:confused:
 
Solarion said:
Company I'm with wants to move to EF. My first question was "why?"
Click to expand...

Do you mean why EF specifically, or why move to any ORM, or even to a simple object mapper?

I agree that it's a debate in either case.

I personally use EF often since it's a standardization, but even a simple object mapper function could be enough sometimes.

Without further justification, I typically use it within a service layer where the majority of usage would be for simple CRUD, lookups, and simple views. Complex queries are done in SQL.

Once in a while it's a good fit for certain complex updates as well... when entire sets of records need to be iterated as part of the processing.
Most often though, automatic object tracking needs to be disabled for bulk updates, which again means that EF is only being used as a read-only repository.
 
Solarion said:
Company I'm with wants to move to EF. My first question was "why?"

As yet I've not received a straight forward answer.

:(:confused:
Click to expand...
I'm not a fan of this; there have been many attempts to simplify SQL and the computations around it; EF and ORMs have not magically made things simpler. Some simply use them because the API is a bit more streamlined, but as I tried to show there are other ways to tackle this problem that can deliver far more terse APIs without the overhead of an ORM.

Personally I prefer the direct process of writing stored procedures and SQL statements instead of having some of it dynamically generated, because for example:
  • It's a proven solution that provides greater compatibility across different languages and databases.
  • There are proven set of tools available for optimization of this, ORMs just muddy the water.
  • Its a skill that is unbound from a specific language or framework.
  • Testability; the more complexity that is woven into a unit of code, the more impossible it becomes to test and diagnose problems. Linq to SQL was Microsoft's first run at this, and although EF was a directional shift from that it IMO did little to address the problem of testability.
  • Etc.

ORMs can easily over step the bounds of principles like SOLID in a similar way to the original Linq to SQL did.
FP, SOLID, Cleancode, ... principles have in general proven to me that the smaller a unit of code is, the easier it will be to reason about and test, and the less obvious spin off is that it greatly increases the potential for reuse of that unit of code; hence codebases that employ these principles become not only more terse, but also easier to reason about.
 
I'm going to try and talk them out of it. Also at this phase of crucial new development I think it would be counterproductive aka it's not going to make things easier. You've made some excellent points Roi. One thing to add I find that ORMs do not make for a neat project in general and the whole thing just feels like it's been wrapped in creeper vines all tightly interwoven whereas I like many others prefer a loose coupling of everything. Simplicity is the highest form of complexity.
 
Solarion said:
I'm going to try and talk them out of it. Also at this phase of crucial new development I think it would be counterproductive aka it's not going to make things easier. You've made some excellent points Roi. One thing to add I find that ORMs do not make for a neat project in general and the whole thing just feels like it's been wrapped in creeper vines all tightly interwoven whereas I like many others prefer a loose coupling of everything. Simplicity is the highest form of complexity.
Click to expand...
I too find it unnecessary complex; but it's not the end of the world if a project chooses to use it.

There are many opportunities to refine ORMs like EF or any framework for that matter; meaning you don't just have to accept complexity; there will always be many opportunities to simplify the API and/or the code around it.
 
Last edited:
My 2c , although you seem to be completely dismissing EF for all scenarios as if it's a GUI RAD tool... :)

I do find EF useful but have learnt to avoid the frustrations.

Using 'code first from an existing database', you bypass all the frustrations involved with mapping the model to your db. If you do want to extend the generated classes, you can do so in partial classes.

Limit the use of EF to a certain layer. You may have to consider whether unit testing will mock the data access or whether you'll use an in-memory dbcontext. It's tricky because the usefulness of EF is limited when wrapped inside a data access layer, but that's what is required for simple unit testing. At the least avoid any EF dependency past a service layer.

I much prefer having/creating statically typed queries as part of my code.
So to me it makes sense to use EF when 95% of your queries are no better when done via SQL... the remaining 5% can be done in SQL rather than forcing everything via Linq to entities.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X