sql server 2008: optimize store procedure ( more speed)-C# 2008

sql server 2008: optimize store procedure ( more speed)-C# 2008.I want to make more speed store procedure in sql server 2008.I made store procedures via C# (visual studio 2008).Can I open connection only once?In order to maximize speed?And if any other suggestion I wiil accept them.  
public class StoredProceduresData
    {
        private static SqlConnection _conn;
        private LogFile log;
 
        public StoredProceduresData()
        {            
            log = new LogFile();           
        }
        public void CallSpInsertColumns(ArrayList nevId, ArrayList gameCode, int draw,
                                         int lotosRevision, ArrayList forecast, ArrayList odds,
                                         int totalMultiplier, DateTime time, int date,
                                         int columnNumber)
        {
            try
            {
                SqlCommand cmd;
                if (_conn.State == ConnectionState.Closed)
                    _conn.Open();
                if (columnNumber == 1)
                {                    
                    cmd = new SqlCommand("SP_INSERT_COLUMNS_1", _conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@nevId", SqlDbType.BigInt).Value = (long)nevId[0];
                    cmd.Parameters.Add("@gameCode", SqlDbType.Int).Value = (int)gameCode[0];
                    cmd.Parameters.Add("@draw", SqlDbType.Int).Value = draw;
                    cmd.Parameters.Add("@lotosRevision", SqlDbType.Int).Value = lotosRevision;
                    cmd.Parameters.Add("@forecast", SqlDbType.Int).Value = (int)forecast[0];
                    cmd.Parameters.Add("@odds", SqlDbType.Decimal).Value = (float)odds[0];
                    cmd.Parameters.Add("@totalMultiplier", SqlDbType.Int).Value = totalMultiplier;
                    cmd.Parameters.Add("@time", SqlDbType.Time).Value = time.TimeOfDay;
                    cmd.Parameters.Add("@date", SqlDbType.Int).Value = date;
                    cmd.Parameters.Add("@columnNumber", SqlDbType.Int).Value = columnNumber;
 
                    cmd.ExecuteNonQuery();
                }
                else
                {                   
                    cmd = new SqlCommand(string.Format("SP_INSERT_COLUMNS_{0}", columnNumber), _conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    for (int ii = 1; ii <= columnNumber; ii++)
                    {
                        cmd.Parameters.Add(string.Format("@nevId_{0}", ii), SqlDbType.Int).Value = (long)nevId[ii - 1];
                        cmd.Parameters.Add(string.Format("@gameCode_{0}", ii), SqlDbType.SmallInt).Value = (int)gameCode[ii - 1];
                        cmd.Parameters.Add(string.Format("@odds_{0}", ii), SqlDbType.Decimal).Value = (float)odds[ii - 1];
                        cmd.Parameters.Add(string.Format("@forecast_{0}", ii), SqlDbType.SmallInt).Value = (int)forecast[ii - 1];
                    }
                    cmd.Parameters.Add("@draw", SqlDbType.Int).Value = draw;
                    cmd.Parameters.Add("@lotosRevision", SqlDbType.SmallInt).Value = lotosRevision;
                    cmd.Parameters.Add("@totalMultiplier", SqlDbType.Int).Value = totalMultiplier;
                    cmd.Parameters.Add("@time", SqlDbType.Time).Value = time.TimeOfDay;
                    cmd.Parameters.Add("@date", SqlDbType.Int).Value = date;
                    cmd.Parameters.Add("@columnNumber", SqlDbType.Int).Value = columnNumber;
 
                    cmd.ExecuteNonQuery();
                }
            }
 
            catch (Exception ex)
            {
                log.Log(ex.ToString());
                throw (ex);
            }
        }
        #region "Properties"
        public static SqlConnection SqlConnDatawarehouse
 //this is static in order to open only once!!! from Client!!!!!
        {
            get
            {
                return _conn;
            }
            set
            {
                _conn = value;
            }
        }
        #endregion
    }  
 
///--------------------------------------DEPLOY STORE PROCEDURE AS BELLOW-------------------------------------------
 
[Microsoft.SqlServer.Server.SqlProcedure]
    public static void SP_INSERT_COLUMNS_1(int nevId, int gameCode, int draw,
                                         int lotosRevision, int forecast, float odds,
                                         int totalMultiplier ,DateTime time, int date,
                                         int columnNumber)
    {       
        using (SqlConnection conn = new SqlConnection("Context Connection = true"))
        { 
            conn.Open();//--->CAN AVOID THIS?IN order to open only once?
            SqlCommand cmd = conn.CreateCommand();
         
            cmd.CommandText = string.Format("insert into Columns_{0} Values (@nevId, @gameCode, @draw, @lotosRevision, @forecast, @odds, @totalMultiplier, @date, @time )", columnNumber);
            cmd.Parameters.Add("@nevId", SqlDbType.Int).Value = nevId;
            cmd.Parameters.Add("gameCode", SqlDbType.SmallInt).Value = gameCode;
            cmd.Parameters.Add("@draw", SqlDbType.Int).Value = draw;
            cmd.Parameters.Add("@lotosRevision", SqlDbType.Int).Value = lotosRevision;
            cmd.Parameters.Add("@forecast", SqlDbType.Int).Value = forecast;
            cmd.Parameters.Add("@odds", SqlDbType.Decimal).Value = odds;
            cmd.Parameters.Add("@totalMultiplier", SqlDbType.Int).Value = totalMultiplier;
            cmd.Parameters.Add("@time", SqlDbType.Time).Value = time.TimeOfDay;
            cmd.Parameters.Add("@date", SqlDbType.Int).Value = date;
          
            
            SqlContext.Pipe.ExecuteAndSend(cmd);
            conn.Close();
        }
    }//....these are 30 stores procedures....SP_INSERT_COLUMNS_1,SP_INSERT_COLUMNS_2  e.t.c

Open in new window

nikavakAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sunil_mailsCommented:
Best option is to create SP with SQL in SQL 2008 and call it by passing the parameter from front end. Nothing gonna run faster than this.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.