/// <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); }
/// <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); }
/// <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); }
/// <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); }