Want to know more?
TalkIT provides ADO.Net 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 C# 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:using 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 void Page_Load(object sender, EventArgs e)
{
    SqlConnection conn = 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.string queryString = "SELECT FirstName, LastName FROM dbo.Employees";
7. Create a commandobject using the connection and the query string:Dim com As SqlCommand = New SqlCommand(queryString, conn)
8. Call the command’s ExecuteReadermethod and assign the result to a datareader object:SqlDataReader dr = com.ExecuteReader();
9. Iterate through the datareader to add the employee names to the list box. Use a while loop:while (dr.Read())
{
    listBox1.Items.Add
    (dr["FirstName"].ToString() + " " + dr["LastName"].ToString());
}

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 namespacein to the class:using System.Data.SqlClient;
4. Declare at class level these ADO.Net objects:private SqlConnection conn;
private SqlDataAdapter da;
private DataSet ds;
5. In the Page Load eventadd code to create a new connection using this connection string:"Data Source=.;Initial Catalog=Northwind;Integrated Security=True"
6. Instantiate the SqlDataAdapterusing 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 builderobject:private SqlCommandBuilder cb;
12. In the button’s click event add code to instantiate the command builder object using data adapter.
13. Create an update commandand 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 namespacein to the class:using 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 objectand set its properties:SqlCommand com = new SqlCommand();
com.Connection = conn;
com.CommandText = "CustOrderHist";
com.CommandType = CommandType.StoredProcedure;
7. Create the parameter objectand add it to the command’s parameter collection:SqlParameter param = 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 readerobject:SqlDataReader dr = 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());
}
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 NWindDataContextand display customers (you may have to adjust the connection string):
using System.Data.Linq;
//Get customers' collection from DataContext
NWindDataContext context = new NWindDataContext("Data Source=.;Initial Catalog=Northwind;Integrated Security=True");
//Select customer's from USA with LINQ query
var custs = from p in context.Customers
where p.Country == "USA"
select new { 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 void gvCustomers_SelectedIndexChanged(object sender, EventArgs e)
{
    //Get customer object from selected row
    var cust = context.Customers.Where(p =>
    p.CustomerID == gvCustomers.SelectedValue.ToString());
    //Bind filtered collection to detailview
    dvCustomers.DataSource = cust;
    dvCustomers.DataBind();
}
5. View code file.

Back to the beginning