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.
Remember
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.
not complete. [-(
Sam.. i am not SQL queries teacher. (p) . I only give idea here about the queries we use used in Visual Studio. & for SQL Database i give examples here basic & mostly used . & if you are intelligent enough you can easily use them in VS.
thx you so much ! it help me alot! Good job! however, can u advice me a sort to searching more example that W3.Shool did not have. thx you again.
ประภัสสร. Great to hear that it helped you. (h) … plz can you elaborate what you are asking about. ? . i have complete documentations & examples …ask anything. & i will tell you about it… :p
wow! thx!
Sorry for taking to long, i just wanna try to do my best first! Hahaha.>>And Now! i really get stuck!
1. my datatype is text .
2. i want to count on (ProQty) that less than (ProWarn).
i try string sql = "select COUNT(ProductID) from Product where ProQty <= ProWarn";
cmd = new OleDbCommand (sql);
and so on…..
but it count only ProductQty greater than ProWarn
thx you for ur help;
Thanks. the query class you created solved my problem. i was using it again & again every f****king time when i needed.
You have left some of the important ones but i will manage it. I am in 2nd semester.
Sami Yousuf
Hi, I want to subscribe for this web site to take hottest updates,
therefore where can i do it please help out.
I just included a Newsletter subscription widget to site. Check it out Isabella.
Hi there developers, good article and arguments commented at this place, I am actually following all these advices. Thanks.
Heya tҺis iѕ somewҺat of off oрic but Ⅰ wаs wondering if blogs use WYSIWYG editors or iff you have to annually code with HTML. I’m starting a bⅼog soon bbut have no сoding knowledge so I wanted to get advice from someone with experience.
Any help wߋuld be enormously appгeciated!
Appreciate the recommendation. Let mme try it out.
My programmer is trying to persuade me to move to .net from PHP. I have always disliked the idea because of the costs. But he’s trying none the less. I’ve been using WordPress on several websites for about a year and am concerned about switching to another platform. Any help would be greatly appreciated!