Washington State University



Featherman’s GlossaryAs part of MIS325 you are given a database (or you are asked to create your own). Here is a glossary of the terms. In general databases store data that is stored in two-dimensional tables which have rows and columns. It would be very normal for example to have one database that has 10 tables in it. Each table would be used to store similar information for example having a Customers table and a Vendors table.Each of the tables has rows and columns. You specify the columns first, delineating the Active Data Objects (ADO) are a set of data connectivity objects created by Microsoft developers that fit into the .NET development environment. These ADO objects are tuned for speed of data retrieval and data manipulation (for example there are objects designed to work specifically with the SQL Server database). The list below represent some of the common ADO classes for the .NET development platform, thus called the objects.ArrayAn array is a grid, tabular shaped stack of variables. Arrays are useful and efficient for programs that need to store a lot of similar data in memory of the webpage. An example would be to store similar data for store 1, store 2, store 3 etc. of a chain store. An array can have one column (a vertical stack of the same datatype that looks like a ladder), or many columns such as 5 columns of data about an entity (store #, Store revenue, store # transactions, etc.) that looks like a grid.Arrays are great to receive and store data downloaded from a database table (used in the second half of the course) to facilitate data entry (ie populate all the list controls for a webpage) and to store temporary values in-memory for further processing and later storage.In data tables are the arrays that are utilized. These are ‘strongly typed’ arrays as columns can heave different datatypes (string, decimal, date, etc.)ConnectionConnections provide the authentication information needed to make the link from a web page or excel file to a remote database stored on a server. The connection supplies the identifying information (user credentials) used to make a request to a datastore. For example when attempting to connect to a database to retrieve data or to perform updates, the connection must supply the servername, database name, userid and password. Connections provide the linkage and pipe to the underlying datasource be it a database, XML stream, or other data formats.Connections are therefore used to authenticate the program user and grant access to a specific database on a specific database server. We will use a SQLConnection which is optimized to work with SQL Server mandsCommands are an object which serve as a vehicle to fire off SQL queries from the Visual Studio project to the back-end database. We will use SQLCommands to communicate with SQL Server database. Specifically we use SQL INSERT, SQL UPDATE SET, or SQL DELETE statements from a Visual studio project to the back-end database. SQL Commands therefore take the SQL statements and run them. Commands are optimized for speed and are useful for writing parameterized SQL statements. Typical methods for SQL Commands are executenonquery (which just run the SQL INSERT, UPDATE or DELETE statement and do not return any value (non-query refers to the database not returning any values back to the Visual Studio project. Executescalar is used less often to run a SQL statement such as a COUNT, SUM or AVERAGE and return one value that can be assigned to a local variable or displayed.DataAdapterDataAdapters are MSFT’s name for a dataprovider. There are dataproviders for OLEDB, SQL Server, MySQL, and ORACLE, etc. DataAdapters are a bundle of functionality which handles a lot of the heavy-lifting of data retrieval in just a few lines of code. DataAdapters enable a Visual Studio project (windows, web, or phone) to interact with a database, usually to retrieve data, but can also be used to block-update the database, taking data from the user interface form and pushing it back to the database.You are advised to create the SQL SELECT commands in SSMS (SQL Server management studio) then after the SQL query works the way you want; then paste the query into your Visual Studio project, ensuring the SQL Statement is one long red string. The error checking in SSMS is much better than Visual Studio, so perfect your queries there OK?The DataAdapter uses its built-in command objects to execute SQL Statements at the data source to both load (fill) the DataSet with data, and reconcile changes (INSERTS, UPDATES, DELETES) made to the data in the DataTable back to the data source (insert, update, and deleting data rows).So the database is queried and data is drawn down from database tables over the network and passed to the in-memory dataset or datatable (an array that lives in the web page). Usually the retrieved data is next displayed in a gridview control. If you only wanted 1 column of data you would load it into a dropdown list.DataSetA DataSet is a set of datatables each of which is a strongly typed array (meaning that different columns can have different datatypes). Datasets are programming object that serves as an in-memory relational database which is made up of tables. A dataset can contain one or multiple datatables (each loaded by its own dataAdapter). The datatables inside the dataset can use primary and foreign keys to maintain entity and referential integrity. The dataset is a holding place for the data. The dataset then is a plan to implement an in-memory relational database, the data is normalized.Datasets are useful for facilitate data entry (the tables and columns needed for look-up values for dropdown lists are available so you don't need to constantly refer back to the database) and data editing. You can push all changes to rows back to the database tables very easily (using dataAdapter.update commands).The concept of an in-memory dataset with relationships forming a logical data model with primary and foreign keys has been brought forward into PowerPivot in Excel and PowerBI. The logical (tabular) model for the dataset allows the data to be copied into memory so that a business analyst can query it locally with a table, pivot table, pivot chart or powerview report. We do not use datasets in MIS325, but you should know what they are.As a side-note datasets are very useful in that they can hold in-memory many different types of data in them (excel, access, SQLServer, MySQL, Oracle, notepad, etc.)DataTableA datatable is an object that holds data in the memory of a webpage. We use datatables in two ways, the first of which is to store rows of transaction data (ie a sale) for later upload to the database. The transaction data rows are typically not updated, and rather are used as the dataset for later analytics.We also use datatables to store summary data for groups. For example for each girlscout troop we keep track of different measures related to cookie sales. This type of array is used to store updates and running totals, so the data changes to remain accurate.A datatable is a container used to hold data either generated in the web page or retrieved from the database. A database table refers to a table in a database. A datatable refers to an array stored in the memory of the webpage.A datatable is a strongly typed array, a programming object which serves as a two-dimensional tabular storage unit similar to an Excel spreadsheet. Datatables have columns which define the schema (column names, default values, whether NULLS are allowed, datatypes such as integers, and constraints such as primary and foreign keys). Datatables have rows which hold the data and an items collection which define the columns. You can use datatables as part of a dataset or by themselves without a dataset.DataGridView orgridviewThis is a grid looking control you place on a web form. It resembles a table of data (a spreadsheet) so use this control when you want to see many rows and many columns of data. You must set its datasource property, telling it to display a datatable.Data TypesThe different types of data that a column in a database table can hold. When you create a table in a SQL Server database, you need to choose the data type for each column. Similarly if you create a datatable in a webpage you need to assign some columns that match the data types of the data that will be stored. If you need to hold dates, then you specify the usage of a datetime column, if you need whole numbers then choose integer, numeric, etc.ItemsEach row in a datatable (a container that holds data in your webpage) has an items collection which are the columns. For example if you have a datatable with a column called “FirstName” then the first row of the datatable can be referred to as datatable.rows(0).items(“Firstname”).When you retrieve one row of data from the database for updating, the code above is the code that you could use to pull the data out of each column for the one row of data that is retrieved; for example textbox1.text = datatable.rows(0).items(“Firstname”).ParametersParametersThese are variables that are used in a SQL statements that are used to pass values from the form to the SQL statement so they can be used to insert, update, delete, or filter data. For example parameters allow program users to enter criteria into a web form and that data is transferred into the SQL SELECT statement that is run by the DataAdapter or Command. Usage of parameters adds security to data entry and retrieval systems (google SQL injection attacks to discover why parameters were invented by Microsoft). Again parameters are used to transfer user values from a webpage into a SQL Statement that is run (usually INSERT, UPDATE or DELETE). You will see parameters names @p1 or @p2, but it’s safer to use a name such as @Firstname (to match the name of the column in the database table.RowsThe data in a table is stored in its rows (aka records) collection (list of rows). The rows know what columns it has (its schema). Rows is used in both datatables and SQL Server database tables.SchemaSchemaThe structure of a table or database is referred to as its schema. The schema of a table refers to the column names, their data types (ex: integer, datetime, character, etc) and the primary and foreign keys (the relationships/constraints between the tables).?The schema of a database is a general term that refers to the tables of a database and how they connect to each other (what foreign key constraints are implemented, what look-up fields are specified).Stored?ProcedureA stored procedure is a SQL query stored in a database such as SQL Server. Stored procedures are useful in web applications in that using them add security. Rather than place the SQL query text in the code portion of the webpage or windows form (in the definition of the dataAdapter or command) the SQL query is named, and saved on the database server. Using stored procedures makes the system more secure.The web or windows form can call the stored procedure (stored query) and sends it the values from the parameters. If a web application calls stored procedures, only the numeric, date, and character values are sent across the web, not the actual SQL syntax. So there is no SQL statement to intercept. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download