Writing SQL commands in C# - Part 2 - Reading and deleting records

[ArekBulski]

Welcome all! This is a second part of my SQL in C# tutorial series. If you are interested in writing INSERT commands then take a look at part 1: INSERT into table. In this tutorial I would like to show you how to read several records by using a SELECT statement, and how to delete records by using DELETE statement. Enjoy this short tutorial.

Reading records with SQL SELECT statment: using SqlDataReader class

So, so far we created a database, with a table for numbers and descriptions, and added a record. Remember, table NumbersTable has two columns, numbers and description. During insertion and deletion column names are not used, you need only their order. So when you write some code, take a look again at the table. If you make it wrong then god help you with debbuging.

SqlConnection connection1 = new SqlConnection(
    Properties.Settings.Default.some_numbersConnectionString);

SqlCommand insertCommand = new SqlCommand(
    "INSERT into NumbersTable values (1002, 'will delete this one')",
    connection1);

connection1.Open();
insertCommand.ExecuteNonQuery();
connection1.Close();

MessageBox.Show("Record inserted. Please check your table data. :)");

But a database is useless if you cannot use the data you inputed there, right? After all this is why created a database in the first place. So, let us read all the records from our table. While you can use some conditions to select only several records, it is not necessary in this case.

For a start you need a SqlConnection and SqlCommand classes. They are required for any SQL statement. This time however, we could also use a SqlDataReader class. It fetches the received data and lets you access them one record at a time.

After calling reader = selectCommand.ExecuteReader(), you are able to access your records one-by-one. By turns, call reader.Read() while checking are there any more records to read. If it returns false then you are done with reading. Then process the current record by reading reader[0], reader[1] and so on. Again, field order is important. Try hard to avoid making mistakes here.

SqlConnection connection1 = new SqlConnection(
    Properties.Settings.Default.some_numbersConnectionString);
SqlCommand selectCommand = new SqlCommand(
    "SELECT * FROM NumbersTable",
    connection1);

connection1.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
string fetchedRecords = string.Empty;

while (reader.Read() == true)
{
    fetchedRecords += "Record: (number) " + reader[0] + " (descriptio) " + reader[1] + " \n";
}

connection1.Close();
MessageBox.Show("Found following records: \n \n" + fetchedRecords);

Deleting several records: SQL DELETE statement

Well, deletion is as easy as a pie. There is nothing more than the short code below. Note that you can use several conditions, but ntext and varchar field types do not play with equal-to operator (=). That is why the other version is commented out.

SqlConnection connection1 = new SqlConnection(
    Properties.Settings.Default.some_numbersConnectionString);

SqlCommand insertCommand = new SqlCommand(
    "DELETE NumbersTable WHERE number = 1002",
    //"DELETE NumbersTable WHERE number = 1002 AND description = 'will delete this one'",
    connection1);

connection1.Open();
insertCommand.ExecuteNonQuery();
connection1.Close();

MessageBox.Show("Record 1002 deleted. Please check your table data again. :)");

Thank the author, share some reputation points

I would like to thank everyone, especially Jordan and Siten, that were supporting me. Writing tutorials is a hard work. If you want to leave a comment or +rep my post then go ahead. I thank you in advance.

[Jordan]

Wow, very nicely done! I enjoy reading your tutorials and will continue to support you. +rep!

[gaylo565]

I like the both of your recent SQL tut's. Very nicely put together. Should be helpfull to many a learning programmer. +rep

[WingedPanther]

+rep

[ArekBulski]

Thank you all three. I appreciate your comments a lot. Thanks to you I achieved a Guru status, too. *hurray*