/// <summary> /// 自动保存数据表中的数据到数据库 /// <remarks>更新时间:2011.11.16</remarks> /// </summary> /// <param name="dt"></param> /// <param name="insertSQL"></param> /// <param name="updateSQL"></param> /// <param name="DB"></param> /// <returns></returns> private static int SaveDataTable(DataTable dt, string insertSQL, string updateSQL, CommonDB DB) { //CommonDB DB = MyDB.GetDBHelper(); string ParaChar = GetDBParaChar(DB); insertSQL = insertSQL.Replace("@@", ParaChar); updateSQL = updateSQL.Replace("@@", ParaChar); int count = 0; DB.BeginTransaction(); try { foreach (DataRow dr in dt.Rows) { IDataParameter[] paras = new IDataParameter[dt.Columns.Count]; for (int i = 0; i < dt.Columns.Count; i++) { paras[i] = DB.GetParameter(ParaChar + dt.Columns[i].ColumnName, dr[i]); } //先更新,如果没有记录受影响再次尝试执行插入 int tempCount = DB.ExecuteNonQuery(updateSQL, CommandType.Text, paras); if (tempCount <= 0) tempCount = DB.ExecuteNonQuery(insertSQL, CommandType.Text, paras); count += tempCount; if (DB.ErrorMessage != "") throw new Exception(DB.ErrorMessage); } DB.Commit(); } catch (Exception ex) { DB.Rollback(); throw ex; } return count; }
/// <summary> /// 更新数据表,带数据访问对象 /// </summary> /// <param name="dt"></param> /// <param name="SQL"></param> /// <param name="DB"></param> /// <returns></returns> private int UpdateDataTable(DataTable dt, string SQL, CommonDB DB) { string ParaChar = GetDBParaChar(DB); SQL = SQL.Replace("@@", ParaChar); int count = 0; foreach (DataRow dr in dt.Rows) { IDataParameter[] paras = new IDataParameter[dt.Columns.Count]; for (int i = 0; i < dt.Columns.Count; i++) { paras[i] = DB.GetParameter(ParaChar + dt.Columns[i].ColumnName, dr[i]); } count += DB.ExecuteNonQuery(SQL, CommandType.Text, paras); if (DB.ErrorMessage != "") throw new Exception(DB.ErrorMessage); } return count; }
}//end function /// <summary> /// 根据数据集中在指定的表中,根据表中的指定列的值在数据源中删除数据,带数据访问对象 /// </summary> /// <param name="ds">数据集</param> /// <param name="tableName">表名称</param> /// <param name="columnName">列名</param> /// <param name="DB">数据访问对象</param> /// <returns></returns> public int DeleteDataSet(DataSet ds, string tableName, string columnName, CommonDB DB) { DataTable dt = ds.Tables[tableName]; string ParaChar = GetDBParaChar(DB); int count = 0; string sqlDelete = "DELETE FROM " + tableName + " WHERE " + columnName + "=" + ParaChar + columnName; foreach (DataRow dr in dt.Rows) { IDataParameter[] paras = { DB.GetParameter(ParaChar + columnName, dr[columnName]) }; count += DB.ExecuteNonQuery(sqlDelete, CommandType.Text, paras); if (DB.ErrorMessage != "") throw new Exception(DB.ErrorMessage); if (count >= dt.Rows.Count) break; } return count; }