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>

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

        }