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