Chapter 1 An introduction to relational databases and

Chapter 1 An introduction to relational databases and

Chapter 1 An introduction to relational databases and SQL Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 1 Objectives Knowledge Identify the three main hardware components of a client/server system. Describe the way a client accesses the database on a server using these terms: application software, data access API, database management system, SQL query, and query results. Describe the way a database is organized using these terms: tables, columns, rows, and cells. Describe how the tables in a relational database are related using

these terms: primary key and foreign key. Identify the three types of relationships that can exist between two tables. Describe the way the columns in a table are defined using these terms: data type, null value, default value, and identity column. Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 2 Objectives (continued) Describe the relationship between standard SQL and Microsoft SQL Servers Transact-SQL. Describe the difference between DML statements and DDL statements. Describe the difference between an action query and a SELECT query. List three coding techniques that can make your SQL code easier to read and maintain. Explain how views and stored procedures differ from SQL statements that are issued from an application program.

Describe the use of command and connection objects when .NET applications access a SQL Server database. Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 3 A simple client/server system Database server Network Client Client Client Murachs SQL Server 2005, C1

2007, Mike Murach & Associates, Inc. Slide 4 The three hardware components of a client/server system The clients are the PCs, Macintoshes, or workstations of the system. The database server is a computer that stores the files and databases of the system and provides services to the clients. When it stores databases, its often referred to as a database server. The network consists of the cabling, communication lines, and other components that connect the clients and the servers of the system. Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 5

Client/server system implementations In a simple client/server system, the server is typically a highpowered PC that communicates with the clients over a local area network (LAN). The server can also be a midrange system, like an IBM iSeries or a Unix system, or it can be a mainframe system. A client/server system can also consist of one or more PC-based systems, one or more midrange systems, and a mainframe system in dispersed geographical locations. This is commonly referred to as an enterprise system. Individual systems and LANs can be connected and share data over larger private networks, such as a wide area network (WAN), or a public network like the Internet. Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 6 Client software, server software, and the SQL CLIENT Machine(s)

Database SERVER Machine interface SQL queries Results Software Client on Client Machine(s) 1. Application software 2. Data access API The application software can be created with Visual Basic, Java, C++ or any other programming language Murachs SQL Server 2005, C1 Software on Database Server Machine

Database server 1. Database management system (DBMS) 2. Database data files Data access API is a library of special purpose code that can be used by the Application software to communicate with the DBMS 2007, Mike Murach & Associates, Inc. Slide 7 Software on the Client machine The application software does the work that the user wants to do. The data access API (application programming interface) provides the interface between the application program and the database management system (DBMS), (which generally runs on the server).

The processing thats done by the client software is typically referred to as front-end processing, and the client is typically referred to as the front end. Software on the Database Server machine To store and manage databases, the database server requires a database management system (DBMS) like Microsoft SQL Server. The processing thats done by the DBMS is typically referred to as back-end processing, and the database server is referred to as the back end. Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 8 The SQL interface The application software communicates with the DBMS by sending SQL queries through the data access API. When the DBMS receives a query, it provides a service like returning the requested data (the query results) to the client.

SQL stands for Structured Query Language, which is the standard language for working with a relational database. SQL is often pronounced as sequel. Client/server versus file-handling systems In a client/server system, the processing done by an application is typically divided between the client and the server. In a file-handling system, all of the processing is done on the clients. The clients may access data thats stored in files on the server, but none of the processing is done by the server. Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 9 A Windows-based system that uses an application server User request SQL queries

Response Client User interface Murachs SQL Server 2005, C1 Results Application server Business components 2007, Mike Murach & Associates, Inc. Database server DBMS Database Slide 10 A simple web-based system User request

User request SQL queries Internet Response Client Web browser Murachs SQL Server 2005, C1 Results Response Web server Web applications Web services 2007, Mike Murach & Associates, Inc. Database server

DBMS Database Slide 11 Other client/server system architectures In addition to a database server and clients, a client/server system can also include additional servers, such as application servers and web servers. Application servers are typically used to store business components that do part of the application processing. In particular, these components are used to process database requests from the client. Web servers are typically used to store web applications and web services. These are like standard applications and business components, but theyre designed to run on a web server. In a web-based system, a web browser running on a client sends a request to a web server over the Internet. Then, the web server passes any requests for data on to the database server. Murachs SQL Server 2005, C1

2007, Mike Murach & Associates, Inc. Slide 12 The Vendors table in an Accounts Payable database Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 13 How a database table is organized A relational database consists of tables. Tables consist of rows and columns, which can also be referred to as records and fields. A table is typically modeled after a real-world entity, such as an invoice or a vendor. A column represents some attribute of the entity, such as the amount of an invoice or a vendors address. A row contains a set of values for a single instance of the entity, such as one invoice or one vendor.

The intersection of a row and a column is sometimes called a cell. A cell stores a single value. Most tables have a primary key that uniquely identifies each row in the table. If a primary key consists of two or more columns, its called a composite primary key. Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 14 How a database table is organized (continued) Some database management systems also let you define one or more non-primary keys. Like a primary key, a non-primary key uniquely identifies each row in the table. In SQL Server, non-primary keys are called unique keys. A table can also be defined with one or more indexes. An index provides an efficient way to access data from a table based on the values in specific columns. An index is automatically created for a tables primary and nonprimary keys.

Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 15 The relationship between the Vendors and Invoices tables in the database Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 16 How the tables in a relational database are related The tables in a relational database are related to each other through their key columns. A foreign key identifies a primary key in another table. A table may contain one or more foreign keys. When you define a foreign key for a table in SQL Server, you cant add rows to the table with the foreign key unless theres a matching primary key in the related table.

The relationships between the tables in a database correspond to the relationships between the entities they represent. The most common type of relationship is a one-to-many relationship. A table can also have a one-to-one relationship or a many-to-many relationship with another table. Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 17 The columns of the Invoices table Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 18 Common SQL Server data types Type

bit int, bigint, smallint, tinyint money, smallmoney decimal, numeric float, real datetime, smalldatetime char, varchar nchar, nvarchar Murachs SQL Server 2005, C1 Description A value of 1 or 0 for True or False. Integer values of various sizes. Monetary values that are accurate to four decimal places. Decimal values that are accurate to the least significant digit. Floating-point values that contain an approximation of a decimal value. Dates and times. A string of letters, symbols, and

numbers in the ASCII character set. A string of letters, symbols, and numbers in the Unicode character set. 2007, Mike Murach & Associates, Inc. Slide 19 How the columns in a table are defined The data type thats assigned to a column determines the type of information that can be stored in the column. Each column definition indicates whether or not it can contain null values. A null value indicates that the value of the column is unknown. A column can also be defined with a default value. Then, that value is used if another value isnt provided when a row is added to the table. A column can also be defined as an identity column. An identity column is a numeric column whose value is generated automatically when a row is added to the table. Murachs SQL Server 2005, C1

2007, Mike Murach & Associates, Inc. Slide 20 A comparison of relational databases and conventional file systems Feature Definition File system Each program that uses the file must define the file and its record layout Maintenance If the file definition changes, each program that uses the file must be modified Each program that updates a file must

include code to check for valid data Validity checking Murachs SQL Server 2005, C1 Relational database Tables, rows, and columns are defined within the database and can be accessed by name Programs can be used without modification when the definition of a table changes Can include checks for valid data 2007, Mike Murach & Associates, Inc.

Slide 21 A comparison of relational databases and conventional file systems (continued) Feature Relationships File system Each program must provide for and enforce relationships between files Data access Each I/O operation targets a specific record in a file based on its relative position in the file or its key value Murachs SQL Server 2005, C1

Relational database Can enforce relationships between tables using foreign keys; ad hoc relationships can also be used A program can use SQL to access selected data in one or more tables of a database 2007, Mike Murach & Associates, Inc. Slide 22 A comparison of relational databases and other database systems Feature Supported relationships

Data access Murachs SQL Server 2005, C1 Hierarchical database One-to-many only Network database One-to-many, one-to-one, and many-to-many Relational database One-to-many, one-to-one, and many-to-many; ad hoc relationships can

also be used Programs must Programs must Programs can include code to include code to access data navigate through navigate through without knowing the physical the physical its physical structure of the structure of the structure database database 2007, Mike Murach & Associates, Inc. Slide 23 A comparison of relational databases and other

database systems (continued) Hierarchical Feature database Maintenance New and modified relationships can be difficult to implement in application programs Murachs SQL Server 2005, C1 Network database New and modified relationships can be difficult to implement in application

programs 2007, Mike Murach & Associates, Inc. Relational database Programs can be used without modification when the definition of a table changes Slide 24 How relational databases compare to other data models To work with any of the data models other than the relational database model, you must know the physical structure of the data and the relationships between the files or tables. Because relationships are difficult to implement in a conventional file system, redundant data is often stored in these types of files.

The hierarchical database model provides only for one-to-many relationships, called parent/child relationships. The network database model can accommodate any type of relationship. Relational databases are less efficient than the other data models because they require more system resources. However, their flexibility and ease of use typically outweighs this inefficiency. Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 25 Important events in the history of SQL Year Event 1970 Dr. E. F. Codd developed the relational database model. 1978 IBM developed the predecessor to SQL, called Structured English Query Language (SEQUEL). This language was used on a database system called System/R, but neither the system nor the query language was ever released. 1979 Relational Software, Inc. (later renamed Oracle) released the

first relational DBMS, Oracle. 1982 IBM released their first relational database system, SQL/DS (SQL/Data System). 1985 IBM released DB2 (Database 2). 1987 Microsoft released SQL Server. Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 26 Important events in the history of SQL (continued) Year Event 1989 The American National Standards Institute (ANSI) published the first set of standards for a database query language, called ANSI/ISO SQL-89, or SQL1. 1992 ANSI published standards (ANSI/ISO SQL-92, or SQL2) that were more stringent than SQL1 and incorporated many new features. These standards introduced levels of compliance that indicated the extent to which a dialect met the standards. 1999 ANSI published SQL3 (ANSI/ISO SQL-99). These standards

incorporated new features, including support for objects. Levels of compliance were replaced by a core specification along with specifications for nine additional packages. 2003 ANSI published SQL4 (ANSI/ISO SQL:2003). These standards introduced XML-related features, standardized sequences, and identity columns. Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 27 The ANSI SQL standards SQL-92 initially provided for three levels of compliance, or levels of conformance: entry, intermediate, and full. A transitional level was later added between the entry and intermediate levels because the jump between those levels was too great. SQL:1999 includes a core specification that defines the essential elements for compliance, plus nine packages. Each package is designed to serve a specific market niche. Although SQL is a standard language, each vendor has its own SQL dialect, or variant, that may include extensions to the standards.

SQL Servers SQL dialect is called Transact-SQL. Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 28 How knowing standard SQL helps you The most basic SQL statements are the same for all SQL dialects. Once you have learned one SQL dialect, you can easily learn other dialects. How knowing standard SQL does not help you Any non-trivial application will require modification when moved from one SQL database to another. Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc.

Slide 29 Features of Oracle, DB2, and SQL Server Category Released Current version Platforms Oracle 1979 Oracle 10g IBM 1985 DB2 9 Microsoft 1987 SQL Server 2005 Unix

OS/390 Windows Windows Strengths Typical system Reliable Medium to very large Network-based Mission-critical Enterprise-wide OS/390, z/OS, and AIX Unix Windows Reliable Large to very

large Centralized architecture Mission-critical Enterprise-wide Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Easy to use Small to medium Network-based Slide 30 A comparison of Oracle, DB2, and SQL Server Oracle is typically used for large, mission-critical, systems that run on one or more Unix servers. DB2 is typically used for large, mission-critical systems that run on legacy IBM mainframe systems using the z/OS or OS/390 operating system.

SQL Server is typically used for small- to medium large-sized systems that run on one or more Windows servers. Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 31 SQL statements used to work with data (DML) Statement SELECT INSERT UPDATE DELETE Murachs SQL Server 2005, C1 Description Retrieves data from one or more tables. Adds one or more new rows to a table. Changes one or more existing rows in a table.

Deletes one or more existing rows from a table. 2007, Mike Murach & Associates, Inc. Slide 32 SQL statements used to work with database objects (DDL) Statement CREATE DATABASE CREATE TABLE CREATE INDEX ALTER TABLE DROP DATABASE DROP TABLE DROP INDEX Murachs SQL Server 2005, C1 Description Creates a new database. Creates a new table in a database.

Creates a new index for a table. Changes the structure of an existing table. Deletes an existing database. Deletes an existing table. Deletes an existing index. 2007, Mike Murach & Associates, Inc. Slide 33 Two types of SQL statements The SQL statements can be divided into two categories: the data manipulation language (DML) and the data definition language (DDL). The DML statements let you work with the data in the database. The DDL statements let you work with the objects in the database. SQL programmers typically work with the DML statements, while database administrators (DBAs) use the DDL statements. Murachs SQL Server 2005, C1

2007, Mike Murach & Associates, Inc. Slide 34 A statement that creates a new database CREATE DATABASE AP A statement that creates a new table CREATE TABLE Invoices (InvoiceID INT NOT NULL IDENTITY PRIMARY KEY, VendorID INT NOT NULL REFERENCES Vendors(VendorID), InvoiceNumber VARCHAR(50) NOT NULL, InvoiceDate SMALLDATETIME NOT NULL, InvoiceTotal

MONEY NOT NULL, PaymentTotal MONEY NOT NULL DEFAULT 0, CreditTotal MONEY NOT NULL DEFAULT 0, TermsID INT NOT NULL REFERENCES Terms(TermsID), InvoiceDueDate SMALLDATETIME NOT NULL, PaymentDate SMALLDATETIME NULL) Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 35

A statement that adds a new column to the table ALTER TABLE Invoices ADD BalanceDue MONEY NOT NULL A statement that deletes the new column ALTER TABLE Invoices DROP COLUMN BalanceDue A statement that creates an index on the table CREATE INDEX IX_Invoices_VendorID ON Invoices (VendorID) Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 36 How to query a single table You use the SELECT statement to retrieve selected columns and rows from a base table. The result of a SELECT statement is a result table, or result set.

A result set can include calculated values that are calculated from columns in the table. The execution of a SELECT statement is commonly referred to as a query. Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 37 The Invoices base table Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 38 A SELECT statement that retrieves and sorts selected columns and rows from the Invoices table SELECT InvoiceNumber, InvoiceDate, InvoiceTotal,

PaymentTotal, CreditTotal, InvoiceTotal PaymentTotal CreditTotal AS BalanceDue FROM Invoices WHERE InvoiceTotal PaymentTotal CreditTotal > 0 ORDER BY InvoiceDate The result set defined by the SELECT statement Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 39 A SELECT statement that joins data from the Vendors and Invoices tables SELECT VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal FROM Vendors INNER JOIN Invoices ON Vendors.VendorID = Invoices.VendorID WHERE InvoiceTotal >= 500

ORDER BY VendorName, InvoiceTotal DESC Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 40 The result set defined by the SELECT statement Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 41 How to join data from two or more tables A join lets you combine data from two or more tables into a single result set. The most common type of join is an inner join. This type of join returns rows from both tables only if their related columns match. An outer join returns rows from one table in the join even if the

other table doesnt contain a matching row. Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 42 How to add, update, and delete data in a table You use the INSERT statement to add rows to a table. You use the UPDATE statement to change the values in one or more rows of a table based on the condition you specify. You use the DELETE statement to delete one or more rows from a table based on the condition you specify. The execution of an INSERT, UPDATE, or DELETE statement is often referred to as an action query. Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 43

A statement that adds a row to the Invoices table INSERT INTO Invoices (VendorID, InvoiceNumber, InvoiceDate, InvoiceTotal, TermsID, InvoiceDueDate) VALUES (12, '3289175', '7/18/2006', 165, 3, '8/17/2006') A statement that changes the value of the CreditTotal column for a selected row UPDATE Invoices SET CreditTotal = 35.89 WHERE InvoiceNumber = '367447' A statement that changes the values in the InvoiceDueDate column for all invoices with the specified TermsID UPDATE Invoices SET InvoiceDueDate = InvoiceDueDate + 30 WHERE TermsID = 4 Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc.

Slide 44 A statement that deletes a selected invoice from the Invoices table DELETE FROM Invoices WHERE InvoiceNumber = '4-342-8069' A statement that deletes all paid invoices DELETE FROM Invoices WHERE InvoiceTotal PaymentTotal CreditTotal = 0 Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 45 A SELECT statement thats difficult to read select invoicenumber, invoicedate, invoicetotal, invoicetotal paymenttotal credittotal as balancedue from invoices where invoicetotal paymenttotal

credittotal > 0 order by invoicedate A SELECT statement thats coded in a readable style Select InvoiceNumber, InvoiceDate, InvoiceTotal, InvoiceTotal PaymentTotal CreditTotal As BalanceDue From Invoices Where InvoiceTotal PaymentTotal CreditTotal > 0 Order By InvoiceDate Note Line breaks, white space, indentation, and capitalization have no effect on the operation of a statement. Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 46 A SELECT statement with a block comment

/* Author: Bryan Syverson Date: 8/22/06 */ SELECT InvoiceNumber, InvoiceDate, InvoiceTotal, InvoiceTotal PaymentTotal CreditTotal AS BalanceDue FROM Invoices A SELECT statement with a single-line comment SELECT InvoiceNumber, InvoiceDate, InvoiceTotal, InvoiceTotal PaymentTotal CreditTotal AS BalanceDue -- The 4th column calculates the invoice balance due FROM Invoices Note Comments can be used to document portions of code or to clarify what the coding does. They arent executed by the system. Murachs SQL Server 2005, C1

2007, Mike Murach & Associates, Inc. Slide 47 How to code a comment To code a block comment, type /* at the start of the block and */ at the end. To code a single-line comment, type -- followed by the comment. SQL coding recommendations Start each new clause on a new line. Break long clauses into multiple lines and indent continued lines. Capitalize the first letter of each keyword and each word in column and table names. Use comments only for portions of code that are difficult to understand. Then, make sure that the comments are correct and up-to-date. Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc.

Slide 48 A CREATE VIEW statement for a view named VendorsMin CREATE VIEW VendorsMin AS SELECT VendorName, VendorState, VendorPhone FROM Vendors The virtual table thats represented by the view Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 49 A SELECT statement that uses the VendorsMin view SELECT * FROM VendorsMin WHERE VendorState = 'CA' ORDER BY VendorName

The result set thats returned by the SELECT statement Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 50 How to work with views A view consists of a SELECT statement thats stored with the database. A view behaves like a virtual table. Since you can code a view name anywhere youd code a table name, a view is sometimes called a viewed table. Views can be used to restrict the data that a user is allowed to access or to present data in a form thats easy for the user to understand. Because views are stored as part of the database, they can be managed independently of the applications that use them. Murachs SQL Server 2005, C1

2007, Mike Murach & Associates, Inc. Slide 51 A CREATE PROCEDURE statement for a procedure named spVendorsByState CREATE PROCEDURE spVendorsByState @State char(2) AS SELECT VendorName, VendorState, VendorPhone FROM Vendors WHERE VendorState = @State ORDER BY VendorName A statement that executes the stored procedure EXEC spVendorsByState 'CA' The result set Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc.

Slide 52 How to use stored procedures, triggers, and user-defined functions A stored procedure is one or more SQL statements that have been compiled and stored with the database. It can be started by application code on the client. A stored procedure can also include control-of-flow language, which lets you perform conditional processing. Stored procedures can improve database performance because the SQL statements in each procedure are only compiled and optimized the first time theyre executed. A trigger is a special type of procedure thats executed when rows are inserted, updated, or deleted from a table. Triggers are typically used to check the validity of the data in a row. A user-defined function (UDF) is a special type of procedure that can return a value or a table. Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc.

Slide 53 Common options for accessing SQL Server data .NET application Java application Visual Basic 6 application ADO.NET JDBC ADO Java driver OLE DB SQL Server Murachs SQL Server 2005, C1

2007, Mike Murach & Associates, Inc. Slide 54 Common data access models To work with the data in a SQL Server database, an application uses a data access model. For a Visual Basic .NET application, that model is typically ADO.NET. For a Java application, that model is typically JDBC (Java Database Connectivity). And for a Visual Basic 6 application, that model is typically ADO (ActiveX Data Objects). Each data access model defines a set of objects you can use to connect to and work with a SQL Server database. Some of the data access models require additional software, called drivers, to communicate with SQL Server. For example, ADO requires an OLE DB driver, and JDBC requires a Java driver. ADO.NET, which is Microsofts newest data access model, includes its own driver so it can communicate directly with SQL Server. Murachs SQL Server 2005, C1

2007, Mike Murach & Associates, Inc. Slide 55 Basic ADO.NET objects in a.NET application .NET application Dataset Data table Data adapter Command Connection SQL Server Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc.

Slide 56 How to use ADO.NET from a .NET application The data used by a .NET application can be stored in a data table within a dataset. A dataset can contain multiple data tables. To retrieve data from a database and store it in a data table: (1) a data adapter issues a SELECT statement thats stored in a command; (2) the command uses a connection to connect to the database and retrieve the data; (3) the data is passed back to the data adapter, which stores the data in the dataset. To update the data in a database based on the data in a data table, the data adapter issues an INSERT, UPDATE, or DELETE statement thats stored in a command. Then, the command uses a connection to connect to and update the database. After the database data is retrieved or updated, the connection is closed and the resources used by the connection are released. This is referred to as the disconnected data architecture. Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc.

Slide 57 Visual Basic code that uses ADO.NET to retrieve data from a SQL Server database ' Create the Connection, DataAdapter, Command, ' and DataSet objects Dim connAP As New SqlClient.SqlConnection() Dim daVendors As New SqlClient.SqlDataAdapter() Dim cmdVendorsSelect As New SqlClient.SqlCommand() Dim dsAP As New DataSet() ' Set the connection string for the Connection object connAP.ConnectionString = _ "Data Source=localhost\SqlExpress;" _ & "Initial Catalog=AP;Integrated Security=True" ' Set the Connection object used by the Command object cmdVendorsSelect.Connection = connAP ' Set the SELECT statement for the Command object cmdVendorsSelect.CommandText = "SELECT VendorID, " _ & "VendorName, VendorAddress1, VendorAddress2, " _ & "VendorCity, VendorState, VendorZipCode " _ & "FROM Vendors ORDER BY VendorName"

Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 58 Visual Basic code that uses ADO.NET to retrieve data from a SQL Server database (continued) ' Set the Command object for the DataAdapter daVendors.SelectCommand = cmdVendorsSelect ' Open the connection to the database connAP.Open() ' Retrieve the data specified by the SELECT statement ' and load it into the Vendors table that is stored in the ' DataSet object daVendors.Fill(dsAP, "Vendors") ' Close the connection to the database connAP.Close() Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc.

Slide 59 C# code that uses ADO.NET to retrieve data from a SQL Server database // Create the Connection, DataAdapter, Command, and DataSet // objects SqlConnection connAP = new SqlConnection(); SqlDataAdapter daVendors = new SqlDataAdapter(); SqlCommand cmdVendorsSelect = new SqlCommand(); DataSet dsAP = new DataSet(); // Set the connection string for the Connection object connAP.ConnectionString = "Data Source=localhost\\SqlExpress;" + "Initial Catalog=AP;Integrated Security=True"; // Set the Connection object used by the Command object cmdVendorsSelect.Connection = connAP; // Set the SELECT statement for the Command object cmdVendorsSelect.CommandText = "SELECT VendorID, " + "VendorName, VendorAddress1, VendorAddress2, " + "VendorCity, VendorState, VendorZipCode " + "FROM Vendors ORDER BY VendorName";

Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc. Slide 60 C# code that uses ADO.NET to retrieve data from a SQL Server database (continued) // Set the Command object for the DataAdapter daVendors.SelectCommand = cmdVendorsSelect; // Open the connection to the database connAP.Open(); // Retrieve the data specified by the SELECT statement // and load it into the Vendors table that is stored in the // DataSet object daVendors.Fill(dsAP, "Vendors"); // Close the connection to the database connAP.Close(); Murachs SQL Server 2005, C1 2007, Mike Murach & Associates, Inc.

Slide 61

Recently Viewed Presentations

  • Cycles of Matter - Livingston Public Schools

    Cycles of Matter - Livingston Public Schools

    Biogeochemical cycles of matter involve biological processes, geologicalprocesses, and chemical processes. ... Oxygen participates in parts of the carbon, nitrogen, and phosphorus cycles by combining with these elements and cycling with them through parts of their journeys.
  • Chapter 2: Basic Standards for Web Services

    Chapter 2: Basic Standards for Web Services

    Tahoma Arial Wingdings Courier New Blends 1_Blends 2_Blends 3_Blends 4_Blends 5_Blends 6_Blends 7_Blends 8_Blends 9_Blends 10_Blends 11_Blends Microsoft Visio Drawing PowerPoint Presentation Highlights of this Chapter Standards for Web Services Markup and Metadata History XML Basics and Namespaces XML Schema...
  • Building a Semantic Parser Overnight Overnight framework   Which

    Building a Semantic Parser Overnight Overnight framework Which

    Using a small grammar, all logical forms expressible in natural language can be realized compositionally based on the logical form. Grammar. Functionality-driven. Generate superlatives, comparatives, negation, and coordination. Grammar. Grammar.
  • PENS Lesson 4 - scjh.org

    PENS Lesson 4 - scjh.org

    What is an imposter? In sentences imposters are words that we might mistake for the real verb or the real subject of the sentence. We are going to mark out the imposters, so we don't make a mistake when we...
  • The Work of Elders

    The Work of Elders

    * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * The Work of Elders The Work of...
  • Spring Celebration Chris Vulpe and Luoping Zhang, Ph.D.

    Spring Celebration Chris Vulpe and Luoping Zhang, Ph.D.

    Gene 6000 KO Gene 1 KO Gene 2 KO Gene 3 KO Gene n KO Toxicant X one 1 X two 2 X three 3 X n n X 6000 6000 Grow yeast with Collect and count flags Resistant Grows...
  • Paying and Funding the Community Centred Midwife in New Zealand

    Paying and Funding the Community Centred Midwife in New Zealand

    Responsible for workforce support including locum service and IT infrastructure. Could be bulk funded and hold an outcomes based contract to drive innovation. Commitment and Dedication Stability and Consistency. Rearrange Trsuted. Blended Payment Model.
  • Advanced CPT (Java)

    Advanced CPT (Java)

    Specular reflection adds highlights to the reflection (the shiny area). The amount of specular reflection that the user sees depends on the angle θ between r (the direction of a perfect reflector vector) and v (the direction of the viewer)....