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