Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

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

sql command records

  • Please log in to reply
5 replies to this topic

#1 ArekBulski

ArekBulski

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 480 posts

Posted 31 August 2009 - 01:40 AM

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

http://forum.codecal...=1&d=1251710678

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. :)");

http://forum.codecal...=1&d=1251711030

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. :)

Attached Thumbnails

  • sshot-4.jpg
  • sshot-6.jpg

Attached Files


  • 4

#2 Guest_Jordan_*

Guest_Jordan_*
  • Guest

Posted 31 August 2009 - 10:37 AM

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

#3 gaylo565

gaylo565

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 258 posts

Posted 31 August 2009 - 12:26 PM

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

#4 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 31 August 2009 - 04:09 PM

+rep
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#5 ArekBulski

ArekBulski

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 480 posts

Posted 01 September 2009 - 02:32 AM

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

#6 mvr210

mvr210

    CC Lurker

  • New Member
  • Pip
  • 3 posts
  • Learning:C#

Posted 10 January 2013 - 09:20 AM

Great job again! With your tutorial (1 and 2), anyone who is starting has a fighting chance. Keep up with good work.

Thank you, Miro

+rep

Edited by mvr210, 10 January 2013 - 09:23 AM.

  • 0





Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download