How to connect to a SQL server database in c#

The following code snippet will show you how to query an SQL Server database in C# using ADO.NET.

Namespaces you will need to include:

using System.Data;

using System.Data.SqlClient;

Source Code:

SqlConnection conn = new SqlConnection("Data Source=Server Name;UID=User Name;PWD=Password;Initial Catalog=Database Name");

           SqlCommand command = new SqlCommand("SELECT TOP 100 * FROM  tbl_Users",conn);

           DataTable dt = new DataTable();

            command.Connection.Open();

           SqlDataAdapter adapter = new SqlDataAdapter(command);

           adapter.Fill(dt);

           adapter.Dispose();

           command.Connection.Close();

           command.Dispose();

           foreach (DataRow dr in dt.Rows)

           {

               Console.WriteLine(dr["FirstName"].ToString()+" "+dr["LastName"].ToString());

           }

Breakdown

Initialize a SqlConnection object by passing you server connection string as a parameter. Then, initialize a SqlCommand object which you can use it to execute a SQL query or a stored procedure (We passed the SqlConnection object as an additional parameter).  I will be retrieving a set of data from the sample query; so, I need either a DataSet or DataTable object to store the result. Next, we initialize a SqlDataAdapter object that will serve us as a bridge between the SqlCommand and the DataTable (we passed the SqlCommand object as a parameter). Finally, we simply open a connection and call the Fill() method to retrieve our result.