A RoadMap For ADO.NET

In this article I would like to show the basic steps of using specific objects of Microsoft ADO .NET. Most of them are used in typical database application for example recording customer form or show the customer information on a datagrid.

Let's think about a flowchart which shows the objects and operations, and the usage orderly.



Figure: Flowchart for choosing and using ADO .NET objects.

Firstly, connection object must be specified. Connection object specifies the server, database and the authentication information.

A typical declaration for SQL Server connection string is:

Data Source = FARUK; Initial Catalog = FCELEKTRONIK; Integrated Security = true;

In this declaration: SQL Server instance name: FARUK, Database name: FCELEKTRONIK

Secondly, perform the data operations:

In this stage we must decide how the data will be accessed. It can be direct or indirect. In other words "connected" or "disconnected".

DataSet objects are used generally in a disconnected style. A DataSet is a buffer which stores data just like a database that we navigate for data or processing data. For example create a dataset then run a SQL select statement against it. Fill the customer records to dataset then populate a datagrid from the dataset which is done by DataAdapter object.

A typical example:

[Visual C#]

            string conStr = @"Data Source=FARUK ;Initial Catalog=FCELEKTRONIK;Integrated Security=True";
            string sql = "SELECT * FROM CUSTOMERS";
            SqlConnection con = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand(sql, con);
            SqlDataAdapter dataadapter = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            con.Open();
            dataadapter.Fill(ds, "CUSTOMERS");
            con.Close();
            dataGridView1.DataSource = ds.Tables[0].DefaultView;

Note that the connection never opened and closed. Because this operations done by DataAdapter automatically.

How about the connected style? Command objects are used generally in this style. Connection is opened then a SQL Operation is done directly. For example an INSERT INTO statement. These operations are directly made on the database; that is why they are called "direct". This means database is opened, operation is done and database is closed manually.

Typical adding (insert):

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = @"Data Source=FARUK; Initial Catalog=FCELEKTRONIK ;Integrated Security=True;";
            // open connection
            try
            {
                conn.Open();
                // command - insert
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "INSERT INTO COSTOMERS (CODE, NAME, AMOUNT) " +
                "VALUES (@CODE, @NAME, @AMOUNT)";
                cmd.Parameters.Add("@CODE", SqlDbType.VarChar, 10).Value = textBox1.Text;
                cmd.Parameters.Add("@NAME", SqlDbType.VarChar, 10).Value = textBox2.Text;
                cmd.Parameters.Add("@AMOUNT", SqlDbType.Decimal).Value = textBox3.Text;
                cmd.ExecuteNonQuery(); // run the command
                MessageBox.Show("FC- successful added!");

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }

In this example Parameters are used instead of real data or textboxes. Using parameters is the way of more secure data that will be added to database.

How about the execution of a SELECT statement on a database or specifically reading data sequentially (read only)? This is generally done by the DataReader object.

Typical example is (reading data and populating listbox):

[Visual C#]

            SqlConnection con = new SqlConnection();
            con.ConnectionString =
            @"Data Source=FARUK;Initial Catalog=FCELEKTRONIK;Integrated Security=True";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            con.Open();
            cmd.CommandText = "Select * from CUSTOMERS";
            SqlDataReader dr = default(SqlDataReader);
            dr = cmd.ExecuteReader();
            while (dr.Read)
            {
                this.lstFCCustomers.Items.Add(dr.GetValue(1));
            }
            dr.Close();
            con.Close();

I hope this helps you to use ADO .NET objects.

Faruk