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:

Will the connection stay open, or will SQL server drop it if it is unused for some time?

To minimize the cost of opening connections, ADO.NET uses an optimization technique called connection pooling. [...] It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool.

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]

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???