Working with Data and ADO.Net

1. Working with Data and ADO.Net

2. Objectives • To understand the Underlying Technologies of Data and ADO.NET • Overview of ADO.NET • Creating Connection to the Database • Displaying data into various controls • Usage of Datagrid using Dataset • Accessing Data with DataSets • Accessing Data with DataReaders • Using SqlDataReader

3. What is ADO.NET ADO.NET is essentially a collection of .NET Classes that expose methods and attributes used to manage communication between an application and a data store. ADO.NET is set to become a core component of any data driven .NET application or Web Service.

4. ADO.NET ADO.NET is Microsoft Latest Data Access Technology to retrieve, manipulate and update data in many different ways. ADO.NET is an integral part of .NET Framework that’s why it shares many of its features such as multi-language support, garbage collection, object oriented design etc.

5. ADO.NET • It is a model used by .NET applications to communicate with a database for retrieving, accessing, and updating data. • It is a set of classes • Can be used with any .NET language • Can handle Disconnected Data Stores • Can interact with XML

6. Step 3: Close the Connection Step 1: Connect to database Step 2: Access data Database What is Connected Architecture?

7. Step 4: Manipulate data at Local Copy Step 2: Retrieve data and store it locally Step 3: Disconnect Step 1: Connect Database DataSet Step 5: Connect, Update the database with changes in Local Copy, Disconnect Introduction to Disconnected Architecture

8. ADO.NET Object Model DATA PROVIDER Establishes connection with the database CONNECTION Accessing retrieved data .NET APPLICATION (WINDOWS/ WEB FORM) Retrieves data in a read-only, forward only mode DATA READER Executes a command to retrieve data from the database COMMAND DATABASE Accessing retrieved data Transfers data to the dataset and reflects the changes made in the dataset to the database DATASET Filling datasetwith data DATA ADAPTER

9. Architecture of ADO.NET ADO.NET architecture consists of three main components: 1. Data Provider 2. DataSet 3. DataView

10. DataSet DataView DataRelation DataTable DataTable Data Column Data Column Data Column Data Row Data Row Data Provider DataReader DataAdapter Command DB Connection Architecture of ADO.NET UI `

11. Earlier Scenario of Database Access Native API Win Application ADO RDO DAO ODBCDirect Declare SQL Server ODBC Manager ODBC Driver Oracle OLE DB Provider MSDAORA SQLOLEDB

12. Data Providers The link to the database is created and maintained by the Data Provider. A Data Provider is not a single component, but a set of related components that work together. The components of Data Provider are followings: 1. Connection 2. Command 3. DataReader 4. DataAdapter

13. .NET Data Providers .NET Application ADO.NET MS-SQL System.Data.SqlClient SqlDataProvider Layer System.Data.OracleClient OracleDataProvider Oracle Layer System.Data.OleDb OledbDataProvider System.Data.Odbc OdbcDataProvider Layer OLE DB Data source ODBC Data source

14. Components of Connected Architecture • SqlConnection for establishing connection with the database • SqlCommand for executing a command to retrieve data from the database • SqlDataReader for retrieving data in a read-only and forward-only mode

15. Connection • To work with database connection • Few Properties • ConnectionString is a string that represents the properties of a database connection • State ( Read Only ) provides the current state of the connection • ConnectionTimeout ( Read Only ) • Database ( Read Only ) • DataSource ( Read Only ) • Few Methods • Open() • Close()

16. ConnectionString • SqlConnection connection = new SqlConnection( “Initial Catalog=pubs; User ID=sa;Password=; Data Source=localhost”); • SqlConnection connection = new SqlConnection( “Initial Catalog=pubs; integrated security=SSPI; Data Source=localhost”); • Integrated security uses the current windows OS user name and password to connect to database server.

17. Data Base Command • To interact with database using SQL queries and Stored procedure • Few Properties • CommandText • Connection • CommandType • CommandTimeout • Parameters

18. Data Base Command • Few Methods • int ExecuteNonQuery() for INSERT, UPDATE, DELETE and CREATE queries. • SqlDataReader ExecuteReader() for retrieving multiple rows from the database • object ExecuteScalar() for retrieving single data from database

19. Command Command object can be used to execute SQL Commands and Stored Procedures. The command object can run all type of SQL statements SELECT, INSERT, UPDATE, DELETE etc. .NET provides two types of Command classes: 1. SqlCommand 2. OleDbCommand

20. Creating a Command Dim sql As String = “SELECT * FROM Products" Dim myCommand As New SqlCommand( sql, myConn ) Or Dim myCommand As New SqlCommand() myCommand.CommandText = “SELECT * FROM Products”

21. Running a Command Command object provides three methods that are used to execute commands on the database: ExecuteNonQuery() – Execute commands that have no return value such as DELETE, INSERT, UPDATE. ExecuteScaler() – Returns a single value from a database query. ExecuteReader() – Return a DataReader object which is a result set returned by SELECT statement.

22. Running a Command DimmyConn AsNew SqlConnection()myConn.ConnectionString= "Data Source=localhost; Integrated Security=SSPI; Initial Catalog=Northwind"; DimsqlAs String = “SELECT * FROM Products" Dim myCommand As New SqlCommand( sql, myConn ) myConn.Open() SqlDataReader result = myCommand.ExecuteReader() myConn.Close()

23. DataReader • Data reader • Is used to retrieve data from a data source in a read-only and forward-only mode. • Stores a single row at a time in the memory. • Few Properties • FiledCount gets the total number of fields or columns in the row retrieved. • HasRows is used to check if data exists.

24. DataReader • Few methods: • Read() is be used to read the next available row in the result and will return true if row is available else false • Close() is used to close the DataReader. • NextResult() is used for Batch Transaction queries. it will advance the DataReader to the next query’s result.

25. Constructing SqlCommand object with SQL query SqlCommand cmd = new SqlCommand( “SQL Query ", ConnectionObject ); SqlCommand cmd = new SqlCommand(); cmd.CommandText = “SQL Query “; cmd.Connection = ConnectionObject;

26. ExecuteNonQuery() Method • int ExecuteNonQuery() - to execute commands that return no records , such as INSERT, UPDATE, DELETE, CREATE statements. • It returns the number of rows affected. • SqlCommand cmd = new SqlCommand("Update authors SET City=‘Noida‘ ", connection ); • int RowAffected = cmd.ExecuteNonQuery(); Note: Before calling the Execute methods of Command, The connection must be opened.

27. ExecuteScalar() Method • object ExecuteScalar() - to retrieve single value. • If more rows are retrieved from database, the value from the first column of the first row will be returned. • SqlCommand cmd = new SqlCommand("SELECT count(*) FROM authors",connection ); • int RowCount = (int) cmd.ExecuteScalar();

28. ExecuteReader() Method • SqlCommand cmd = new SqlCommand("SELECT * FROM authors", connection ); • SqlDataReader reader = cmd.ExecuteReader(); • The Read() method of DataReader can be used to access the results row by row. To access every single row, the Read() method has to be invoked. • The column values can be accessed as • reader.GetString(0) - returns .NET string type value • reader.GetSqlString(0) - returns SqlDB string type value • reader["au_fname"] – return object type. • The reader has to be closed. Otherwise the connection can not be used for further database interaction.

29. Multi-Query as CommandText SqlCommand cmd = new SqlCommand( “SELECT Query1;SELECT Query2;SELECT Query n ", ConnectionObject ); do { while( reader.Read() ) // Move to next row, if available. { // Access the current row data } } while( reader.NextResult() ); // Move to next query’s result, if available.

30. MyEmployee Table

31. Connected Architecture – Update Query SqlConnection connection = new SqlConnection("Initial Catalog=pubs;User ID=sa;Password=;Data source=localhost"); connection.Open(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = “Update myEmployee set salary=50000”; cmd.Connection = connection; int rowAffected = cmd.ExecuteNonQuery(); connection.Close(); /* The other types of query applicable in this scenario INSERT, DELETE, CREATE TABLE */

32. Connected Architecture – Query with Aggregate function SqlConnection connection = new SqlConnection("Initial Catalog=pubs;User ID=sa;Password=;Data Source=localhost"); connection.Open(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = “SELECT sum( salary) from myEmployee”;cmd.Connection = connection; int TotalSalary = (int) cmd.ExecuteScalar(); connection.Close();

33. Connected Architecture – Retrieving set of rows /* Create connection and cmd objects as it was done in the previous slides */ SqlDataReader reader = cmd.ExecuteReader(); while( reader.read() ) { int eid = (int) reader.GetSqlInt32( 0 ); string name = (string) reader.GetSqlString( 1 ); double salary = (double) reader.GetSqlDecimal( 2 ); } reader.Close(); connection.Close();

34. Connected Architecture – Retrieving set of rows /* Create connection and cmd objects as it was done in the previous slides */ SqlDataReader reader = cmd.ExecuteReader(); while( reader.read() ) { int eid = (int) reader[“Eid”]; string name = (string) reader[“Ename”]; decimal salary = (decimal) reader[“salary”]; } reader.Close(); connection.Close();

35. Database Components of Disconnected Architecture • DataSet – Local database in the application • SqlDataAdapter – interacts with database for DataSet • The communication for data between database and DataSet happens through DataAdapter • Initially, the data are retrieved from database and loaded into DataSet. • After the business logic has processed the data in the DataSet, only the modified data are updated in the database. DataAdapter DataSet

36. DataColumnCollection ConstraintCollection DataRowCollection The General Object Model of DataSet DataSet DataTableCollection DataTable DataRelationCollection

37. DataAdapter The DataAdapter serves as a bridge between a DataSet and data source for retrieving and saving data. DataAdapter provides four properties that represent database commands: 1. SelectCommand 2. InsertCommand 3. DeleteCommand 4. UpdateCommand

38. The DataAdapter use: Fill() method to load data from the data source into the DataSet Update() method to send changes made in the DataSet back to the data source. .NET provides two types of DataAdapter classes: 1. SqlDataAdapter 2. OleDbDataAdapter

39. DB DataAdapter SelectCommand DeleteCommand InsertCommand Update( ) UpdateCommand DataSet Fill( )

40. DataSet The DataSet acts as a container in which all the data coming from the database is dumped for the local machine to use and manipulate. DB DataSet

41. DataSet ADO.NET uses the disconnected approach using the DataSet. Connection Break  DB DataSet

42. DataSet The DataSet is explicitly designed for data access independent of any data source. Oracle Access XML File SQL Server 2000 DataSet

43. DataSet The database is then later updated back from the DataSet. Data Updated DB DataSet

44. The DataSet contains a collection of one or more DataTable objects, DataTables are similar to tables or relations in DBMS. A DataTable consists 0 or more DataColumn and DataRow objects. DataSet DataColumn DataTables DataRow

45. DataSet Tables DataTable Columns DataColumn Constraints DataConstraint Rows DataRow Relations DataRelation DataSet also has DataRelation object which maintain relationship between two or more tables in DataSet just like Database. DataTable object in DataSet has DataConstraint object which apply Primary Key and Foreign Key Constraint on DataColumns in DataSet just like Database.

46. DataRelation and DataConstraint Emp_ID Name Salary Date Order_ID Date Amount Emp_ID DataConstraint (Emp_ID) DataTable (Employee) DataRelation DataTable (Order) DataConstraint (Emp_ID)

47. DataSet Another Feature of DataSet is that it tracks changes that are made to the data it holds before updating the source data. DataSet are also fully XML featured. They contain methods such as GetXml( ) and WriteXml( ) that respectively produce and consume XML data easily. Due to XML support ADO.NET DataSet can be used in n-tier architecture.

48. Presentation Tier Windows Forms MyApp.Exe DataSet Business Tier Data Tier InternetIntranet Web Forms Data Object (Class) IE Data Adapter DataSet XML DataSet Data Adapter Business to Business (BizTalk, for example)

49. DataSet Life Cycle 1. Extract tables from database and store it in DataSet. 2. Setup Relationship between Tables in DataSet. 3. Make changes to Data. 4. Update database from DataSet changes.

50. Connected Operations Extracts tables With Managed Provider Disconnected Operations Resolves changes with Managed Provider Sets up relationships Makes changes to data DataSet Table1 Original Database Table2 DataSet DataSet Table1 Table1 Table2 Table2