Common method for INSERT,UPDATE ,DELETE (part 01)

Hi guys

Last few months I couldn’t blog any post; here is the first one in 2015

When we are developing database driven application, INSERT,UPDATE and DELETE operations are frequently used.in early we develop (write) entire code to against the database eg. (create sqlconnection,command,Tansaction object etc.).

Then ORM introduced by Microsoft which is Entity Framework (other ORM tool also available Subsonic,nhibernate etc), It provides more flexibility way to accessing data base. It also has some drawbacks (multiple database, memory consumption etc).

This post is about the INSERT,UPDATE and DELETE with SqlTransaction using common method to reduce code duplication and coding less method to saving data in database.

Common Class:

    public class Tra : IDisposable

    {

        SqlTransaction tra;

        SqlConnection connection = null;

        public Tra()

        {

            if (connection == null)

            {

                connection = new SqlConnection(sqlConnection);

            }

        }

        

        public   long Save(string query,bool isIdentity,CommandType cmdType, params SqlParameter[] paras)

        {          

            try

            {

                if (connection.State != ConnectionState.Open)

                {

                    connection.Open();

                }

                SqlCommand command = new SqlCommand();

                if (tra == null)

                {

                    tra = connection.BeginTransaction();

                }

                command.Transaction = tra;

                command.Connection = connection;

                if (cmdType == CommandType.StoredProcedure)

                {

                    command.CommandType = CommandType.StoredProcedure;

                }

                else

                {

                    command.CommandType = CommandType.Text;

                }

                    

                command.CommandText = query;

                command.Parameters.AddRange(paras);

                long x;

                if (isIdentity)

                {

                    x = long.Parse(command.ExecuteScalar().ToString());

                }

                else

                {

                    x = long.Parse(command.ExecuteNonQuery().ToString());

                }

                return x;

            }

            catch (Exception ex)

            {

                tra.Rollback();

                throw ex;

            }

        }

        public SqlTransaction sqlTransaction { get { SqlTransaction tra=null; return tra; } }

       

        public string sqlConnection { get { return @" PUT CONNECTION STRING"; } }

        public void Commit()

        {

            tra.Commit();

        }

        public void Dispose()

        {

            tra.Dispose();

        }

    }

Calling:

            try

            {

                using (var tra = new Tra())//required

                {// required

                 var a = tra.Save(@"insert into dbo.Table_1 (name) values (@name) SELECT SCOPE_IDENTITY()",true,CommandType.Text,

                                    new SqlParameter("@name", "test1"));

                    var b = tra.Save(@"insert into dbo.Table_2 (name) values (@name)", false,CommandType.Text,

                                     new SqlParameter("@name", "test1"));

                    tra.Commit();//required

                }//

                

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

        }

HAPPY CODING :)