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