[Last Call] Learn how to a build a cloud-first strategyRegister Now


Simple Single Record SQL SELECT statement in C#/ASP.net

Posted on 2006-05-25
Medium Priority
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 ADO.net 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

AGBrown earned 500 total points
ID: 16765587
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;


Expert Comment

ID: 16767285
I would check out this http://channel9.msdn.com/showpost.aspx?postid=114680 it's not fully released yet but will make database access alot slicker




Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

834 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