Simple Single Record SQL SELECT statement in C#/

Posted on 2006-05-25
Last Modified: 2012-06-27
I'm looking for a simple way to do SQL Selects from a table and place the results in a set of variables in my program. It seems to me that the SQLCommand syntax is sooooooo convoluted, with the DataReader and DataSet stuff. It makes sense when your results will be multiple records, but very messy when your looking up a single value.

For instance, in PowerBuilder I could use:

LONG li_autokey = 21
STRING ls_firstname

SELECT Systemuser.FirstName
      INTO :ls_firstname
      FROM systemuser
      WHERE systemuser.autokey = :li_autokey

     Messagebox('SQL Error ' + STRING(SQLCA.SQLCODE), 'Unable to SELECT from Systemuser. Result = ' + String(sqlca.sqlca) + ' - ' + sqlca.sqlerrtext)

In this example I would get ls_firstname set and error checking done. I know how to do it with and DataReader, SQLCommand syntax, but they seem soooo convoluted when ALL I want is a simple SELECT to pull some values from the database. Is there any other way?

Question by:josephbarron
    LVL 12

    Accepted Solution

    You can use a tool to do this, and reduce the amount of boilerplate, but fundamentally you will be going through the standard steps:
    1) get a connection
    2) get a command
    3) declare parameters
    4) fill parameters
    5) execute command
    6) read parameters and/or resultset(s)

    I would suggest looking at one of the following:
    1) Data access application block (DAAB) in the Enterprise Library from Microsoft (free download)
    2) DAAB 2.0 - an earlier, arguably slightly simpler version of the Enterprise Library DAAB
    3) Developing your own boilerplate assembly that takes the work out of doing this.
    Using these out of the box, or developing on top of it to reduce the flexibility and take advantage of the specificity of your situation will stop all the extra bits as they will be in one place.

    For example, my boilerplate assembly (developed a while ago based loosely on the DAAB 2.0) now requires me to do only this:
    QueryHelper qh = new QueryHelper(connectionString, commandText, commandType);
    qh.Parameters[0] = intKey;
    string firstName = (string)qh.Parameters[1].Value;

    LVL 1

    Expert Comment

    I would check out this it's not fully released yet but will make database access alot slicker



    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
    Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now