Question Advice on Accessing & Updating my Database

[vodkasoda]

I have a few projects, relating to the same subject. The programs are written in Excel VBA using Access, VB.net, and even a very old one in GFA Basic !!! I am now going to rewrite all of them in VB.net and incorporate the one Database, using SQL Server Express. The logic and all the problems I am going to face, I am ok with, my question revolves solely around SQL Server Express, the Database is created and mostly populated ...

So, should I :

Do all my Database transactions, reading, writing, updating, deleting, inserting etc., using code written in my VB.net program(s) ?

Create each and every Query I am likely to need in SQL Server Management Studio and then call those Queries from my program(s) ?

Some advice from people who have done this sort of thing before and know what they are talking about, rather than me taking a guess and doing it all wrong, would be really helpful. Also, any links to examples would be great ...

 

[jmcilhinney]

There are pros and cons to storing your queries in the database and in the application. As an application developer though, I'd suggest that the latter should be your first choice. In your case, I would build the database and then generate a data access library. You can do that in VS using the Data Source wizard and create either a typed DataSet or an Entity Framework model. I would absolutely go with the latter although it means learning a few things, most notably LINQ. Whichever way you go, you can then use that one DAL in all your applications.

You may already be aware but also note that, if you want to use the one database for multiple applications, you will have to create the database in Management Studio and it will have to be permanently attached to an instance that all users can access. Otherwise your multiple applications will all be working with separate databases.

 

[vodkasoda]

Thank you, I realise I may have to learn (yet more !) new things, but I do want to do this properly so that (a) it lasts me and it should be easily maintainable & update-able and (b) I will be able to get help when I need it !!!

I have created the Database in Management Studio, but it is a single-user application, specifically for me at this point in time. If I deceide to share it in future then there is another good reason for it to be written properly, but again it will also be used by a single-user, so I shouldn't have problems in that respect ... I hope !!!

Thanks for getting back to me so soon, I appreciate it ...