Using SQL in .NET: Part 1

Related EbooksTop Videos

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 we go to SQL queries, we need to know how to make a connection to a database. I will use 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 namespace in my application before writing any thing else.

using System.Data;
using System.Data.SqlClient;

Now let's create a connection object. The following code snippet creates a SqlConnection by 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

I want to show you that it is fairly easy to use SQL queries in your application. In this article, I will explain the CREATE, INSERT, and SELECT queries.

Now if you wonder why do 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 real 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 the code you can understand easily what's going on there.

The last thing we have to do is make a Primary key this is 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 use this query instead of ours "CREATE DATABASE csharpcorner". Use this instead of the create table query. You also need to change the connection string delete this part from it "Initial Catalog=database_name;"+

INSERT

The INSERT SQL query is second most used queries. I think you have an idea what it is for. Yes, it's for inserting data into a database table. You will see how the INSERT query works in a momeny. The INSERT query works almost the same as the create 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 in 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 little less comments but if you need any help with connection see the first code. I first let the user enter a string this was just to let you know how it works. For example you could make an application that has a Textbox in it en when clicking a button it will insert the string from the textbox to the table.
Next thing was count to count the numbers of rows in the database. I will explain this at the SELECT section because it uses a select query but you will know how it works in just a matter of 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', 0)" NOTE that the order is reversed because we created the table like id, text
This would be the normal query "INSERT INTO <table> VALUES (1, 'some text')"
But I always use the first one this is because it will give you more control of how it is ordered.

SELECT

And at last after the though work with creating and inserting something to our database we will read it from it. I guess this is the most used command in SQL this because the query let you show you what's in the database. I will make a application that count the rows in the table this is because we need to know how long our for loop will be. This application will be more difficult then you already saw this article this because we also need a SqlDataReader for reading from the database.

I will first show you the code this code will be commented 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 code.

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";

What we do here is select simle_id and simple_text from simplesql and order it by simple_id that means like 1, 2, 3,...
if you use ORDER BY simple_id DESC it 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 the gives you fewer control.
So if we only want to select the text we could do this.
"SELECT simple_text FROM simplesql ORDER BY simple_id"
Or if we want to select text where id equals 3 we would do this.
"SELECT simple_text FROM simplesql WHERE simple_id=3"
There is one more I want to teach you and that is if you
want to select 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 usefully if you have a news system and want to show the last
5 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 so
if you had the query like this: "SELECT simple_text, simple_id ..."
simple_text was dr[0]
But now simple_id is.

Summary

As you can see from this article, it is a very basic SQL 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.