Introduction to ADO.NET

1. Introduction to ADO. NET iFour Consultancy https://www.ifourtechnolab.com/aspdotnet-web-development

2. A markup language is a set of markup tags Introduction to ADO.NET : Definition • Object-oriented set of libraries that allows interaction with data sources • Commonly, the data source is a database, but it could also be a text file, an Excel spreadsheet, or an XML file. It has classes and methods to retrieve and manipulate data • It provides a bridge between the front end controls and the back end database • The ADO.NET objects encapsulate all the data access operations and the controls interaction with these objects to display data, thus hiding the details of movement of data https://www.ifourtechnolab.com/aspdotnet-web-development

3. Introduction to ADO.NET : Data Providers • Provides a relatively common way to interact with data sources, but comes in different sets of libraries for each way you can talk to a data source • These libraries are called Data Providers and are usually named for the protocol or data source type they allow you to interact with. • Table shows data providers, the API prefix they use, and the type of data source they allow you to interact with https://www.ifourtechnolab.com/aspdotnet-web-development

4. Introduction to ADO.NET : Classes • SqlConnection Class • SqlCommand Class • SqlDataReader Class • SqlDataAdaptor Class • DataSet Class https://www.ifourtechnolab.com/aspdotnet-web-development

5. Introduction to ADO.NET : SqlConnection Class • To interact with a database, connection to database is must • The connection helps identify the database server, the database name, user name, password, and other parameters that are required for connecting to the data base • A connection class object is used by command objects to know which database to execute the command https://www.ifourtechnolab.com/aspdotnet-web-development

6. SqlConnection Class - Example • Add Connection String in Web config <connectionStrings> <add name="ConnectionName" connectionString="Data Source=192.168.0.27\sql2014;Initial Catalog=DatabaseName;Integrated Security=false;UID=username;PWD=Password;" providerName="System.Data.SqlClient" /> </connectionStrings> • Create A Connection Object SqlConnection con=new SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionName").ConnectionString); con.Open(); // Write Insert/Update/Delete/Select Query con.Close() https://www.ifourtechnolab.com/aspdotnet-web-development

7. Introduction to ADO.NET : SqlCommand Class • Used to send SQL statements to the database • It uses a connection object to figure out which database to communicate with • The Command class provides methods for storing and executing SQL statements and Stored Procedures. The following are the various commands that are executed by the Command Class • ExecuteReader: Returns data to the client as rows. This would typically be an SQL select statement or a Stored Procedure that contains one or more select statements. This method returns a DataReader object that can be used to fill a DataTable object or used directly for printing reports and so forth • ExecuteNonQuery: Executes a command that changes the data in the database, such as an update, delete, or insert statement, or a Stored Procedure that contains one or more of these statements. This method returns an integer that is the number of rows affected by the query • ExecuteScalar: Returns a single value. This kind of query returns a count of rows or a calculated value • ExecuteXMLReader: (SqlClient classes only) Obtains data from an SQL Server 2000 database using an XML stream. Returns an XML Reader object https://www.ifourtechnolab.com/aspdotnet-web-development

8. SqlCommand Class – Example • Creating a SqlCommand Object • Similar to other C# objects, Instantiate a SqlCommand object via the new instance declaration, as follows: • Querying Data • When using a SQL select command, Retrieve a data set for viewing. To accomplish this with a SqlCommand object, use the ExecuteReader method, which returns a SqlDataReader object. We’ll discuss the SqlDataReader in a future lesson • The example below shows how to use the SqlCommand object to obtain a SqlDataReader object: https://www.ifourtechnolab.com/aspdotnet-web-development

9. SqlCommand Class – Example (Cont.) • Inserting Data • To insert data into a database, use the ExecuteNonQuery method of the SqlCommand object. The following code shows how to insert data into a database table: https://www.ifourtechnolab.com/aspdotnet-web-development

10. SqlCommand Class – Example (Cont.) • Updating Data • The ExecuteNonQuery method is also used for updating data. The following code shows how to update data: https://www.ifourtechnolab.com/aspdotnet-web-development

11. SqlCommand Class – Example (Cont.) • Deleting Data • Delete data using the ExecuteNonQuery method. The following example shows how to delete a record from a database with the ExecuteNonQuery method: https://www.ifourtechnolab.com/aspdotnet-web-development

12. SqlCommand Class – Example (Cont.) • Getting Single values • Sometimes need single value from database, which could be a count, sum, average, or other aggregated value from a data set • Performing an ExecuteReader and calculating the result in code is not the most efficient way to do this • The following example shows how to do this with the ExecuteScalar method: https://www.ifourtechnolab.com/aspdotnet-web-development

13. Introduction to ADO.NET : SqlDataReader Class • Many data operations require reading only a stream of data • This object allow to obtain the results of a SELECT statement from a command object • For performance reasons, the data returned from a data reader is a fast forward-only stream of data • This means pull the data from the stream in a sequential manner This is good for speed, but if need for manipulating data, then a DataSet is a better object to work with https://www.ifourtechnolab.com/aspdotnet-web-development

14. SqlDataReader Class - Example • Creating a SqlDataReader Object • Getting an instance of a SqlDataReader is a little different than the way other ADO.NET objects are initialized. Call ExecuteReader on a command object, like this: • The ExecuteReader method of the SqlCommand object, cmd , returns a SqlDataReader instance • Creating a SqlDataReader with the new operator doesn’t do anything • The SqlCommand object references the connection and the SQL statement necessary for the SqlDataReader to obtain data https://www.ifourtechnolab.com/aspdotnet-web-development

15. SqlDataReader Class – Example (Cont.) • Reading Data • The typical method of reading from the data stream returned by the SqlDataReader is to iterate through each row with a while loop. The following code shows how to accomplish this: https://www.ifourtechnolab.com/aspdotnet-web-development

16. Introduction to ADO.NET : SqlDataAdaptor Class • The data adapter makes it easy to accomplish these things by helping to manage data in a disconnected mode • The data adapter fills a DataSet object when reading the data and writes in a single batch when persisting changes back to the database • A data adapter contains a reference to the connection object and opens and closes the connection automatically when reading from or writing to the database • The data adapter contains command object references for SELECT, INSERT, UPDATE, and DELETE operations on the data • Data adapter defined for each table in a DataSet and it will take care of all communication with the database https://www.ifourtechnolab.com/aspdotnet-web-development

17. SqlDataAdaptor Class - Example • Creating A SqlDataAdapter • It holds the SQL commands and connection object for reading and writing data • The code above creates a new SqlDataAdapter, daCustomers. The SQL select statement specifies what data will be read into a DataSet • The connection object, conn, should have already been instantiated, but not opened • SqlDataAdapter’s responsibility to open and close the connection during Fill and Update method calls https://www.ifourtechnolab.com/aspdotnet-web-development

18. Introduction to ADO.NET : DataSet Class • DataSet objects are in-memory representations of data • Contains multiple Datatable objects, which contain columns and rows, just like normal database tables. Possible to define relations between tables to create parent-child relationships • Specifically designed to help manage data in memory and to support disconnected operations on data, when such a scenario makes sense • An object that is used by all of the Data Providers, which is why it does not have a Data Provider specific prefix https://www.ifourtechnolab.com/aspdotnet-web-development

19. DataSet Class - Example • Creating a DataSet Object • There isn’t anything special about instantiating a DataSet. Create a new instance, just like any other object: • Filling the DataSet • Fill method of the SqlDataAdapter: • The Fill method, in the code above, takes two parameters: a DataSet and a table name • The DataSet must be instantiated before trying to fill it with data. The second parameter is the name of the table that will be created in the DataSet • Its purpose is to identify the table with a meaningful name later on. Typically, same name as the database table is taken. However, if the SqlDataAdapter’s select command contains a join, need to find another meaningful name. https://www.ifourtechnolab.com/aspdotnet-web-development

20. Introduction to ADO.NET : Connected architecture • The architecture of ADO.net, in which connection must be opened to access the data retrieved from database is called as connected architecture • Built on the classes connection, command, datareader and transaction • This creates more traffic to the database but is normally much faster for doing smaller transactions • Example: Database Web Form https://www.ifourtechnolab.com/aspdotnet-web-development

21. Introduction to ADO.NET : Disconnected architecture • The architecture of ADO.net in which data retrieved from database can be accessed even when connection to database was closed is called as disconnected architecture • Built on classes connection, dataadapter, commandbuilder and dataset and dataview • Method of retrieving a record set from the database and storing it giving the ability to do many CRUD (Create, Read, Update and Delete) operations on the data in memory, then it can be re-synchronized with the database when reconnecting • Example: Data Adapter Database Web Form https://www.ifourtechnolab.com/aspdotnet-web-development

22. Introduction to ADO.NET : 3-Tier Architecture • A layer is a reusable portion of code that performs a specific function • This specific layer is in charge of working with other layers to perform some specific goal • Data Layer • It contains methods that helps the Business Layer to connect the data and perform required actions, whether to return data or to manipulate data (insert, update, delete) • Business Layer • A BAL contains business logic, validations or calculations related to the data. Though a website could talk to the data access layer directly, it usually goes through another layer called the Business Layer. • This ensures the data input is correct before proceeding, and can often ensure that the outputs are correct as well • Presentation Layer • Contains pages like .aspx or Windows Forms where data is presented to the user or input is taken from the user.  • The ASP.NET website or Windows Forms application (the UI for the project) is called the Presentation Layer. This is the most important layer simply because it’s the one that everyone sees and uses • Even with a well structured business and data layer, if the Presentation Layer is designed poorly, this gives the users a poor view of the system https://www.ifourtechnolab.com/aspdotnet-web-development

23. Introduction to ADO.NET : 3-Tier Architecture Example • Graphical representation of 3-Tier Architecture BLL C#.net E.g.: c=a+b; UI Asp.net MVC E.g.: a=20; B=10; Store c; DAL ADO.NET E.g.: Output c; Database E.g.: Ms Sqlserver Business Logic Layer Does all the logic operations Presentation Layer also called User Interface. Computer, Mobile phone,etc. Data Access Layer Communicate with the database https://www.ifourtechnolab.com/aspdotnet-web-development

24. Thank You.. https://www.ifourtechnolab.com/aspdotnet-web-development