Basic Database Tutorial

[Mikefly95]

I have seen several posts on here about using a database or using CSV, Excel or Access and i thought i would try to help out a bit and see if i can get you guys up and running with MS SQL Server.

I am not a Youtuber!! I tend to juggle multiple projects all day everyday so i will just point you to some videos on installing MS SQL Server and some of the other basic stuff. I will attempt to make the AHMI videos you will need.

I do not use Entity for my database transactions. I am not saying it is good or bad just I just choose not to use it. I will post the class that i built and use in every project i create that will make for easy database transactions.

What I will cover-
1. Installation of MS SQL Server
2. Creating a Database and Tables
3.Basic SQL Statements
   a. Insert
   b. Select
   c. Update
   d. Delete
4. Adding a project to AHMI
5. Adding a connection/transaction class i wrote and use in every project
6. Using SQL Statements in AHMI
7. Using a Data table
8. Populate a datagrid

Get it installed!!

The video i decided to use if very detailed and easy to follow. The only thing is on the download page you will see a choice that will let you download and install both the Engine and SQL Server Management all in one and install them at the same time. Up to you. Make sure you make your SA password he shows you to set up something you can remember we will need it later on!!

https://www.youtube.com/watch?v=E_zFM7mzFUg

This next video there is no speaking but he does a very good job of explaining both ways to create a new database and table using Management Studio. Also you will see how to use a Select and Insert statment.

https://www.youtube.com/watch?v=OEpaCxu2SWk

Very in depth look at SELECT. The gentleman that put this tutorial up is an amazing instructor!!

https://www.youtube.com/watch?v=R9pXnHIFj_8&index=10&list=PL08903FB7ACA1C2FB

That should get you all started and i will find the rest of the intro videos and start working on the AHMI specific tutorials over the next week.

Hit me up with any questions or requests!!!

Have fun



If you have 4 hours to devote to learning SQL Server please watch this video
https://www.youtube.com/watch?v=u2huSNXLYUg

[Mikefly95]

Sorry got super busy. I will get back to this in about a week.

[bachphi]

Appreciated the effort!!

[EE_Kraig]

Mikefly95 - You may want to look into SQLite https://www.sqlite.org/index.html

I was using MS SQL for an application and all was well, but deployment to the target machine was a mess. I switched over to SQLite and you can use simple xcopy deployment. This made things *way* easier. All the SQL commands remain the same so you don't have to relearn anything, other than to figure out which download package you need.

If you decide to try it, you'll want this too. https://marketplace.visualstudio.com/items?itemName=ErikEJ.SQLServerCompactSQLiteToolbox

[thirdeye73]

I have used both MS SQL and SQL Lite on different projects, and here's my short pros/cons list:

SQLite
Pros:  easy to install and deploy; great for small/simple databases; single data file
Cons:  no stored procedures; requires additional library reference for .NET; requires third-party software for db admin (but several good ones are available for free)

MS SQL
Pros:  better for complex/large databases; stored procedures mean less database-specific code in the .NET project; integrated admin tool
Cons:  setup and deployment can be messy and confusing

When I need to interface with a database, my decision usually boils down to whether or not I need stored procedures.

Scott

[Mikefly95]

Sorry i have not had a free moment to finish this tutorial, between main job, contract jobs, family and band i am swamped.

EE_Kraig I would never use Lite, everything i do is mission critical.

[Noe]

I have used both MS SQL and SQL Lite on different projects, and here's my short pros/cons list:

SQLite
Pros:  easy to install and deploy; great for small/simple databases; single data file
Cons:  no stored procedures; requires additional library reference for .NET; requires third-party software for db admin (but several good ones are available for free)

MS SQL
Pros:  better for complex/large databases; stored procedures mean less database-specific code in the .NET project; integrated admin tool
Cons:  setup and deployment can be messy and confusing

When I need to interface with a database, my decision usually boils down to whether or not I need stored procedures.

Scott

I would like to start using SQLite but have been using MS Access (yes I know is the worst option but I learned following and continue development of projects here in plant). Do you think you can give us small tutorial on how to add SQLite to AHMI?

[Mikefly95]

Give this a look.

https://channel9.msdn.com/Blogs/MVP-Azure/Using-SQLite-in-Visual-Studio-Projects

[Phrog30]

Mikefly95 - You may want to look into SQLite https://www.sqlite.org/index.html

I was using MS SQL for an application and all was well, but deployment to the target machine was a mess. I switched over to SQLite and you can use simple xcopy deployment. This made things *way* easier. All the SQL commands remain the same so you don't have to relearn anything, other than to figure out which download package you need.

If you decide to try it, you'll want this too. https://marketplace.visualstudio.com/items?itemName=ErikEJ.SQLServerCompactSQLiteToolbox

Thank you for posting info about SQLite.  Having a solution that is "portable" is just plain awesome!!  So far, it does exactly what I need.

[Archie]

A little know feature in Visual Studio is to directly use mdf files without SQL Server.

- In Solution Explorer, right click the project name
- Select Add->New Item
- The next popup will have a Data category, select it
- Select "Service Based Database"
- Give it a name then click Add
- You will now have an mdf file in your solution that is set to always copy to the output directory with the execuatable
- If you double click the mdf file in Solution Explorer, it will show as a Data Connection in Server Explorer
- At this point you can add tables, views, etc.

I have never tried, but I believe this mdf file can be attached to a SQL Server if your application needs to scale up

[Phrog30]

I tried this early on and found issues when running on other machines. I spent more time trying to ensure the correct supporting files and applications were installed.  So far with sqlite, I haven't seen any issues.