Connecting SQL server from C#
To connect to a SQL server database, you first need to set up a SQL Connection object. You then need something called a connection string to tell C# where the database is.
Specify the connection string
<connectionStrings>
<addname=”OsmEmpConString” connectionString=”Data Source=.\sqlexpress;Initial Catalog=EmpolyeeDB; User ID= Sqluser ; Password= 123456789;”
providerName=”System.Data.SqlClient” />
</connectionStrings>
DataSource = Your database source
Initial Catalog = Database name
User ID = User id of the database
Password = Password to the database
Initial Catalog = Database name
User ID = User id of the database
Password = Password to the database
Use that connection in .cs file
string strDbCon = WebConfigurationManager.ConnectionStrings["OsmEmpConString"].ToString();
SqlConnection _cnEmpDB = new SqlConnection(strDbCon);
_ cnEmpDB.Open();
_ cnEmpDB is the instance of SqlConnection.
The namespace required for doing this is using System.Data.SqlClient;
C# code
Below are the methods in which you can do all types of operations from C# code.
private SqlConnection _cnEdu;
// Open Connection
public voidOpenConnection()
{
AppSettingsReader objAppReader = newAppSettingsReader();
string strDbCon = objAppReader.GetValue(“DbconEdu”, typeof(string)).ToString();
_cnEdu = new SqlConnection(strDbCon);
_cnEdu.Open();
}
// Close Connection
public voidCloseConnection()
{
if (_cnEdu == null)
{
return;
}
_cnEdu.Close();
_cnEdu.Dispose();
}
// Method to check the Database connection. If the connection is closed, Opens it.
private voidCheckDbConnection()
{
if (_cnEdu == null)
{
//checks for connection
OpenConnection();
}
else if(_cnEdu.State == ConnectionState.Closed)
{
OpenConnection();
}
}
// Method which takes SQL query as parameter and returns a DataTable.
public DataTable ExecuteDataTable(string strQuery)
{
if (strQuery == null&& strQuery.Length == 0)//checks for the query
{
return null;
}
CheckDbConnection();
DataTable dtReturn = new DataTable();//creates table
using (SqlDataAdapter daExecute = new SqlDataAdapter(strQuery, _cnEdu))
{
daExecute.Fill(dtReturn);
}
return dtReturn;
}
// Method which takes SQL query as parameter and return true if the record exists in the database.
public boolRecordExists(string strQuery)
{
CheckDbConnection();
using (SqlCommand cmdEdu = new SqlCommand())
{
cmdEdu.Connection = _cnEdu;
cmdEdu.CommandText = strQuery;
cmdEdu.CommandType = CommandType.Text;
Object objColValue = cmdEdu.ExecuteScalar();
bool blnExists = true;
if (objColValue == null)
{
blnExists = false;
}
return blnExists;
}
}
// Method which takes SQL insert and update query and executes in the database.
public voidExecQuery(string strQuery)
{
CheckDbConnection();
using (SqlCommand cmdEdu = new SqlCommand())
{
cmdEdu.Connection = _cnEdu;
cmdEdu.CommandText = strQuery;
cmdEdu.CommandType = CommandType.Text;
int intColValue = cmdEdu.ExecuteNonQuery();
}
}
// Method which takes a input parameter and executes the specified storedprocedure.
public voidExecuteStoredProcuedure(string strEmpID)
{
CheckDbConnection();
SqlCommand cmd = new SqlCommand(“updateEmployee”, _cnEdu);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(“@pi_emp_id”, SqlDbType.VarChar, 20).Value = strEmpID;
cmd.Parameters.Add(“@pi_name”,SqlDbType.VarChar, 50).Value = “Name”;
cmd.Parameters.Add(“@po_address”, SqlDbType.VarChar, 500).Value = “Address of the Employee”;
cmd.Parameters.Add(“@po_email”, SqlDbType.VarChar, 50).Value = “name@name.com”;
cmd.ExecuteNonQuery();
}
