Accessing SQL Server through C# - Beginners guide

In this article, I am going to demonstrate how to access SQL server through C#.

Adding Namespaces 

 

      using System.Data.SqlClient; // For the database connections and objects. 

Inserting & Retrieving data using CommandText

      public void CommandTextConnectionTest()

        {

            try

            {

                string ConnectionString = "ConnectionString";

                SqlConnection sqlConnection = new SqlConnection();

                SqlCommand sqlCommand = new SqlCommand();

                sqlConnection.ConnectionString = ConnectionString;//

                sqlConnection.Open();

                sqlCommand.Connection = sqlConnection;

                //Insert Data

                //The ExecuteNonQuery method is ideal for when you are inserting data

                sqlCommand.CommandType = CommandType.Text;

                sqlCommand.CommandText = "INSERT INTO Student values('" + "StudentID" + "','" + "StudentName" + "','" + "Address" + "','" + "Tpnum" + "')";

                if (sqlCommand.ExecuteNonQuery() > 0)

                {

                    MessageBox.Show("Successfully Inserted");

                }

                //Retrive Data

                //SqlDataReader is a fast way to read table data

                sqlCommand.CommandText = "select * from student where name='" + "StudentID" + "'";

                sqlCommand.CommandType = CommandType.Text;

                SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

                while (sqlDataReader.Read())

                {

                    student.StudentID = sqlDataReader["StudentID"].ToString();// Assign StudentID to student class

                    student.StudentName = Convert.ToInt32(sqlDataReader["StudentName"]);

                }

                sqlConnection.Close();

            }

            catch (Exception)

            {

               

                 MessageBox.Show("Error");

            }

        }

 Inserting data using Stored procedure 

     public void StoredProcedureConnectionTest()

        {

            try

            {

                string ConnectionString = "ConnectionString";

                SqlConnection sqlConnection = new SqlConnection();

                SqlTransaction sqlTransaction;

                SqlCommand sqlCommand = new SqlCommand();

                sqlConnection.Open();

                sqlCommand.Connection = sqlConnection;

                sqlTransaction = sqlConnection.BeginTransaction();

                {

                    sqlCommand.Connection = sqlTransaction.Connection;

                    sqlCommand.Transaction = sqlTransaction;

                    sqlCommand.CommandType = CommandType.StoredProcedure;

                    sqlCommand.CommandText = "SP_SPNAME";

                    //Adding Parameters

                    sqlCommand.Parameters.AddWithValue("@Parameters1", "Parameters1 value");

                    sqlCommand.Parameters.AddWithValue("@Parameters2", "@Parameters2 value");

                    //Adding Return Parameter

                    sqlCommand.Parameters.Add("@ReturnParameter", SqlDbType.VarChar, 50).Direction = ParameterDirection.Output;

                    sqlCommand.ExecuteNonQuery();

                    string reurnValue = (sqlCommand.Parameters["@Str_Service_out_Invoice"].Value.ToString());

                }

                sqlTransaction.Commit();

                sqlConnection.Close();

                MessageBox.Show("Successfully Inserted");

            }

            catch (Exception ex)

            {

                MessageBox.Show("Error");

            }

        }

Retrieving data using Stored procedure 

    public DataTable StoredProcedureConnectionTest()

        {

            try

            {

                string ConnectionString = "ConnectionString";

                SqlConnection sqlConnection = new SqlConnection();

                sqlConnection.Open();

                //The using statement is excellent for handling important system resources

                using (SqlCommand sqlCommand = new SqlCommand())

                {

                    sqlCommand.CommandText = "SP_SPNAME";

                    sqlCommand.CommandType = CommandType.StoredProcedure;

                    //Adding Parameter1

                    SqlParameter SqlParameter1 = new SqlParameter("@SqlParameter1", "SqlParameter1value1");

                    sqlCommand.Parameters.Add(SqlParameter1);

                    //Adding Parameter2

                    SqlParameter SqlParameter2 = new SqlParameter("@SqlParameter2", "SqlParameter1value2");

                    sqlCommand.Parameters.Add(SqlParameter1);

                    DataTable dataTable = new DataTable();

                    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);

                    sqlDataAdapter.Fill(dataTable);

                    return dataTable;

                }

            }

            catch (Exception ex)

            {

                MessageBox.Show("Error");

                return null;

            }

        }

References :
http://www.dotnetperls.com/sqlclient