C# - What are the best practices when working with DBs in Windows Applications? [closed]

Question

I mostly write Windows Applications using C#. I find that when I need to implement Databases, using Data Sources, Table Adapters etc. my code becomes really untidy. I also find that when I need to do very specific processing of data, I need to write Classes for the tables anyway (And I usually use Dapper). Another thing is if I wish for my application to be able to use more than one Database Provider, it is quite a tedious process to connect to the other Provider using Table Adapters etc. The final thing is, I don't like the fact of simply clicking a + (Add) button on a Data Navigator and everything gets handled "Behind the scenes" and having a local set of the data which only gets sent to the Database after clicking a "Save" button (EndEdit(), etc.).

I like being able to click a button and whatever is on the current form gets Created, Read, Updated, Deleted or whatever without having a bunch of events being fired. (Which I don't even know happens half the time.)

Is it a good idea to just use normal queries and a DbConnection? As a simple example:

void OnLoad(object sender, EventArgs e){
    string query = "SELECT * FROM Foo";
    BindingList<Foo> FooCollection = this.dbInstance.QueryMany<Foo>(query);
    dataGridView.BindingSource = FooCollection;
}

Then adding a row would simply be something like:

void OnAddClick(object sender, EventArgs e){
    Foo foo = new Foo();
    foo.UserName = "John Snow"
    bool success = this.dbInstance.Create(foo);
    if(success)
        FooCollection.Add(foo);
}

Is this a semi-decent approach? Would I run into many errors or problems using this type of approach? What would you recommend or how do you work with Databases when writing a Windows Forms Application? I would really appreciate your suggestions.

Don't really know why my question got closed. Seeing as the first answer was quite informative and gave me a better insight into working with Databases.

Answer 1

It depends on your needs, but basically it's a matter of performance and how big your project will be.

ORMs are designed to do things behind the scene to automate the process of mapping data object to your code and to be able to access data objects from your programming language.

If your application is primarily concerned with performance, you can choose to use ADO.NET, ODBC, or OLEDB connections.

But the queries would be better to express them in a way that you don't have to change the code to be able to change them.

context.Database.SqlQuery<YourEntityType>("storedProcedure")

This is a really good reference for this question

Words of Wisdom: Dapper just does mapping but you need to code a lot , EF does much more on the top of it and not just mapping. So EF will be slow. I can also say that pure ADO.NET is faster than Dapper , OLEDB is faster than ADO.NET and ODBC can be faster than OLEDB. So if I am serious about performance I would probably avoid any ORM.