Exemplo n.º 1
0
        /// <summary>
        /// <para>根据一个SELECT语句判断数据表中是否存在记录.</para>
        /// <para>cmdText参数的形式应该形如:SELECT * FROM t1 WHERE id=5.</para>
        /// <para>函数通过将它转换为如下形式而达致功能实现:</para>
        /// <para>SELECT CASE WHEN EXISTS(SELECT * FROM t1 WHERE id=5) THEN 1 ELSE 0 END.</para>
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="acntStr"></param>
        /// <returns></returns>
        public static bool Exists(string cmdText, string acntStr)
        {
            bool b = false;

            using (SqlConnection conn = new SqlConnection(acntStr))
            {
                conn.Open();
                string cmdTextA = "EXISTS" + ClsQ.Q0(cmdText, '(');
                Debug.WriteLine(cmdTextA);
                cmdTextA = "SELECT CASE WHEN " + cmdTextA + " THEN 1 ELSE 0 END";
                Debug.WriteLine(cmdTextA);
                SqlCommand cmd = new SqlCommand(cmdTextA, conn); try
                {
                    b = Convert.ToBoolean(cmd.ExecuteScalar());
                }
                catch (Exception ex)
                {
                    ClsMsgBox.Cw("Exists遇到了错误。", ex);
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
            }
            return(b);
        }
Exemplo n.º 2
0
        /// <summary>
        /// <para>执行SQL语句或存储过程,返回结果集第一行第一列的值。</para>
        /// <para>可以使用ArrayList arrLstParams传入参数,参数可以是输入型也可以是输出型,</para>
        /// <para>调用者可使用arrLstParams的实参检查输出型参数的返回值。</para>
        /// <para>如果是存储过程,则需要设置isStProc为true。</para>
        /// </summary>
        /// <param name="aSQLCmd"></param>
        /// <param name="aConStr"></param>
        /// <param name="arrLstParams"></param>
        /// <param name="isStProc"></param>
        public static Object GetValue(string aSQLCmd, string aConStr,
                                      ArrayList arrLstParams = null, bool isStProc = false)
        {
            Object ret = null;

            using (SqlConnection conn = new SqlConnection(aConStr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(aSQLCmd, conn);
                if (arrLstParams != null) //如果是null不能AddRange
                {
                    //cmd.Parameters.AddRange向SqlParameterCollection的末尾添加值数组,
                    //arrLstParams.ToArray将arraylist的元素复制到一个指定元素类型的新数组
                    cmd.Parameters.AddRange(arrLstParams.ToArray(typeof(SqlParameter)));
                }
                if (isStProc)
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                }
                try
                {
                    ret = cmd.ExecuteScalar();
                }
                catch (Exception ex)
                {
                    ClsMsgBox.Cw("GetValue遇到了错误。", ex);
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
            }
            return(ret);
        }
Exemplo n.º 3
0
        /// <summary>
        /// 返回数据表
        /// </summary>
        /// <param name="aSelectCmd"></param>
        /// <param name="acntStr"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string aSelectCmd, string aCntStr)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(aCntStr))
            {
                conn.Open();
                SqlCommand     cmd = new SqlCommand(aSelectCmd, conn);
                SqlDataAdapter da  = new SqlDataAdapter(cmd);
                try
                {
                    da.Fill(dt);
                }
                catch (Exception ex)
                {
                    ClsMsgBox.Cw("GetDataTable遇到了错误。", ex);
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
            }
            return(dt);
        }
Exemplo n.º 4
0
        /// <summary>
        /// <para>执行SQL语句或存储过程,返回影响的行数。</para>
        /// <para>可以使用ArrayList arrLstParams传入参数,参数可以是输入型也可以是输出型,</para>
        /// <para>调用者可使用arrLstParams的实参检查输出型参数的返回值。</para>
        /// <para>如果是存储过程,则需要设置isStProc为true。</para>
        /// </summary>
        /// <param name="aSQLCmd"></param>
        /// <param name="aConStr"></param>
        /// <param name="arrLstParams"></param>
        /// <param name="isStProc"></param>
        public static int ExecuteCmd(string aSQLCmd, string aConStr,
                                     ArrayList arrLstParams = null, bool isStProc = false)
        {
            int rows = 0;

            using (SqlConnection conn = new SqlConnection(aConStr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(aSQLCmd, conn);
                if (arrLstParams != null) //如果是null不能AddRange
                {
                    cmd.Parameters.AddRange(arrLstParams.ToArray(typeof(SqlParameter)));
                }
                if (isStProc)
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                }
                try
                {
                    rows = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    ClsMsgBox.Cw("ExecuteCmd遇到了错误:", ex);
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
            }
            return(rows);
        }