Every application which deals with database needs to be connected with it and maybe your next question is What, How and in which way, So we work in two different ways with database connectivity and which deal with dynamic data needs to connect with database and there are many ways to get connected with a database, So in this blog, I am going describe you “How To Work On Connected Disconnected Environment”. I am a .
NET developer so I’ll explain the ways of doing this in .NET.As the number of users accessing application increases to access same data simultaneously which may cause network congestion and this will lead the application to crash because of data transfer between the client application and the data source. So for this kind of situation .Net introduce ADO in which there are both connected and disconnected environment and both types of connection is a difference just on the basis of connection made to the database. Here’s a diagram of both environment to give guys an overview.OverviewThe ADO.
NET environment categorized into connected and disconnected environments. A connected environment requires a constant open connection to transfer data between the client application and the data source until transfer gets completed or con.close(). However, a disconnected environment retrieves data and performs modification without a constant connection to the network, it only connects to a database when it is needed and also open and close the connection automatically.
Connected EnvironmentIn a connected environment, an application is in connected mode continuously with a data source. Connected architecture or environment was built on the classes connection, command, DataReader and transaction.When you constantly make trips to the database for any CRUD (Create, Read, Update and Delete) operation you wish to do. This creates more traffic to the database but is normally much faster as you should be doing smaller transactions.
They include Connection, Command, DataReader, Transaction, ParameterCollection, and Parameter classes.Few Queries that are used to get data from the database under connected environment:1. ExecuteReader (To get a set of data from a table)using (SqlConnection connection = new SqlConnection(connectionString)){ connection.Open(); SqlCommand command = new SqlCommand(“Select * from TableName”, connection); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Console.
WriteLine(String.Format(“{0}”, reader0)); }}2. ExecuteScalar (To get the single value from a database, below query we are getting the autogenerated ID)string sql =”INSERT INTO dbo.TableName (Name) VALUES (@Name);”+ “SELECT CAST(scope_identity() AS int)”;using (SqlConnection conn = new SqlConnection(connString)){ SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.Add(“@Name”, SqlDbType.VarChar); cmd.
Parameters”@name”.Value = newName; try { conn.Open(); ID = (Int32)cmd.ExecuteScalar(); } catch (Exception ex) { Console.WriteLine(ex.Message); }}3.
ExecuteNonQueryusing (SqlConnection connection = new SqlConnection(connectionString)){ queryString = “Select * from Employee where Deparment=’engineer'”; SqlCommand command = new SqlCommand(queryString, connection); command.Connection.Open(); command.ExecuteNonQuery();}Disconnected EnvironmentIn a disconnected environment, we can retrieve data from the data source and manipulate as per need and later reconnect with the data source to update the existing data it’s not a continuously connected with the database. The main feature of the .NET framework is Disconnected in ADO.
NET contains various classes that support this architecture. The classes are designed in a way that they automatically open and close the connection. The data is stored client-side and is updated in the database whenever required.
The ADO.NET Disconnected architecture considers primarily the following types of objects:DataSetA DataSet is on memory store data and it can hold many numbers of tables and also didn’t interact with data source directly.DataSet ds = new DataSet();Initialization of the DataSet doesn’t require parameters as the constructor of DataSet don’t have parameters. But we can overload that accepts a string used for naming the DataSet, which is used if you were to serialize the data to XML. Since that isn’t a requirement.
SqlDataAdapterIt’s the main part of disconnected environment because it manages the connection with the database only when it’s required and close the connection when a task is performed or when it’s not required. SqlDataAdapter also fills the dataset after fetching data from data source.The SqlDataAdapter holds the SQL commands and connection object to read and write data.SqlDataAdapter da = new SqlDataAdapter(“select CustomerID, CompanyName from Customers”, conn);da.Fill(ds, “Customers”);The code above creates a new SqlDataAdapter, da and also a SQL statement specifies what data will be read/write into a DataSet.
The connection object, conn is already been instantiated, but not opened. It is the SqlDataAdapter’s responsibility to open and close the connection during Fill and Update method calls.SqlConnectionA connection string contains initialization information that is passed as a parameter from a data provider to a data source and SqlDataAdapter needs the SqlConnection string to connect with a database as it contains the server name, database name, username, password etc.SqlConnection conn = new SqlConnection(“Server=(local);DataBase=Northwind;Integrated Security=SSPI”);The code above creates a new SqlConnection conn contains the local as a servername which localhost with windows authentication and database name is Northwind.SqlCommandBuilderA CommandBuilder helps to generate an update, delete and insert commands on a single table for a SqlDataAdapter. Similar to other objects, each data provider has a command builder class.SqlCommandBuilder cmd = new SqlCommandBuilder(da);The code above that the SqlCommandBuilder is instantiated with a single constructor parameter of the SqlDataAdapter da, instance.
The SqlCommandBuilder will read the SQL select statement, infer the insert, update, and delete commands, and assign the new commands to the Insert, Update, and Delete properties of the SqlDataAdapter, respectively.Implementation of Disconnected Environment
