Library tutorials & articles
Using ADO.NET with SQL Server
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 ...
Introduction
Using ADO.NET with SQL Server and Access
As a developer, I find that an incredible amount of my time is spent dealing with databases in some form or another - be it displaying, manipulating or analyzing data; creating client or server applications. The advent of the .NET Framework has brought with it some major changes, and database access is certainly no exception. Although ADO has been around since 1996, with the arrival of ASP, its latest incarnation present in the .NET Framework - ADO.NET - really does represent its coming-of-age. This new architecture is so extensive, that I can only hope to give you a brief introduction. Although the article is using the SQL server data access components, much applies equally as well to using Microsoft Access. We'll be covering connecting to a SQL Server database, executing queries, calling stored procedures, filtering data, and reflecting changes in a database - and point you in the direction of further resources for more in-depth discussions on particular topics. I've provided code in both VB and C# if there are any major syntatical differences, and the rest in C#.
If you've got any comments, or if I've made any stupid mistakes, then feel free to drop me a line!
Connecting to the database
In order to use ADO.NET, we'll need to import two namespaces:
[VB]
Imports System.Data
Imports System.Data.SqlClient
[C#]
using System.Data;
using System.Data.SqlClient;
In order to establish a connection with SQL Server, we use the SqlConnection object. There are also generic OleDbConnection and OdbcConnection objects present in the System.Data.OleDb and System.Data.Odbc namespaces for use with other data sources such as Access (NB: support for ODBC was added in .NET 1.1). This would work in exactly the same way as illustrated here, but as Microsoft were kind enough to provide a data access class specifically optimized for SQL server, we might as well take advantage of it! If you're using Access, then you can literally replace every occurence of SqlSomething with OleDbSomething.
Opening a connection is very simple:
[VB]
' create a new SqlConnection object with the appropriate connection string
Dim sqlConn As New SqlConnection(connectionString) 'OleDbConnection i
' open the connection
sqlConn.Open()
// do some operations here...
// close the connection
sqlConn.Close()
[C#]
// create a new SqlConnection object with the appropriate connection string
SqlConnection sqlConn = new SqlConnection(connectionString)
// open the connection
sqlConn.Open();
// do some operations ...
// close the connection
sqlConn.Close();
with the connection string usually taking this form:
server=serverAddress;uid=username;pwd=password;database=database;
It is probably worth noting at this stage something about connection pooling. The idea behind connection pooling is simple - instead of incurring a large amount of overhead each time a connection to database server is established and closed, once a connection has been opened, it remains open for the lifetime of the process, available to be used again if needed. Pooling database connections can significantly enhance the performance and scalability of your application. The .NET data providers automatically pool connections for you. However, to take maximum advantage of these, you should take note of the following:
- Connections are only pooled if they have the same connection string; so ensure this is always constant.
- When finished with a
SqlConnectionobject, callDispose()orClose()to release the connection back to the pool. - In order to keep the maximum number of connections available, you should keep connections open for a short as period as possible - remembering that thanks to connection pooling, re-opening a database connection will incur little overhead.
For more information on connection pooling, take a look at this MSDN page.
Once open, you can't do much with a SqlConnection object on its own - other than close the connection again, and query its connection status using the ConnectionState property, so we'll move on to how we go about querying the database.
Related articles
Related discussion
-
VB.NET Type 'SqlDatabaseException' not defined
by Mulish Mehdi (1 replies)
-
An Introduction to VB.NET and Database Programming
by yen (12 replies)
-
Using ADO.NET with SQL Server
by jkoder59 (19 replies)
-
Connection String format between ADO and ADO.NET
by Sukhjinder (1 replies)
-
Creating a Database Table? [C#]
by jmcilhinney (5 replies)
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...
Related jobs
-
Microsoft .Net Architect
in AMSTERDAM (€50K-€90K per annum) -
Software Architect
in n/a (€45K-€70K per annum) -
.net developer
in Rijswijk (€2K-€4K per annum)
Events coming up
-
Dec
6
Developing AJAX Web Applications with Castle Monorail
London, United Kingdom
Monorail is the model-view-controller engine of the Castle Project, bringing many of the best ideas of Ruby on Rails to the .NET world. In this talk, David De Florinier and Gojko Adzic show how Monorail makes it easy to develop .NET based AJAX applications, and how to use the Castle Project to build Web 2.0 applications effectively. Come to this session if you are a .NET web developer. Everyone is welcome!
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
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
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.
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
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.
---------------
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
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
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
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
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)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
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
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.
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
Very clear and concise, well done! Keen to read your next article on this topic...
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!!
yeah. I haven't quite figured out how to get time to write more of these!
I enjoyed this article. Clearly constructed, good basic examples, logical progression of complexity. All fairly basic stuff, but a good grounding.
Thank you!
is there anything you dont know?
This thread is for discussions of Using ADO.NET with SQL Server.