?
Solved

Which is Better Code MySQL CSharp Parameter

Posted on 2007-10-02
12
Medium Priority
?
927 Views
Last Modified: 2012-08-13
Which is Better code?

       private void LogMessage(string Message)
        {
            this.LogMessageL(Message);
            if (mySqlConnection1 == null)
            {
                mySqlConnection1 = new MySqlConnection();
                this.mySqlConnection1.ConnectionString = Properties.Settings.Default.MySQL_ConnectionString;

            }
            try
            {
                MySqlCommand cmd = new MySqlCommand("sp_service_log_nightlybatch", mySqlConnection1);
                cmd.Parameters.Clear();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new MySqlParameter("LOGID", MySqlDbType.UInt32));
                cmd.Parameters.Add(new MySqlParameter("STATME", MySqlDbType.LongText));
                cmd.Parameters.Add(new MySqlParameter("DATECREATED", MySqlDbType.VarChar, 20));
                cmd.Parameters[0].Value = 1;
                cmd.Parameters[1].Value = Message.Replace('\'',' ');
                cmd.Parameters[2].Value = DateTime.Now.ToString("yyyyMMddHHmmss");
                cmd.UpdatedRowSource = UpdateRowSource.None;
                if (mySqlConnection1.State.Equals(ConnectionState.Closed))
                    mySqlConnection1.Open();
                cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
            catch (Exception ex)
            {
                this.LogMessageL(ex.Message);
            }
            finally
            {
                mySqlConnection1.Dispose();
            }
        }

or


        private void LogMessage2(string Message)
        {
            this.LogMessageL(Message);
            if (mySqlConnection1 == null)
            {
                mySqlConnection1 = new MySqlConnection();
                this.mySqlConnection1.ConnectionString = Properties.Settings.Default.MySQL_ConnectionString;

            }
            try
            {
                MySqlCommand cmd = new MySqlCommand("sp_service_log_nightlybatch", mySqlConnection1);
                cmd.Parameters.Clear();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("LOGID",1);
                cmd.Parameters.AddWithValue("STATME",Message.Replace('\'', ' '));
                cmd.Parameters.AddWithValue("DATECREATED", DateTime.Now.ToString("yyyyMMddHHmmss"));
                cmd.Parameters["LOGID"].MySqlDbType = MySqlDbType.UInt32;
                cmd.Parameters["STATME"].MySqlDbType = MySqlDbType.LongText;
                cmd.Parameters["DATECREATED"].MySqlDbType = MySqlDbType.VarChar;
                cmd.Parameters["DATECREATED"].Size = 20;
                cmd.Parameters["LOGID"].Direction = ParameterDirection.Input;
                cmd.Parameters["STATME"].Direction = ParameterDirection.Input;
                cmd.Parameters["DATECREATED"].Direction = ParameterDirection.Input;
                cmd.UpdatedRowSource = UpdateRowSource.None;
                if (mySqlConnection1.State.Equals(ConnectionState.Closed))
                    mySqlConnection1.Open();
                cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
            catch (Exception ex)
            {
                this.LogMessageL(ex.Message);
            }
            finally
            {
                mySqlConnection1.Dispose();
            }
        }
0
Comment
Question by:rctems
  • 5
  • 5
11 Comments
 
LVL 13

Expert Comment

by:dungla
ID: 20003870
private void LogMessage(string Message)
        {
            this.LogMessageL(Message);
            if (mySqlConnection1 == null)
            {
                mySqlConnection1 = new MySqlConnection();
                this.mySqlConnection1.ConnectionString = Properties.Settings.Default.MySQL_ConnectionString;

            }
            try
            {
                MySqlCommand cmd = new MySqlCommand("sp_service_log_nightlybatch", mySqlConnection1);
                cmd.Parameters.Clear();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new MySqlParameter("LOGID", MySqlDbType.UInt32)).Value = 1;
                cmd.Parameters.Add(new MySqlParameter("STATME", MySqlDbType.LongText)).Value = Message;
                cmd.Parameters.Add(new MySqlParameter("DATECREATED", MySqlDbType.VarChar, 20)).Value = DateTime.Now.ToString("yyyyMMddHHmmss");
                if (mySqlConnection1.State.Equals(ConnectionState.Closed))
                    mySqlConnection1.Open();
                cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
            catch (Exception ex)
            {
                this.LogMessageL(ex.Message);
            }
            finally
            {
                mySqlConnection1.Dispose();
            }
        }

reduce the line of code :D
0
 

Author Comment

by:rctems
ID: 20003885
Possibly

But Apparently the .add function is deprecated ie it is nolonger in use and some people are suggesting AddWithValue is prefered.

I'm trying to make a reasoned rational argument. Less lines is better but depreicated code introduces a quality aspect.

Ryan
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 20003990
Personally, I would not use either one. What you have here ties an awful lot of database-specific and database provider-specific code to the execution of a single stored procedure. What if you have 50 of them? What if you have a large application and several hundred selects to support against different tables and views, each with different numbers and types of parameters?What if you write all that code, and you are told you now have to support Oracle on the back end?

Here's an alternative. Create a helper class for each provider that creates provider-specific objects. The caller sends requests through to whichever provider you are using today to construct appropriate parameters. Then it sends in a SQL string, or in this case a procedure name, and this method performs the actual database-specific activity.

public void InvokeStoredProc( string procName, ArrayList spParams )
{
   int paramCount, index;

   IDataParameterCollection parameters = mIParamCollection;
   parameters.Clear();

   paramCount = spParams.Count;
   index = 0;
   while (index < paramCount)
   {
      parameters.Add(spParams[index++]);
   }
   mICommand.CommandType = CommandType.StoredProcedure;
   mICommand.CommandText = procName;
   mICommand.Connection = mIConnection;

   OpenConnection();
   if (mInTransaction)
   {
      mICommand.Transaction=mITransaction;
   }
   try
   {
      mICommand.ExecuteNonQuery();
      if (!mInTransaction)
      {
         CloseConnection();
      }
   }
   catch(Exception ex)
   {
      if (!mInTransaction)
      {
         CloseConnection();
      }
      throw new Exception("Error: The procedure:\n" + procName + "\nCaused this exception: " + ex.Message, ex);
   }
}

All this uses references to the interfaces that any provider-specific objects must implement. I can stop my application, change the connection string, and switch from Oracle to SQL Server to anything else I need without changing a single line of code.

Jim
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:rctems
ID: 20004074
Wow,

This is a completely new, and very smart ..... I like it for a lot of reasons

I'm battling to find a reason not to rewrite all my code using this idea.

Ryan
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 20004139
I was lucky - I thought of it when we first started making the transition from C++ to C#. I knew I had to support both Oracle and SQL Server, and did not want to have two sets of everything. There is one drawback - except for stored procedures, you need to beat anyone over the head who writes non-ANSI SQL.

I also had to create a few functions for simple things. For example, SQL Server uses '+' for string concatenation, while Oracle uses '||' (which is ANSI). So I had to create a CONCATENATE() function and rewrite a handful of SQL statements.

Jim
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 20004174
I also recently added the ability to trace all our database accesses by adding a few lines to about 15 methods. I pulled it out before to avoid confusion. We build a large MES (OLTP) system used by Fortune 100 companies. It's a web app, and can have several thousand concurrent users. All our screens are built dynamically, so I have to keep a close watch on DB access and be able to easily track down and remove any that are unnecessary, or fix any that get ugly or inefficient. Here's the version of that method with the trace code in.

public void InvokeStoredProc( string procName, ArrayList spParams )
{
   if (mLogger != null)
   {
      mTimer.Reset();
      mTimer.Start();
   }
   int paramCount, index;

   IDataParameterCollection parameters = mIParamCollection;
   parameters.Clear();

   paramCount = spParams.Count;
   index = 0;
   while (index < paramCount)
   {
      parameters.Add(spParams[index++]);
   }
   mICommand.CommandType = CommandType.StoredProcedure;
   mICommand.CommandText = procName;
   mICommand.Connection = mIConnection;

   OpenConnection();
   if (mInTransaction)
   {
      mICommand.Transaction=mITransaction;
   }
   try
   {
      mICommand.ExecuteNonQuery();
      if (!mInTransaction)
      {
         CloseConnection();
      }
   }
   catch(Exception ex)
   {
      if (!mInTransaction)
      {
         CloseConnection();
      }
      if (mLogger != null)
      {
         mTimer.Stop();
         StringBuilder sb = new StringBuilder(2048);
         sb.Append("<Method>InvokeStoredProc</Method><SQL>" + procName + "</SQL><Parameters>" + DumpParameters(spParams) + "</Parameters><Error>" + ex.Message + "</Error><ElapsedTime>" + mTimer.ElapsedMilliseconds.ToString() + "</ElapsedTime>");
         mLogger.Debug(sb.ToString());
      }
      throw new Exception("Error: The procedure:\n" + procName + "\nCaused this exception: " + ex.Message, ex);
   }

   if (mLogger != null)
   {
      mTimer.Stop();
      StringBuilder sb = new StringBuilder(2048);
      sb.Append("<Method>InvokeStoredProc</Method><SQL>" + procName + "</SQL><Parameters>" + DumpParameters(spParams) + "</Parameters><Error></Error><ElapsedTime>" + mTimer.ElapsedMilliseconds.ToString() + "</ElapsedTime>");
      mLogger.Debug(sb.ToString());
   }
}

The logger also appends the call stack, so it's easy to trace where where the whole thing originated. I wrapped each of the logged items in XML-like tags, and wrote a windows app to let me view and search the log files. It has been a life-saver for me. I also have the ability to turn it on at a customer site for a single user to help track data-dependent bugs.

The logger we use is called NLog. You can check it out here if you are interested.

http://www.nlog-project.org/

Jim

I highly recommend something like that to help you keep track of things.
0
 

Author Comment

by:rctems
ID: 20004258
Awsome,

I dream about that .. but the cold hard reality is that my company doesn't allow me the freedom to impliment labour saving fault finding technology as this because they view it as a 'waste of time' and too much code rewriting.(Accountants <shrug>) Little do they know that error logging saves time and money!

*sigh*

Ryan
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 20004334
I'm lucky - I started my own company, so I pull a bit more weight with the bean counters. However, it shouldn't be difficult to justify over time anywhere. Did I mention that NLog is free?

Jim
0
 

Author Comment

by:rctems
ID: 20004409
J-

I mentioned it but boss says "low priority". He seams to think that programing only consists of coding and doesn't seam to comprehend that testing is equally if not more important! The ability to solve those hard bugs is determined by the quality of information used to frame questions. And that information is only availalbe from automated, comphrehensive testing.   How do I explain that automated testing software saves money, not costs money!

He seams to think that perfect code just happens and that testing is not necessary.

*sigh*

-R
0
 
LVL 22

Accepted Solution

by:
JimBrandley earned 2000 total points
ID: 20004515
He would not last long in my company. I have a QA department that would disagree strongly, as would several hudred of my customers.

Jim
0
 

Author Comment

by:rctems
ID: 20004562
J-

Your company sounds great.

-R
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question