Introduction
I usually see code samples using SELECT SQL queries, but I don't see many articles using other SQL queries. In this article, I will show you how to execute SQL queries from your C# applications.
Before going to SQL queries, we need to know how to connect to a database. I will use an SQL data provider to connect to the SQL Server. The SQL data provider classes are defined in the System.Data.SqlCliet namespace and some general database-related classes are defined in the System. Data namespace. Hence I import these two namespaces into my application before writing anything else.
using System.Data;
using System.Data.SqlClient;
Now let's create a connection object. The following code snippet creates a SqlConnection using userid as sa and password as pass. After that code opens and closes the connection:
SqlConnection conn = new SqlConnection("Data Source=computer_name;"+"Initial Catalog=database_name;"+ "User ID=sa;"+"Password=pass;");
conn.Open();
conn.Close();
CREATE Query in SQL
I want to show you that using SQL queries in your application is relatively easy. I will explain the CREATE, INSERT, and SELECT queries in this article.
Now if you wonder why I start with the CREATE SQL query, then the simple answer is I want to show you how to create a database and database table before you can select any data. Because to use the SELECT SQL query, you must have a database, database table, and data in the table.
The following source code shows you how to use the CREATE SQL query.
using System;
using System.Data;
using System.Data.SqlClient;
namespace SimpleSql {
class CreateTable {
[STAThread]
static void Main(string[] args) {
// Make a SqlConnection and the Connection string as an argument
SqlConnection conn = new SqlConnection("Data Source=computer_name;" + "Initial Catalog=database_name;" + "User ID=sa;" + "Password=pass;");
/* Next thing we will do is make a sqlcommand so
* we can exucute our query
*/
SqlCommand cmd = new SqlCommand();
// We could give this as argument but that won't make our code real clear
cmd.CommandTimeout = 60; //
cmd.Connection = conn; // Sets wich connection you want to use
cmd.CommandType = CommandType.Text; // Sets the command type to use
cmd.CommandText = "CREATE TABLE simplesql " + "(" + "simple_id int," + "simple_text text" + ")";
/* Why this approach you could write it in one line right???
* Yes you can you coudl make it like this
* "CREATE TABLE simplesql ( simple_id int, simple_text text )";
* most people would do it the simple way inclusive me :)
* but I want to make it really clear how to do it right
*/
// Next We will open the connection and make the query
try {
conn.Open(); // Open the connection if it fails
// it will let you know with an exception
if (conn.State == ConnectionState.Open) {
// Execute the query for this nothing is returned
cmd.ExecuteScalar();
Console.WriteLine("Table is Created");
}
} catch (Exception exp) {
Console.Write(exp.Message);
} finally {
conn.Close(); // close the connection
}
Console.WriteLine("Press any key to quite");
Console.Read();
}
}
}
As you can see from the code, I have commented on the code so you can uneasily understand what's going on there.
The last thing we have to do is make a Primary key t. Thiss needed so that our simple_id can't have the same value twice "CREATE UNIQUE INDEX PrimaryID ON simplesql (simple_id)".." use this instead of the create table query.
NOTE
I expected you to have a database available if you have not used this query instead of ours, "CREATE DATABASE csharpcorner." "Use this instead of the create table query. You also need to change the connection string and delete this part from it "Initial Catalog=database_name;"+
INSERT Query in SQL
The INSERT SQL query is the second most used query. I think you have an idea of what it is for. Yes, it's for inserting data into a database table. You will see how the INSERT query works in a moment. The INSERT query works almost the same as the create a statement, but to make it more interesting, I will make something for you so you get an idea of how you could do it, for example, a guestbook with asp.net.
- We will make something to count of id already in the table to make an exclusive ID.
- We let you type in the string to insert into the database
And here is the code
using System;
using System.Data;
using System.Data.SqlClient;
namespace SimpleSql1 {
class Insert {
[STAThread]
static void Main(string[] args) {
SqlConnection conn = new SqlConnection("Data Source=Computername;" +
// TODO: change comp name
"Initial Catalog=simplesql;" + "User ID=sa;" + "Password=pass;");
string strText = Console.ReadLine();
try {
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = 60;
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT Count(*) FROM simplesql"; // Couldn't do it without sorry
conn.Open();
if (conn.State == ConnectionState.Open) {
object objCount = cmd.ExecuteScalar();
int iCount = (int) objCount;
Console.WriteLine("Count was succesfull");
cmd.CommandText = "INSERT INTO simplesql (simple_id, simple_text) VALUES (" + iCount + ",'" + strText + "')";
cmd.ExecuteScalar();
Console.WriteLine("Succesfully inserted the string");
}
} catch (Exception exp) {
Console.Write(exp.Message);
} finally {
conn.Close();
}
Console.WriteLine("\n\n Press any key to quite");
Console.Read();
}
}
}
As you can see a few fewer comments, but if you need any help with connection, see the first code. I first let the user enter a string. This is just to let you know how it works. For example, you could make an application with a Textbox in it when clicking a button; it will insert the string from the textbox into the table.
The next thing was counting to count the number of rows in the database. I will explain this in the SELECT section because it uses a select query, but you will know how it works in just a few minutes.
The select query is used like this, and I will take our example for this "INSERT INTO <table> (simple_text, simple_id) VALUES ('some text,'')" NOTE that the order is reversed because we created the table like id, text.
T.his would be the normal query "INSERT INTO <table> VALUES (1, 'some text')"
I. constantly use the first one because it gives you more control over how it is ordered.
SELECT Query in SQL
And finally, after the thought works with creating and inserting something into our database, we will read it from it. I guess this is the most used SQL command because the query lets you show what's in the database. I will make an application that counts the rows in the table because we need to know how long our for loop will be. This application will be more complicated than you saw in this article because we also need a SqlDataReader to read from the database.
I will first show you the code. This code will be commented on like the first one, so read carefully.
using System;
using System.Data;
using System.Data.SqlClient;
namespace SimpleSql1 {
class Select {
[STAThread]
static void Main(string[] args) {
/* we will first do the same thing
* connecting to the database as we did before
*/
SqlConnection conn = new SqlConnection("Data Source=computer_name;" + "Initial Catalog=simplesql;" + "User ID=sa;" + "Password=pass;");
SqlDataReader dr;
try {
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = 60;
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT Count(*) FROM simplesql";
conn.Open();
if (conn.State == ConnectionState.Open) {
object objCount = cmd.ExecuteScalar();
int iCount = (int) objCount;
Console.WriteLine("Count was succesfull \n");
cmd.CommandText = "SELECT simple_id, simple_text FROM simplesql ORDER BY simple_id";
dr = cmd.ExecuteReader(CommandBehavior.SingleResult);
Console.WriteLine("Succesfully Selected \n");
// For loop to read everything from the table
for (int i = 0; i < iCount; i++) {
dr.Read(); // Read one row from the table
Console.WriteLine("ID: {0} \t Text: {1}", dr[0], dr[1]);
}
}
} catch (Exception exp) {
Console.Write(exp.Message);
} finally {
conn.Close();
}
Console.WriteLine("\n\n Press any key to quite");
Console.Read();
}
}
}
Now I will explain a couple of codes.
cmd.CommandText = "SELECT Count(*) FROM simplesql";
What we do here is count all the rows from simplesql. This must be done to know how long our for loop will be.
cmd.CommandText = "SELECT simple_id, simple_text FROM simplesql ORDER BY simple_id";
We select simle_id and simple_text from simplesql and order it by simple_id, which means like 1, 2, 3,...
If Ifu use ORDER BY simple_id DESC I,t will order reversed like ..., 3, 2, 1
Again you could choose to do it like this.
"SELECT * FROM simplesql"
This will make the query shorter, but it gives you less control.
So if we only want to select the text w,e could do this.
"SELECT simple_text FROM simplesql ORDER BY simple_id"
Or if we're going to select the text where id equals three w,e would do this.
"SELECT simple_text FROM simplesql WHERE simple_id=3"
There is one more I want to teach you: if you want to select the text between some id numbers, you will use this.
"SELECT simple_text FROM simplesql BETWEEN simple_id=0 AND simpel_id=5"
This would be useful if you have a news system and want to show the last five news items. This is the way
dr.Read();
Console.WriteLine("ID: {0} \t Text: {1}", dr[0], dr[1]);
Read one row from the database
dr[0] means the first variable you selected s,o if you had a query like this, simple_text was dr[0]. But now simple_id is.
Summary
As you can see from this article, it is a va fundamental article. In this article, I wasn't expecting you to know any SQL. I tried to keep this article very simple. I will be back with more articles on SQL with more SQL queries.