Click here to download the source code for this lesson.
The SqlDataSource server control can make searching your database easy without writing any C# code. For example, if you are showing the results of a query on a web page and you want the user to be able to search based on what the user types in a TextBox control. You can link the TextBox control and other input controls to your SqlDataSource query using parameters. In this example, on my web page, I want the user to be able to search the Customer database table based on either a last name or an e-mail address so I created two TextBox controls accordingly.
First, you should have established a binding between the GridView control and an SqlDataSource control on your web form. Refer to the prior lesson Retrieving database data with the ASP.NET SqlDataSource and GridView controls to learn how to do that if you haven’t already.
Next, modify the SelectQuery property of the SqlDataSource control. You have to add the “where” clause to the Select statement so that the database can search based on information that was typed in by the user. You can accomplish this by putting named parameters in your select statement “where” clause like this: “where last_name = @LastName OR email_address = @Email”. In this example I searched two different columns last_name and email_address. The at(@) symbol variables are the parameters: I have two parameters named LastName and Email.
When the query runs it will replace the @LastName and @Email with values from my web page TextBox controls. In order to connect those parameters to my TextBox controls I have to click the “Add Parameter” button in the SelectQuery editor popup window and add two parameters. The names of these two parameters have to match the names that I used in the “where” clause: LastName and Email. For each parameter that I add, I have to set the “Parameter source” property to “Control” and the “ControlID” property to the ID of the corresponding TextBox control on the web form (e.g. txtLastName). I also set the “DefaultValue” property for each parameter to “nullvalue” in case the user doesn’t type anything in. I put “nullvalue” because I want to force the user to type something in order for the query to work. You can put an actual default value for your scenario if one applies or you can use a bogus value like I did and that way the user has to enter something in order for the query to work.
Click here to watch an example video where I setup searching a database table with parameters using the SqlDataSource and TextBox controls.
Click here to download the source code for this lesson.
The SqlDataSource server control can make your GridView control easily allow data paging for the end user of your web application without writing any C# code. For example, if you are showing the results of a query on a web page and you want the user to be able to look at one page of data at a time. This of course applies to situations where you have several rows of return data and one screen is not sufficient to display all of the results. You can define how many rows are shown in a page, but by default it is 10. If more rows are in your DataSet than your page size, .NET will show a hyperlink at the bottom of your GridView that allows the user to view page 2, 3, etc.
First, you should have established a binding between the GridView control and an SqlDataSource control on your web form. Refer to the prior lesson Retrieving database data with the ASP.NET SqlDataSource and GridView controls to learn how to do that if you haven’t already.
Next, set the AllowPaging property of your GridView control to True. Then set the PageSize property to how many records you want to show on each page. Again, the default is 10. That is all you have to do. Pretty simple huh?
Click here to watch an example video where I setup paging in my GridView control.
Click here to download the source code for this lesson.
The SqlDataSource server control can make your GridView control easily allow data sorting for the end user of your web application without writing any C# code. For example, if you are showing the results of a query on a web page and you want the user to be able to sort one of the columns in ascending or descending order.
First, you should have established a binding between the GridView control and an SqlDataSource control on your web form. Refer to the prior lesson Retrieving database data with the ASP.NET SqlDataSource and GridView controls to learn how to do that if you haven’t already.
Next, set the AllowSorting property of your GridView to True. Then you need to edit the columns in your GridView. Go to each column that you want to make sortable and set the SortExpression property to the name of the column in the SQL query. In my example I wanted to make the Last Name column sortable in my GridView so I set the SortExpression to last_name which is the name of a column in my database query. When you set this property of a column in your GridView ASP.NET will show a hyperlink on the column header of the grid when the application runs. The user can click on the column header hyperlink to sort the data in ascending or descending order.
Click here to watch an example video where I setup sortable columns in my GridView control.
Click here to download the source code for this lesson.
In the prior lesson Using the ASP.NET GridView control to query a SQL Server database I showed you how to connect to and pull data from a database table and display it in a GridView. In that lesson I wrote C# code to do the data retrieval and data binding to the GridView server control. When you write code to perform these types of operations you have the ultimate flexibility and control over how your program behaves, however there are situations where you may want to connect to interact with a database without writing any code. This is where the SqlDataSource data control comes in. With the SqlDataSource server control, you can connect to a database and retrieve or update data without writing any C# code. Here are some benefits of the SqlDataSource control and reasons to use it versus writing your own C# code:
- Use it in situations when you want to connect to a database directly from your web form and run queries. If you are working in an N-Tier design and you have back-end libraries or web services that integrate with your database, you probably want to avoid this control and you may not be able to use it. For example, in many government and commercial implementations the web server is usually in a DMZ without the ability to connect to the database.
- Use it in situations when you want to implement filtering, sorting and paging without any code. This is one of the primary benefits of the SqlDataSource control.
- Use it in situations when you want to query and update database data without writing any code. This is a rapid application development characteristic of the SqlDataSource control which makes Visual Web Developer 2008 and Visual Studio 2008 truly agile integrated development environments.
- You can pass parameters to queries or stored procedures by binding to TextBoxes and other server controls to solicit input data from the end user. For example, you can implement a table search and ask the user to type in a last name that will be used in the query Select statement. The SqlDataSource control handles all the parameter binding.
As you can see, this is a powerful control and can help make your C# development experience more productive, but cannot be used in every situation so you will have to determine whether or not it is feasible for you. In this lesson and in the next few lessons I will explore how to use the SqlDataSource control to make the GridView control do many useful tasks such as sorting and paging.
Let me get started by talking about what the SqlDataSource control actually is. It is a server control, just like the TextBox and Label controls, however it is not a visual control. In other words, you don’t see it on a web page. It works in the background on the server when your .aspx page runs to perform integration with a database. You first have to drag the control from the Data group of controls in the Toolbox onto your web form. After you do that, you configure some of the properties of the control and then you can associate it to your GridView using one line of code in a Button click event handler method that we will explore later in this lesson.
After you drag the SqlDataSource control onto your web form, change the ID property to something more meaningful. In my example I changed it to CSharpDataSource. Next set the ConnectionString property. You can paste in any valid database connection string. In my example I put an SQL Server 2008 connection string to my csharpuniversity database. Next you have to set the SelectQuery property. This property contains a popup window that lets you enter valid SQL query statements. Finally once you have all of these properties configured, you can add a Button to your web form and create a click event handler by double clicking on the Button in design view. Then put the following line of code in the click event method (my method is named btnCustomers_Click).
gvCustomers.DataSourceID = "CSharpDataSource";
Notice that I used the DataSourceID property of the GridView control gvCustomers. I assigned the ID of my SqlDataSource control on my web form which is CSharpDataSource. That line of code basically tells .NET to take the SqlDataSource control and bind it to the GridView. If you don’t do this, nothing will happen when your program runs and you won’t see any data. If you need to use the SqlDataSource in a scenario where you don’t want the user to have to click on a Button in order to retrieve data from the database (e.g. you want to show some data when the page loads), you don’t need to write any code, you can simply set the DataSourceID property value in the Design view of your web form using a point and click method. Either way the SqlDataSource control is essentially connecting to the database, running a query and then storing the results of the query in a DataSet. Then, because you bound the SqlDataSource to the GridView, the DataSet result data will be displayed on the web page. Works like magic doesn’t it? Play around with the SqlDataSource and see if it fits well in your environment. Click here to watch an example video where I setup and use the SqlDataSource control to retrieve data and show it in a GridView control.
Click here to download the source code for this lesson.
Normally when you use a GridView control, it will show all the columns in the associated DataSource. For example, in my prior post I executed a query to retrieve all the columns from the Customer table: “select * from Customer”. The results of that query were saved in a DataSet variable called dbdata. I then assigned dbdata to the DataSource property of my GridView control. When I run the application, the GridView shows all the columns. The GridView also displays the column names as the header for the grid on the web page.
In a real world scenario, you probably are going to need more control over what is displayed in your grid. The way to achieve this is to set the AutoGenerateColumns property of the GridView to False. When you do this, it lets ASP.NET know that you are going to manually define how you want the data displayed in your grid. After you set that property to False, you then have to define each display field, one by one. This is pretty easy to do since Visual Web Developer has an interface that lets you control all of the display fields for the grid. Open your .aspx page in Design view and then put your cursor over the GridView control that is on your form. You will see a little button with a “>” symbol on it. Click on that to see the GridView Tasks window. Click on the “Edit Columns…” option to bring up the Fields window.
In the Fields window, you can add new BoundFields to the grid. A BoundField means that you are creating a display field that is “bound” to a column in the DataSource. Think of binding like connecting a visual item on a web page to a data item coming from a database. This is why the GridView control is considered to be a data bound server control. In my example code I created two display fields and bound them to two columns in my DataSet: first_name and last_name respectively. I also set the HeaderText property for each bound field to: “First Name” and “Last Name” respectively. The HeaderText property gives you the ability to control what shows up as the header for each grid column. Notice that even though my query is returning more than two columns from the database table, I am only showing two of them on the web page.
One last tip that I will close the lesson with is that you can also go to Source view of your .aspx page and manipulate the columns of the GridView from there. That comes in handy sometimes if you want to add a new BoundField. You can copy and paste one of the existing BoundFields and then change the properties for the newly added field.
Click here to watch a video that shows how to show or hide columns in an ASP.NET GridView control.
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.
Click here to download the source code for this lesson.
This lesson builds on the previous lesson regarding ASP.NET redirects. In this lesson I will cover the ASP.NET Session object. When you are implementing a requirement that needs information shared among different web pages, you can use the Session object to store state between the group of related pages. There are also situations where you may want to store information in the Session that is shared among ALL web pages in your ASP.NET application. The ASP.NET Session object is another special object that is available to you in addition to the Response object that I covered in the previous lesson. In this lesson I will show you an example of how you can use both of these special objects together to implement a two page credit card validation scenario.
The Session object is similar to a Dictionary object as it contains key/value pairs. You can store as many variables in the Session as you need and they can be of any type, including your own User defined Class types. When you store a variable in the Session, and you want to retrieve it back, use the same key value. For example, if you store an object in the Session using the key “Myvar”, make sure you use “Myvar” when you want to retrieve the object back.
In the example for this lesson, I setup a web form called Page1 that contains some TextBox controls to allow the user to type their name and credit card information. When the user clicks the Next button, I store the information entered into the Session object and redirect the application to another web form called Page2 (which is a confirmation page).
Session["FirstName"] = txtFirstName.Text;
Session["LastName"] = txtLastName.Text;
Session["CreditCardNumber"] = txtCreditCardNumber.Text;
Session["ExpirationDate"] = txtExpirationDate.Text;
Response.Redirect("Page2.aspx");
On the Page2 confirmation page, I read back the variables from the Session object and display them on the web page.
txtFirstName.Text = Session["FirstName"].ToString();
txtLastName.Text = Session["LastName"].ToString();
txtCreditCardNumber.Text = Session["CreditCardNumber"].ToString();
txtExpirationDate.Text = Session["ExpirationDate"].ToString();
Click here to watch an example video where I show you how I setup my web pages and implemented the Session variable storage and retrieval.
Click here to download the source code for this lesson.
While developing in ASP.NET, a lot of functionality can be implemented using a single web form, but there are scenarios where multiple web forms are necessary. A common example of this on the web is order forms on shopping web sites. Order forms can typically be comprised of several web pages, each page requests different information from the user about the purchase. One page may request shipping information, whereas another web page may request credit card information.
Normally when you add a Button control to an ASP.NET web form and create the Click event, the Button will postback to the same web form when it is clicked. But suppose you want to bring up another web form when the Button is clicked. You can accomplish this by using a redirect. A redirect simply means you are sending a message to the browser, telling it to visit a different web page.
ASP.NET gives you access to a special object called the Response object. The Response object contains a method called Redirect that you can use to perform the redirection to another web page. You should pass the name of the web page to the Redirect method as a string parameter.
Response.Redirect("Page2.aspx");
Click here to watch an example video where I redirect from Page1.aspx to Page2.aspx.
In this lesson I will cover a very basic and common task for an ASP.NET web developer: how to create new web pages (web forms) in your ASP.NET web site. When you first create a new web site, you will see only Default.aspx in your project. You can create new web forms by using the Solution Explorer. Right click on the project name and choose “Add New Item”. When the Add New Item window comes up, choose Web Form in the templates list and then enter the name of your new web form .aspx page. You can create as many web forms as you need by using the Add New Item functionality.
Click here to watch an example video where I add some new web forms to my project. In the video I create two new web forms called Page1.aspx and Page2.aspx. I also deleted the Default.aspx web form that was created by default. Make sure to choose the option to “Place code in separate file” when you create a new web form. This creates the .cs code behind file and facilitates clean separation between the presentation layer and your C# source code.
By using the SQL Server Management Studio that installs with SQL Server you can perform a number of database administration tasks such as creating databases and inserting test data. In this lesson I will show you how to perform three administrative tasks to create and start using a new database.
First, let’s create a new database. Logon to the Management Studio and right click on Databases, then choose “New Database”. You can enter the name for the new database there and Management Studio will fill in the rest of the information for you. Click here to watch a video example of how to create a new database. In the example I created a new database called “csharpuniversity”.
After you have a database created, it should show up in the list of available databases when you expand the Databases node in Management Studio. If you expand the new database that you created, you should see a list of available things to administer such as Database Diagrams, Tables, Views, Synonyms, Security, etc. Right click on Tables and choose “New Table”. Once the table creation window comes up, you will be able to define all the columns for your new table. You have the ability to specify what data types and size to use for each column, as well as specify the primary key for the table. Click here to watch a video example of how to create a new table. In the example I created a new table called “customer”.
Once your new table is created, you may want to populate it with some test data. Management Studio has a feature to help you easily do that. Expand the list of Tables in your database and right click on the table name, then choose “Edit Top 200 Rows”. Once the data editor window comes up, you can create and edit records in the database table. Click here to watch a video example of how to insert test data. In the example I created three new test data records.
Once you have a SQL Server database to connect to and a table to query from, you should be able to start programming with ADO.NET to interface with your database. You can bring up the SQL Server Management Studio anytime that you need to create more tables or test data in your database.
*Most of the concepts and configuration lessons in this category of blog posts “SQL Server Installation - Config” also apply to SQL Server 2005 Express and Full editions (as well as SQL Server 2008 Full edition). Also, if you have installed any version of SQL Server 2008 or 2005 on your machine already, you may be reading these lessons to look for hints regarding how to set up SQL Server for smooth integration with ASP.NET and C# database programming; if that is your situation, you will probably find value in going through the lessons to learn the configuration parameters and setup that I tweaked to make SQL Server work smoothly with ASP.NET and ADO.NET.
If you have not already done so, please read the previous lessons Preparing your computer to install SQL Server 2008 Express and How to install SQL Server 2008 Express. Those lessons contain some important videos to watch and examine for the setup of SQL Server. Let me recap some important configuration items to note if you are an ASP.NET (ADO.NET) developer trying to interface with SQL Server or a DBA setting up a SQL Server instance for an ASP.NET development team:
- Setup the SQL Server service to runas the lowest possible privelege user in Windows. I did that by creating a user account on my machine called “SQL Server Service” and then configured the SQL Server database engine to runas that account.
- Use Mixed Mode authentication. Setup a strong password for the “sa” account.
Now, let’s continue to the rest of the configuration that is necessary to smoothly integrate SQL Server 2008 with ASP.NET and ADO.NET development. The next important item on the list is to setup the TCP port for SQL Server. You have to use the SQL Server Configuration Manager program to setup port 1433 for SQL Server and then enable the protocol. *Pay attention in the TCP/IP Properties popup window, where you specify the IPAll properties, that you enter 1433 for the TCP Port and BLANK for the TCP Dynamic Ports property (remove 0 if that is in there). Also be sure that IP1, IP2 and IP3 are not enabled (Enabled = No). You have to restart SQL Server to make those changes take effect. Click here to watch a detailed video showing all the steps necessary to configure the TCP Port. By enabling port 1433 you are making it easier for the ADO.NET driver to communicate with SQL Server since that is the default port.
The final step left in the configuration is to enable the “sa” account which is may be disabled depending on your installation. You can use the Microsoft SQL Server Management Studio to enable the account. If it is already enabled, you don’t need to do anything more. Click here to watch a detailed video showing how to enable the SA account.
*Most of the concepts and configuration lessons in this category of blog posts “SQL Server Installation - Config” also apply to SQL Server 2005 Express and Full editions (as well as SQL Server 2008 Full edition). Also, if you have installed any version of SQL Server 2008 or 2005 on your machine already, you may be reading these lessons to look for hints regarding how to set up SQL Server for smooth integration with ASP.NET and C# database programming; if that is your situation, you will probably find value in going through the lessons to learn the configuration parameters and setup that I tweaked to make SQL Server work smoothly with ASP.NET and ADO.NET.
If you haven’t already done so, please read the lesson Preparing your computer to install SQL Server 2008 Express before proceeding with the installation. As I said in the lesson, installing SQL Server can be a bumpy ride at times; just hang in there and you will be successful. Please note that the installation and configuration of SQL Server in these lessons is written for .NET developers who are looking to integrate their ASP.NET applications with a database, anybody that is working with SQL Server may benefit from the information covered here.
The setup process described here has evolved over time after I experimented with various configurations and chose the one that integrated with ASP.NET with the least issues. For example, I had many problems using Windows Authentication with ASP.NET and SQL Server, so I switched to SQL Server Authentication which Microsoft labels as an older technology, but it is readily used in the industry and from ADO.NET is more similar in configuration/integration with other databases like Oracle and MySQL.
Before you actually start the SQL Server 2008 Express installation program, you need to create a new user account for SQL Server on your computer. SQL Server runs as a service in Windows and Windows requires that you choose a user account to run the service as. It is recommended that you use an account that has the least amount of priveleges on the computer as necessary. For example, you can create an account that is NOT part of Administrators and that should be fine. This helps improve the security of your computer. Click here to watch a video showing exactly how to create a Windows user account for SQL Server.
- Open Windows Explorer. The easiest way to do that is either to press the Microsoft button on your keyboard, hold it down and then press E. If you don’t have a Microsoft button, click Start->Run, type “explorer” without the quotes and press Enter.
- Once Explorer comes up, right click on My Computer and choose Manage. That will bring up the Computer Management window.
- From there you should go into the Local Users and Groups. Open the Users list.
- Follow the sequence in the video above to learn how to create a new User. I named my new account “SQL Server Service” so that I could identify it easily. Make sure you remember or write down the password because you are going to be required to type it in again later while running the SQL Server installation program.
Now, let’s begin the actual installation. Navigate to the folder where you download SQL Server 2008 Express and run the program SQLEXPRWT_x86_ENU.exe. Your file name may be different depending on what particular version you are installing. Windows may ask you if you want to run the program or cancel, click Run to proceed. Following that the installation program will initialize itself and that may take a couple of minutes. Eventually the SQL Server Installation Center window will come up. Click on Installation underneath Planning. Then on the right side, click on “New SQL Server stand-alone installation or add features to an existing installation”. That will bring up another window called SQL Server 2008 Setup. This is the window where most of the installation takes place. Click here to watch a comprehensive video example of how to install SQL Server. As you watch the video, here are some important configuration steps to pay attention to along the way:
- In the Feature Selection window, you should choose “Database Engine Services” and “Management Tools - Basic” at a minimum; other features are optional.
- In the Server Configuration window, you will have to configure the SQL Server Service to use the account “SQL Server Service” that you created earlier in this lesson. You will be required to enter the password and it must match what you entered earlier in the lesson when you created the account.
- In the Database Engine Configuration window, make sure to choose Mixed Mode. Type in a password for the system administrator account. This account is the famous “sa” account in SQL Server. Make sure you either write down or remember this password because you will need it later in your ADO.NET connection string when you want to connect to the database.
- In the Database Engine Configuration window, you will also need to Specify SQL Server administrators. The easiest way to do this is choose the Local computer group Administrators. This way, when you logon to your computer as a user with administrative priveleges, you will be able to administer your SQL Server instance.
- You will notice in the example video that I had an error message at the bottom of my Installation Rules window. The error stated “Previous releases of Microsoft Visual Studio 2008″ and when I clicked on the Failed hyperlink it gave me more detailed information along with what I needed to do, which was install Visual Web Developer 2008 Express with SP1. I left the SQL Server installation running while I installed the Visual Web Developer.
- The following part 2 video continues where Part 1 left off, after I installed the new Visual Web Developer. Notice that the error message went away after I clicked Re-run. This video ends when the installation starts running and files are being copied etc. That part takes a while; about 15 minutes on my computer.
- Finally, the last part 3 video shows that everything was installed successfully and the installation is complete.
Stay tuned for the next lessons, where I will cover how to configure SQL Server 2008 so that integration with ADO.NET and ASP.NET will be smooth. This will help you practice with ADO.NET and write database programs.
*Most of the concepts and configuration lessons in this category of blog posts “SQL Server Installation - Config” also apply to SQL Server 2005 Express and Full editions (as well as SQL Server 2008 Full edition). Also, if you have installed any version of SQL Server 2008 or 2005 on your machine already, you may be reading these lessons to look for hints regarding how to set up SQL Server for smooth integration with ASP.NET and C# database programming; if that is your situation, you will probably find value in going through the lessons to learn the configuration parameters and setup that I tweaked to make SQL Server work smoothly with ASP.NET and ADO.NET.
The version of SQL Server 2008 Express that I prefer is the one that comes with the SQL Server Management Studio. The Management Studio software is a nice graphical user interface that allows you to create databases, tables, import data, perform backups, etc. The SQL Server version that contains the Management Studio is officially called “Microsoft SQL Server 2008 Express with Tools”. . If you don’t install the version with Tools, you may have problems tweaking the database to configure it properly and even create tables with data. Also, I do not recommend installing SQL Server during your Visual Web Developer installation; install it separately.
SQL Server 2008 has some kind of compatibility issue with versions of Visual Studio prior to Visual Studio 2008 SP1. This includes older versions of the Express Editions of Visual Web Developer and Visual C#. If you have the older versions of those products installed, you probably have to remove them and upgrade to the 2008 SP1 version in order to complete the SQL Server 2008 installation. I have experienced this incompatibility first hand because it happened to me. I had Visual Web Developer 2008 Express installed on my computer and my SQL Server 2008 Express installation displayed an error during the process telling me that I had an older version of Visual Studio installed. It was easy to fix; all I had to do was download and install Visual Web Developer 2008 Express with SP1 and that allowed me to complete my SQL Server installation. In these types of situations, you may be able to install the software that SQL Server needs and continue with installation of SQL Server without having to start it from scratch again. In other situations, a reboot may be required to install prerequisite software so you will end up having to start the SQL Server installation again.
Before you get started installing SQL Server 2008 Express, be mindful that there may be roadblocks along the way. It is a long installation with many steps, including some not so intuitive configuration parameters that need to be setup correctly during the process. If you install all the prerequisite software and follow the steps here closely, you will be successful. For those of you that already installed SQL Server in the past and are having problems integrating with .NET, you may consider uninstalling SQL Server and installing it fresh using the advice in these lessons. NOTE: if you installed SQL Server in the past and created any database tables with data, before you uninstall, make sure to backup your databases somewhere first so that you can restore them into the new database once you complete a fresh installation.
Here is the list of software that you must have installed before attempting to install SQL Server:
- Visual Studio 2008 with SP1 or the Visual Studio Express Editions with SP1 (Visual Web Developer and Visual C#) - older versions of these products should be removed as stated previously in this lesson.
- Microsoft .NET Framework 3.5 Sp1
- Windows Installer 4.5
- Windows PowerShell 1.0
If you visit the link above to download SQL Server 2008 Express with Tools, you should see download hyperlinks to the prerequisite products in the “Instructions” section. I recommend that you take notes during this entire process of installing and configuring SQL Server 2008 because chances are that you may have to do it again sometime soon and you may also want to share tips with friends or fellow Internet users looking for advice.
Click here to download the source code for this lesson.
In the previous lessons about User defined Classes we had created a Class called Arithmetic that was able to add numbers. In this lesson we will talk more about User defined Classes and using them to store information (i.e. store state) so that the information can be passed around between different parts of your application. One of the strengths of the C# language is that it is Object-Oriented and one of the benefits of Object-Oriented languages is the ability to encapsulate data in objects.
For this example I created a web page called Default.aspx that has 3 pieces of information that the user can input: first name, last name and age; each in separate TextBox controls. If I wanted to create a Method in my web application that will take this information and do something with it (e.g. save it to a database), I would need a way to pass that information to the Method. I could declare a Method that takes 3 parameters and pass the information that way, however this is not a very Object-Oriented solution and if I wanted to change the parameters or add new parameters in the future, it would require changes to the Method declaration and the code that calls the Method. A more robust approach is to create a User defined Class to store the information and pass around the Class.
I created a Class called Customer that will encapsulate the 3 data elements:
public class Customer
{
string mFirstName;
string mLastName;
string mAge;
public Customer(string pFirstName, string pLastName, string pAge)
{
mFirstName = pFirstName;
mLastName = pLastName;
mAge = pAge;
}
public string getFirstName()
{
return mFirstName;
}
public string getLastName()
{
return mLastName;
}
public string getAge()
{
return mAge;
}
}
Notice that the 3 pieces of information are stored as instance variables in the Class Customer. In my web application I am able to store the information in an Object of this Class with one line of code and pass it to a Method called DisplayCustomerInformation as follows:
Customer mycustomer = new Customer(txtFirstName.Text,
txtLastName.Text, txtAge.Text);
DisplayCustomerInformation(mycustomer);
Notice that I passed the values of the 3 TextBox controls to the Customer constructor. Then I passed the variable mycustomer to a Method that displays a summary of all the values in a multiline TextBox control. Although this is a simple example, you can experiment with passing around User defined Classes in your applications. You can use them just like regular C# variables. I recommend using User defined Classes as much as possible to help keep your program more maintainable and Object-Oriented.
This lesson is a two part discussion of some syntax rules that are part of the C# language. The first rule is that statements (i.e. lines of code) in a C# program must be terminated by the semicolon “;” character. This lets the compiler know that a particular statement is complete.
int i = 33;
You can break apart your statements into multiple lines. This is allowed by the language. Take a look at how I broke apart the definition and initialization of the variable i into two lines of code below.
int i
= 33;
Some C# language constructs do not require a semicolon at the end of a statement. Conditionals and loops are examples of these. Take a look at an “if” statement example.
int i = 33;
if (i == 33)
i = 10;
Notice that the line of code “if (i == 33)” does not have a semicolon terminator.
The second part of this lesson deals with syntax rules for blocks of code in C#. All blocks of code must be surrounded by open and closed curly braces { }. If you read the previous lessons in the “Getting Started with the language” category, you will have seen an example of curly braces denoting the beginning and end of blocks of code; this example is the Button1_Click method that has both curly braces. Here are the places in a C# source code file that you will use curly braces to denote the start and end of a block of code:
- The class declaration (highest level) - you should see a curly brace on the line following the name of the class and at the end of the file. In some situations you will use curly braces at another level higher than this, which is when Namespaces are used, but more about that in more advanced lessons.
- Method declarations (inside the class) - you should see curly braces denoting the start and end of each Method in a class.
- Blocks of code inside Methods (lowest level) - you will use curly braces for blocks of code inside Methods such as for loops and conditionals. There will be situations where the curly braces are nested, such as for a loop within another loop.
Here is an example where curly braces are used to denote the start and end of a loop block.
for (int i = 0; i < 5; i++)
{
int myvar = i;
txtOutput.Text = myvar.ToString();
}
Notice that inside the curly braces are two statements. Those two statements make up the block of code.
Here is an example of a nested loop so that you can see how nested curly braces can be used.
for (int i = 0; i < 5; i++)
{
for (int j = 5; j < 10; j++)
{
int myvar = j;
txtOutput.Text = myvar.ToString();
}
}
Notice that both the outer and nested for loops use curly braces. You will encounter situations when using conditionals and loops where use of the curly braces is not necessary; this is when the conditional or loop have only one line of code in the block. While it is not required to use curly braces in those situations, you may consider using them anyway since they clearly separate a distinct block of code. This helps some programmers read the code.
Click here to download the sample source code for this lesson.
In previous lessons in this category we talked about User defined Classes and Objects. Before we move on to additional related topics, let’s review terminology that has been covered already:
- Class - A user defined type. A Class is a collection of related Methods (i.e. functions or procedures) that are logically grouped together. Classes are used to organize your application code and facilitate re-use.
- Object - An instantiation of a Class. In order to use a Class in C#, the Class must be instantiated by using the “new” operator. Instantiation creates an “object of the class”.
Up to this point, we have experimented with User defined Classes, but now we will also talk about Classes that are not defined by you the programmer, but are already available in the .NET Framework Class Library. The .NET Framework Library contains a lot of prebuilt Classes. These Classes are provided for you by the Framework and contain many different functions that you can use in your program. These Framework Classes are organized into a distinct set of Class Libraries (.dll files). Each Library or set of Classes is organized into what is referred to as a Namespace in .NET. A Namespace is a set of related Classes that are grouped together by functionality. Here are some of the common .NET Namespaces that you can use in your applications:
- System.Collections.Generic - Contains Classes that deal with lists and hash tables.
- System.Configuration - Contains Classes that assist in using configuration files such as web.config
- System.Data - Contains ADO.NET Classes that assist in interfacing with data sources like databases. The most common data related Class in this Namespace is the DataSet class.
- System.IO - Contains Classes that assist with reading and writing to files/streams.
- System.Text - Containts Classes that encode/decode strings and byte blocks.
There are many more Namespaces in the Framework, just about everything from networking to security is addressed somewhere in the .NET Framework. *Notice that the Namespace name can be composed of multiple parts, separated by a period, such as System.Collections.Generic which is a three part name. .NET stores these Namespaces in separate .dll files; each file matches the Namespace name so for example there is a Class Library file called System.Collections.Generic.dll that contains that Namespace. There is another file called System.Configuration.dll and so on. Let’s add some new definitions:
- Class Library - A .dll file that contains a group of related Classes.
- Namespace - A logical category name for a Class. The Namespace name usually always matches the Class Library name (e.g. the System.Text Namespace is stored in the System.Text.dll Class Library file).
- .NET Framework Class Library - A set of pre-built Class Library files that come with .NET.
The only difference between the .NET Framework Classes and User defined Classes that you as the programmer can create yourself is that the Framework Classes are already coded by Microsoft and shipped with .NET when you install it onto your computer. The way in which your application calls or instantiates either types of Classes is identical. In both cases, you must use the “new” operator. Let’s see an example where the .NET Framework library System.Text is called. In this example, I instantiate and use the Class called StringBuilder.
System.Text.StringBuilder mybuilder = new System.Text.StringBuilder();
mybuilder.Append("This");
mybuilder.Append(" is");
mybuilder.Append(" a");
mybuilder.Append(" string builder.");
TextBox2.Text = mybuilder.ToString();
Let’s compare that with how we call a User defined Class called Arithmetic:
Arithmetic mynewclass = new Arithmetic();
int iResult = mynewclass.AddTwoNumbers(50, 20);
TextBox1.Text = iResult.ToString();
Both types of Classes are instantiated by using the “new” operator”. You will notice that in the StringBuilder example above that I prefixed the name of the Class in the instantiation with the Namespace name System.Text. This can be avoided by putting a special statement at the top of the file called the “using” keyword:
using System.Text;
If you put the “using” keyword at the top, you can avoid having to use the Namespace prefix in the instantiation:
StringBuilder mybuilder = new StringBuilder();
mybuilder.Append("This");
mybuilder.Append(" is");
mybuilder.Append(" a");
mybuilder.Append(" string builder.");
TextBox2.Text = mybuilder.ToString();
Now the syntax for instantiating a .NET framework Class versus a User defined Class looks identical.
Click here to download the source code for this lesson.
ASP.NET has a lot of programming and behavior features, but there is another aspect of the web programming environment that we haven’t discussed much in prior lessons; that aspect is the look and feel of the application. The look and feel of an application is just as important as the functionality because an application that has an organized and easy to use graphical user interface design helps improve the overall experience for the end user.
In ASP.NET, you can control the way that the application looks and feels by using Cascading Style Sheets (CSS). Cascading Style Sheets are a web programming standard and are not specific to ASP.NET so you may have been exposed to them before. Cascading Style Sheets are text files with a .css extension. These files contain a list of styles that are defined for an application. A style is a descriptor that defines how an object in a web page should look and how it should be layed out within the page. For example, with a style you can define that the font size for text box should be 12 point and it should be left aligned. Applications typically contain many styles for various objects in a web application. To learn more about to see a comprehensive list and defintion of the available styles. In addition to styles, to become an effective ASP.NET web developer, you also need to understand HTML tags and how they are used. You don’t have to be an expert, but you should be able to at least understand how to layout a basic web page using DIV and TABLE tags. and see a comprehensive list of available tags.
In an .aspx file, you will typically find a combination of HTML tags and ASP.NET server tags. ASP.NET server tags are identified by the property runat=”server”. Styles can be applied to both HTML tags and ASP.NET server tags. To apply a style to an HTML tag you set the Class property to the name of the style from the style sheet. To apply a style to an ASP.NET server tag you set the CssClass property to the name of the style.
In Visual Web Developer, to create a new style sheet file, right click on the project name in the Solution Explorer and choose “Add New Item”. Choose “Style Sheet” from the template list. It will create a file called StyleSheet.css. As you are developing you can keep creating styles in this file and then reference those styles in your .aspx files using the properties discussed above. You can use as many style sheet files as you want in your application and you can re-use existing style sheet files as well by adding them into your project.
After you have created the style sheet file to the project, you need to reference the file in your .aspx pages. To do that, add the following line inside the “head” tag:
<link href="StyleSheet.css" type="text/css" rel="stylesheet"/>
*Just remember to change the href property if your style sheet file name is different. Once you put that line inside the “head” tag, you will be able to reference any of the styles in that file. In my example style sheet file I defined two different styles: one for my labels called “formlabel” and one for my text boxes called “formtext”. My label style defines that the font should be bold and my text box style defines that the color of the text should be blue. In each style, you can define multiple characteristics. To apply the style to my ASP.NET Label control, I set the CssClass property to “formlabel”. If you apply a style to one of your ASP.NET controls and you don’t see the style reflected in the Design view, go back to your “head” tag in the .aspx file and make sure you added the “link” reference tag to the style sheet.
Click here to watch an example video where I create a new CSS style sheet file and apply some styles to an .aspx web form. Remember that earlier I mentioned that CSS styles can define both how something looks and how it is layed out. In order to control layout of a web page, you have to use a combination of HTML DIV tags and styles. A DIV tag defines a distinct layout area of controls on a web page. Think of the DIV tag as a way to group visual objects. You can apply a style to a DIV tag; for example if you want a group of controls to be 20 pixels from the left edge of the web page you can create a style that defines “margin-left: 20px” and then apply that style to the DIV tag. Another HTML tag that can be used to effectively control layout is the TABLE tag. TABLE tags give you a lot of control over placement of objects on a web page. I recommend that you keep it simple at first and add additional styles as you develop your application.
Click here to download the sample source code for this lesson.
This lesson is a continuation of the discussion regarding ASP.NET validation controls. Validation controls do not require any coding, thus saving time performing redudant tasks.
In this exercise we will cover how to ensure that the user typed information according to a rule expression. ASP.NET offers the RegularExpressionValidator control to ensure that fields follow a custom rule. The control comes with some common built-in rule expressions that you can choose from or you can define your own for the rule. A couple of the practical built-in expressions are the e-mail address expression and the URL expression. The RegularExpressionValidator is very easy to use; just drag it from the Toolbox window onto your web form. It is located in the Validation group in the Toolbox window, so you may have to scroll down the Toolbox to find it. You should drag the control to the location where you want the error message to show up, in case the validation fails; which in this case is if something entered in a field doesn’t match the expression rule.
Once you have dragged the validator onto the web form, you need to set some of the properties. The ErrorMessage property sets what error text will be displayed in case the validation fails. The error text will be shown in red. The ControlToValidate property sets which server control to examine at runtime to see if it follows the rule. Finally, the ValidationExpression property defines the regular expression rule that will be used. To choose from the list of built-in expressions click on the … ellipses button.
Click here to watch an example video. In the example I dragged a RegularExpressionValidator next to the e-mail TextBox control. Notice that I set the expression for the rule by choosing from the built-in list of expressions. When I run the program, if I don’t type a valid e-mail address, the validator will display the error message.
Click here to download the sample source code for this lesson.
This lesson is a continuation of the discussion regarding ASP.NET validation controls. Validation controls do not require any coding, thus saving time performing redudant tasks.
In this exercise we will cover how to ensure that the user typed information within an allowed range of values. ASP.NET offers the RangeValidator control to ensure that fields follow range rules. The RangeValidator is very easy to use; just drag it from the Toolbox window onto your web form. It is located in the Validation group in the Toolbox window, so you may have to scroll down the Toolbox to find it. You should drag the control to the location where you want the error message to show up, in case the validation fails; which in this case is if something entered in a field doesn’t match the range rule.
Once you have dragged the validator onto the web form, you need to set some of the properties. The ErrorMessage property sets what error text will be displayed in case the validation fails. The error text will be shown in red. The ControlToValidate property sets which server control to examine at runtime to see if it follows the rule. The MaximumValue property sets the largest possible value in your validation rule that is acceptable. The MinimumValue property sets the smallest possible value in your validation rule that is acceptable. Finally, the Type property lets the validator know what type of data will be validated: String, Integer, Date, etc.
Click here to watch an example video. In the example I dragged a RangeValidator next to the Age TextBox control. Notice that I set the minimum value to 18 for the age field. When I run the program, if I don’t type an age that is at least 18, the validator will display the error message.
Click here to download the sample source code for this lesson.
A very common requirement for many web applications is validating user input. ASP.NET helps improve developer productivity in the area of validation by offering a set of server controls called Validation Controls. Validation controls do not require any coding, thus saving time performing redudant tasks. These controls are a little different than the server controls that we examined in prior lessons. The validation controls are normally hidden on the web page, unless a validation error occurs, at which time they display an error message alerting the user of the validation failure. They are also similar to other server controls in the way you drag them from the Toolbox window onto your web form and then set the necessary properties to control how the validation controls will behave.
In this exercise we will cover how to ensure that the user typed information into required fields (i.e. fields that must be filled out) before the form is submitted to the server. This helps prevent null or empty data from getting into the database whenever the fields are mandatory. A very common example of a requirement to use required fields is a website registration form. Most websites require that you fill in some mandatory set of fields when registering, such as first name, last name, etc.
ASP.NET offers the RequiredFieldValidator control to ensure that mandatory fields are filled in. The RequiredFieldValidator is very easy to use; just drag it from the Toolbox window onto your web form. It is located in the Validation group in the Toolbox window, so you may have to scroll down the Toolbox to find it. You should drag the control to the location where you want the error message to show up, in case the validation fails; which in this case is if something isn’t entered into a field.
Once you have dragged the validator onto the web form, you need to set a couple of properties. The ErrorMessage property sets what error text will be displayed in case the validation fails. The error text will be shown in red. The other property to set is the ControlToValidate, which lets the validator control know which server control to examine at runtime to see if it’s empty or not.
Click here to watch an example video. In the example I dragged a RequiredFieldValidator next to the First Name TextBox control. Notice that I changed the error message to “First Name is required” and the ControlToValidate to txtFirstName which is the ID of the First Name TextBox. When I run the program, if I don’t type anything into the First Name field, notice that the error message is displayed.
With the Visual Web Developer and Visual Studio environments, it is very easy to exchange code and other artifacts between web applications. If you have an ASP.NET web form that you want to copy from one project to another, you can use the Solution Explorer to perform the operation. The Solution Explorer has an option called “Add Existing Item” that takes existing files on the hard drive and makes a copy of them into your web site project’s folder.
Watch an example video where I copy a web form called “Default5″ from a project called “Lab6_csharpuniversity” into another project called “Lab7_csharpuniversity”. *Note that when you want to copy a web form, you have to copy both the .aspx file and the .cs code behind file. This option makes a brand new copy of the files, there is no reference to the source files in your web site project.
|
|
|
|
