This article will help you understand the syntax for basic queries that are most used in SQL Server. In the first part we will list down important queries that you need to understand & then in second part we will see how we can use these queries in Projects in Visual Studio using C# Programming Language.
Before We Start
Since these queries will be run against a Database, you should first create a database if you don’t have it already. Follow these steps to create a database.
- Right click on Databases & click on New Database.
- Add Tables in Database.
- Then Put Columns in these Tables.
Below is a Screenshot of Database I created. You can create it like this if you want the same columns & datatypes.
Part 1 : Most used SQL Queries
Below is the List of Important queries. I have commented them so you can understand them easily. Copy each of them one by one & run them in your SQL server on a sample database such as North Wind. This will help you to practically understand them.
Once you have tried the above queries in SQL server, we can now move on to Part 2.
Part 2 : Using SQL Queries in Projects
We develop our projects mostly through Visual Studio. I prefer to use C# Programming language because I am good at it but you can use language that you are comfortable with.
I created a class named Query. Where I put my important methods as follows.
Now I will create an object of this class anywhere I want to use queries. I will pass my Query & Database Path to the methods from this object. Below is the example where I am passing Insert query & Database to this object.
Here Customer ID, Invoice ID, Date etc are my values which i am going to store. ( q ) is the object of Query class & Connect is the method of Query Class written in the First Step. Here ( path ) is the path of Your SQL Database. My path is this.
string path = @"Data Source=.sqlexpress;Initial Catalog=Invoicer;Integrated Security=True";
Here is another Example that demonstrate Updating a record.
Similarly for Deleting a record.& Lastly Checking the Record.
In above examples CustomerDetails is my table name where as CustomerID, CustomerName & CustomerAddress are the column names of my table. You can see I am setting their values like
' "+CustomerID+" ' , ' "+CustomerName+" ' & ' "+CustomerAddress+" '
in queries. These value have to be specified in your code either from initializing them or by getting their values from Console or Windows Form GUI.
These queries are for beginners & you can use them in your practice work however If you are developing an app for a client you should learn more. Here are some handy guidelines that you should follow.
- Use parameterized queries.
- Use stored procedures
- Use Hashing if data is important
There are tons of other guidelines but that would be out of scope for current topic. That’s all for now. I hope you now have basic understanding of these queries & their use. Ask questions if you have any misconception.