How to connect to MSSQL in C# ?

This aspx code connects to MSSQSL and retrieve data from “product” table.

 

<%@ Page Language="C#" AutoEventWireup="true"%>

<%@ Import namespace="System.IO" %>
<%@ Import namespace="System.Collections" %>
<%@ Import namespace="System.Collections.Generic" %>
<%@ Import namespace="System.Data" %>
<%@ Import namespace="System.Data.SqlClient" %>

<%

// define some variables to store DB credentials 
string sUserName = "dbuser";
string sPassword = "dbpassword";
string sServerUrl = "127.0.0.1";
string sDatabase = "testdb";

try
{
      // create a connection to DB
     SqlConnection myConnection = new SqlConnection(
              "user id="+ sUserName +
              ";password="+ sPassword +
              ";server="+ sServerUrl +
              ";database="+ sDatabase +
               ";Max Pool Size=200;connection timeout=30;MultipleActiveResultSets=True;");

              // open the connection
             myConnection.Open();

             // form a query in SQL 
             string query = "SELECT top 10 * from product ";

             // create a 'command' with your query and db connection
             SqlCommand myCommand = new SqlCommand(query, myConnection);

             // create a table to store the result
             DataTable ret = new DataTable();
             SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
             myAdapter.Fill(ret);

            int nRetVal = ret.Rows.Count;

            // loop all the record retrieved
           foreach (DataRow dataReader in ret.Rows)
           {
                  var nvc = new Dictionary<string, object>();

                 // iterate all the column from database
                foreach (DataColumn dc in ret.Columns)
               {
                      string fieldName = dc.ColumnName;
                      var value = dataReader[fieldName];

                     Response.Write(fieldName + " = " + value + "<br>");
              }
        } // end for loop

        myCommand.Dispose();
        myCommand = null;
        myConnection.Close();
        myConnection = null;
}
catch (Exception ex)
{
        Response.Write(ex.ToString());
}
%>