Hi,
NET Framework
offers you 2 possibilities to connect your front-end with the back-end (
MSSQL
or any other relational database, if having the appropriate connector):
ADO.NET
connected and disconnected environment.
1.
Connected environment relates to the following approach. In order to query the database you'll need the following classes
SqlConnection, SqlCommand, SqlDataReader
.
First you create a connection to the database:
var connection = new SqlConnection(connectionString);
Then define the command for your query (one of the INSERT/DELETE/UPDATE/READ).
SqlCommand sqlCommand = SqlCommand sqlComm = new SqlCommand("SELECT * FROM Albums")
{
CommandType = CommandType.StoredProcedure,
CommandTimeout = 60,
Connection = (SqlConnection) connection
};
Before executing the command you'll need to explicitly open the connection.
sqlCmd.Connection.Open();
And execute the command itself:
var reader = sqlCmd.ExecuteReader();
Once executed, you can iterate through all the records that are returned from the database:
while (reader.Read())
{
var album = reader[FIELD_NAME];
}
Do not forget to explicitly close the connection, as this resource can be considered very expensive one.
sqlCmd.Connection.Close();
The approach that was described above is called connected environment, because all the database operation are executed with an open connection, thus the results of the operation (E.g. INSERT/UPDATE) will be immediately seen, once the operation ends.
2. The second approach is the
disconnected environment, its here where you need define the
SqlDataAdapter
and
DataSet
objects.
A
DataSet
is an in-memory data store that can hold numerous tables (the tables and records are store in RAM, so you dont need permanently open connection to the database, in order to update/insert the data into the
DataSet
. You can define the changes in the
DataSet
, and before exiting the application, call the
Update
method on the
SqlDataAdapter
in order to make all the changes on the datasource in a bulk).
DataSets
only hold data and do not interact with a data source. The
SqlDataAdapter
is the class that manages connections with the data source and gives us disconnected behavior. The
SqlDataAdapter
opens a connection only when required and closes it as soon as it has performed its task. For example, the
SqlDataAdapter
performs the following tasks when filling a
DataSet
with data (the
Fill
method, that fills the data from the datasource. Note that in order to fill the data
DataAdapter
will need a valid Connection string and a
Command
):
a) Open connection b) Get data into
DataSet
c)Close connection
and performs the following actions when updating data source with
DataSet
changes (the
Update
method):
a) Open connection b)Write changes from DataSet to data source c)Close connection
In between the
Fill
and
Update
operations, data source connections are closed and you are free to read and write data with the
DataSet
as you need. These are the mechanics of working with disconnected data. Because the applications holds on to connections only when necessary, the application becomes more scalable. More details on this you can find:
here[
^]
The choice between disconnected and connected environment really depends upon the task that you need to perform as well as the datasource and the architecture itself.
Kind regards