Beginning ASP.NET 1.1 with Visual C# .NET 1002003 [Electronic resources]

Chapter 9

Exercise 1

Load a

DataSet using the

Shippers table from the

Northwind database and add the following data into it:

Company Name: FastShippers

Phone: (503) 555-9384

Solution

This isn't too hard and can easily be done in the

Page_Load() method of an ASP.NET page:

string connectionString; 
string strSQL; 
DataSet data = new DataSet(); 
OleDbConnection dbConnection; 
OleDbDataAdapter dataAdapter; 
OleDbCommandBuilder commandBuilder; 
// set the connection and query details 
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " + 
"Data Source=C:\\BegASPNET11\\data\\Northwind.mdb"; 
strSQL = "SELECT * FROM Shippers"; 
// open the connection and set the command 
dbConnection = new OledbConnection(connectionString); 
dataAdapter = new OledbDataAdapter(strSQL, dbConnection); 
// fill the dataset with the data 
dataAdapter.Fill(data, "Shippers"); 
// add a new row to the table 
DataTable table; 
DataRow newRow; 
table = data.Tables["Employees"]; 
newRow = table.NewRow(); 
newRow["CompanyName"] = "FastShippers"; 
newRow["Phone"] = "(503) 555-9384"; 
table.Rows.Add(newRow);  

Exercise 2

Using the

CommandBuilder object, create an

InsertCommand to insert this new data.

Solution

Create an

OleDbCommandBuilder object, use the

GetInsertCommand() method, and send the changes back to the database:

// create the other commands 
commandBuilder = new OleDbCommandBuilder(dataAdapter); 
dataAdapter.InsertCommand = commandBuilder.GetInsertCommand(); 
// update the database 
dataAdapter.Update(data, "Shippers"); 

Notice that we didn't specify other commands, as we are only adding a row of data. If we had changed a row or deleted one, then those changes would be reflected in the database as we haven't specified the

UpdateCommand or

DeleteCommand .

Exercise 3

Using direct SQL commands, change the phone number of

FastShippers to

(503) 555-0000 .

Solution

This is quite similar to the previous examples, only in those we added a row. In this case, we use the SQL

UPDATE command to set the

Phone column to the value of the supplied parameter.

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " + 
"Data Source=C:\BegASPNET11\data\Northwind.mdb"; 
OleDbConnection dbConnection = new OleDbConnection(connectionString); 
dbConnection.Open(); 
string commandString = "UPDATE Shippers SET Phone = @Phone " + 
"WHERE CompanyName = 'FastShippers'"; 
OleDbCommand dbCommand = new OleDbCommand(commandString, dbConnection); 
OleDbParameter firstNameParam =  
new OleDbParameter("@Phone", OleDbType.VarChar, 24); 
firstNameParam.value = "(503) 555-0000"; 
dbCommand.Parameters.Add(firstNameParam); 
dbCommand.ExecuteNonQuery(); 
dbConnection.Close(); 

Exercise 4

Use the Web Matrix Data templates to create an Editable

DataGrid . Have a look at the code and see how many familiar techniques you see.

Solution

This uses many advanced features of the

DataGrid , but the ADO.NET code is similar to what you've been working with. Take the

DataGrid_Update() method for example, which caters for new and updated data. It either builds a SQL

INSERT or

UPDATE statement, uses

Parameters to set the values, and then uses the

ExecuteNonQuery() method of the

DataAdapter to send changes back to the database.

The

DataGrid_Delete() method does a similar thing, constructing a SQL

DELETE statement.

The

AddNew_Click() method uses an interesting technique – it adds a new row to a table but doesn't update the database. Instead, it sets a flag indicating that a new row is being added. This means that the user can modify the data in the new row, or even cancel the addition before sending changes to the database.