A Closer Look at ADO.NET: The Connection Object - The Example
(Page 2 of 5 )
Copy the following code and paste it into your VS.NET class file, then run the application.
using System;
// reference to the namespace that contains most
// of the classes that form the ADO.NET Architecture
using System.Data;
// referene to the namespace of the SQL Server .NET Data provider
using System.Data.SqlClient;
namespace AdoApp
{
class Class1
{
static void Main(string[] args)
{
SqlConnection SqlConn1 = new SqlConnection();
SqlConn1.ConnectionString = "Server=MichaelServer;Database=pubs;" +
"Integrated Security=true";
// We use the Open() method to establish the connection
SqlConn1.Open();
SqlCommand SqlComm1 = new SqlCommand();
SqlComm1.Connection = SqlConn1;
SqlComm1.CommandText = "Select * from titles";
SqlDataReader SqlReader = SqlComm1.ExecuteReader();
while(SqlReader.Read())
{
Console.Write(SqlReader["title_id"]);
Console.Write("--");
Console.Write(SqlReader["title"]);
Console.WriteLine();
}
// here we call the Close() method to close the Connection
SqlConn1.Close();
Console.ReadLine();
}
}
}
You will get the following results to the console when you run this code example. Note that you need to have SQL Server installed in order to run this example.
In the above example, we have connected to the database pubs and issued a SQL Query (Select * from titles); we then printed out the results. For the purpose of our article I have instantiated two more objects in order to get the above results (to illustrate that we have established a connection to the database), but we will not discuss those objects in detail here. We will do that in another article. Let's take a look at the SqlConnection instantiation code:
SqlConnection SqlConn1 = new SqlConnection();
SqlConn1.ConnectionString = "Server=MichaelServer;Database=pubs;" +
"Integrated Security=true";
// We use the Open() method to establish the connection
SqlConn1.Open();
After we instantiated our SqlConn1 object, we set the ConnectionString property (which provides the information needed to connect to the database and begins manipulating its tables). Then we have opened the connection.
SqlCommand SqlComm1 = new SqlCommand();
SqlComm1.Connection = SqlConn1;
SqlComm1.CommandText = "Select * from titles";
SqlDataReader SqlReader = SqlComm1.ExecuteReader();
while(SqlReader.Read())
{
Console.Write(SqlReader["title_id"]);
Console.Write("--");
Console.Write(SqlReader["title"]);
Console.WriteLine();
}
// here we call the Close() method to close the Connection
SqlConn1.Close();
The above code creates the object SqlComm1 of type SqlCommand. Actually, after we establish the connection we need to issue or execute a query or a stored procedure, so we can think of them as commands to send to the SQL Server to execute (again, we will discuss commands in another article). The next statement that follows sets the connection that the command uses.
Of course we have set the SqlCommand.Connection property to our connection object SqlConn1. Next we need the command that we want to execute, which is a simple Select statement that selects all the rows of the table titles. Then we use an object of type SqlDataReader to read the records, and after that we close the connection. Now we need to discuss the ConnectionString property in detail.
Next: The ConnectionString Property Setting >>
More Database Code Articles
More By Michael Youssef
