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?

Improve company productivity with a Business Account.Sign Up

x
 
sunil_mailsConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.