Library tutorials & articles

Using ADO.NET with SQL Server

  • Comments (24)
  • PDF
  • AddThis
By James Crowley, published on 16 Feb 2004
Page 2 of 5
  1. Introduction
  2. Executing a SQL query and SqlDataReader
  3. Stored Procedures and other queries with parameter
  4. Using the DataSet
  5. Doing without CommandBuilder & Wrapping Up

Executing a SQL query and SqlDataReader

In order to execute a SQL query, or a stored procedure (covered later) on the server, we use the SqlCommand object. The constructor that we'll use here accepts the query to execute, and the connection that it should use:

[VB]
' create the command object
Dim sqlComm As New SqlCommand("SELECT userid,username FROM users ORDER BY username", sqlConn)

[C#]
// create the command object
SqlCommand sqlComm = new SqlCommand("SELECT userid,username FROM users ORDER BY username", sqlConn);

From here, we have a number of methods to choose from in order to execute the query.

Item Description
ExecuteReader Executes the query, and returns a SqlDataReader object.
ExecuteNonQuery

Executes the query, and does not collect any results. Generally used for queries such as UPDATE and DELETE.

ExecuteScalar Executes the query, and returns a single value (from the first column of the first row).

If we're not interested in the result of a query (other than whether it executed successfully or not), then use ExecuteNonQuery(). For example,

[C#]
// create the command object
SqlCommand sqlComm = new SqlCommand("DELETE FROM users WHERE userid=1", sqlConn);
sqlComm.ExecuteNonQuery();

If you're performing a query, and are only interested in the first column of the first row returned, then use ExecuteScalar(), and cast the result to the appropriate data type. For example,

[C#]
// create the command object
SqlCommand sqlComm = new SqlCommand("SELECT COUNT(*) FROM users", sqlConn);
int userCount = (int)sqlComm.ExecuteScalar();

Using the DataReader

The most common case, however, is going to be when we're retrieving multiple rows and columns of data. In this case, we use the ExecuteReader method, which returns an instance of a SqlDataReader object. Like its cousin OleDbDataReader, this provides read-only forward access to rows returned by the SqlCommand object.

So, now we've got this SqlDataReader object, what can we do with it? If you're interested in whether the query returned any results at all, or how many rows were affected by the query (usually due to DELETE or UPDATE statements) then check the HasRows and RecordsAffected properties.

First, call the Read() method, which advances the reader to the first row (and then to the next row on subsequent calls), and returns a boolean value indicating whether there was actually a row to read. Now we're free to access any columns that we see fit - we've got three different ways to do this - and then remember to call Close() on the DataReader (and the database connection, if need be).

If you want to access them by name - probably the most common and readable option - you can use the reader's indexer:

[VB]
Dim sqlComm As New SqlCommand("SELECT userid,username FROM users ORDER BY username", sqlConn)
Dim r As SqlDataReader = sqlComm.ExecuteReader()
While r.Read()
    Dim username As String = CStr(r("username"))
    Dim userID As Integer = CInt(r("userid"))
    Debug.WriteLine((username + "(" + userID + ")"))
End While
r.Close()

[C#]
SqlCommand sqlComm = new SqlCommand("SELECT userid,username FROM users ORDER BY username", sqlConn);
SqlDataReader r = sqlComm.ExecuteReader();
while ( r.Read() ) {
    string username = (string)r["username"];
    int userID = (int)r["userid"];
    Debug.WriteLine(username + "(" + userID + ")");
}
r.Close();

Note that, as with the ExecuteScalar method, we have to cast the value to the type we need - and if the column username wasn't a string in the code above, we'd get an InvalidCastException. For strings, we could equally well have used r["username"].ToString() instead of the formal cast. If you're trying to squeeze every ounce of performance from your application, then its certainly worth noting that the DataReader performs a case-sensitive search for the column name before resorting to a case-insensitive one - so it does help to be consistent in the way you case the field names.

A more efficient method (note: we're still talking small efficiency gains here!) is to use the column's index (rather than its name) - but this requires you to be very careful when writing your SQL statements (and in later modifications) that you don't change this:

int integerColumnValue = (int)r[i];

And finally, you could use one of the many Get methods that the SqlDataReader offers:

int integerColumnValue = r.GetInt(i);

These include GetInt, GetString, GetDouble etc, all of which accept an integer parameter for the column index.

One thing to bear in mind. If your query has the possibility of returning columns that are NULL, then you'll need to be careful before casting or calling the Get methods - the indexers will not return null, but DBNull.Value, and the Get methods will throw an exception, so for those you will need to check using sqlDataReader.IsDbNull(columnIndex) first.

Data Binding

Alternatively, if you simply want to bind a control like the DataGrid or DataRepeater objects to the rows, you can use the SqlDataReader as a DataSource:

[VB]
Dim sqlComm As New SqlCommand("SELECT userId,username FROM users ORDER BY username", sqlConn)
Dim r As SqlDataReader = sqlComm.ExecuteReader(CommandBehavior.CloseConnection)
myDataGrid.DataSource = r
' Call to DataBind needed in ASP.NET
'myDataGrid.DataBind()
r.Close()

[C#]
SqlCommand sqlComm = new SqlCommand("SELECT userId,username FROM users ORDER BY username", sqlConn);
SqlDataReader r = sqlComm.ExecuteReader(CommandBehavior.CloseConnection);
myDataGrid.DataSource = r;
// Call to DataBind needed in ASP.NET
//myDataGrid.DataBind();
r.Close();

Note that in the above code we provide the ExecuteReader command with a parameter CommandBehavior.CloseConnection - this tells the data reader to call close on the database connection when we call its own Close method. If we hadn't used this, then we would need to call sqlConn.Close() at some point in our code!

Comments

  1. Manjot Bawa
    11 Jun 2009 at 06:00

    The new VSTS enables you to convert your imagination into the perfect material images on screen ! Let your mind do the thinking and VSTS will do the rest ..... http://bit.ly/izsu9

  2. Phil Karras
    20 Apr 2009 at 14:48
    I can't seem to get the hang of sending code, I clicked on "code" but it still didn't work, so a few extra blank lines might make it clearer? My version of your code gives an error when trying to DataBind it: "The data source does not support server-side data paging." what am I doing wrong? -pk string sql = "select * from casefolder"; SqlConnection sqlConn = new SqlConnection(connectionString); // Sets up the connection, it is closed here -pk01 sqlConn.Open(); SqlCommand sqlComm = new SqlCommand("select * from casefolder", sqlConn); SqlDataReader r = sqlComm.ExecuteReader(CommandBehavior.CloseConnection); gridCases.DataSource = r; // Call to DataBind needed in ASP.NET - Fails at this point: "The data source does not support server-side data paging." -pk01 gridCases.DataBind(); sqlConn.Close();
  3. Phil Karras
    20 Apr 2009 at 14:45
    Sorry about that! See if this works better. -pk My version of your code gives an error when trying to DataBind it: "The data source does not support server-side data paging." what am I doing wrong? -pk string sql = "select * from casefolder"; SqlConnection sqlConn = new SqlConnection(connectionString); // Sets up the connection, it is closed here -pk01 sqlConn.Open(); SqlCommand sqlComm = new SqlCommand("select * from casefolder", sqlConn); SqlDataReader r = sqlComm.ExecuteReader(CommandBehavior.CloseConnection); gridCases.DataSource = r; // Call to DataBind needed in ASP.NET - Fails at this point: "The data source does not support server-side data paging." -pk01 gridCases.DataBind(); sqlConn.Close();
  4. Phil Karras
    20 Apr 2009 at 14:42
    My version of your code gives an error when trying to DataBind it: "The data source does not support server-side data paging." what am I doing wrong? -pk string sql = "select * from casefolder"; SqlConnection sqlConn = new SqlConnection(connectionString); // Sets up the connection, it is closed here -pk01 sqlConn.Open(); SqlCommand sqlComm = new SqlCommand("select * from casefolder", sqlConn); SqlDataReader r = sqlComm.ExecuteReader(CommandBehavior.CloseConnection); gridCases.DataSource = r; // Call to DataBind needed in ASP.NET - Fails at this point: "The data source does not support server-side data paging." -pk01 gridCases.DataBind(); sqlConn.Close();
  5. john mott
    05 Sep 2007 at 20:32

    Excellant article, very meaty and precise!

    Here is a little utility for helping get the precise sequence of objects correct for a particular combination of request/data type, at least for SqlClient

    http://www.nicecleanexample.com/Tools/SqlBuilder/SqlClient.aspx

    jk

     

  6. amischief
    04 Jun 2007 at 12:55

    Hi, finally getting somewhere with my understanding of ADO but am still a bit lost with my predicament.

    I have an Empress (SQL) database on a Linux server that I want to link to an MS Access database on a Win2000 PC (they are both on the same closed network).

    Although I have read and understood the topic and have tried and failed to get my link to work I have somehow managed to get myself stuck in a neverending loop of confusion. I think I'm trying to do too many things with too many programming languages at the minute and am one stage away from placing my underpants on my head with a pencil up each nostril and need someone to guide me through to the next stage before I implode!

    Thanks

  7. enzo menoni
    18 Mar 2007 at 22:40

    Hi Folks,

    I keep getting this error when trying to connect to my sql dbase.

    An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

    I'm currently learning to use vb.net and sql server, so i am a novice. Any help would be gratefully appreciated.

    kindest regards.

  8. krutika gambhir
    19 Feb 2007 at 09:39
    hey pls need help wit the connection string for using sql server wit ado.net. im not gettin the code lines for the connection .please help.its urgent
  9. Barry Sumpter
    09 Feb 2007 at 05:49
    Hi all,

    After spending about 6 months part-time experimenting with the 2005 pro vb.net interface using wizards, its my opinion that wizards should NOT be used to develop anything but fixed database prototypes.

    I had actually started using code in 2005 but it was crashing so much that I tried the wizards.
    The wizards didn't crash so much when building solutions.  I thought I had finally found a code generator that actually worked.  Come to find out only up to a point.

    Once I started using the wizard, I had so many work arounds and crashes from attempting to change a database field that I finally gave up.  Some of the crashes were so bad they actually corrupted files.

    The wizards are there just to "wow" unsuspecting parties into thinking its super easy to use.
    Its not.  Its super easy to use exactly how they demo in the limited way of the drag-n-drop features and thats all.

    Absolutly no support or demos regarding how to modify once built.
    I tried chasing down the code behind the wizards but like every other code generator its so complex its propriatary and very easy to break.

    Don't use the wizards as a coder.  Develop everything in code.

    I could suggest only to use the wizards on a fixed database and never try to modify the db or the code.
    Just re-wizards everything.

    Or use the wizards to dupe unsuspecting clients into thinking its easy to use.

    So please, please don't waste your time using the wizards.

    ---------------

    James Crowley wrote:
    This was still aimed with code-behind in mind. However, I've found a large number of people happily dragging and dropping components from the toolbox, or using the Microsoft wizards, with no real idea as to what each object actually *does*. Although the wizards are great time savers for many things, personally I still prefer writing my own logic for making calls to database stored procedures etc. But maybe I'm a control freak! In terms of layer segregation - there's no hint of layout code here. At the most, we're setting the DataSource for a datagrid - and then presumably the .aspx page would be dealing with the layout/appearance.


























  10. fakhi84
    05 Jan 2007 at 05:52

    hello,

    hey very gud article. but i am facing  a problem that when i connect to SQLServer usisng C# it doesnt allowed me to connect by saying its not a valid user. as i am using Windows authentication for connecting to the SQLServer

  11. fakhi84
    05 Jan 2007 at 05:47

    hello,

    hey very gud article. but i am facing  a problem that when i connect to SQLServer usisng C# it doesnt allowed me to connect by saying its a valid user. as i am using Windows authentication for connecting to the SQLServer

  12. sukanta dass
    22 Sep 2006 at 09:54

    dear sir,

    i 'm facing some problem in insert data from an application to database using dataset. pls. help me in parameterised insert data into a datasource.

     

    sukanta

  13. Alasia Delgado
    01 Aug 2006 at 02:00

            Great article, very helpfull. Although I'm writing because I have lingering doubts on the subject in general, I've been programming with visual basic, all the way from 6.0 to 2005 and several database managers, and I've generally avoided the use of datasets, datatables, etc etc, I work writing my updates, inserts and deletes (you have to set them up anyway using datasets), and fill my datagrids using arraylists of the objects I build, which gives me certain freedom populating them, since I can manipulate the data before showing it... on the other hand, I've also found confusing the interaction between tables and datatables with an auto-numeric value, could more likely be lack of experience, but it creates dificulties that I do not have working more directly with the database itself... So my question is, mainly, why use datasets, datatables and so on? what definites advantegaes do I get from using them instead of working with the queries myself? (which by the way I hear is faster (in runtime), and cleaner...) .. Any thoughts would be welcome of course, I'm just looking for opinions...

     Thanks!

    Alita

  14. Trilarian
    15 Mar 2006 at 18:24
    Very nice article.  It helped me a lot in converting some VB6 code to VB .NET 2005.  The only thing I would add is a message at the top that this is for VS 2003.  In VS 2005 they have made some minor changes to the syntax and the above code would not work.  Ex.

    Dim sqlConn As New SqlConnection(connectionString)



    would now be:

    Dim sqlConn as New SqlClient.SqlConnection(connectionString)





  15. nsuresh_rasr
    21 Jan 2005 at 04:57

    It was really useful in reading this article. great stuff... could have explained the update method little  bit in detail.
    Thanks for the good work though....
    Take care,
    Suresh.N

  16. Martin Moerman
    29 Nov 2004 at 14:45

    I am very happy !


    I wanted to know how the asp.net database stuff exactly works.. and well here it is..
    step by step.


    Thnx
    /mamoman

  17. James Crowley
    14 Apr 2004 at 08:24
    This was still aimed with code-behind in mind. However, I've found a large number of people happily dragging and dropping components from the toolbox, or using the Microsoft wizards, with no real idea as to what each object actually *does*. Although the wizards are great time savers for many things, personally I still prefer writing my own logic for making calls to database stored procedures etc. But maybe I'm a control freak!

    In terms of layer segregation - there's no hint of layout code here. At the most, we're setting the DataSource for a datagrid - and then presumably the .aspx page would be dealing with the layout/appearance.

  18. Tim Farrell
    07 Apr 2004 at 14:40

    I wanted to know if this article was written with inline code in mind or code-behind.


    It would appear to me to be written from an inline perspective but I am new to this and may be wrong.


    I am desperately looking for someone to write something that is geared more towards (what I thought was ) the Microsoft vision.  Where the developer is using the full functionality of the IDE in terms of the drag and drop features and connecting the controls via the code-behind page.  This would also allow for segregation between layers (presentation, business, database).


    Do you know where I can find such a tutorial?


    Thank you for your time.


    Sincerely,


    Tim

  19. Kai Chen
    01 Apr 2004 at 16:33

    Very clear and concise, well done! Keen to read your next article on this topic...

  20. Abhinav Shrivastava
    30 Mar 2004 at 06:04

    Bravo!! James...... You wrote that article so fine....so lucid it is....Got a good insight into Ado.net.
    Would like to see you going into more details on it...Well Done!!

  21. James Crowley
    02 Mar 2004 at 12:33
    yeah. I haven't quite figured out how to get time to write more of these!
  22. John Braga
    29 Feb 2004 at 04:02

    I enjoyed this article.  Clearly constructed, good basic examples, logical progression of complexity.  All fairly basic stuff, but a good grounding.


    Thank you!


  23. Luke Lesurf
    26 Feb 2004 at 07:32

    is there anything you dont know?

  24. Developer Fusion Bot
    01 Jan 1999 at 00:00

    This thread is for discussions of Using ADO.NET with SQL Server.

Leave a comment

Sign in or Join us (it's free).

Related tags

.net, ado.net, c#, sql, sql server
James Crowley James first started this website when learning Visual Basic back in 1999 whilst studying his GCSEs. The site grew steadily over the years while being run as a hobby - to a regular monthly audience ...

Related articles

Ask a question

Related discussion

Related podcasts

  • A Practical Look at Silverlight 2 Part 1

    Now that Silverlight 2 is at the Olympics and making a big splash, we wanted to explore this fascinating technology more. Microsoft Silverlight 2 is a cross-browser, cross-platform, and cross-device plug-in for delivering the next generation of .NET based media experiences and rich interactive ap...

Events coming up

  • Mar 15

    DevWeek 2010

    London, United Kingdom

    DevWeek is Europe’s leading independent conference for software developers, database professionals and IT architects, and features expert speakers on a wide range of topics, including .NET 4.0, Silverlight 3, WCF 4, Visual Studio 2010, REST, Windows Workflow 4, Thread Synchronization, ASP.NET 4.0, SQL Server 2008 R2, LINQ, Unit Testing, CLR & C# 4.0, .NET Patterns, WPF 4, F#, Windows Azure, ADO.NET, Entity Framework, Debugging, T-SQL Tips & Tricks, and more.

Languages

Web Development

Frameworks & Architecture

Other major sections

Cancel
We'd love to hear what you think! Submit ideas or give us feedback