Development ADO.NET.txt

The flashcards below were created by user mateotete on FreezingBlue Flashcards.

  1. What is ADO.NET?
    ADO.NET is a set of computer software components that programmers can use to access data and data services based on disconnected DataSets and XML. It is a part of the base class library that is included with the Microsoft .NET Framework. It is commonly used by programmers to access and modify data stored in relational database systems, though it can also access data in non-relational sources.
  2. What two namespaces DLL's are required to work with ADO.NET?
    System.Data.dll and System.Data.SQLClient.dll
  3. What are the two main components of ADO.NET? Explain.
    They are the .NET Framework data providers and the DataSet.
  4. What is a .NET Framework Data Provider?
    Providers are components that have been explicitly designed for data manipulation and fast, forward-only, read-only access to data. The Connection object provides connectivity to a data source. The Command object enables access to database commands to return data, modify data, run stored procedures, and send or retrieve parameter information. The DataReader provides a high-performance stream of data from the data source. Finally, the DataAdapter provides the bridge between the DataSet object and the data source. The DataAdapter uses Command objects to execute SQL commands at the data source to both load the DataSet with data and reconcile changes that were made to the data in the DataSet back to the data source.
  5. What are the four (4) core objects that make up the .NET Framework Data Provider?
    (a) Command object (b) Connection object (c) DataReader object (d) DataAdapter object
  6. What is a Connection Object?
    Establishes a connection to a specific data source.
  7. What is a Command Object?
    Executes a command against a data source.
  8. What is a DataReader?
    Reads a forward-only, read-only stream of data from a data source, caching and reading each record one at a time for fast speeds. Because of this caching, the reader can't go back because the record no longer exists in cache.
  9. What is a DataAdapter?
    Object used to populate a DataSet and resolves updates with the data source. It provides the means for
  10. What is a DataSet?
    The DataSet object is central to supporting disconnected, distributed data scenarios with ADO.NET. The DataSet is a memory-resident representation of data that provides a consistent relational programming model regardless of the data source. It can be used with multiple and differing data sources, with XML data, or to manage data local to the application. The DataSet represents a complete set of data, including related tables, constraints, and relationships among the tables. An ADO.NET DataSet contains a collection of zero or more tables represented by DataTable objects. The DataTableCollection contains all the DataTable objects in a DataSet.
  11. What are the following acronyms: ADO, DAO, RDO, ODBC, OLEDB
    ActiveX Data Object (ADO), Data Access Objects (DAO), Remote Data Objects (RDO), Open Data Base Connectivity (ODBC), and Object Linking and Embedding Data Base (OLE DB).
  12. Provide the connection String object for a NorthWind database on a server named "Server1"; do the same for the local machine.
    Data Source=Server1;Initial Catalog=NorthWind;User ID=Doman\UserName;Password=Password; or Data Source=(local);Initial Catalog=NorthWind;Integrated Security=SSPI;
  13. Write a connection object in the web.config file under the connection name: Leads
  14. What's the difference between a DataAdaptor, DataReader, and a DataSet?
    A DataSet object is a memory-resident representation of data which holds the schema and tables of a relational database, including relationships, constraints, and keys. A DataAdapter is an object designed to speak with the database, it is used by a DataSet or a DataReader to interact with the database and get the requested information, much like a bridge between those two objects. A DataReader is simply a light weight reader of a single table of data, reading simply one-way, caching each record one at a time, then moving onto the next record, it has no memory persistence and as such, is much quicker than a dataset.
  15. What is the main differences between ADO and ADO.NET?
    ADO is ActiveX Data Objects, an older framework than ADO.NET framework based on COM objects. With ADO.NET, communication is XML based, meaning it is strongly typed and can be disconnected, allowing a table to be downloaded and worked on the client's machine, in a stateless mode, meaning I can treat it as a separate in-memory database where I can play with several tables (relationships, keys, constraints, etc) and changes locally before posting changes, if needed.
  16. Give me the acronym timeline of database types.
    First, there was DAO created for local databases with Jet engine (Excel and others), then moved to RDO for remote machines, then to ADO which works with client-server capabilities on a connected recordset. Finally, a move was made to ADO.NET which is unconnected, freeing up the database to work faster and more efficiently and allowing the client to work with his/her own mini-database in memory, meaning it was more fluid and faster, and only needed to touch the database when retrieving or updating information.
  17. Show syntax to query three or more different tables from a DataSet.
    While (reader.Read()) { Response.Write("User Name: " + reader["Name"].ToString()) } reader.NextResult(); Then keep doing this for each table.
  18. Write code to place a web.config connection string into code behind sqlConnection string AND connect to a datagrid using binding.
    • SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["connLeads"].ToString());
    • DataAdapter da = new SqlDataAdapter("select * from Leads", conn); DataSet ds = new DataSet(); ds = da.fill(ds); GridView.DataSource = ds; GridView.DataBind();
  19. What are the three main types of sqlCommand methods? Explain.
    (a) ExecuteNoneQuery used to update the database and return a value representing number of rows affected, (b) ExecuteReader used to retrieve rows and columns returning a sqlDataReader object, and (c) ExecuteScalar is useful for returning a single value.
  20. How do you design a function which access a database, specifically for closing connections and checking for errors?
    (a) objects created outside of try, catch, finally block (b) use try, catch, finally block with catch looking for errors (c) finally will check if DataAdaptor is open, close it if not, then the same is done to close the sqlConnection object.
  21. What's the difference between optimistic and pessimistic locking?
    Pessimistic locking: On updating the data, the record gets locked and no one else can access that record for updating. It becomes a read-only record till the lock is released. Once the lock gets released, the record can be locked again and get updated for a different user. Optimistic locking: This allows multiple user to open up the same record for updating. Record gets locked only while updating the record. This is the most preferred way of locking for the web application.
  22. How can we determine if changes have been made on a DataSet after it has been loaded?
    Use the DataSet.HasChangs() function. Once changes have been identified, you can search each row of the dataset to determine which records was modified using DataRowState.Modified().
  23. Write code to query the database using a parameterized stored procedure and read through its contents.
    SqlConnection conn = new SqlConnection(connLeads);SqlCommand cmd = new SqlCommand(); SqlDataReader reader; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = conn; cmd.CommandText = "usp_GetLeads";cmd.Parameters.Add("@State", SqlDbType.Char, "CA"); try { conn.Open(); reader = cmd.ExecuteReader(); While (reader.Read()) { Console.WriteLine(reader["Firstname"]); } catch { } finally { if (reader != null) { reader.Close(); } if (conn != null) { conn.Close();} }
  24. Write code needed to check a return value using a parameterized stored procedure, making sure that it worked correctly.
    SqlParameter returnValue = cmd.Parameters.Add("return_value", SqlDbType.Int); returnValue.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(returnValue);
  25. What can of errors can occur when you do an insert into a database?
    (a) connection issues, (b) error within the stored procedure such as invalid column name, (c) record being updated may have already been updated previously.
  26. How do you handle a transaction in SQL?
    First, create a label name for the transaction, then use BEGIN @TransName; do the work you need and when ready, run COMMIT @TransName; if an error occurs, then use the ROLLBACK TRAN label to rollback all changes and send an error code back to user.
Card Set:
Development ADO.NET.txt
2014-04-01 18:23:33
Software Development ADO NET
Software Development ADO.NET
Software Development ADO.NET
Show Answers: