Database

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

WHERE C.ROW_ID        = T5.PR_EMP_ID
  AND T1.ROW_ID       = T4.ROW_ID(+)
  AND T1.PR_POSTN_ID  = T5.ROW_ID(+)
--AND T1.PR_INDUST_ID = T6.ROW_ID(+)
  AND C.EMP_FLG(+)    = 'Y'
  AND T1.CUST_STAT_CD IN ('Active','Inactive','Pending - Workflow Wizard')
  AND T1.X_CLIENT_TYPE IS NOT NULL

SELECT MSGNUM, LISTAGG(DESCR, ',') WITHIN GROUP (ORDER BY LVL) AS MSGDESCR
FROM (SELECT DISTINCT MSGNUM, LEVEL LVL
            ,SUBSTR(NVL2(MSGNUM, MSGNUM || ',', NULL)
                    ,(CASE WHEN LEVEL > 1 THEN 
                          INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL - 1) + 1 
                        ELSE LEVEL END)
                    ,INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL) 
                      - (CASE WHEN LEVEL > 1 THEN 
                              INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL - 1) + 1 
                            ELSE LEVEL END)
                  ) MSGNUM_TO_ID
        FROM
        -- REPLACE FOLLOWING BLOCK WITH YOUR FIRST TABLE
            (SELECT SYSDATE - 1 STARTDATE, '1,2,3' MSGNUM FROM DUAL UNION 
            SELECT SYSDATE - 2 STARTDATE, '4' MSGNUM FROM DUAL UNION 
            SELECT SYSDATE - 3 STARTDATE, '5,6' MSGNUM FROM DUAL UNION 
            SELECT SYSDATE - 4 STARTDATE, '' MSGNUM FROM DUAL UNION 
            SELECT SYSDATE - 5 STARTDATE, '4,2,5' MSGNUM FROM DUAL UNION 
            SELECT SYSDATE - 6 STARTDATE, '' MSGNUM FROM DUAL)
        CONNECT BY INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL) != 0
      ) T1
      -- REPLACE FOLLOWING BLOCK WITH YOUR SECOND TABLE
    ,(SELECT 1 ID, 'ONE' DESCR FROM DUAL UNION
      SELECT 2 ID, 'TWO' DESCR FROM DUAL UNION
      SELECT 3 ID, 'THREE' DESCR FROM DUAL UNION
      SELECT 4 ID, 'FOUR' DESCR FROM DUAL UNION
      SELECT 5 ID, 'FIVE' DESCR FROM DUAL UNION
      SELECT 6 ID, 'SIX' DESCR FROM DUAL) T2
 WHERE T1.MSGNUM_TO_ID = T2.ID
GROUP BY MSGNUM
  ;