C# to SQL

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
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();
        }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s