1 / 35

Data Access

Data Access. Tonga Institute of Higher Education. Introduction. Many programs work with large amounts of data Databases are an good place to store data Programs need a way to connect to, get data from and save data to databases

iden
Download Presentation

Data Access

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data Access Tonga Institute of Higher Education

  2. Introduction • Many programs work with large amounts of data • Databases are an good place to store data • Programs need a way to connect to, get data from and save data to databases • ActiveX Data Objects .Net (ADO .Net) provides way to access data from Object Linking and Embedding Database (OLE DB)-compliant data sources such as SQL Server and Oracle.

  3. ADO .Net Components • .Net Data Providers – Objects designed for connecting to a database, executing commands and retrieving results • Connection • Command • DataReader • DataAdapter

  4. Connection Object • Connection Object – Provides connectivity to a data source • SQLConnection object – Allows connection to a Microsoft SQL Server 7.0 or later database • Located in System.Data.SQLClient namespace • OLEDbConnection object – Allows connection to an OLE DB data source • Located in System.Data.OleDb namespace • Microsoft Access is an OLE DB compliant database

  5. Connection Strings • The SQLConnection and OLEDbConnection objects both use connection strings to connect to a data source • Connection String – Provides information needed to connect to a database • You do not need to include everything in a connection string. Only include what is needed. • SQLConnection object • SQL Server • Initial Catalog=pubs;Data Source=(local);User ID=sa;password=; • OLEDbConnection object • Oracle • Provider=msdaora;Data Source=MyOracleDB;User ID=myUsername;Password=myPassword; • Microsoft Access • Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\northwind.mdb • Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\tihe1\student.mdb You don’t always need a User ID and Password Local DB Network DB

  6. Opening and Closing Connections • Before a Connection object can work with a database, the connection must be opened • Use the Open() method • After the Connection object is used, the connection must be closed • Use the Close() method

  7. Command Object • Command Object – Enables the execution of queries and return of data from a data source • A Connection object must be specified to connect to a data source • A SQL statement must be specified for execution at the data source • There are different ways to execute SQL statements • ExecuteNonQuery() – Use this when data will not be returned • ExecuteReader() – Use this when data will be returned

  8. ExecuteNonQuery Code ExecuteNonQuery does not return anything

  9. Demonstration Connections, Commands and ExecuteNonQuery

  10. DataReader Object • DataReader Object – Provides a stream of data from the data source • Useful members • FieldCount • Get<DataType> • GetByte • GetString • And more • HasItems • Item • Read

  11. DataReader and ExecuteReader Code Using a DataReader to display data from database ExecuteReader returns A DataReader Keeps looping until all rows are read Gets string from first index

  12. Demonstration Connections, Commands and DataReader.ExecuteReader Code

  13. DataSet Object • DataSet –An in-memory database • Useful members (* covered later) • Clear • Tables* DataSet

  14. DataTable Object • A DataTable object is an in-memory database table. It contains: • Rows and columns of data • Primary key, foreign key, constraint and relation information about the data • Useful members (* covered later) • ChildRelations* • Clear • Columns* • Constraints* • DataSet • NewRow • ParentRelations* • PrimaryKey • TableName DataTables

  15. DataColumn Object • A DataColumn object represents a table column • Useful members • AllowDBNull • AutoIncrement • AutoIncrementSeed • AutoIncrementStep • Caption • ColumnName • DataType • DefaultValue • MaxLength • Ordinal • Table • Unique DataColumn

  16. DataRow Object • A DataRow Object represents a row of data in a table • Useful members • Item • Table DataRow

  17. Demonstration DataSets, DataTables, DataColumns and DataRows

  18. DataTableCollection • A DataTableCollection contains DataTables • Access the DataTableCollection of a DataSet by using the Tables property • Useful members • Add • Count • Clear • Item • Remove

  19. Demonstration DataTableCollection

  20. DataColumnCollection • A DataColumnCollection contains DataColumns • Access the DataColumnCollection of a DataTable by using the Columns property • Useful members • Add • Count • Clear • Item • Remove

  21. Demonstration DataColumnCollection

  22. DataRowCollection • A DataRowCollection contains DataRows • Access the DataRowCollection of a DataTable by using the Rows property • Useful members • Add • Count • Clear • Item • Remove

  23. Demonstration DataRowCollection

  24. DataAdapter Object • DataAdapter – Used to retrieve data from a database and put it in a DataSet. Can also send changes from the DataSet to the database • Useful members • Fill • Update

  25. DataAdapter Code The Fill method opens the Connection if the connection is not already open. If Fill opened the connection, it will also close the connection.

  26. Demonstration DataAdapter Object

  27. Programmatically Creating DataSet Objects • DataSets, DataTables, DataRows and DataColumns are all objects • We can create and customize all of these objects

  28. Programmatically Creating DataSet Objects Code

  29. Demonstration Programmatically Creating DataSet Objects

  30. DBNull, Nothing and “” • There is a difference between having • No Data And • “” • In a database, use null when you have no data • In VB.Net • Use “” when you have an empty string • Use nothing when there is no data in a variable • Use dbnull when there is no data in a database field

  31. Demonstration DBNull, Nothing and “”

  32. Storing Single Quotation Marks in a Database • We often store SQL queries in a string • Dim SQL as String = “Insert into Students (FirstName) values (‘Tevita’)” • SQL queries use single quotation marks • To store a single quote in a database, use the single quote twice • Dim SQL as String = “Insert into Students (FirstName) values (‘Ma’’afu’)”

  33. Demonstration Storing Single Quotation Marks in a Database

  34. Data Access Exceptions • Often, errors occur when databases are used. • The name of the file is changed • The location of a file is incorrect • The program does not have the proper username and password to access the database • Common Data Access Exceptions • DataException - Exception that is thrown when errors are generated using ADO.NET components. • ConstraintException - Exception that is thrown when attempting an action that violates a constraint. • NoNullAllowedException - Exception that is thrown when attempting to insert a null value into a column where AllowDBNull is set to false. • ReadOnlyException - Exception that is thrown when attempting to change the value of a read-only column.

  35. Demonstration Data Access Exceptions

More Related