Connectivity in C# and Sql Server Stored Procedure

Connectivity in C# and Sql Server Stored Procedure

Insertion in C#

        /// Used to insert records into database

  
        public int Insert(string firstName, string lastName, int age)

        {

            SqlConnection conn = new SqlConnection(connStr);

            conn.Open();

            SqlCommand dCmd = new SqlCommand("InsertData", conn);

            dCmd.CommandType = CommandType.StoredProcedure;

            try

            {

                dCmd.Parameters.AddWithValue("@firstName", firstName);

                dCmd.Parameters.AddWithValue("@lastName", lastName);

                dCmd.Parameters.AddWithValue("@age", age);

                return dCmd.ExecuteNonQuery();

            }

            catch

            {

                throw;

            }

            finally

            {

                dCmd.Dispose();

                conn.Close();

                conn.Dispose();

            }

        }

Updation in C#

  /// 


        /// Update record into database


        public int Update(int personID, string firstName, string lastName, int age)

        {

            SqlConnection conn = new SqlConnection(connStr);

            conn.Open();

            SqlCommand dCmd = new SqlCommand("UpdateData", conn);

            dCmd.CommandType = CommandType.StoredProcedure;

            try

            {

                dCmd.Parameters.AddWithValue("@firstName", firstName);

                dCmd.Parameters.AddWithValue("@lastName", lastName);

                dCmd.Parameters.AddWithValue("@age", age);

                dCmd.Parameters.AddWithValue("@personID", personID);

                return dCmd.ExecuteNonQuery();

            }

            catch

            {

                throw;

            }

            finally

            {

                dCmd.Dispose();

                conn.Close();

                conn.Dispose();

            }

        }


Load and Connectivity

        /// 


        /// Load all records from database


        public DataTable Load()

        {

            SqlConnection conn = new SqlConnection(connStr);

            SqlDataAdapter dAd = new SqlDataAdapter("LoadAll", conn);

            dAd.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataSet dSet = new DataSet();

            try

            {

                dAd.Fill(dSet, "PersonTable");

                return dSet.Tables["PersonTable"];

            }

            catch

            {

                throw;

            }

            finally

            {

                dSet.Dispose();

                dAd.Dispose();

                conn.Close();

                conn.Dispose();

            }

        }


Deletion in C# 

        /// Delete record from database

        public int Delete(int personID)

        {

            SqlConnection conn = new SqlConnection(connStr);

            conn.Open();

            SqlCommand dCmd = new SqlCommand("DeleteData", conn);

            dCmd.CommandType = CommandType.StoredProcedure;

            try

            {

                dCmd.Parameters.AddWithValue("@personID", personID);

                return dCmd.ExecuteNonQuery();

            }

            catch

            {

                throw;

            }

            finally

            {

                dCmd.Dispose();

                conn.Close();

                conn.Dispose();

            }

        }



    }

Resources:http://www.dotnetfunda.com/articles/article71.aspx