Database

Richard.Berry100 wrote:

Is it bad practice to open a connection when the application starts, to avoid delays caused by opening the connection each time a query is executed?

Richard.Berry100 wrote:

or will SQL server drop it if it is unused for some time?

PIEBALDconsult wrote:

It is still preferable to use Open to get the
connection from the pool and Close to return it.

private void cmdInsert_Click(object sender, EventArgs e)
        {
            Stopwatch sw = new Stopwatch();
            string cnStr = "Data Source=" + Server + ";Initial Catalog=" + Database + ";User Id=" + DBUser + "; Password=" + DBPass + ";";
            string strSql = "Insert INTO dbo.SpeedTest (Mydata) VALUES ('Hello')";
            sw.Start();
            using (System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(cnStr)) 
            {

                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(strSql, cn);
                
		for (int i = 0; i < 10000; i++)
                {
                    cn.Open();
                    cmd.ExecuteNonQuery();
                    cn.Close();
                }
            }

            sw.Stop();
            this.txtResults.Text = sw.ElapsedMilliseconds.ToString();
                      
        }


//7958 miliseconds



private void cmdInsert_Click(object sender, EventArgs e)
        {
            Stopwatch sw = new Stopwatch();
            string cnStr = "Data Source=" + Server + ";Initial Catalog=" + Database + ";User Id=" + DBUser + "; Password=" + DBPass + ";";
            string strSql = "Insert INTO dbo.SpeedTest (Mydata) VALUES ('Hello')";
            sw.Start();
            using (System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(cnStr)) 
            {

                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(strSql, cn);
                cn.Open();
		for (int i = 0; i < 10000; i++)
                {
                    
                    cmd.ExecuteNonQuery();
                    
                }
                cn.Close();
            }

            sw.Stop();
            this.txtResults.Text = sw.ElapsedMilliseconds.ToString();
                      
        }

//6894 miliseconds


private void cmdInsert_Click(object sender, EventArgs e)
        {
            Stopwatch sw = new Stopwatch();
            string cnStr = "Data Source=" + Server + ";Initial Catalog=" + Database + ";User Id=" + DBUser + "; Password=" + DBPass + ";";
            string strSql = "Insert INTO dbo.SpeedTest (Mydata) VALUES ('Hello')";
            sw.Start();
            for (int i = 0; i < 10000; i++)
            {
                using (System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(cnStr)) 
                {

                    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(strSql, cn);
                    cn.Open();
		            cmd.ExecuteNonQuery();
                    cn.Close();
                }
            }
            sw.Stop();
            this.txtResults.Text = sw.ElapsedMilliseconds.ToString();
                      
        }

//7963 miliseconds

SELECT SUBSTR(A.CHAR_STR, 0,  LENGTH(A.CHAR_STR) - LEVEL + 1) OUTPUT
FROM (SELECT 'HPN12345' CHAR_STR, 3 MIN_DEPTH FROM DUAL) A
CONNECT BY LENGTH(A.CHAR_STR) - LEVEL + 1 >= A.MIN_DEPTH;

Member 9999112 wrote:

How can i over come this error.

select * 
from @t
where
cast((case when isnumeric(val) = 1 then val else 0 end) as int) > 3

Bernhard Hiller wrote:

JavaScript could cast '12r' into an int

Mycroft Holmes wrote:

There is certain crappy banking software that will allow the traders to enter 12.65m or 328k as a trade values, WTF!

Richard.Berry100 wrote:

what does J-script convert '12r' to???