Want to know more?
TalkIT provides VB training in Bath, Bristol and around the U.K.

Lab 1 Data Access

Exercise 1

Retrieving data with a Data Reader
In this exercise, you will create a web application to display a list of employees. You will use connection, command, and data reader objects to retrieve data from the Northwind database.

1. Create a new empty web application project called DataReader:
   • Start Visual Studio 2012.
   • Select the File menu, then New, then Project.
   • Make sure (if you are using the Professional Version) that Visual Basic and then Web is selected at the left of the screen under Installed Templates.
   • Name the project and the solution: DataReader.
   • Browse to the place on your computer where you wish Visual Studio to create the directory for your solution (keeping the tick box selected).
   • Select ASP.NET Web Application.
2. You will need to create a connection to the Northwind Database:
   • Select Server Explorer (accessed via the View menu).
   • Right-click on ‘Data Connections’ and select ‘Add Connection’.
   • For ‘Data source’ select Microsoft SQL Server (SqlClient).
   • Type in a full stop for the Server name (it will then select the Server Name of the local machine, treating it as the default one).
   • For authentication choose ‘Windows Authentication’.
   • Under ‘Connect to a database’ choose the ‘Select or enter a database name’ option. The Northwind database will then appear in the drop-down list.
   • Finish by clicking ‘Test Connection’ at the bottom to confirm that Visual Studio has successfully connected to the Northwind Database (which is stored within SQL Server 2008).
3. Add a ListBox control to the default form.
4. Import the SQLClient namespace in to the class: Imports System.Data.SqlClient
5. In the Page Load event, add code to create and open a new SqlConnection. Put a dot in the connection string for Data Source.(the dot may have to be changed to your local server):
   Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) _Handles Me.Load
'Create and open a connection to Northwind database
Dim conn As SqlConnection = New SqlConnection("Data Source=.;Initial _ Catalog=Northwind;Integrated Security=TRUE")
conn.Open()

6. Create a string to get rows from the Employees table. Use a SQL SELECT statement.Dim queryString As String = "SELECT CustomerID, CompanyName FROM dbo.Customers"
7. Create a command object using the connection and the query string:Dim com As SqlCommand = New SqlCommand(queryString, conn)
8. Call the command’s ExecuteReader method and assign the result to a datareader object:Dim dr As SqlDataReader = com.ExecuteReader()
9. Iterate through the datareader to add the employee names to the list box. Use a while loop:While (dr.Read())
    'dr.GetString(1);
    ListBox1.Items.Add(dr("CustomerID").ToString() + " " + _
    dr("CompanyName").ToString())
End While

10. Run the application to test if employee data is displayed.
11. View code file.

Exercise 2

Updating the Database from a Data Set
In this exercise, you will create an application that shows and edits customer data. You will add an update button that writes any changes made to the data back to the database.

1. Create a new empty web application project called Dataset.
2. Add a GridView control to the default form.
3. Import the SQLClient namespace in to the class: Imports System.Data.SqlClient
4. Declare at class level these ADO.Net objects: Private conn As SqlConnection
Private da As SqlDataAdapter
Private ds As DataSet
5. In the Page Load event add code to create a new connection using this connection string: "Data Source=.;Initial Catalog=Northwind;Integrated Security=True"
6. Instantiate the SqlDataAdapter using the connection and this SQL statement: "SELECT CustomerID, CompanyName FROM dbo.Customers"
7. Instantiate the data set. Fill the data set using the data adapter with this statement: da.Fill(ds, "Customers")
8. Bind the GridView control to the data set.
9. Run the application to test if customer data is displayed.
10. Add a button control to the form and change its text property to Update.
11. Declare at class level a command builder object: Private cb As SqlCommandBuilder
12. In the button’s click event add code to instantiate the command builder object using data adapter.
13. Create an update command and assign it to the data adapter’s update command property: da.UpdateCommand = cb.GetUpdateCommand()
14. Update the database using the data adapter’s Update method:da.Update(ds.Tables("Customers"))
15. Run the application to test if customer data is updated.
16. View code file.

Exercise 3

Display data from the Customers table in a Gridview control
In this exercise, you will drag a gridview control on to a web form. You will then configure a datasource control to display rows from the customer table in the gridview.

1. Create a new empty web application project called GridView.
2. Add a GridView control to the default form.
3. Use GridView’s SmartTag to create a new datasource control
4. Configure the datasource to connect to the Northwind database.
5. Select the Customers table from the database.
6. Run the application to test if customer data is shown.

Exercise 4

Calling a Stored Procedure
In this exercise, you will create a web application to display a customer’s order history. You will use connection, command, parameter and data reader objects to retrieve data from the Northwind database. The command will call a stored procedure using the customer’s ID as a parameter.

1. Create a new empty web application project called CustomerOrders.
2. Add a Web Form called frmEmployees.
3. Add a ListBox, Label, TextBox and a button control to the form.
4. Import the SQLClient namespace in to the class:

Imports System.Data.SqlClient

5. In the button’s click event, add code to create and open a new connection, using this connection string:

"Data Source=.;Initial Catalog=Northwind;Integrated Security =True"

6. Create the command object and set its properties:

Dim com As SqlCommand = new SqlCommand()
com.Connection = conn
com.CommandText = "CustOrderHist"
com.CommandType = CommandType.StoredProcedure

7. Create the parameter object and add it to the command’s parameter collection:

Dim param As SqlParameter = new SqlParameter("@CustomerID",SqlDbType.NVarChar, 5)
param.Value = txtID.Text
com.Parameters.Add(param)

8. Call the command’s ExecuteReader method and assign the result to a data reader object:

Dim dr As SqlDataReader = com.ExecuteReader()

9. Iterate through the data reader to add the product names & totals to the list box. Use a while loop:

While (dr.Read())
    ListBox1.Items.Add(dr("ProductName") + " "
    + dr("Total").ToString())
End While


10. Run the application to test if customer data is displayed; type ‘arout’ into the Customer ID box and click ‘Show Order History’ in order to display the data for that specific customer.
11. View code file.

Lab 2. Using LINQ to SQL

Exercise 1
Display the Customers table from the Northwind database using a LINQ to SQL Query

1. Create a new empty web application project called LINQ to SQL.
2. Add an App_Code folder to the project.
3. Add a new LINQ to SQL Classes file to the App_Code folder, call this NWind.dbml.
4. Open Server Explorer, then drag the Customers table to the LINQ to SQL Classes design surface. This will create an object that you can query.
5. Add a new web form to the project and call this Customers.aspx. Make this the start page.
6. Add a GridView control to the Customers.aspx designer and call this gvCustomers.
7. Add code similar to this to create a NWindDataContext and display customers (you may have to adjust the connection string):
Imports System.Data.Linq
'Get customers' collection from DataContext
Dim context As NWindDataContext = new NWindDataContext _
("Data Source=.;Initial Catalog=Northwind; _
Integrated Security=True")
'Select customers from USA with LINQ query
Dim custs = From p In dc.Customers Where p.Country = "USA" _
Select p.CustomerID, p.ContactName, p.CompanyName, p.Country
'Bind filtered collection to gridview
this.gvCustomers.DataSource = custs
this.gvCustomers.DataBind()

Exercise 2
Display a customer’s details when a customer row is selected

1. Add a DetailsView control to the Customers.aspx designer and call this dvCustomers.
2. Set the Data Key Name property of the gridview to CustomerID.
3. Include a select column in the gridview, by selecting Edit Columns after clicking the gridviews smart tag.
4. Add code similar to this to display a customer’s details (you may have to adjust the connection string:Protected Sub gvCustomers_SelectedIndexChanged(ByVal sender As _
Object, ByVal e As EventArgs)
    'Get customer object from selected row
    Dim cust = dc.Customers.Where(Function(p) p.CustomerID = _
    gvCustomers.SelectedValue.ToString())
    'Bind filtered collection to detailview
    dvCustomers.DataSource = cust
    dvCustomers.DataBind()
End Sub

5. View code file.

Back to the beginning