1 / 30

Embedded SQL

Embedded SQL. Data connection DataReader DataSet Bonus info: Concurrency and Database Transactions. Architecture. We need something to make it fit together!. OO programme. DB Server. DBMS. ?????. DB. SQL. Table. Client. SQL Management Tool. Architecture. Client – OO programme.

calder
Download Presentation

Embedded SQL

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. Embedded SQL Data connection DataReader DataSet Bonus info: Concurrency and Database Transactions FEN 2014-05-01

  2. Architecture We need something to make it fit together! OO programme DB Server DBMS ????? DB SQL Table Client SQL Management Tool FEN 2014-05-01

  3. Architecture Client – OO programme DB Server DBMS DB Access Layer DB SQL FEN 2014-05-01

  4. DBConnection • Connection: • Open Connection • Execute DB operations • Close Connection • Working on actual (live) data • Other applications can not access data. FEN 2014-05-01

  5. Overview of using databases • 4 steps: • Open connection to database • Execute SQL for updating DB or fetching records (rows in the result table) • Handle data • Close connection FEN 2014-05-01

  6. Connection Step 1: Open Connection • Connection are opened according to connection string info • here is a connection to a MS SQL Server database opened • The database is located here: @"Data Source=NO122065\MSSQLSRV2012;” (my SQL Server) • (‘@’ means that escape characters are discarded) • Name of the database: Initial Catalog=MiniBankAK // Create and open a connection. SqlConnectioncn = newSqlConnection(); cn.ConnectionString= @"Data Source=NO122065\MSSQLSRV2012;” +”Initial Catalog=MiniBankAK;Integrated Security=True"; cn.Open(); ShowConnectionStatus(cn); connection See ado\ReaderExample01 FEN 2014-05-01

  7. Connection Strings • Connection strings are product specific (DB specific) and often very well-documented. • Help may be found at: • www.connectionstrings.com • www.able-consulting.com/ADO_conn.htm • If you are on the .NET platform using Visual Studio and MS SQL Server, VS can help. See ado\ReaderExample01 FEN 2014-05-01

  8. Short exercise: • Change the connection string in ado\ReaderExample01, so that the example works on our PC. FEN 2014-05-01

  9. Two ways of DB access • Connected: • Open connection. • Read/Write access (select, insert, update and delete) using a Command object. • When reading (select) a DataReader object is returned. A DataReader is an iterator (cursor) into the result table. • Close Connection. • Disconnected: • Fill a DataSet object (a copy of a part of the database) using a DataAdapter. • DataAdapter wraps SQL-statement(s). • A DataSet object contains DataTable objects. • DataTable objects contain collections of rows and columns. FEN 2014-05-01

  10. Step 2: Get / fetch data from the database • Data are fetched by creating a Command object and use it to execute a SQL statement. • Data can be stored in either a DataReader object or a DataSet object. • Using DataReader the result table lives server side, and you look at live data. • Using DataSet the result table(s) is (are) copied to the client, and live data may be changed by other users. FEN 2014-05-01

  11. Connection vs. Connectionless • Connection: • Open Connection • Execute DB operations • Close Connection • Working on actual (live) data • Other applications can not access data. FEN 2014-05-01

  12. Connection vs. Connectionless • Connectionless: • Create a copy of a part of the database • Execute DB operations on the copy • Other applications may change date • The copy may be come inconsistent. • Data are changed in the local copy: • at update it is checked if the data in the database have been modified by others • in that case the update is rejected (ConcurrencyException). FEN 2014-05-01

  13. Differences betweenDataReaderand DataSet/DataAdapter • DataReader can only be used for reading data. • It can only be traversed once (forward). • DBCommand can update the database by ExecuteNonQuery. This update is executed immediately. • DataAdapter is the connection between DataSet and database. • Data are fetched to the DataSet, might be modified and sent back to the database. • Updates are executed on a local copy. Concurrency problems must be handled. • Possible to traverse forward and backward. • A DataSet can contain multiple tables. • We will focus on DataReader. FEN 2014-05-01

  14. data reader record record record Step 2: Get records using DataReader • Get records via SQL Select query • read-only access to the database stringstrSQL = "SELECT * FROM Customer"; SqlCommandmyCommand = newSqlCommand(strSQL, cn); SqlDataReadermyDataReader; myDataReader = myCommand.ExecuteReader(); FEN 2014-05-01

  15. What is achieved so far? • We have created a connection to a database. • The connection is placed in the connection object. • We have done a search by using a SQL-statement. • The search was executed by using a command object. • The result of the search was stored in a DataSet or as here a DataReader object. • Now it is possible to get the data from this object for viewing, passing on to client or handle in other ways. FEN 2014-05-01

  16. data reader record record record Step 3:Getting the result and handle data • Loop through the result table row by row. • For each row: get the attribute value: SqlDataReadermyDataReader; myDataReader = myCommand.ExecuteReader(); // Loop over the results. while (myDataReader.Read())//while(more rows) { Console.WriteLine("Custno: {0}, Name: {1} ", myDataReader.GetInt32(0), myDataReader.GetString(1).Trim()); } FEN 2014-05-01

  17. Step 3:Getting the result and handle data • This is very flexible, one need to the details of the result table. • This is more flexible: // Loop over the results. while (myDataReader.Read())//while(more rows) { //Display each attribute in current row for (inti = 0; i < myDataReader.FieldCount; i++) { Console.Write("{0} = {1} ", myDataReader.GetName(i), myDataReader.GetValue(i).ToString().Trim()); } Console.WriteLine(); } See ado\ReaderExample02 FEN 2014-05-01

  18. Step 4: Close the connection • DataReaders work on live data, locking other user out, so remember: • Close DataReader and connection when you are done: myDataReader.Close(); cn.Close(); FEN 2014-05-01

  19. Short exercise: • Change the connection string in ado\ReaderExample02, so that the example works on our PC. • Change the example (connections string and SQL statement), so the VW-database is accessed and this SQL query (query 4) is executed: selectdistinctmodel fromCar,spc whereCar.cNo=spc.cNoandsNo='s1' FEN 2014-05-01

  20. Building objects • You can do more than just printing the result. • For instance, assume that you have this class: publicclassCustomer { privateintcustNo; privatestringname; privateList<BankAccount> accounts; publicCustomer(intcNo, string n) { this.custNo= cNo; this.name= n; accounts= newList<BankAccount>(); } //--- FEN 2014-05-01

  21. Building objects • Then objects may build from the database: SqlDataReadermyDataReader; myDataReader= myCommand.ExecuteReader(); List<Customer> customers = newList<Customer>(); // Loop over the results. // Create objects and add them to a list while(myDataReader.Read())//while(more rows) { intcustNo = myDataReader.GetInt32(0); stringcustName = myDataReader.GetString(1).Trim(); Customer c = newCustomer(custNo, custName); customers.Add(c); } See ado\ReaderExample03 FEN 2014-05-01

  22. Building objects • Then the connection may be closed, and you can use the objects in your application: Console.WriteLine(); Console.Write("Close connections?"); Console.ReadLine(); Console.WriteLine(); myDataReader.Close(); cn.Close(); Console.WriteLine(); Console.Write("Show customer list?"); Console.ReadLine(); foreach(Customerc incustomers) Console.WriteLine(c); Console.WriteLine(); DB Connection is closed Objects areused as POCOs (”Plain Old C# Objects”) See ado\ReaderExample03 FEN 2014-05-01

  23. Updating the database • Update (or insert or delete): //get new value Console.Write("Entercustomernumber: "); intcustNo = Convert.ToInt32(Console.ReadLine()); Console.Write("Enter new customer name: "); stringcustName = Console.ReadLine(); cn.Open(); //Build SQL statement: strSQL= "UPDATE Customer SET name = '" + custName + "' WHERE custNo = " + custNo; myCommand= newSqlCommand(strSQL, cn); myCommand.ExecuteNonQuery(); cn.Close(); ExecuteNonQuery See ado\UpdatingReader FEN 2014-05-01

  24. Bonus info: Database Transactions • A database normally has many users at the same time (concurrency): • My bank account: I am at my wine merchant trying to buy 12 bottles of Chambertin using my card. At the same time my wife using home banking is trying to move my money to our savings account. Who wins? • Many database operations requires more than SQL statement to be executed: • Transferring an amount from my account to our savings account: the amount must be withdrawn from my account (one SQL update) and inserted to our savings account (another SQL update). • We want both update to complete (or non), but nor just one of the updates. • This is known as an transaction: • Both updates or non! FEN 2014-05-01

  25. Bonus info: Database Transactions • ACID: • Atomic • Consistent • Isolation • Durability • Problems: • lost update • “dirty read”, uncommitted dependency, temporary update • inconsistent analysis (incorrect summary) • Synchronization is needed FEN 2014-05-01

  26. Bonus info: Database Transactions • T2: Deposits M DKK on account Y: • read_item(Y); • Y:= Y+M; • write_item(Y); T1: Transfers N DKKs from account X to account Y: read_item(X); X:= X-N; write_item(X); read_item(Y); Y:= Y+N; write_item(Y); time Any possible problems? FEN 2014-05-01

  27. Bonus info: Lost Update FEN 2014-05-01

  28. Bonus info: Dirty Read (Uncommitted dependency) FEN 2014-05-01

  29. Bonus info: Inconsistent Analysis FEN 2014-05-01

  30. Database Transactions:C#/ADO.NET/MS SQL Server ado\BankTransactions FEN 2014-05-01

More Related