Connecting to the SQL Server database

by CameronM 6. October 2009 19:39

Performing operations on data stored in a database is one of the most common tasks performed by developers. In fact, most modern web and desktop applications rely on data stored in databases at least to some degree. In the .NET world, SQL Server and especially SQL Server Express is almost the defacto standard database management system and even the free Express versions of the popular Visual Studio development tools support SQL Server database straight out of the box.

Of course you can use other database types, such as Access and Oracle, by simply using the libraries that are available for each specific database. Microsoft provide the System.Data.OleDB, which can be used for Access databases and third party libraries, such as the Oracle Data Provider for .NET (ODP.NET) are also available for a number of .NET framework versions.

Class Declarations

using System.Data.SqlClient;

Function Code

//Create a new connection object

SqlConnection conn = new SqlConnection();

conn.ConnectionString = "Data Source=local;Initial Catalog=TestDB;Integrated Security=True";

 

//Create a new command object

SqlCommand cmd = new SqlCommand();

cmd.Connection = conn;

 

//to define dynamically created SQL

cmd.CommandType = CommandType.Text;

cmd.CommandText = "SELECT * FROM SAMPLE";

 

//to define a Stored Procedure

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "sp_SAMPLE"; //stored procedure name

 

//when using a data reader you must explicitly open and close the connection

conn.Open();

 

SqlDataReader dr = cmd.ExecuteReader();

 

conn.Close();

NOTE: Most experts recommend avoiding dynamically created SQL statements, as these can cause performance issues and represents a potential security threat. Use stored procedures unless there is no other choice (and yes there is always a choice).

Tags: , ,

SQL Server