Connecting to the SQL Server database
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).