Dr. Rathindra Sarathy

1. MSIS 5133 Advanced MIS - E-CommerceSpring 2003Lecture 4: DotNet Technologies - AddendumWorking with Databases in Visual Studio.Net Dr. Rathindra Sarathy

2. Visual Studio .NET Built-In Database Tools • There are a variety of Visual Studio .NET tools that allow you to create and manipulate a database • Server Explorer - creates your database, tables, and stored procedures • Table Designer – creates the columns in the tables • Query and View Editor - creates database queries • SQL Editor - creates and edits SQL scripts and stored procedures

3. Creating a Database Connection to the GrocerToGo Database

4. Creating a SQL Server Database in Visual Studio .NET • Authentication is required for access to the SQL Server • Windows NT authentication • SQL Server authentication • SQL Server User ID identifies users and controls which users can access the database objects • Each user has roles that identify if they are able to create or modify a data object • The user needs permission to create the database • See MSDE readme document provided in Chapter6Data directory for MSDE installation instructions

5. Creating a Database

6. The Table Designer • Table Designer - creates the schema for the table. Enter the data, create new columns, and modify the properties of the columns • In Table Data view, you can create a new row, modify an existing row, or delete a row • In Table Design view, create the table structure • Lower half - Properties Pane • The properties will depend upon the data type

7. The Table Designer • Column Name - Do not use any blank spaces or special characters in a column name other than an underscore • Data Type - The ADO.NET managed providers convert the data types to Managed .NET data types when you retrieve your data using ADO.NET. There are many data types defined within SQL Server. For example, the int data type is used to identify an integer • Allow Nulls - If a column does not contain a value for a particular record, a null value is returned

8. SQL Server 2000 data types • INT data type • Identity property is the identity column used to provide a unique value for each row to locate a specific row of data • Identity Seed property shows the initial value of the column which is used to create the value for the first row • Identity Increment is the value that is used to increment the seed when a new row is added • For example, you can increment the identity column by one, so that the value of the identity column increases by one for each new record

9. Primary Key • Primary Key is used to identify the column as a unique column • Each value for each row must be unique within this column • All rows must have a value for this column • No record will be allowed that contains a null value for this column • The primary key is configured for a specific column using the row selector, the Table toolbar, or the Diagram menu • A yellow key appears at the side of the column name to indicate that this column is the Primary Key column

10. The SQL Server Column Options

11. Create a Table • Create the Products Table and columns using the Table Designer • Create the ProductID column and set the properties • Data type - int • length - 4 • Allow Nulls check box – deselected • Identity - Yes • Identity Seed – 1 • Identity Increment - 1 • Set Primary Key

12. Creating a Database Table Using the Table Editor

13. Table Values

14. Creating a View with Query and View Editor • Query and View Editor allows you to create a query in the database. When you make a change in one pane, the other panes are automatically updated • Table Pane you can add tables and select which columns to include in the query. The query is displayed visually with icons next to the columns • SQL Pane generates the SQL for you based upon the Table Pane and Grid Pane • Output Paneis where the output from the Query is displayed and is used to test your queries • Grid Pane is used to select columns and criteria, just as you do when using Microsoft Access. The column name and table indicate where to retrieve the values for the column

15. Grid Pane • Output property indicates if the column should be visible in the results when the query is executed. (Note: The output property is like the “show” check box in Access.) • Sort order is used to indicate one or more columns to sort the results. • Sort type as ascending or descending. • Alias property is used to display an alternate name in the results. • Criteria property creates a conditional statement that must be met before the record can be retrieved. • Or property indicates an alternative condition that could be met before the record can be retrieved.

16. The Query and View Editor

17. Creating a Relationship with the Data Diagram • Database Designer allows you to define relationships between tables using columns • A line is drawn from a field from one table to another to indicate which fields are used to define the relationship between the tables • The endpoints of the line indicate whether the relationship is one-to-one or one-to-many • key at one endpoint and a figure-eight at the other, it is a one-to-many relationship • key at each endpoint, it is a one-to-one relationship

18. Creating a Relationship with the Data Diagram • Table view property - view of the table • Column view – displays the tables and columns • Standard view - displays the column names and the schema for the columns • Keys view - displays the names of the tables in the title bar and the names of the primary key columns • Name view - displays only the names of the tables in the title bar • Custom view - allows you to add any of the properties to the view

19. Create a Relationship • Create a relationship between the Categories table and the Products table • The relationship is a one-to-many relationship • The CategoryID is used to create a one-to-many relationship between the two tables • There is only one CategoryID value in the Categories table • There can be many products that have the same CategoryID value

20. Create a Relationship

21. Building SQL Scripts and Stored Procedures • Stored procedures can be used to create a SQL command that is stored within the database • Because the command is stored with the database server, it has already been parsed and compiled by the server and is more efficient than a SQL statement • You can use a stored procedure to merely run a SQL query • Values within the SQL statement can be replaced with the input parameters

22. Input Parameters • An input parameter is a value that is passed to the stored procedure when it is called • All parameters are considered input parameters by default • Must match the data type and length that is specified within the stored procedure • Name of the parameter within the stored procedure always begins with the @ symbol • Usually, the input parameter is compared to a value • A default value can be provided for the parameter • A default value for the parameter must be a constant or it can be NULL • Wildcard characters such as the asterisk are permitted with the default value

23. Output Parameters • Output Parameters can send values back to the object that called the stored procedure • ReturnValue parameter is a return value passed back to the stored procedure • Called with the RETURN keyword • The return value can be information such as the number of records affected • Values returned can be integer, money, and varchar but not text

24. Creating Stored Procedures with the SQL Editor • To create and edit SQL scripts and stored procedures • inserts a skeletal stored procedure when you create a new stored procedure • color code SQL keywords, which helps minimize syntax and spelling errors • comments in the SQL skeleton script are indicated with /* and */ characters • comments are displayed in green text • change the editor's default behaviors such as tab size, word wrapping, and line numbers by selecting Options on the Tools menu

25. SQL Editor • CREATE and ALTER Procedures • Once you save a procedure, the first line SQL commands changes from CREATE PROCEDURE to ALTER PROCEDURE • This means that the procedure has been created • You can only change, or alter the procedure using the ALTER PROCEDURE command

26. SQL Editor

27. Create Stored Procedures Using the SQL Editor • Search a field in the database for a matching value • Search a text string under the ModelName field, then display the results • The input parameter is named @param_SearchProducts which is mapped to the ModelName column • The data type is nvarchar with a length of 50 bytes • Retrieve a subset of the columns from the database • Use the WHERE clause to search for a condition that contains the string that was passed with the parameter • Use the wild card character and the LIKE keyword to locate any text that contains the string passed

28. Modifying Stored Procedures with the SQL Query Builder • The SQL Query Builder has the same user interface as the Query and View Editor that you used earlier • However, the code to create the query is stored in the stored procedure • Within the stored procedure, you can edit the blocks of code that are enclosed within a blue line, via the SQL Query Builder