Working with Data—Connecting to a SQL Server Database

Microsoft .NET Framework, ASP.NET, Visual C# (CSharp, C Sharp, C-Sharp) Developer Training, Visual Studio

CSharp-Online.NET:Articles

Database Articles

Working with Data Databases Intro to SQL ADO.NET Classes Roll Your Own Database Define Table Structure Reading Rows Adding Rows Storing as XML Filtering Data Connect to Access Connect to SQL Server Data Binding Using DataGridView

© 2006 Jeffery Suddeth

Connecting to a SQL Server Database

The SQL Server versions of the connection, command, and adapter classes are listed below in Table 15.6. They are defined in the System.Data.SqlClient namespace.

Table 15.6 – SQL Server Database Classes

Class		Description
SqlConnection		The physical database connection
SqlDataCommand		A command to execute on the database
SqlDataAdapter		Moves data between the DataSet and the physical Database using a connection and a command
SqlDataReader		Can iterate results returned from a command

SQL Server also installs a copy of the Northwind Trader database. The next example is similar to the previous except that it uses the SQL Server versions of the connection and adapter.

While the previous listing used an adapter to execute a command internally and fill a DataSet, this example uses the command object directly. The program calls the SqlCommand object’s ExecuteReader method, which returns a SqlReader object. Then, it uses the SqlReader to loop through the records that have been returned.

Listing 15.3

// Example15_3.cs
using System;
using System.Data;
using System.Data.SqlClient;
 
namespace csbook.ch15 {
 
   class Example15_3 {
      static void Main(string[] args) {
         // create an open the connection
         SqlConnection conn = 
            new SqlConnection("Data Source=DESKTOP;"
               + "Initial Catalog=Northwind;"
               + "Persist Security Info=True;"
               + "User ID=jeff;Password=password");
 
         conn.Open();
 
         // create a SqlCommand object for this connection
         SqlCommand command = conn.CreateCommand();
         command.CommandText = "Select * from Customers";
         command.CommandType = CommandType.Text;
 
         // execute the command that returns a SqlDataReader
         SqlDataReader reader = command.ExecuteReader();
 
         // display the results
         while (reader.Read()) {
            string output = reader["CompanyName"].ToString();
            Console.WriteLine(output);
            }
 
         // close the connection
         reader.Close();
         conn.Close();
         }
      }
   }