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>
<add
name="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
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 void
OpenConnection()
{
AppSettingsReader objAppReader = new
AppSettingsReader();
string strDbCon = objAppReader.GetValue("DbconEdu", typeof(string)).ToString();
_cnEdu = new SqlConnection(strDbCon);
_cnEdu.Open();
}
// Close Connection
public void
CloseConnection()
{
if (_cnEdu == null)
{
return;
}
_cnEdu.Close();
_cnEdu.Dispose();
}
// Method to check the Database connection. If the
connection is closed, Opens it.
private void
CheckDbConnection()
{
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 bool
RecordExists(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 void
ExecQuery(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 void
ExecuteStoredProcuedure(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();
}
2) Change CRM URL's and Connections in the Configuration File.
Line 17 - Discovery Service URL
