Click here to download the source code for this lesson. When you open the project files, set GridView_SQLServer.aspx as the Start Page.
In this lesson I will do an introduction to SQL Server database programming in ASP.NET by explaining how to use the GridView control. The GridView control can be found in the Toolbox window in the Data group of controls. If you would like to learn how I setup a SQL Server database with test data for this lesson please click here. The database that I am using in this example is called “csharpuniversity” and I created a very simple table called “customer” with a few columns.
Let me start by saying that database programming in ASP.NET and .NET in general is very straightforward. It may seem complicated at first because there are so many .NET Framework database classes that are available. Once you get the hang of it though, it becomes easier, so don’t be discouraged or overwhelmed when you begin to practice with .NET database programming. If something doesn’t work the first time, go back and re-read the examples and watch the videos again, just in case you missed a step along the way.
The .NET design for interacting with SQL Server databases is organized into two parts: a generic namespace called System.Data and a SQL Server specific namespace called System.Data.SqlClient. A namespace is a collection of related .NET classes organized into a single library (.dll file). Microsoft created a special class for database programming called the DataSet. The DataSet class is in the System.Data namespace and is probably the most commonly used database related .NET class.
A DataSet is a set of rows that can hold data in memory. A DataSet has columns, just like a database table. The DataSet was purposely designed to be disconnected from the database. By disconnected I mean that the DataSet does not hold a database connection open in order to be used. This is very beneficial since it can be passed around in your application without worrying about locking up any resources. The DataSet is also disconnected in another sense, in that it does not necessarily have to match the exact structure of a database and can even be populated with data by using code (without the need for a database at all). For example, you could read data from an alternate data source like an XML file and hold the data in a DataSet object.
In order to populate a DataSet object with data from a SQL Server database, you have to use the System.Data.SqlClient namespace. That namespace contains connection and query oriented classes. There is a class called SqlConnection that allows you to open a database connection to SQL Server. This is necessary of course as a prerequisite in order to execute any commands such as queries. In order to actually perform queries you have to also use another class called SqlDataAdapter. The SqlDataAdapter class is responsible for executing the queries. Let’s take a look at some sample source code and break it down line by line. I created a new web form and dragged a Button control onto the form. I then dragged a GridView control from the Data group of Toolbox controls onto the form. I double clicked the Button to create the event handler. At the top of the source code file I added the following lines.
using System.Data; using System.Data.SqlClient;
In the click event handler for the Button control I put the following.
SqlDataAdapter dbadapter = null;
DataSet dbdata = new DataSet();
using (SqlConnection dbconn = new SqlConnection(
"Server=localhost;"
+ "Database=csharpuniversity;"
+ "User ID=sa;"
+ "Password=Sqlserverpass$123;"
+ "Trusted_Connection=False;"))
{
dbadapter = new SqlDataAdapter("select * from Customer", dbconn);
dbadapter.Fill(dbdata);
}
gvCustomers.DataSource = dbdata;
gvCustomers.DataBind();
You can see at the top of the handler that I declared two variables dbadapter and dbdata. The dbdata variable will hold the results of the query in memory. The dbadapter object will be responsible for executing the query. Following those two declarations I created a special block of code called a “using” block with open and closed curly braces. The using statement is used in situations where you want .NET to automatically close a resource when you are finished using it. In this example the resource that will automatically close is the database connection because I instantiated an SqlConnection object called dbconn inside the parentheses following the using keyword. It is important to close database connections in your code because if you don’t, your application will eventually run out of connection objects since they are never being explicitly released. The using statement block will make sure that the dbconn object is available until the closed curly brace. After the closed curly brace, the dbconn object isn’t available for use anymore.
Inside the using statement, I first instantiate the SqlDataAdapter object and call the constructor method. The constructor takes two parameters: a string containing the SQL query you want to execute and an SQLConnection object (dbconn) that tells .NET which database to execute against. Notice in my query that I retrieve all the records from the Customer table”. On the next line I call a special method of the SqlDataAdapter object called Fill. Fill allows you to take the results of the query and store them in a DataSet object which is dbdata in my example. In about 5 lines of C# code, I was able to open a connection to a SQL Server database and query a table.
Once I have the DataSet object populated with data from the database, there are many things that I can do with it. In this example, I am displaying the data in an ASP.NET GridView (similar to an HTML table tag) control. In order to achieve this, I have to perform data binding. In .NET data bound controls are basically server controls like a GridView that have a DataSet object assigned (bound) to them. Once a DataSet is bound to a server control, the server control (e.g. GridView) can display the data that is in in the DataSet on the web page. For a GridView control, this is very easy and only takes two lines of code. First you have to assign the GridView’s DataSource property to a DataSet object that contains data. Then you call the GridView’s DataBind method to let ASP.NET know that you want to display the data. In total, this entire example is about 7 lines of C# code. That is pretty powerful and you can see how quickly an application can be developed that contains database integration. Click here to see an example video where I query data in a SQL Server database and display the results in an ASP.NET GridView control.
I am having a lot of trouble displaying the data. I have the following:
using (SqlConnection dbconn = new SqlConnection(”Server=localhost;Database=csharpuniversity;User ID=sa;Password=Password1234*;Trusted_Connection=False;”))
{
//Here I run an SQL query statement against the database object
//that I instantiated in the using statement above.
dbadapter = new SqlDataAdapter(”select * from Customer”, dbconn);
When I click the Show Customers button I get an error:
login failed for user ’sa’
Comment by Lee Griffith — April 28, 2009 @ 7:10 pm
Please see the following video how to get to the Login properties window for the “sa”
account. Then change the password to Password1234*
Also make sure the account is enabled. That is in the video too.
/videos/configure_sql_server_enable_sa.html
You can also test to make sure that you have the right password by trying to logon to the
SQL Server Management Studio using the sa account username and password. That is a good
test.
Comment by ted — April 28, 2009 @ 11:18 pm
It worked. Thank you.
I rebuilt the database after setting up the database per your suggestion. Thanks much.
Comment by Lee Griffith — May 3, 2009 @ 6:55 pm
Error 1 ‘ASP.details_aspx’ does not contain a definition for ‘GridView1_SelectedIndexChanged’ and no extension method ‘GridView1_SelectedIndexChanged’ accepting a first argument of type ‘ASP.details_aspx’ could be found (are you missing a using directive or an assembly reference?) E:\aspnet87\gridviewshpping\details.aspx 1 1 E:\aspnet87\gridviewshpping\
Comment by — May 26, 2009 @ 6:11 am
string sCustomerId = Request.QueryString[”customerid”];
int customerid = int.Parse(sCustomerId);
if database column CustomerID i use type nvarchar. How do I? I don`t know convert into type nvarchar. Can you help me? Thank!!!
Comment by Kaka — September 2, 2009 @ 11:55 am
Hi Kaka,
If you want to store the CustomerID as an nvarchar you don’t need to convert it. Just use the sCustomerId (string) variable instead of the customerid (int) variable.
Also, if you are storing numeric data in your database table, you should use a numeric database type like “int” instead of a text string type like nvarchar.
Using the numeric type takes less space and is more efficient overall.
Comment by ted — September 3, 2009 @ 8:48 am
Thank you.
It is a very good tutorial.
Your tutorial helped me to start coding in ASP.net, not only using existing controls.
Elena
Comment by Elena — November 20, 2009 @ 10:53 am
I have created the table customers and have been banging my head against the wall for 2 days now. When I debug GridView_SQLServer.aspx.cs I continue to get the following error: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Can you help me out?
Comment by Kerry — November 21, 2009 @ 10:25 am
Kerry,
Getting SQL Server to integrate with ASP.NET is not always easy. There are many database configuration options that can cause the error you are seeing. I recommend that you configure your SQL Server using the instructions that I have in the following article first and see if that fixes your error:
/2008/12/15/how-to-configure-sql-server-2008-express-for-aspnet-and-adonet/
Watch the example videos closely because you have to make sure you follow each step.
Comment by ted — November 21, 2009 @ 10:49 am
i love to do both over that the internet shopping and on line window shopping
for goods and other stuffs
Comment by — June 28, 2012 @ 10:11 am
REMEMBERthis is the man who removed the American flag from his airplane during that the campaign nd replaced it with a
Pepsitype flag of his own. That was the first warning to us.
Comment by — July 5, 2012 @ 7:55 am
well of course, everyone loves to find rich conversely not everyone would love to do challenging
work
Comment by — July 7, 2012 @ 11:33 pm
you employ a wonderful blog here! wish to have invite posts in my minor blog
Comment by — July 8, 2012 @ 6:18 pm
I believe one of your commercials caused my browser to resize, you can still well want to put that on your blacklist.
Comment by — July 9, 2012 @ 11:13 am
I must thank you for the efforts you have made in publishing this web site post.
I hope that the the same best article by you later on also.
Really your creative writing expertise has encouraged me to start out my very own website now.
Actually the blogging is spreading it is wings
without delay. Your write up is actually a first-class model of it.
Comment by — July 9, 2012 @ 5:36 pm
There are a variety of posts out there near
this, I do believe taking there reference could
experience made this spot or article really informative. Thats not me expression this
information is poor quality. Merely I need to pronounce that
the fact that information provided here was unique, merely
rebuild more near perform, supporting to former info
will receive been really excellent. That the points you have touched listed below have been really
important, thus Ill spot many of that the information here to make this really best for entirely
that the newbies here. Thank you this information. Really helpful!
Comment by — July 10, 2012 @ 12:57 am
Hi! Ive been reading your web site for a while now and finally
got the bravery to go ahead and give away you a shout out out of Kingwood
Texas! Just wanted to mention keep up that the great job!
Comment by — July 14, 2012 @ 6:07 am
After I originally left a comment I appear to have clicked on the -Notify me when new comments are added-
checkbox and now whenever a comment is added I recieve four emails
with the same comment. Perhaps there is a way
you can remove me from that service? Cheers!
Comment by — July 14, 2012 @ 8:11 am
I am actually delighted to read this blog posts which consists of tons of valuable data, thanks for providing these kinds of statistics.
Comment by — July 14, 2012 @ 8:27 am
Hello there, just became alert to your blog through Google, and found that its truly informative.
Im gonna watch out for brussels. Ill be grateful whenever you continue this in future.
A lot of people will be benefited from your writing.
Cheers!
Comment by — July 14, 2012 @ 8:37 am
very nice post, i definitely love this site, persist in it
Comment by — July 14, 2012 @ 9:27 am
naturally like your web-site but you need to test the spelling on several of your
posts. A number of them are rife with spelling issues and I in finding it very troublesome to inform the reality
on the other hand I’ll certainly come back again.
Comment by — July 14, 2012 @ 9:50 am
Howdy! This blog post could not be written much better!
Reading through this post reminds me of my previous roommate!
He always kept talking about this. I most certainly will send
this article to him. Fairly certain he’ll have a very good read. Thanks for sharing!
Comment by — July 14, 2012 @ 10:44 am
you might have an amazing blog right here! would you like to make some
invite posts on my weblog
Comment by — July 14, 2012 @ 10:49 am
Wow I found this place on Yahoo poking around for something
else entirely, and now Im going to need to go back and go all the old material.
So much for without charge time today, then again this was a spectacular get a hold of.
Comment by — July 14, 2012 @ 10:52 am
it sometimes demanding to opt for that the right kind of mens clothing conversely there have been
helpful buying guides on that the net
Comment by — July 15, 2012 @ 10:11 am
Ive been exploring for a bit for any highquality articles
or blog posts on this sort of area . Exploring
in Yahoo I at last stumbled upon this website. Reading this info So
i am happy to convey that I have an incredibly good uncanny feeling I discovered
just what I needed. I most certainly will make sure to dont forget this website and give out it a glance regularly.
Comment by — July 15, 2012 @ 1:20 pm
Im impressed, I have to admit. Genuinely
rarely should i encounter a blog thats both educative and entertaining, and without a doubt, youve got hit that the nail for that the head.
Your thought is outstanding that the question is really a thing that there are not enough everyone is speaking
intelligently about. My company is happy i found this in my hunt for
some thing regarding this.
Comment by — July 15, 2012 @ 1:42 pm
Awesome! I appreciate your blog post to this matter.
It has been insightful. my blog maple syrup
Comment by — July 15, 2012 @ 3:03 pm
This is that the precise weblog for anybody who needs to uncover
out about this topic. You realize so much it’s just about onerous to argue with you not that I really would need aHa. You definitely put a brand new spin on a topic thats been written about for years. Great stuff, simply nice!
Comment by — July 15, 2012 @ 3:49 pm
Hello my friend! I would something like to say that this article is amazing, nice written and include almost
all important infos. Id for instance to see more posts
something like this .
Comment by — July 16, 2012 @ 2:51 am
Its perfect time to make some plans for that the future and its
time to be happy. Ive read this post and if I could I would for example to suggest you
some interesting things or tips. Perhaps you could write next articles referring to this
article. I want to read even more things about it!
Comment by — July 16, 2012 @ 4:01 am
there have been insurance organizations that are scam too
so make sure that you deal with legit insurance
businesses,.
Comment by — July 16, 2012 @ 5:50 am
very nice submit, i certainly love this web site, carry on
it
Comment by — July 16, 2012 @ 5:53 am
There is noticeably quite a lot of money comprehend
this. I assume youve made certain nice points in functions also.
Comment by — July 16, 2012 @ 6:40 am
Id need to talk to you here. Which is not some
thing I do! I love reading an article that can figure out people to believe.
Also, many thanks allowing me to comment!
Comment by — July 22, 2012 @ 1:45 pm
Typewriter. or. . “UROPYOURETER . meaning ‘a collection of urine
and pus in that the ureter.
Comment by — July 24, 2012 @ 3:29 pm
some jewelry stores present a fine deal of bargain for
their new jewelry styles
Comment by — July 26, 2012 @ 8:52 pm
Im impressed, I should say. Truly seldom do
I encounter a weblog thats that the two educative and entertaining, and permit me tell you, you have hit the nail on the head.
Your thought is remarkable the issue is some thing that not
adequate people are speaking intelligently about. I am incredibly pleased that I stumbled across this in my lookup for something relating to this.
Comment by — July 29, 2012 @ 3:51 pm
Some genuinely nice stuff on this website , I love it.
Comment by — July 31, 2012 @ 7:23 am
What’s up colleagues, fastidious paragraph and nice arguments commented here, I am actually enjoying by these.
Comment by — August 1, 2012 @ 7:22 am
First-rate I should definitely pronounce, impressed with your web site.
I had no trouble navigating through all tabs and related info ended up being truly simple to do to access.
I recently found what I hoped for before you identify
it at all. Quite unusual. Is likely to appreciate it for those who add forums
or something, web site theme . a tones manner for
your customer to communicate. Good task.
Comment by — August 2, 2012 @ 8:10 am
Respect to article author , some wonderful information .
Comment by — August 3, 2012 @ 6:58 pm
Hi! This is my first visit to your blog! We are a collection of volunteers and starting another project in
the community in that the same niche. Your blog
provided us valuable information to work on. You have done a extraordinary job!
Comment by — August 4, 2012 @ 11:07 pm
I keep listening to what is this great update lecture
about receiving boundless on that the web
grant applications and so i are already shopping for
the most beneficial site to get one. Could you advise me please,
where could i receive some
Comment by — August 5, 2012 @ 8:02 pm
I was also reading a topic such as this one from an extra site.
Comment by — August 7, 2012 @ 12:10 am
Strong blog. I acquired several nice information.
Ive been keeping an eye fixed on this technology for some time.
Itutes attentiongrabbing that the manner it retains solely
different, but a quantity of of the primary parts stay constant.
have you ever observed plenty amendment since Search engines created their own
latest purchase within that the field
Comment by — August 13, 2012 @ 12:57 pm
Now youve got your new website and youre eager to begin making some gross sales!
Conversely, how are you going to make sales whenever you happen to do not have excessive volumes of tourists
to your website
Comment by — August 13, 2012 @ 3:15 pm
An fascinating discussion will probably be worth comment.
I think someone to ought to write much more about this topic,
it will not become a taboo subject conversely typically everyone is too few to communicate on such topics.
Yet an alternative. Cheers
Comment by — August 20, 2012 @ 1:09 am
In this great pattern of things you really secure a B+ with regard
to demanding work. Where exactly you lost me was first in your specifics.
You recognize, people say, details make or break the argument.
And that couldnt be much more true right here. Having said that, let me reveal to you precisely what did provide excellent results.
Your writing is really highly powerful and that’s probably that the reason why I am taking that the effort in order to comment. I do not actually make it a regular habit of doing that. Second, even though I can see a leaps in reasoning you come up with, I am not necessarily confident of exactly how you seem to unite the points which produce that the actual final result. For now I will, no doubt subscribe to your issue conversely hope in that the near future you connect the dots better.
Comment by — August 23, 2012 @ 9:47 pm