Working in a Connected And Disconnected Environment: Part 1

Sudhakar Chaudhary

ADO.NETADO.NET tutorialsConnected Environment in ADO.NETDisconnected Environment in ADO.NET

4 Comments

Join the conversation! Your thoughts help the community grow.

Sudhakar Chaudhary14y agoedited
hi dear, you can simple using for loop save your data from gridview to table. here is example..any problem mail me. [email protected] using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.SqlClient;namespace frontind {public partial class Form1 : Form{public Form1(){InitializeComponent();}private void Form1_Load(object sender, EventArgs e){dataGridView1.Columns.Add("", "Name");}private void buttonToGridView_Click(object sender, EventArgs e){int row = dataGridView1.Rows.Count;dataGridView1.Rows.Add();dataGridView1.Rows[row - 1].Cells[0].Value = textBox1.Text;}private void buttonToDatabase_Click(object sender, EventArgs e){try{string connectionString = "Data Source=.\\Sqlexpress;Initial Catalog=democorner;Integrated Security=True";SqlConnection cn = new SqlConnection();cn.ConnectionString = connectionString;for (int i = 0; i < dataGridView1.Rows.Count; i++){if (cn.State == ConnectionState.Closed){cn.Open();}MessageBox.Show(dataGridView1.Rows[i].Cells[0].Value.ToString());SqlCommand cmd = new SqlCommand("insert into table1(fname) values('" + dataGridView1.Rows[i].Cells[0].Value.ToString() + "')", cn);cmd.ExecuteNonQuery();}MessageBox.Show("Record Saved...");}catch (Exception) { }}} }
Sharad Gupta14y ago
If i am inserting a value by textbox ,into gridview in frontend, then how can i save these value in database parmanently.
Sudhakar Chaudhary14y agoedited
No. You use datasets if you want to work with a set of tables and rows while disconnected from the data source. dataset designed for disconnected architecture. dataset stored data in xml format so it database independent.
Sam Hobbs14y ago
Are the DataSet and DataTable classes ever used for connected access?

A challenge related to data-centric applications is that multiple users need to access data simultaneously from the data source. As the number of users accessing an application increases the network becomes slow and might lead to network congestion. Therefore, some of the factors that decide the type of data access environment used for an application include the number of users who intend to use the application and the functionality of the application.

The ADO.NET environment can be categorized into connected and disconnected environments. A connected environment requires a constant connection to transfer data between the client application and the data source. However, a disconnected environment retrieves data and performs modification without a constant connection to the network. Here I am not discussing the advantages and limitations of these.

The following figure shows the classes related to connected and disconnected environments in ADO.NET:

As can be seen from the preceding diagram, the ADO.NET class hierarchy can be categorized as disconnected and connected classes. Connected classes consist of DataAdapters, DataReaders, Connection and Command objects. Disconnected classes consist of DataSets and DataTables.

Working in a Connected Environment

In a connected environment, an application is continuously connected to a data source. In this, a DataReader object is used. A DataReader object, which is a component of a data provider, uses the Connection object to connect to the database. It then uses the Command object to retrieve data, and provides data to the application in a read-only and forward-only mode.

Working with Command Objects

A Command object is specific command that is used to manipulate data in a data source. It is an object representing a DML statement or a stored procedure that is used to insert, delete, or modify data in a data source. A data command is an instance of a SqlCommand class.

To retrieve data by using data commands, first a Connection object is created to connect to the database from where data is to be retrieved. Then, a Command object is created. To access a data source, a data command should provide information about the connection for communication with the data source.

The two types of operations performed by a command object to retrieve and modify data in a data source are :

Synchronous operations
Asynchronous operations

Performing Synchronous Operation Using Command Objects

During synchronous operation the command objects are linked to each other. Executing command objects synchronously result in a sequential execution, where each database command must complete before the next command is executed. For example, when one command object is deleted, the command object linked with the first command object is also deleted.
Synchronous operations are performed using the following command objects:

DbCommand object
DbParameters object
DbDataReader object

DbCommand Object

The DbCommand object is used to execute a command in a data source. The DbCommand object can be a Data Manipulation Language (DML) command, which is used to retrieve, insert, update or delete data in the database.

To create a DbCommand object, you need to create a DbConnection object that will generate a valid open connection to the data source.

To execute the command, the CreateCommand () Method is called. The CreateCommand () method creates and returns a DbCommand object associated with the DbConnection object.
The CreateCommand () method allows the DbConnection object to automatically create the appropriate provider-specific DbCommand object. In addition the CommandText and Commandtype Properties of the DbCommand object are also specified.

Properties of DbCommand object

CommandText
CommandTimeout
CommandType
Connection
Container
DesignTimeVisible
Parameters
Site
Transaction
UpdatedRowSource

Methods of DbCommand object

Cancel ()
CreateObjRef ()
CreateParameter ()
Dispose ()
Equals ()
ExecuteNonQuery ()
ExecuteReader ()
GetHashCode ()
GetLifeTimeService ()
GetType ()
InitializeLifeTiimeService ()
Prepare ()
ReferenceEquals ()
ToString ()

Consider an example, where a Windows form needs to be created to display the product record data having a record value equal to 750. This data will be fetched, by sending a SQL command to the database with the help of the DbCommand object. The code for creating DbCommand object is as follows:

//Createing a connection to the AdventureWorks database
string connectionString = "Data Source=.\\Sqlexpress;Initial Catalog=AdventureWorks;Integrated Security=True";
SqlConnection cn = new SqlConnection();
cn.ConnectionString = connectionString;
cn.Open();

//Call the CreateCommand method
SqlCommand cmd = cn.CreateCommand();
cmd.CommandType = CommandType.Text;

//Execute teh SQL Query
cmd.CommandText = "Select * from Production.Product Where RecordPoint=750";

//Execute the SQL Query Against the connecion object
cmd.ExecuteNonQuery();

In the preceding code, the SqlCommand executes the SQL query. The CommandText property of the SqlCommand contains the SQL query. The CommandType property of the SqlCommand calls the SQL query.

You can find the full source code of this series of articles in the last article.