Microsoft® Visual Studio® .NET includes a host
of tools and data-access classes that make building database applications with
Microsoft SQL Server™ 2000 easier than ever. All of the tools developers need
are now included in the Visual Studio integrated development environment (IDE).
This white paper introduces the new tools and data classes, walks you through
the process of building a database application, and highlights the support for
new SQL Server 2000 features such as XML support, indexed views, and cascading
referential integrity.
To build the sample application in white paper,
you must have the following software installed on your computer:
·
Microsoft Windows® 2000 Server
·
Visual Studio .NET (Visual Studio .NET
Enterprise Edition is required for some samples, as noted)
·
SQL Server 2000 Enterprise Edition
You must also have a SQL Server logon with
database owner (dbo) permissions.
The Visual Studio .Net IDE contains a suite of
Visual Database Tools designed to facilitate database application development
using SQL Server 2000. The Visual Database Tools consist of the following
items:
·
Server Explorer
·
Database Designer
·
Table Designer
·
SQL Editor
·
Query and View Designer
The following sections describe these tools in
detail.
Server Explorer is the portal for the Visual
Database Tools and most of the other tools and designers. Using Server
Explorer, you can connect to SQL Server databases and see a hierarchical
tree-view with nodes for the SQL Server computers, database diagrams, tables,
views, stored procedures, and functions. Many of the tasks developers have
traditionally used SQL Server Enterprise Manager for can now be accomplished in
Server Explorer, eliminating the need for developers to use Enterprise Manager.
To open Server Explorer, start Visual Studio
.NET, and from the View menu, click Server Explorer.
When you first open Server Explorer, you see two
nodes, as shown in Figure 1. The first node, Data Connections,
enumerates all database connections previously created on the computer. The
second node, Servers, lists all of the servers on the current network.
Under the Servers node is a node named SQL Servers
with which you can access the Visual Database Tools without creating a data
connection. After you create a data connection, you can also access the Visual
Database Tools through the Data Connections node.
Figure 1: Server Explorer
With Visual Studio .NET Enterprise Edition, you
can create a new SQL Server database within the Visual Studio .NET IDE. In the
following procedure, we will create a sample database named ServiceCall.
To create a new database
1.
Open Server Explorer: from the View menu,
click Server Explorer.
2.
Right-click Data Connections, and then click Create
New SQL Server Database. The Create Database dialog
box opens.
Figure 2: Create New SQL Server Database
3. In the Create Database dialog box, do the following:
·
In Server, type the name
of your server
·
In Login Name and Password, type authentication information for the new
database.
·
In New Database Name,
type ServiceCall, and then click OK.
Figure 3: Create Database dialog box
The new database is created with the default
settings on the server along with a data connection to the new database. Click
the plus sign (+) next to the database to expand the Database Diagrams, Tables,
Views, Stored Procedures, and Functions nodes.
Table Designer
You can use Table Designer to create and
maintain database tables from Server Explorer.
To start Table Designer, right-click the Tables
node, which is located under the database connection that was created when we
created the ServiceCall database, and then click New Table.
Table Designer consists of two sections. The top
section contains a grid in which you define the columns in the table. The Data Type drop-down list includes all valid data types including
those specific to SQL Server 2000 (Bigint and Sql_variant). The bottom section contains a tab in which you
specify additional column characteristics, much like you do in the table
designer in Enterprise Manager.
In the following procedure, we will create the Customers and ServiceCall tables for
the ServiceCall database.
To create the Customers and ServiceCall
tables
1. Define columns for the Customers table as shown in Figure 4.
Figure 4: Table Designer Property sheet
for the Customers table
2. Right-click the CustomerID column, and then click Set
Primary Key (alternatively, you can use the Set Primary
Key control on the toolbar).
3. Click Save, and in the Choose Name
dialog box, type Customers.
4.
Repeat this process for the ServiceCall table,
setting ServiceCallID as the primary key and the Identity
property to Yes, as shown in Figure 5.
Figure 5: Table Designer Property Sheet
for the ServiceCall table
In addition to defining columns and keys in a
table, you can also use Table Designer to create and modify relationships,
constraints, and indexes for tables.
To create a relationship between the
ServiceCall table and the Customers table
1.
Open Table Designer for the ServiceCall table,
right-click anywhere in the column grid, and then select Relationships
(alternatively, you can use the Manage Indexes and Keys control on the
toolbar). The Property Pages dialog box appears.
2.
Click the Relationships tab, and then
click New. The relationship name defaults to FK_ServiceCall_Customers.
3.
Define the primary key table as Customers
and the primary key column as CustomerID.
4.
Define the foreign key table as ServiceCall and the foreign key column as CustomerID.
Figure 6: Property Pages dialog box
5.
Click Close, and then click Save.
SQL Server will now ensure that the ServiceCall
database cannot include a row in the ServiceCall table with a CustomerID
that does not appear in the Customers table.
In Table Designer, one feature specific to SQL
Server 2000 is the capability to define cascading referential integrity.
To declare referential integrity between
tables
1.
In Server Explorer, right-click the Customers
table, and then select Design Table. Table Designer opens.
2.
Click Relationships. The Property
Pages dialog box appears with the Relationships tab selected. At the
bottom of the dialog box, you see the following two options:
·
Cascade Update Related Fields Causes the database to propagate new key values to corresponding
foreign keys when a primary key is updated.
·
Cascade Delete Related Records Causes the database to delete rows from the foreign key table
whenever rows in the primary key table are deleted
3.
Select Cascade Delete Related Records,
click Close, and then click Save. Setting this option causes SQL
Server to delete all rows in the ServiceCall table that match the CustomerID
of each row deleted from the Customers table.
Note You
may receive a Save Warning dialog box with the message, “The following
tables will be saved to your database. Do you want to continue?” The Customers
and ServiceCall tables will be listed. Click Yes. The save
warning appears because the Warn about Tables Affected option is turned
on; you can turn off this option from the Save Warning dialog box.
Figure 7: Relationships tab with Cascade
Delete Related Records selected
Important To populate the tables with sample data, run the database load
scripts in Appendix 1 in SQL Query Analyzer.
Database Designer is a graphical interface you
can use to add or modify database tables, relationships, indexes, keys,
constraints, and so on.
Note Database
Designer requires Visual Studio .NET Enterprise Edition.
You can create any number of diagrams for a
given database and a table can appear in any number of diagrams. These diagrams
can also serve to document the design of your database.
There are some things to take into consideration
when working with SQL Server database diagrams. For more information, see the
topic, “Database Designer Considerations for SQL Server Databases,” in the
Visual Studio .NET documentation. This topic covers changing data types,
case sensitivity, and more.
To create a database diagram
1.
Open Database Designer: in Server Explorer,
under the database node, right-click Database Diagrams, and then
click New Diagram. Database Designer opens, and you see the Add
Table dialog box, which is used to add existing tables to your diagram.
2.
Add both the Customers and ServiceCalls
tables to the diagram, and then click Close.
3.
Close Database Designer and save the diagram if
you want to.
Once the diagram is created, you can right-click
the background of the diagram to add new tables to the diagram or to the
database. You can also right-click a table to maintain columns, keys, indexes,
constraints, and relationships. Database Designer is very similar to the
diagram tool in SQL Server Enterprise Manager (see Figure 8).
Figure 8: Database Designer diagram pane
To demonstrate the remaining Visual Database
Tools and the data access classes, we will build a sample application using the
ServiceCall database we just created.
To begin the sample application, we need to
create a new Visual Studio .NET project.
To create a new project
1.
From the File menu, select New,
and then click Project.
2.
Under Project Types,
click Visual Basic Projects and under Templates,
click Windows Application.
3.
Name the project ServiceCall, and then
click OK. This creates a solution and a project, both of which are named
‘ServiceCall’. Each Visual Studio .NET solution may contain one or more
projects.
4.
Open the Server Explorer window: from the View
menu, click Server Explorer. If you expand the Data Connections
node, you see the connection to the ServiceCall database created
earlier.
The first step in building the sample
application is to create a user-defined function to compute the total cost of a
service call ((LaborRate * Hours) + PartsCost). User-defined functions are
either scalar-valued or table-valued (Visual Studio .NET Enterprise Edition is
required to create user-defined functions). Scalar-valued functions return a
single value of a scalar data type and can appear in a query anywhere a column
name would appear. Table-valued functions return a table and can be used in the
FROM clause of a Transact-SQL query. In Visual Studio .NET, you can create
three types of user-defined functions:
·
Scalar-valued function. A function that returns a single scalar value.
·
In-line function. A table-valued function that returns a table value defined through a
single SELECT statement
·
Table-valued function. A table-valued function that returns a table defined by multiple
Transact-SQL statements. Table-valued functions are also called multi-statement
functions.
To create the function
1.
In Server Explorer, under the connection you
just created, right-click the Functions node, and then select New
Scalar-valued function. This opens SQL Editor, which is used to create and
maintain functions and stored procedures. A template is provided to help you
create functions.
2. Copy and paste the following code into SQL Editor to create the
function:
Create FUNCTION dbo.ComputeServiceCallTotal
(@LaborRate money = 0,
@Hours float = 0,
@PartsCost money = 0)
RETURNS money
AS
BEGIN
RETURN ((@LaborRate * @Hours) +
@PartsCost)
END
3.
Save the function by clicking Save
(alternatively, from the File menu, click Save <function name>.
The function is saved as an object in the database.
SQL Editor provides color-coding of Transact-SQL
keywords to minimize syntax errors and make the code more readable. It also
provides templates for creating new functions and stored procedures. SQL Editor
also places a thin-lined box around each Transact SQL statement to break the
Transact-SQL code into command sections and identify blocks of Transact-SQL
statements that can be built and modified using Query and View Designer.
To demonstrate how Query and View Designer
works, we will create a stored procedure within Server Explorer that uses the
function we just created.
To create the stored procedure
1.
Under the ServiceCall connection, right-click
the Stored Procedures node, and then click New Stored Procedure. This opens the
SQL Editor window with a template for a new stored procedure.
Figure 9: Create stored procedure
template
2.
In the SQL Editor window, type following code
for the BrowseInvoiceTotals stored procedure:
Create PROCEDURE dbo.BrowseInvoiceTotals
as
Declare @TotalInvoice as money
Set @TotalInvoice =
dbo.ComputeServiceCallTotal(50, 5, 200)
Select c.CustomerID, c.CustomerName,
sc.ServiceDate,
dbo.ComputeServiceCallTotal(sc.LaborRate,
sc.Hours, sc.PartsCost) as TotalInvoice
From Customers c
Inner Join ServiceCall sc on c.CustomerID
= sc.CustomerID
Return
Note The code to declare and compute the value of @TotalInvoice
with hard-coded values is for demonstration purposes only.
3.
After you complete the stored procedure, save it
in the database; from the File menu, click Save.
Notice the thin-lined box around the SELECT
statement. Right-click inside the Transact-SQL Query box, and then click Design
SQL Block. This launches Query and View Designer, which you can use to
graphically build or modify queries. (see Figure 10)
Figure 10: Query and View
Designer
Query and View Designer consists of four
sections. The top section contains the diagram pane, which shows the tables you
are querying. You can add or remove tables, select or deselect columns, add or
remove table joins, and specify order by columns from this section. The second
section contains the grid pane, which shows the columns in the query and allows
you to select sort type and sort order, filter criteria, and group by columns.
The third section contains the SQL pane, which shows the SQL syntax for the
query. If you modify the query in this pane, the other panes are updated to
reflect those changes. If you make changes to the query that cannot be
expressed in the other panes, they become unavailable. The fourth section
contains the results pane, which shows the results of the query after it has
been executed.
Query and View Designer works the same way for
views as it does for queries, except that features specific to views are
available. One such feature specific to SQL Server 2000 is the capability to
create indexed views.
To create an indexed view
1.
Open Server Explorer, right-click Views,
and then click New View. The Add Table dialog box appears.
2.
Add the Customers table to the view and
close the Add Table dialog box.
3.
In the diagram pane, select the CustomerID
and CustomerName check boxes.
4.
To work with indexes, right-click the background
of the diagram pane, and then click Manage Indexes. This opens
the Indexes dialog box, in which you specify the associated indexes. You
must have proper permissions on the tables in the view to create an indexed
view.
5.
Close the Indexes dialog box, and then
close Query and View Designer.
You can also use Query Designer to browse
through tables and update their content.
·
In Server Explorer, expand the ServiceCall Data
Connection node, expand the Tables node, right-click a table, and then click Retrieve
Data from Table.
The data grid that appears with the contents of
the table is the Query Designer Data Grid. Notice that a toolbar opens with the
data grid that you can use to access the diagram pane, SQL pane, grid pane, and
results pane of Query Designer. Again, these features are very similar to those
of Enterprise Manager.
Another powerful feature in Server Explorer is
Stored Procedure Debugging. To debug the BrowseInvoiceTotals stored
procedure, open Server Explorer and navigate to the Stored Procedure node
within the ServiceCall connection. Right-click BrowseInvoiceTotals, and
then click Step Into Stored Procedure. Alternatively, you can
double-click the stored procedure to open the editor, and on the Database
menu, click Step Into Stored Procedure.
Step through the stored procedure by pressing
the F11 key and notice that when you reach the statement, Set
@TotalInvoice = dbo.ComputeServiceCallTotal(50, 5, 200), the debugger
leaves the stored procedure and enters the function. When the function is
complete, the debugger returns to the stored procedure. You can view the
contents of the variable @TotalInvoice by moving your mouse over it or
by right-clicking it and setting a watch variable. You can also view the
contents of variables in the Locals window: from the Debug menu,
highlight Windows, and then click Locals. In Figure 11, the Locals
window displays the value of @TotalInvoice as 450.0000 after the Set
statement executes. Again, the Transact-SQL statement debugging tool is
accessible from within the Visual Studio .NET IDE.
Figure 11: SQL Debugger window
SQL Debugger has some limitations, including the
following:
·
Table variables cannot be displayed in the Locals
window.
·
Sql_variant, text, ntext, image, and cursor
variables are displayed in the Locals window, but are display only.
·
Stored procedures larger than 64 Kilobytes (KB)
are not supported.
·
Nesting of more than 32 stored procedure calls
is not supported.
·
Passing more than 1,023 arguments to a stored
procedure is not supported.
For additional limitations, see “SQL Debugging
Limitations” at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vccore98/HTML/_core_sql_debugging_limitations.asp.
Note SQL
Debugger uses Distributed Component Object Model (DCOM) for communication
between the client computer and SQL Server. For the debugger to work properly,
you must set the correct DCOM permissions using Dcomcnfg.exe. For more
information, see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servtools_5cfm.asp.
In addition, for debugging to work, SQL Server cannot run as System Account.
The logon associated with the MSSQLSERVER service must be a valid user account.
At this point, we have the ServiceCall database
and connection, a user-defined function, and a stored procedure. Next, we will
use the stored procedure to create a dataset that we will use to populate a
data grid.
To create a dataset
1.
Open the Toolbox: from the View menu,
click Toolbox.
2.
In the Toolbox, click the Data tab, and
then drag a SqlDataAdapter control onto Form1. This places an
icon for SqlDataAdapter1 at the bottom of the design window for Form1
and launches the Data Adapter Configuration Wizard. The Data Adapter
Configuration Wizard gathers information about the data that the adapter will
manage.
3.
When the wizard prompts you for the data
connection to use, specify the ServiceCall connection.
Figure 12: Choose Your Data Connection
property sheet
4.
Next, choose a query type from the following
choices:
·
Use SQL statements. Supply a Transact-SQL
statement or use Query Builder to graphically design a query.
·
Create new stored procedures. The wizard automatically creates stored
procedures for Select, Insert, Update and Delete that are based on Transact-SQL statements supplied in the wizard.
·
Use existing stored procedures. Specify up to four existing stored procedures,
one for the each of the following four actions. You do not have to specify all
four stored procedures. For each stored procedure you specify, the SqlDataAdapter
is configured to associate the stored procedure with the appropriate database
action:
·
Select Reads
existing data from the database
·
Insert Inserts
new rows into the database table or tables
·
Update Updates
existing rows
·
Delete Deletes
existing rows
Regardless of which query type you
choose, you have the option of using the graphical query tool to build the
query and choosing from the following advanced Transact-SQL-generation options:
·
Use the Select statement Generate Insert, Update and Delete statements based on the Select
statement.
·
Use optimistic concurrency Modifies Update and Delete statements to determine whether the
database has changed since the record was loaded into the dataset.
·
Refresh the dataset Adds a Select statement to retrieve identity column values and
refresh values calculated by the database.
Select Use existing stored
procedures as the query type.
Figure 13: Choose a Query Type property
sheet
5. On the Bind Commands to Existing Stored Procedures
page, in the Select drop-down list box, select BrowseInvoiceTotals.
Figure 14: Bind Commands to Existing
Stored Procedures property sheet
6.
Ignore the Insert, Update, and Delete drop-down lists.
Click Next, and then click Finish.
Figure 15: View Wizard Results
Next, we will configure the dataset that the SqlDataAdapter
generates.
To configure the dataset
1.
On Form1, right-click the SqlDataAdapter1
icon, and then click General Dataset.
Figure 16: Generate Dataset for the
SqlDataAdapter
2.
In the Generate Dataset dialog box,
select New dataset, and then type the name, BrowseInvoiceTotalsDS.
3. In the window at the bottom of the page, BrowseInvoiceTotals(SqlDataAdapter1)
should be selected. Click OK.
Figure 17:
Generate Dataset property sheet
Next, we will create the form elements for
Form1.
To create the form elements
1.
Add a DataGrid control to Form1.
2.
Open the Toolbox, and then click Windows Forms.
3.
Drag a DataGrid control onto Form1,
right-click the DataGrid control, and then click Properties.
Change the CaptionText property of the DataGrid control to Invoice
Report.
4. Configure the properties of the DataGrid control to bind it to
the data: Under Properties, in the Data
section, set the DataSource property to BrowseInvoiceTotalsDS1.BrowseInvoiceTotals.
Figure 18: DataGrid Properties page
5.
Resize the DataGrid control so that all
columns are visible.
6.
To fill the dataset, add a button to Form1,
and change its Text and Name properties to RunReport.
7.
Double-click the button to open the code window.
Add the following lines of code to the RunReport_Click subroutine and
save the application:
BrowseInvoiceTotalsDS1.Clear
‘fill the DS using the SqlDataAdapter
SqlDataAdapter1.Fill(BrowseInvoiceTotalsDS1)
We are now ready to test our application. Run
the application and click the RunReport button. The SqlDataAdapter
connects to the database, runs our stored procedure and function, loads the
dataset and passes the data to the data grid. The output should look like the
output shown in Figure 19.
Figure 19: Sample application output
Next, we will build a data maintenance form.
To build the data maintenance form
1.
Add a new form (Form2) to the ServiceCall
project: in Solution Explorer, right-click the project name, click Add,
and then click Add Windows Form.
2.
Under Templates, click
the Windows Form template, and then click Open.
3.
Add a SqlDataAdapter control to the form.
The Data Adapter Configuration Wizard starts.
4.
Select the existing ServiceCall connection for
the SqlDataAdapter. For this SqlDataAdapter control, we are going
to let Visual Studio .NET create the required stored procedures for us.
5.
Click Create new stored
procedures, and then click Next. The Generate
the stored procedures page opens.
6.
Click Query Builder, add the Customers
table to the query, and then close the Add Table dialog box.
7.
In the diagram pane, select all of the columns,
click OK, and then click Next.
8.
On the Create the Stored Procedures
properties page, provide a name for each of the four new stored procedures (for
this example, use CustomersSelect, CustomersDelete, CustomersInsert, and
CustomersUpdate), and then click Next.
9.
When the final page of the wizard displays a
message stating that all of the stored procedures were generated successfully,
click Finish.
10.
Open the property page for the SqlDataAdapter
control and look at the following properties; they have been set to the value
of the stored procedure names we provided:
·
The value of SelectCommand.CommandText is
CustomersSelect.
·
The value of DeleteCommand.CommandText is
CustomersDelete.
·
The value of InsertCommand.CommandText is
CustomersInsert.
·
The value of UpdateCommand.CommandText is
CustomersUpdate.
Visual Studio .NET created four stored
procedures based on the Select statement that we provided, compiled them into
the database, and configured the SqlDataAdapter control to use them.
The next step is to generate the dataset for the
SqlDataAdapter we created for Form2.
To generate the dataset
1.
Right click the SqlDataAdapter, and then
click Generate DataSet.
2.
Name the dataset CustomersDS.
3.
Add a DataGrid control to the form, set
the DataSource property to CustomersDS1.Customers, and set the CaptionText
property to Maintain Customers.
4.
Resize the DataGrid control so that all
columns are visible.
5.
Add a button under the data grid and change its Text
and Name properties to Load.
6.
Add another button next to the first one and
change its Text and Name properties to Update.
7.
Double-click the Load
button to open the code window, and add the following code to the click event
of the Load button:
CustomersDS1.Clear()
SqlDataAdapter1.Fill(CustomersDS1)
8.
In the click event of the Update button,
add the following code:
SqlDataAdapter1.Update(CustomersDS1)
Before you can run the application, you must
change the Startup object for the project to Form2.
To change the Startup object
1.
In Solution Explorer, right-click the ServiceCall
project, and then click Properties.
2.
Under Startup object,
change the Startup object to Form2, and then
click OK.
Run the application to open the maintenance form
(Form2). Click the Load button to load the data grid with the
data currently in the table. The screen should look like the one shown in Figure
20.
Figure 20: Sample application output
To update the ServiceCall database
1.
To delete a row, click the row, and then press
the Delete key.
2.
To add a new row, click in any cell in the empty
row at the end of the grid, and then supply the appropriate values.
3.
After you finish making changes, click Update.
The system checks the RowStatus of each row in
the grid, calls the appropriate stored procedure, and passes in the correct
parameters to process the requested action.
This example demonstrates the power of the
Visual Studio .NET IDE and the framework. With only a few lines of code, we
built a fully functional table-maintenance application.
The Microsoft .NET Framework provides a new
version of ADO called ADO.NET.ADO.NET is enhanced to better support the
development of distributed applications and relies heavily on XML as its
internal data structure.
In previous versions of ADO, the Recordset object was the main object
developers used to work with data from a data store. In ADO.NET the Recordset object is replaced by the DataSet
object. A DataSet is an in-memory copy of the data you
are working with. A DataSet can contain one or more DataTables. You can think of a DataSet
as a virtual relational database. DataSets are
completely independent of the original data source and are not designed to
communicate directly with the data source. For communication with the data
source, ADO.NET uses managed providers.
ADO.NET offers two managed providers; one for
use with SQL Server databases and one for use with any OLE DB-compliant
database. All of the examples we have created so far have used the SQL Server
managed provider. If you look at the options under Data in the Toolbox,
you will notice three pairs of controls that have identical names, except for
their prefixes. The three pairs are listed in the following table.
|
SQL Server
|
OLE DB
|
|
SqlDataAdapter
|
OLEDBDataAdapter
|
|
SqlDataConnection
|
OLEDBDataConnection
|
|
SqlCommand
|
OLEDBCommand
|
The controls prefixed with OLEDB
represent the OLE DB managed provider and can be used for accessing any OLE
DB-compliant database. They are similar to the current ADO objects, but have been rewritten as managed code to work within the
.NET Framework.
The controls prefixed by SQL represent
the SQL Server managed provider and only work with SQL Server. They offer
several advantages over their OLE DB counterparts. First, these objects use the
native Tabular Data Stream (TDS) interface for maximum performance. The
additional interface layers required by the OLE DB objects have been removed.
This results in faster database access. Second, the SQL objects created from
these controls have additional methods that take advantage of features specific
to SQL Server. This provides greater flexibility in design and programming when
using SQL Server.
We used ADO.NET in the sample application we
created earlier, but Visual Studio .NET handled most of the coding for us. All
we had to do was configure the dataset and data adapter through the user
interface of the IDE. Let’s take a look at the Microsoft Visual Basic® .NET
coding required to use ADO.NET to select, insert, update, and delete data from
a table. First, we look at the code for the Select statement:
Dim strCmdText As
String
Dim rowsProcessed As
Integer
Dim CustomerDS As New
DataSet()
strCmdText = "Select
* from Customers"
Dim SqlDataAdapter2 As
New SqlDataAdapter(strCmdText, _
"Data
Source=localhost;Initial Catalog=ServiceCall;User
ID=sa;passsword=yourpassword")
SqlDataAdapter2.Fill(CustomerDS,
"Customers")
In this code sample, we first define the variables
and objects we will use, including CustomersDS, which
is an ADO.NET DataSet object. We then define the query that
will fill the dataset in the variable strCmdText. Next,
we define an ADO.NET SqlDataAdapter object (SqlDataAdapter2),
providing the query and the database connection string as parameters. Notice
that we are using the SQL Server ADO.NET managed provider. Finally, we fill
CustomerDS and create a table within the DataSet named Customers
by calling the Fill method of the SqlDataAdapter object.
Because SqlDataAdapter2
was defined with the query and connection string, the Fill method
establishes a connection to the SQL Server database, executes the query, and
returns the results to the DataSet.
Next, we look at the code that allows us to insert
rows into a table:
Dim strCmdText As String
Dim rowsProcessed As Integer
Dim sqlConn As New SqlConnection _
("Data Source=localhost;Initial
Catalog=ServiceCall;User ID=sa;password=yourpassword")
strCmdText = "INSERT INTO Customers VALUES
('Cust4', 'Customer Four', " _
& "'Cust4 Address', 'City', 'ST',
'11111','(888)-123-4567')"
Dim sqlCmd As New SqlCommand(strCmdText,
sqlConn)
sqlCmd.Connection.Open()
rowsProcessed = sqlCmd.ExecuteNonQuery()
We start by defining the objects and variables
that will be used. We then create an ADO.NET SqlConnection object (sqlConn) and pass in the connection string as a parameter. We
then define the query that inserts rows into the Customers table in the
variable strCmdText.
Next, we define an ADO.NET SqlCommand object
and pass the query and the SqlConnection object as parameters. Then, we
open the connection on the SqlCommand object and call the ExecuteNonQuery
method. The ExecuteNonQuery method returns the number of rows affected
by the database operation.
Except for the query passed to the SqlCommand
object, the code to update and delete the table is the same as the code for the
insert operation. The code is included here, but because it is the same as the
code for the insert operation, this paper does not include a discussion of the
code.
The following code updates a row in the table:
Dim strCmdText As
String
Dim rowsProcessed As
Integer
Dim sqlConn As New
SqlConnection _
("Data
Source=localhost;Initial Catalog=ServiceCall;User
ID=sa;password=yourpassword")
strCmdText =
"Update Customers Set CustomerName = 'Updated Customer' " _
& "Where
CustomerID = 'Cust4'"
Dim sqlCmd As New
SqlCommand(strCmdText, sqlConn)
sqlCmd.Connection.Open()
rowsProcessed =
sqlCmd.ExecuteNonQuery()
The following code deletes a row in the table.
Dim strCmdText As
String
Dim rowsProcessed As
Integer
Dim sqlConn As New
SqlConnection _
("Data
Source=localhost;Initial Catalog=ServiceCall;User ID=sa")
strCmdText =
"Delete Customers Where CustomerID = 'Cust4'"
Dim sqlCmd As New SqlCommand(strCmdText,
sqlConn)
sqlCmd.Connection.Open()
rowsProcessed =
sqlCmd.ExecuteNonQuery()
These examples demonstrate how to use the ExecuteNonQuery
method of the SqlCommand object. The SqlCommand object has
several other methods, including a method specific to SQL Server for processing
XML data. We look at how this works in our next example.
XML and Visual
Studio .NET
SQL Server 2000 has built-in support for XML.
One feature is the For XML clause in the Select statement, which returns a
result set as XML. The SqlCommand object has an ExecuteXMLReader
method that takes advantage of this functionality within SQL Server.
The next example demonstrates another use of the
SqlCommand object. We will query the Customers table, return the
results as XML, and display the XML in a text box on a form.
To query the Customers table and return
the results as XML
1.
Add a new form (Form3)
to your project and add a text box to the form. Change the MultiLine
property of the text box to True, the Width
property to 504, and the Height property to 152, as shown in Figure 21.
Figure 21: TextBox Property page
2.
Add a button to the form and change its Name
and Text properties to GetXML.
3.
Double-click the button to open the code window
for the click event, and add the following code. Remember to change the data
source, user ID, and password to match your server.
Dim XMLTxtReader As
Xml.XmlTextReader
Dim StringBuilder As New
System.Text.StringBuilder()
Dim XMLOutput As String
Dim SQLCommand As New System.Data.SqlClient.SqlCommand()
Dim SQLServiceCallConn As New _
System.Data.SqlClient.SqlConnection _
("Data
Source=YourServer;Initial Catalog=ServiceCall;User
ID=xx;password=yourpassword")
SQLServiceCallConn.Open()
SQLCommand.Connection =
SQLServiceCallConn
SQLCommand.CommandType =
CommandType.Text
SQLCommand.CommandText =
"Select CustomerID, CustomerName," _
& "Phone from Customers
for XML auto"
'Execute the SQL and return XML
XMLTxtReader =
SQLCommand.ExecuteXmlReader()
StringBuilder.Append(XMLTxtReader.GetRemainder.ReadToEnd)
XMLOutput =
StringBuilder.ToString()
Me.TextBox1.Text = XMLOutput
Your code window should look like the one
shown in Figure 22 (your data source, user ID, and password will be different).
Figure 22: Form class for Form3 with code for
GetXML_Click subroutine
In this code example, we start by defining the
objects and variables that will be used. The XMLTextReader object holds
the data returned from the database. The StringBuilder object is used to
build an XML string from the data in the XMLTextReader object.
We start by creating a SqlConnection
object (SQLServiceCallConn) and passing in the
connection parameters for the database. We then set the CommandType and CommandText
properties of the SqlCommand object (SqlCommand).
Note that we use the For XML
Auto statement in our query. This indicates to SQL Server to
return the results of our query as an XML string.
We then use the ExecuteXmlReader method
of the SqlCommand object to execute our Transact-SQL statement. This
method is designed to take advantage of SQL Server 2000’s native XML support
and returns an XMLTextReader object. The last step is to use the StringBuilder
object to extract a string containing the XML returned from SQL Server from the
XMLTextReader object.
You can shorten the fully qualified names for
the StringBuilder, SqlCommand, and SqlConnection objects
by importing the System.Text and System.Data.SqlClient namespaces, as shown in Figure
23. To do this, insert the following code above the form class definition:
Imports System.Text
Imports System.Data.SqlClient
Figure 23: Source code using shorthand notation
to define the StringBuilder and SqlConnection objects
To test the application, change the Startup
object to Form3 and run the application (press F5). Click GetXML.
The contents of the Customers table is returned as XML and displayed in
the text box, as shown in Figure 24.
Figure 24: Sample application output
In the previous example we discussed SQL
Server’s built-in support for XML. This is sometimes referred to as SQLXML.
SQLXML 3.0 includes the following managed classes, which facilitate working
with SQLXML from within Visual Studio .NET.
|
Class
|
Description
|
|
SqlXmlCommand object
|
Returns XML data from SQL Server to a new or
existing Stream object or returns data to an XMLReader object. This object also has a method used to
create parameters.
|
|
SqlXmlParameter object
|
Used to populate parameters created with the SqlXMLCommand object.
|
|
SqlXmlAdapter object
|
Fills a dataset from XML data from SQL Server.
This object also applies updates to SQL Server data from the dataset.
|
These managed classes allow you to manipulate
XML data from SQL Server in several ways, including:
·
Executing SQL queries to return XML data
·
Applying XSL transformations on XML data
·
Executing XPath queries on XML data
For our final sample application, we will expand
the previous sample to use the SQLXML managed classes to return XML data from a
SQL query.
Important
SQLXML 3.0 must be installed for this sample application to work. It is
supplied on the SQL Server 2000 Web Services Toolkit or You can download SQLXML
3.0 from http://msdn.microsoft.com/sqlxml.
Before we can use the SQLXML managed classes, we
must add the SQLXML component reference to our project.
To add the SQLXML component reference
1.
Open Solution Explorer: from the View
menu, click Solution Explorer.
2.
Under the ServiceCall project, right-click References,
and then click Add Reference.
3.
In the Add Reference dialog box, click
the .NET tab.
4.
Find the component named Microsoft.Data.SqlXml,
and then highlight it.
5.
Click Select, and then click OK.
6.
Save the ServiceCall project.
7.
Open Form3.vb and add a new button to the
form beside the GetXML button, and then change the Name and Text
properties of the new button to SQLXML.
8.
Double-click the SQLXML button to open
the code window for the click event and add the following code. Remember to
change the data source, user ID, and password to match your server.
Dim strm As System.IO.Stream
Dim sqlXmlCmd As New
Microsoft.Data.SqlXml.SqlXmlCommand _
("Provider=SQLOLEDB;Data
Source=localhost;” _
& “Initial Catalog=ServiceCall;User
ID=sa;password=yourpassword")
sqlXmlCmd.CommandType =
Microsoft.Data.SqlXml.SqlXmlCommandType.Sql
sqlXmlCmd.CommandText = ("Select
CustomerID, CustomerName," _
& "Phone from Customers for
XML auto")
strm = sqlXmlCmd.ExecuteStream()
Dim streamRdr As New System.IO.StreamReader(strm)
Me.TextBox1.Text =
streamRdr.ReadToEnd().ToString
In this code example, we start by defining the
objects and variables that will be used. The Stream object (strm) will hold the XML data returned from the query. Then,
we define the SqlXmlCommand object (sqlXmlCmd),
passing in the connection string as a parameter.
Next, we set the parameters on the SqlXmlCommand
object. CommandType is set to Sql, which is the default, but is
shown here for clarity. CommandText is set to the value of our query,
which is the same query we used in the first part of this example. Then, we
call the ExecuteStream method of the SqlXmlCommand object, which
executes the SQL statement and puts the XML data into the Stream object.
Next, we define the StreamReader object (streamRdr), passing in the Stream object (strm) as a parameter. This populates the StreamReader
object with the XML data. In the final step, we load the text box with the XML
data from the StreamReader object.
The Startup object
should still be set to Form3, so save the project, run
the application (press F5), and click the SQLXML
button. The output should be exactly the same as when you click the GetXML
button, as shown in Figure 24.
Visual Studio .NET allows you to create database
projects that provide a repository for scripts, queries, data back-up files,
and command files. You create and maintain database projects the same way as
other Visual Studio .NET projects. You access database project though Solution
Explorer, which provides a hierarchical view of all of the projects within a
solution.
To create a database project
1.
Open Solution Explorer: from the View
menu, click Solution Explorer.
2.
Right-click Solution ‘ServiceCall’,
highlight Add, and then click New Project.
3.
Under Project Types:, expand the Other Projects folder, and then click Database Projects.
4.
Under Templates, click
the Database Project template.
5.
Name the project ServiceCallDB, and then
click OK.
Figure 25: Add New Project dialog box
6. In the Add Database Reference dialog box, select the
connection for the ServiceCall database, and then click OK to
create the project.
Figure 26: Add Database Reference dialog
box
Note Generate Create Scripts requires Visual Studio .NET Enterprise
Edition.
The Generate Create Scripts tool is another
feature in Server Explorer that is specific to SQL Server. The tool is designed
to make the developer’s job easier. The Generate Create Scripts tool allows you
to script the entire database or selected objects within the database to a
script file. You can then use the script file to restore the database or
objects or deploy the database on another server.
In addition to creating scripts, you can also
export data from individual tables. This is a useful tool during testing. If a
table will be updated or deleted during a test that may require multiple
iterations, the data from the table can be exported to a file and then restored
at the start of each test to provide a consistent starting point.
To demonstrate these features, we will script
the Customers table and export the data to a backup file so we can
restore them back to their original state.
To generate a script
1.
In Server Explorer, under Data
Connections, expand the Tables node, right-click the Customers
table, and then click Generate Create Scripts. You may be
prompted to provide a user ID and password for the computer running SQL Server
if you are not using Windows 2000 or Windows NT integrated security. The Generate
Create Scripts dialog box opens (this will look very familiar to those who
have used SQL Server Enterprise Manager).
2.
The dialog box contains three tabs: General, Formatting, and Options. Accept the defaults on all three tabs, and then
click OK. The Browse for Folder dialog box opens. Notice that
Visual Studio .NET has already selected the Create Scripts folder within
the database project we created above.
3.
Click OK. The
scripts for the Customers table are created and placed in the Database
Project folder.
The following four files are created.
|
File
|
Description
|
|
Dbo.Customers.tab
|
Contains a SQL script to create the table
|
|
Dbo.Customers.kci
|
Contains a SQL script to create the primary keys and indexes
|
|
Dbo.Customers.fky
|
Contains a SQL script to create the foreign keys
|
|
Dbo.Customers.ext
|
Contains a SQL script to
create the extended properties
|
To export the data table to a file
1.
In Server Explorer, right-click the Customers
table, and then click Export Data. Once again, the Browse for
Folder dialog box opens and is positioned on the Create Scripts
folder within the database project.
2.
Click OK. The data in the Customers
table is placed in a file named Dbo.Customers.dat.
