Managing MS SQL Server - PowerPoint PPT Presentation

1 / 43
About This Presentation
Title:

Managing MS SQL Server

Description:

If seed is 1 and increment is 1, field values for the first three records are 1, 2, 3... SQL Pane generates and displays corresponding SQL statement ... – PowerPoint PPT presentation

Number of Views:127
Avg rating:3.0/5.0
Slides: 44
Provided by: Kathlee
Category:

less

Transcript and Presenter's Notes

Title: Managing MS SQL Server


1
Managing MS SQL Server Data Sources in VS.NET
  • ASP.NET

2
Data Source Processing Requirements
  • In today's computer networks there needs to be
    the capability to share incompatible data from
    differing file types
  • Data from different software companies such as
    Microsoft, Oracle, Sybase, Informix, etc.
  • Formatted data (i.e. currency format with 1000's
    separators), graphics, audio, video, etc.
  • Data from a variety of applications, i.e. word
    processing documents, spreadsheets, etc.
  • Data stored on intranets and the Internet
    including XML data sources

3
Universal Data Access
  • The solution to this need to share data is called
    universal data access (UDA)
  • At core to universal access is OLE (object
    linking and embedding) DB (database) object
    interface
  • Data from a variety of sources can be shared
    across different applications and platforms
  • Accesses data stored in different formats , i.e.
  • I.e., UDA can treat data stored in databases and
    spreadsheets as if they all were in the same
    format, even within the same application

4
Overview of the ADO.NET Framework
  • ActiveX Data Object Model (ADO)
  • Provides objects that interface with the database
  • Separates the process of connecting to the data
    source from the manipulation of the data
  • Ability to write code that can transfer between
    applications

5
Visual Studio .NET and Databases
  • Visual Studio .NET can operate on databases from
    different software companies
  • Microsoft, Oracle, Sybase, Informix, etc.
  • SQL commands are embedded into code enabling
    communication with the DBMS
  • Additionally ASP.NET Toolbox controls may be used
    to provide database connectivity

6
General Steps for Using Web Databases
  • Build your database tables and queries/stored
    procedures
  • Create a connection to the database
  • The connection identifies the location of the
    database (the data source) and the connection
    method (ODBC driver, OLE-DB provider, or an
    OLE-DB.NET data provider), and any other
    settings, i.e. username/password
  • Create an ASP.NET Web page
  • Add ADO.NET connection object that connects to
    database, executes commands, and returns data
    from the database
  • Create code that will interact with the data,
    display the data in an ASP.NET control, perform
    calculations on the data, or upload changes to
    the database

7
.NET Data Provider
8
ADO .NET Data Provider
  • Managed Providers or .NET Data Providers
  • ASP.NET application interacts with .NET objects
  • SQL Server .NET data provider is used to connect
    to a SQL Server database
  • Native communication method to communicate so
    there is a performance improvement
  • OLE DB .NET data provider connect to any other
    data source accessed via the OLE DB interface

9
ADO .NET Data Provider (continued)
  • ADO .NET Data Provider Objects
  • Connection establishes a connection to the data
    source
  • Command executes a command (SQL)
  • Parameter send information with the command
    (retrieve only employees who live in Michigan)
  • DataReader forward only, read only stream of
    data
  • DataAdapter populates a DataSet
  • DataSet data disconnected from the database

10
.NET Managed Data Providers
11
ADO.NET Objects
  • SQL Server .NET data provider Prefix Sql
  • SqlConnection, SqlCommand, SqlDataReader,
    SqlDataAdaptor
  • OLE-DB data provider Prefix OleDb
  • OleDbConnection, OleDbCommand, OleDbDataReader,
    OleDbDataAdaptor.
  • Within the Web page, ASP.NET data controls are
    usually bound to DataReaders or DataSets.
  • DataSet is exposed, so it doesnt matter where
    the data came from, or what application it will
    be used in.

12
The ADO.NET Connection Object
  • Provides the connection to the database
  • Requires a connection string
  • Connection string
  • Provider name of the managed provider.
  • Data Source - name of the server
  • User ID and Password - identifies authentication
  • Initial Catalog - name of the database

13
The ADO.NET Connection Object (continued)
  • SQL Server Connection String
  • Manually configured
  • Data Connections window of Visual Studio .NET
  • Single string or concatenate
  • Dim CS As String
  • CS "Data SourceWindamereServer User IDsa
    PASSWORD Initial CatalogMyDatabase"

14
The ADO .NET Command Object
  • Identify command or a stored procedure
  • Connection - Connection used
  • CommandType - type of command
  • Text - default, text string contains the command
  • StoredProcedure
  • Name of a stored procedure
  • Stored procedures - commands that are stored
    within the database (compiled)
  • TableDirect - specifies table to return

15
The ADO .NET Command Object (continued)
  • CommandText command to execute
  • Written in SQL - Structured Query Language
  • Language used to identify what records to add,
    delete, or modify within the database
  • Exposes a Parameters collection of objects
  • Used to apply values passed to stored procedures

16
The ADO .NET Command Object (continued)
  • CommandText command to execute
  • Written in SQL - Structured Query Language
  • Language used to identify what records to add,
    delete, or modify within the database
  • Exposes a Parameters collection of objects
  • Used to apply values passed to stored procedures
  • WHERE employeeState MI

17
The ADO .NET Command Object (continued)
  • ExecuteReader method
  • Method that executes the command and passes the
    results to the DataReader object
  • ExecuteNonQuery method
  • Does not return rows
  • Used to insert, modify, and delete data
  • Returns an integer of the number of rows affected
  • Perform SQL commands
  • CREATE TABLE, INSERT INTO

18
The ADO.NET DataReader Object
  • Deliver a stream of data from the database
  • High-performance
  • Read-only, forward-only stream of data
  • Requires continual access to the database
  • Must remain connected until you have finished
    reading the stream of data

19
The ADO .NET DataReader Object (continued)
  • Read
  • Returns single row and caches each row in memory
    only once
  • Mmoves current record pointer to the next record
  • Close method
  • Closes the DataReader object and releases the
    references to the rowset
  • CommandObject.CommandBehavior property
  • Closes the connection - CloseConnection

20
The ADO .NET DataReader Object (continued)
21
The ADO .NET DataReader Object (continued)
  • Figure 6-3
  • Connection object provides ConnectionString
  • Command object provides SQL statements
  • CommandType - Text
  • CommantText - SELECT, which returns data
  • ExecuteReader method provides DataReader as a
    result
  • Parameter objects can be used

22
The ADO .NET DataReader Object (continued)
  • Figure 6-3 (continued)
  • DataReader - receive the data
  • Contains a collection of DataRows
  • Read method to literally read through each row
  • Use a looping structure to repeat through the
    rows.
  • Displaying the data - data controls

23
The ADO .NET DataReader Object (continued)
24
The ADO .NET DataReader Object (continued)
  • Figure 6-3 (continued)
  • Connection object provides ConnectionString
  • Command object provides SQL statements
  • CommandType - Text
  • CommandText - INSERT, which creates a new record
  • ExecuteNonQuery method returns number of rows
    affected
  • Parameter objects can be used

25
The ADO .NET DataReader Object (continued)
  • Figure 6-3 (continued)
  • DataReader object
  • Contains information returned (number rows
    affected)
  • No records are returned no data binding to
    controls

26
The ADO .NET DataReader Object (continued)
27
The ADO .NET DataAdapter and DataSet Objects
  • DataAdaptor object
  • Works with Connection object to connect to data
  • Provides bridge between DataSet and data source
  • Commands to manage DataSet
  • Automatically generated via CommandBuilder object
  • SelectCommand - retrieve data
  • InsertCommand - add a new record
  • UpdateCommand - modify the data within a record
  • DeleteCommand - permanently remove a record

28
The ADO .NET DataAdapter and DataSet Objects
(continued)
  • Fill method - populates DataSet
  • Insert data SelectCommand returns into DataSet
  • DataSet object
  • disconnected collection of one or more tables
    that are stored in memory on the server
  • maintains original set of data and changes
  • effectively a private copy of the database
  • does not necessarily reflect the current state

29
The ADO .NET DataAdapter and DataSet Objects
(continued)
  • DataSet Object
  • DataTableCollection
  • Collection of one or more DataTable Objects
  • Each DataTable consists of
  • DataRowCollection and DataColumnCollection
  • store information about the rows and columns of
    data
  • ConstraintCollection
  • includes information about the primary and
    foreign keys, and constraint rules

30
The ADO .NET DataAdapter and DataSet Objects
(continued)
  • Primary key
  • No duplicate records appear in this column.
  • Constraint rules
  • Field contains the correct datatype and values
  • DataRelationCollection contains data required to
    maintain relationships between DataTables
  • Relationships can be made between DataTables
  • Tables are joined using the primary and foreign
    keys defined in the DataTable

31
The ADO .NET DataAdapter and DataSet Objects
(continued)
32
The ADO .NET DataAdapter and DataSet Objects
(continued)
33
The ADO .NET DataAdapter and DataSet Objects
(continued)
  • Figure 6-3
  • DataAdapter provides access to
  • Connection object provides ConnectionString
  • Command object in this case the commands are
    part of the DataAdapter (SelectCommand)
  • DataAdapter populates DataSet
  • Can work with data while its in the DataSet,
    DataTables, or DataView
  • Bind DataSet, DataTables, or DataView to data
    controls within the Web page

34
The ADO .NET DataView Object
  • Contains the data from the DataSet
  • Single DataTable or subset of records
  • DataTable - DefaultView property
  • Returns all the records in the DataTable
  • Can select a subset of records from a table
  • Add columns to the DataColumnCollection
  • RowFilter property - filter a subset
  • Sort property - sort data based upon criteria in
    one or more of the columns

35
Data Related Namespaces
36
Building Database Connections
  • Server Explorer window - access to local and
    network databases and data connections
  • Servers node
  • Access to the services running on the computer,
    such as a database server
  • Data Connections node
  • Data connection to a database
  • Graphical tool used to create a database

37
Building Database Connections (continued)
  • ConnectString - connection properties
  • Database property - database
  • Type property - database application
  • Driver - ODBC driver, OLE-DB provider, or .NET
    managed data provider
  • State - if database is currently connected
  • User
  • Name of user account that created database
  • Creator is database owner, or dbo

38
Building Database Connections (continued)
  • Server property - server
  • Default name MSDE/SQL Server is
    MachineName\NetSDK
  • MachineName - name of your local computer
  • (local)\NetSDK or localhost
  • Driver/Provider
  • Assumed to be SQL Server if it uses the SQLClient
    class - not required in the Connection String

39
Downloading Microsoft SQL Server Desktop Engine
(MSDE) Files
  • Create folder
  • md c\MSDETempDE
  • Download files and extract files
  • Command Window
  • cd C\sql2ksp3\MSDE
  • setup INSTANCENAME"NetSDK" SECURITYMODESQL
    SAPWD"password"
  • Verify files and folders installed
  • C cd\Program Files\Microsoft SQL
    Server\MSSQLNETSDK
  • Binn, Data, Install, and Log folder should be
    there

40
Installing .NET Framework DSK Samples Databases
  1. Click Start and select "All Programs" ?
    "Microsoft .NET Framework SDK v1.1" ? "Samples
    and Quickstart Tutorials"
  2. Select "Setup the Quickstarts" and in the
    download window click ltOpengt button click ltOpengt
    again (ConfigSamples.exe will not harm your
    computer)
  3. When "Congratulations!" window opens, click the
    ltLaunchgt button, then the ltConnectgt button (if
    the page loads, installation was successful)

41
Visual Studio .NET Built-In Database Tools
  • Visual tools to create and manipulate databases
  • Server Explorerestablished database connections,
    creates database, tables, and stored procedures
  • Table Designercreate columns in tables, view and
    edit table data
  • Query and View Editorgraphically create database
    queries, and view results
  • SQL Editorcreate and edit SQL scripts and stored
    procedures, and view results
  • Data Diagramgraphically define relationships
    between columns in two or more tables

42
Creating a SQL Server Database in Visual Studio
.NET
  • Authentication is required for access
  • Windows NT
  • SQL Server
  • User ID identifies users and access to objects
  • Each user has roles - create or modify data
    object
  • Needs permission to create database
  • See Appendix B for working with the MSDE and
    troubleshooting data connection problems

43
The Table Designer (Page 1)
  • Table Design view
  • Create schema or table structure in the Design
    pane
  • Column Name (fieldname)do not use blank spaces
    or special characters other than an underscore
  • Data Typeconvert to .NET data types when you
    retrieve your data using ADO.NET
  • Allow Nullsno value, a null value is returned
  • Properties Panebelow the Design pane available
    options depend upon field's data type
  • Table Data view
  • Edit table data (create new row, modify or delete
    rows)

44
The Table Designer ("Products" Table)
45
The Table Designer (Page 2)
  • Properties Pane entries for the int data type
  • Identity property, when set to a value of "Yes",
    automatically provides a unique value for each
    record to locate a specific row of data
  • Identity Seedshows the initial value of first
    row
  • Identity Incrementvalue by which to increment
    the seed each time a new row is added
  • Therefore
  • If seed is 1 and increment is 1, field values for
    the first three records are 1, 2, 3
  • If seed is 1 and increment is 2, field values are
    1, 3, 5

46
The Properties Pane
47
The Table Designer (Page 3)
  • Primary Key
  • Each value must be unique within this column
  • All rows must have a value for this column
  • No record contains a null value for column
  • Click the Row selector box then click the "Set
    Primary Key" icon on Table toolbar
  • A yellow "key" icon appears at the side of the
    column name(s)

48
The Table Designer ("Products" Table)
49
The Table Designer (Products Table)
50
Creating a View with Query and View
Editor (Page 1)
  • Used to create a query in the database
  • When one pane changes, the other panes updated
  • Table Paneadd tables and select columns
    (displays the query visually with icons )
  • Grid Paneselect columns and criteria, just when
    using Microsoft Access column name and table
    indicate where to retrieve the values for column
  • SQL Panegenerates and displays corresponding SQL
    statement
  • Preview Panetest run queries and displays results

51
Creating a View with Query and View Editor
52
Creating a View with Query and View
Editor (Page 2)
  • The Grid pane elements
  • Aliasdisplay an alternate column name (useful
    for calculated or concatenated columns)
  • Outputshould column be visible or not (some
    columns may be used only for criteria conditions)
  • Sort typeascending or descending
  • Sort ordersorting hierarchy (indicate one or
    more columns to sort the results)
  • Criteriaconditional statement (more than one
    entry indicates an AND condition)
  • Orindicates an alternative condition

53
Setting Relationships with Data Diagram
  • Defines relationships (links) between tables
  • A line is drawn from a field from one table to
    another which indicates the fields that define
    the relationship
  • Referential integrity
  • The value of any row in the linked column of one
    table must match a primary key value in the
    other, or be null
  • Endpoints indicate type of relationship
  • One-to-many relationship
  • Key at one endpoint and infinity symbol at the
    other
  • One-to-one relationship
  • Key at each endpoint

54
Setting Relationships with Data Diagram
55
Building Stored Procedures (Page 1)
  • A stored procedure is a SQL script (command) that
    is stored within the database
  • Runs a SQL query (SELECT, INSERT, etc)
  • Because stored procedures already have been
    parsed and compiled by the server, they are more
    efficient than a SQL statement in a View
  • Values can be replaced with input parameters
    (represent variable values entered by user)

56
Building Stored Procedures (Page 2)
  • When a new procedure is begun, the editor inserts
    a skeletal stored procedure (template)
  • Color codes the SQL keywords in blue
  • Comments are contained within / and /
    characters and displayed in green text
  • The default behaviors of the editor may be
    changed in the Options dialog window on the
    Tools menu
  • Tab size, word wrapping, line numbers, etc.

57
Building Stored Procedures (Page 3)
58
Building Stored Procedures (Page 3)
  • CREATE PROCEDURE dbo.sp_ReorderProducts
  • /
  • (
  • _at_parameter1 datatype default value,
  • _at_parameter2 datatype OUTPUT
  • )
  • /
  • AS
  • SELECT FROM Products
  • WHERE QuantityAvailable lt MinOnHand
  • / SET NOCOUNT ON /
  • RETURN

59
The CREATE and ALTER Procedures
  • When new procedure is being created, first
    statement is CREATE PROCEDURE
  • The first line changes from CREATE PROCEDURE to
    ALTER PROCEDURE after the procedure has been
    created and saved (compiles when saved)
  • Developer may change or alter the procedure using
    the ALTER PROCEDURE command

60
Modifying Stored Procedures with the SQL Query
Builder
  • The SQL Query Builder is a tool within the Stored
    Procedure application
  • It functions just like the Query and View Editor
    used for creating and editing views
  • To use the SQL Query Builder
  • Select the Edit Stored Procedure command for an
    existing procedure
  • Right-click around the block of SQL code enclosed
    within the blue lines and select Design SQL Block
  • Use the Query and View editor as before

61
Data Retrieval in SQL
  • The keyword SELECT is used in SQL to create
    queries that retrieve sets of rows (records) from
    one or more related tables in a relational
    database
  • Basic format
  • SELECT columnName(s)
  • FROM tableName(s)
  • WHERE relationCondition
  • SELECT clause is a comma-delimited list that
    limits columns returned (required)
  • WHERE clause is a condition that limits rows
    returned (optional)

62
The WHERE Clause
  • Base on a truth condition which limits which rows
    are returned in the query
  • Relational operators are , gt, lt, gt, lt, ltgt
  • Examples
  • SELECT
  • FROM Payables
  • WHERE PO number "10003"
  • SELECT
  • FROM Purchase Order
  • WHERE Subtotal gt 38.15

63
Building Stored Procedures
64
Input Parameters in Stored Procedures (Page 1)
  • Variable values passed to the stored procedure
  • Parameters are input parameters by default
  • Must match data type and length of the field that
    it references in the SQL query
  • Name always begins with the symbol "_at_"
  • The input parameter is often compared to a value
    in a SQL WHERE clause
  • A default value can be provided
  • Must be a constant (not a variable) or it can be
    NULL
  • Wildcard characters () are permitted

65
Input Parameters in Stored Procedures (Page 2)
66
Input Parameters in Stored Procedures (Page 2)
  • CREATE PROCEDURE dbo.sp_DisplayProduct
  • _at_param_ModelName nvarchar(50)
  • /
  • (
  • _at_parameter1 datatype default value,
  • _at_parameter2 datatype OUTPUT
  • )
  • /
  • AS
  • SELECT ProductID, ModelName,
  • UnitCost, ModelNumber, ProductImage
  • FROM Products
  • WHERE ModelName _at_param_ModelName
  • / SET NOCOUNT ON /
  • RETURN

67
Input Parameters in Stored Procedures
68
Output Parameters in Stored Procedures
  • Send values back to the object that called stored
    the procedure
  • ReturnValuea return value is passed back
  • Called with RETURN keyword
  • Retrieves identity column value (the primary key)
  • Return _at__at_Identity
  • Information such as number of records affected
  • Values can be integer, money, varchar - not text

69
The SQL INSERT Statement (Page 1)
  • The INSERT command adds a row (record) to a table
  • Format
  • INSERT INTO tableName
  • (columnNames)
  • VALUES (value_list )
  • Example (values must match the number of items
    and data types in the original table)
  • INSERT INTO Payables
  • VALUES ("10004", 650", 1000, 3, .F.)

70
The SQL INSERT Statement (Page 2)
  • Example (null values may be indicated by leaving
    space between commas where they occur in table)
  • INSERT INTO Payables
  • VALUES ("10004", "701", , 8, .F.)
  • Example (a columnName list may be used if not all
    columns will be assigned a value)
  • INSERT INTO Payables,
  • (PO number, Product number, Quantity, Back
    ordered?)
  • VALUES ("10004", "701", 8, .F.)

71
Insert Stored Procedure with Parameter
72
Insert Stored Procedure with Parameter
  • CREATE PROCEDURE dbo.sp_InsertCat
  • _at_param_CatName nvarchar(50)
  • /
  • (
  • _at_parameter1 datatype default value,
  • _at_parameter2 datatype OUTPUT
  • )
  • /
  • AS
  • INSERT INTO Categories (CategoryName)
  • VALUES (_at_param_CatName)
  • / SET NOCOUNT ON /
  • RETURN _at__at_Identity

73
Insert Stored Procedure with Parameter
74
WHERE with the LIKE Operator (Page 1)
  • Uses the symbol "" as a wildcard to substitute
    for any other character(s) in a WHERE clause
  • Format
  • SELECT columnNames
  • FROM tableName
  • WHERE columnName LIKE "criteriaString"
  • One or more wildcard symbols may be inserted at
    beginning middle or end of criteriaString

75
WHERE with the LIKE Operator (Page 2)
  • Example 1 (begins with the letter "P")
  • SELECT
  • FROM Product
  • WHERE Product LIKE "P"
  • Example 2 (contains the letter "R")
  • SELECT
  • FROM Product
  • WHERE Product LIKE "R"

76
WHERE with the LIKE Operator (Page 3)
77
WHERE with the LIKE Operator (Page 3)
  • CREATE PROCEDURE dbo.sp_SearchProducts
  • _at_param_SearchProducts nvarchar(50)
  • /
  • (
  • _at_parameter1 datatype default value,
  • _at_parameter2 datatype OUTPUT
  • )
  • /
  • AS
  • SELECT ProductID, ModelName,
  • ModelNumber, UnitCost, ProductImage,
  • Manufacturer
  • FROM Products
  • WHERE ModelNumber LIKE ''
    _at_param_SearchProducts ''
  • OR ModelName LIKE '' _at_param_SearchProducts
    ''
  • OR Description LIKE '' _at_param_SearchProducts
    ''
  • OR Manufacturer LIKE '' _at_param_SearchProduct
    s ''

78
(No Transcript)
Write a Comment
User Comments (0)
About PowerShow.com