/// <summary>
/// 执行一个带参数的sql语句，并返回受此SQL语句影响的行数
/// </summary>
/// <param name="dParams">SQL参数</param>
/// <returns>受此SQL语句影响的行数</returns>
public int ExeSQL(SqlDataParamters dParams)
{
int iRet = 0;
SqlConnection myCn = new SqlConnection(strConn);
SqlCommand myCmd = new SqlCommand();
myCmd.Connection = myCn;
try
{
myCn.Open();
myCmd.CommandType = dParams.Commandtype;
myCmd.CommandText = dParams.CommandText;
myCmd.Parameters.Clear();
for (int i = 0; i < dParams.Count; i++)
{
myCmd.Parameters.Add(dParams[i]);
}
iRet = myCmd.ExecuteNonQuery();
myCmd.Parameters.Clear();
}
catch (System.Data.SqlClient.SqlException e)
{
iRet = -1;
throw new Exception(e.Message);
}
finally
{
myCmd.Dispose();
myCn.Close();
}
return iRet;
}
/// <summary>
/// 以事务方式执行多个sql语句
/// </summary>
/// <param name="strSqls">SQL语句字符串数组</param>
/// <returns>True:事务成功，False：事务失败</returns>
public bool ExeSQL(SqlDataParamters[] dParams)
{
bool bRet;
SqlConnection myConn = new SqlConnection(strConn);
myConn.Open();
SqlTransaction trans = myConn.BeginTransaction();
SqlCommand myCmd = new SqlCommand();
myCmd.Transaction = trans;
myCmd.Connection = myConn;
try
{
foreach (SqlDataParamters sqlParams in dParams)
{
myCmd.CommandType = sqlParams.Commandtype;
myCmd.CommandText = sqlParams.CommandText;
myCmd.Parameters.Clear();
for (int i = 0; i < sqlParams.Count; i++)
{
myCmd.Parameters.Add(sqlParams[i]);
}
myCmd.ExecuteNonQuery();
}
trans.Commit();
bRet = true;
}
catch (System.Data.SqlClient.SqlException e)
{
trans.Rollback();
bRet = false;
throw new Exception(e.Message);
}
finally
{
myCmd.Dispose();
myConn.Close();
}
return bRet;
}
/// <summary>
/// 执行一个带参数的sql语句或存储过程，并将查询出的结果通过DataSet返回
/// </summary>
/// <param name="dParams">带参数的SQL</param>
/// <param name="strTableName">查询结果返回到DataSet时填充的表名</param>
/// <returns>查询出的结果通过DataSet返回</returns>
public DataSet SelectDataSet(SqlDataParamters dParams, string strTableName)
{
DataSet ds = new DataSet();
SqlConnection myCn = new SqlConnection(strConn);
SqlCommand myCmd = new SqlCommand();
myCmd.Connection = myCn;
try
{
myCn.Open();
myCmd.CommandType = dParams.Commandtype;
myCmd.CommandText = dParams.CommandText;
myCmd.Parameters.Clear();
for (int i = 0; i < dParams.Count; i++)
{
myCmd.Parameters.Add(dParams[i]);
}
SqlDataAdapter da = new SqlDataAdapter(myCmd);
da.Fill(ds, strTableName);
da.Dispose();
myCmd.Dispose();
}
catch (System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}
finally
{
myCmd.Dispose();
myCn.Close();
}
return ds;
}