/// <summary>
/// 得到数据源。
/// </summary>
/// <returns></returns>
public DataTable GetToDataTable()
{
string sql = "SELECT * FROM " + this.ToTable;
DataTable FromDataTable = new DataTable();
switch (this.ToDBUrl)
{
case DA.DBUrlType.AppCenterDSN:
FromDataTable = DBAccess.RunSQLReturnTable(sql);
break;
case DA.DBUrlType.DBAccessOfMSMSSQL:
FromDataTable = DBAccess.RunSQLReturnTable(sql);
break;
case DA.DBUrlType.DBAccessOfOLE:
FromDataTable = DBAccessOfOLE.RunSQLReturnTable(sql);
break;
case DA.DBUrlType.DBAccessOfOracle:
FromDataTable = DBAccessOfOracle.RunSQLReturnTable(sql);
break;
case DA.DBUrlType.DBAccessOfODBC:
FromDataTable = DBAccessOfODBC.RunSQLReturnTable(sql);
break;
default:
throw new Exception("the to dburl error DBUrlType ");
}
return(FromDataTable);
}
/// <summary>
/// 数据源 run sql ,返回table .
/// </summary>
/// <param name="selectSql"></param>
/// <returns></returns>
public DataTable FromDBUrlRunSQLReturnTable(string selectSql)
{
// 得到数据源．
DataTable dt = new DataTable();
switch (this.FromDBUrl)
{
case DBUrlType.AppCenterDSN:
dt = DBAccess.RunSQLReturnTable(selectSql);
break;
case DBUrlType.DBAccessOfMSMSSQL:
dt = DBAccessOfMSMSSQL.RunSQLReturnTable(selectSql);
break;
case DBUrlType.DBAccessOfODBC:
dt = DBAccessOfODBC.RunSQLReturnTable(selectSql);
break;
case DBUrlType.DBAccessOfOLE:
dt = DBAccessOfOLE.RunSQLReturnTable(selectSql);
break;
case DBUrlType.DBAccessOfOracle:
dt = DBAccessOfOracle.RunSQLReturnTable(selectSql);
break;
//case DBUrlType.DBAccessOfOracle1:
// dt=DBAccessOfOracle1.RunSQLReturnTable( selectSql );
// break;
default:
break;
}
return(dt);
}
public void DeleteObjData()
{
#region 除表内容
switch (this.ToDBUrl)
{
case DA.DBUrlType.AppCenterDSN:
DBAccess.RunSQL("DELETE FROM " + this.ToTable);
break;
case DA.DBUrlType.DBAccessOfMSMSSQL:
DBAccess.RunSQL("DELETE FROM " + this.ToTable);
break;
case DA.DBUrlType.DBAccessOfOLE:
DBAccessOfOLE.RunSQL("DELETE FROM " + this.ToTable);
break;
case DA.DBUrlType.DBAccessOfOracle:
DBAccessOfOracle.RunSQL("DELETE FROM " + this.ToTable);
break;
case DA.DBUrlType.DBAccessOfODBC:
DBAccessOfODBC.RunSQL("DELETE FROM " + this.ToTable);
break;
default:
break;
}
#endregion
}
public int ToDBUrlRunDropTable(string table)
{
switch (this.ToDBUrl)
{
case DBUrlType.AppCenterDSN:
return(DBAccess.RunSQLDropTable(table));
//case DBUrlType.DBAccessOfMSSQL1:
// return DBAccessOfMSSQL1.RunSQL(table);
//case DBUrlType.DBAccessOfMSSQL2:
// return DBAccessOfMSSQL2.RunSQL(table);
case DBUrlType.DBAccessOfODBC:
return(DBAccessOfODBC.RunSQL(table));
case DBUrlType.DBAccessOfOLE:
return(DBAccessOfOLE.RunSQL(table));
//case DBUrlType.DBAccessOfOracle1:
// return DBAccessOfOracle1.RunSQLTRUNCATETable(table);
//case DBUrlType.DBAccessOfOracle2:
// return DBAccessOfOracle2.RunSQLTRUNCATETable(table);
default:
throw new Exception("@ error it");
}
}
/// <summary>
/// 是否存在?
/// </summary>
/// <param name="sql">要判断的sql</param>
/// <returns></returns>
public bool ToDBUrlIsExit(string sql)
{
switch (this.ToDBUrl)
{
case DBUrlType.AppCenterDSN:
return(DBAccess.IsExits(sql));
case DBUrlType.DBAccessOfMSSQL1:
return(DBAccessOfMSSQL1.IsExits(sql));
case DBUrlType.DBAccessOfMSSQL2:
return(DBAccessOfMSSQL2.IsExits(sql));
case DBUrlType.DBAccessOfODBC:
return(DBAccessOfODBC.IsExits(sql));
case DBUrlType.DBAccessOfOLE:
return(DBAccessOfOLE.IsExits(sql));
case DBUrlType.DBAccessOfOracle1:
return(DBAccessOfOracle1.IsExits(sql));
case DBUrlType.DBAccessOfOracle2:
return(DBAccessOfOracle2.IsExits(sql));
default:
throw new Exception("@ error it");
}
}
public int ToDBUrlRunSQL(string sql)
{
switch (this.ToDBUrl)
{
case DBUrlType.AppCenterDSN:
return(DBAccess.RunSQL(sql));
case DBUrlType.DBAccessOfMSSQL1:
return(DBAccessOfMSSQL1.RunSQL(sql));
case DBUrlType.DBAccessOfMSSQL2:
return(DBAccessOfMSSQL2.RunSQL(sql));
case DBUrlType.DBAccessOfODBC:
return(DBAccessOfODBC.RunSQL(sql));
case DBUrlType.DBAccessOfOLE:
return(DBAccessOfOLE.RunSQL(sql));
case DBUrlType.DBAccessOfOracle1:
return(DBAccessOfOracle1.RunSQL(sql));
case DBUrlType.DBAccessOfOracle2:
return(DBAccessOfOracle2.RunSQL(sql));
default:
throw new Exception("@ error it");
}
}
/// <summary>
/// 得到数据源。
/// </summary>
/// <returns>数据源</returns>
public DataTable GetFromDataTable()
{
string FromSQL = "SELECT ";
foreach (FF ff in this.FFs)
{
//对日期型的判断
if (ff.DataType == DataType.AppDateTime)
{
FromSQL += " CASE " +
" when datalength( CONVERT(VARCHAR,datepart(month," + ff.FromField + " )))=1 then datename(year," + ff.FromField + " )+'-'+('0'+CONVERT(VARCHAR,datepart(month," + ff.FromField + " ))) " +
" else " +
" datename(year," + ff.FromField + " )+'-'+CONVERT(VARCHAR,datepart(month," + ff.FromField + " )) " +
" END " +
" AS " + ff.FromField + " , ";
}
else
{
FromSQL += ff.FromField + ",";
}
}
FromSQL = FromSQL.Substring(0, FromSQL.Length - 1);
FromSQL += " from " + this.FromTable;
FromSQL += this.FromWhere;
DataTable FromDataTable = new DataTable();
switch (this.FromDBUrl)
{
case DA.DBUrlType.AppCenterDSN:
FromDataTable = DBAccess.RunSQLReturnTable(FromSQL);
break;
case DA.DBUrlType.DBAccessOfMSMSSQL:
FromDataTable = DBAccess.RunSQLReturnTable(FromSQL);
break;
case DA.DBUrlType.DBAccessOfOLE:
FromDataTable = DBAccessOfOLE.RunSQLReturnTable(FromSQL);
break;
case DA.DBUrlType.DBAccessOfOracle:
FromDataTable = DBAccessOfOracle.RunSQLReturnTable(FromSQL);
break;
case DA.DBUrlType.DBAccessOfODBC:
FromDataTable = DBAccessOfODBC.RunSQLReturnTable(FromSQL);
break;
default:
throw new Exception("the from dburl error DBUrlType ");
}
return(FromDataTable);
}
/// <summary>
/// 同步更新.
/// </summary>
public void Inphase()
{
#region 得到源表
this.DoBefore();
string FromSQL = "SELECT ";
foreach (FF ff in this.FFs)
{
//对日期型的判断
if (ff.DataType == DataType.AppDateTime)
{
FromSQL += " CASE " +
" when datalength( CONVERT(VARCHAR,datepart(month," + ff.FromField + " )))=1 then datename(year," + ff.FromField + " )+'-'+('0'+CONVERT(VARCHAR,datepart(month," + ff.FromField + " ))) " +
" else " +
" datename(year," + ff.FromField + " )+'-'+CONVERT(VARCHAR,datepart(month," + ff.FromField + " )) " +
" END " +
" AS " + ff.FromField + " , ";
}
else
{
FromSQL += ff.FromField + ",";
}
}
FromSQL = FromSQL.Substring(0, FromSQL.Length - 1);
FromSQL += " from " + this.FromTable;
FromSQL += this.FromWhere;
DataTable FromDataTable = new DataTable();
switch (this.FromDBUrl)
{
case DA.DBUrlType.AppCenterDSN:
FromDataTable = DBAccess.RunSQLReturnTable(FromSQL);
break;
case DA.DBUrlType.DBAccessOfMSMSSQL:
FromDataTable = DBAccess.RunSQLReturnTable(FromSQL);
break;
case DA.DBUrlType.DBAccessOfOLE:
FromDataTable = DBAccessOfOLE.RunSQLReturnTable(FromSQL);
break;
case DA.DBUrlType.DBAccessOfOracle:
FromDataTable = DBAccessOfOracle.RunSQLReturnTable(FromSQL);
break;
case DA.DBUrlType.DBAccessOfODBC:
FromDataTable = DBAccessOfODBC.RunSQLReturnTable(FromSQL);
break;
default:
break;
}
#endregion
#region 得到目的表(字段只包含主键)
string ToSQL = "SELECT ";
foreach (FF ff in this.FFs)
{
if (ff.IsPK == false)
{
continue;
}
ToSQL += ff.ToField + ",";
}
ToSQL = ToSQL.Substring(0, ToSQL.Length - 1);
ToSQL += " FROM " + this.ToTable;
DataTable ToDataTable = new DataTable();
switch (this.ToDBUrl)
{
case DA.DBUrlType.AppCenterDSN:
ToDataTable = DBAccess.RunSQLReturnTable(ToSQL);
break;
case DA.DBUrlType.DBAccessOfMSMSSQL:
ToDataTable = DBAccess.RunSQLReturnTable(ToSQL);
break;
case DA.DBUrlType.DBAccessOfOLE:
ToDataTable = DBAccessOfOLE.RunSQLReturnTable(ToSQL);
break;
case DA.DBUrlType.DBAccessOfOracle:
ToDataTable = DBAccessOfOracle.RunSQLReturnTable(ToSQL);
break;
case DA.DBUrlType.DBAccessOfODBC:
ToDataTable = DBAccessOfODBC.RunSQLReturnTable(ToSQL);
break;
default:
break;
}
#endregion
string SELECTSQL = "";
string InsertSQL = "";
string UpdateSQL = "";
string DeleteSQL = "";
//int i=0;
//int j=0;
int result = 0;
#region 遍历源表
foreach (DataRow FromDR in FromDataTable.Rows)
{
UpdateSQL = "UPDATE " + this.ToTable + " SET ";
foreach (FF ff in this.FFs)
{
switch (ff.DataType)
{
case DataType.AppDateTime:
case DataType.AppString:
UpdateSQL += ff.ToField + "='" + FromDR[ff.FromField].ToString() + "',";
break;
case DataType.AppFloat:
case DataType.AppInt:
case DataType.AppMoney:
case DataType.AppRate:
case DataType.AppDate:
case DataType.AppDouble:
UpdateSQL += ff.ToField + "=" + FromDR[ff.FromField].ToString() + ",";
break;
default:
throw new Exception("没有涉及到的数据类型.");
}
}
UpdateSQL = UpdateSQL.Substring(0, UpdateSQL.Length - 1);
UpdateSQL += " WHERE ";
foreach (FF ff in this.FFs)
{
if (ff.IsPK == false)
{
continue;
}
UpdateSQL += ff.ToField + "='" + FromDR[ff.FromField] + "' AND ";
}
UpdateSQL = UpdateSQL.Substring(0, UpdateSQL.Length - 5);
switch (this.ToDBUrl)
{
case DA.DBUrlType.AppCenterDSN:
result = DBAccess.RunSQL(UpdateSQL);
break;
case DA.DBUrlType.DBAccessOfMSMSSQL:
string a = UpdateSQL;
result = DBAccess.RunSQL(UpdateSQL);
break;
case DA.DBUrlType.DBAccessOfOLE:
result = DBAccessOfOLE.RunSQL(UpdateSQL);
break;
case DA.DBUrlType.DBAccessOfOracle:
result = DBAccessOfOracle.RunSQL(UpdateSQL);
break;
case DA.DBUrlType.DBAccessOfODBC:
result = DBAccessOfODBC.RunSQL(UpdateSQL);
break;
default:
break;
}
if (result == 0)
{
//插入操作
InsertSQL = "INSERT INTO " + this.ToTable + "(";
foreach (FF ff in this.FFs)
{
InsertSQL += ff.ToField.ToString() + ",";
}
InsertSQL = InsertSQL.Substring(0, InsertSQL.Length - 1);
InsertSQL += ") values(";
foreach (FF ff in this.FFs)
{
if (ff.DataType == DataType.AppString || ff.DataType == DataType.AppDateTime)
{
InsertSQL += "'" + FromDR[ff.FromField].ToString() + "',";
}
else
{
InsertSQL += FromDR[ff.FromField].ToString() + ",";
}
}
InsertSQL = InsertSQL.Substring(0, InsertSQL.Length - 1);
InsertSQL += ")";
switch (this.ToDBUrl)
{
case DA.DBUrlType.AppCenterDSN:
DBAccess.RunSQL(InsertSQL);
break;
case DA.DBUrlType.DBAccessOfMSMSSQL:
DBAccess.RunSQL(InsertSQL);
break;
case DA.DBUrlType.DBAccessOfOLE:
DBAccessOfOLE.RunSQL(InsertSQL);
break;
case DA.DBUrlType.DBAccessOfOracle:
DBAccessOfOracle.RunSQL(InsertSQL);
break;
case DA.DBUrlType.DBAccessOfODBC:
DBAccessOfODBC.RunSQL(InsertSQL);
break;
default:
break;
}
}
}
#endregion
#region 遍历目的表 如果该条记录存在,continue,如果该条记录不存在,则根据主键删除目的表的对应数据
foreach (DataRow ToDR in ToDataTable.Rows)
{
SELECTSQL = "SELECT ";
foreach (FF ff in this.FFs)
{
if (ff.IsPK == false)
{
continue;
}
SELECTSQL += ff.FromField + ",";
}
SELECTSQL = SELECTSQL.Substring(0, SELECTSQL.Length - 1);
SELECTSQL += " FROM " + this.FromTable + " WHERE ";
foreach (FF ff in this.FFs)
{
if (ff.IsPK == false)
{
continue;
}
if (ff.DataType == DataType.AppDateTime)
{
SELECTSQL += " case " +
" when datalength( CONVERT(VARCHAR,datepart(month," + ff.FromField + " )))=1 then datename(year," + ff.FromField + " )+'-'+('0'+CONVERT(VARCHAR,datepart(month," + ff.FromField + " ))) " +
" else " +
" datename(year," + ff.FromField + " )+'-'+CONVERT(VARCHAR,datepart(month," + ff.FromField + " )) " +
" END " +
"='" + ToDR[ff.ToField].ToString() + "' AND ";
}
else
{
if (ff.DataType == DataType.AppString)
{
SELECTSQL += ff.FromField + "='" + ToDR[ff.ToField].ToString() + "' AND ";
}
else
{
SELECTSQL += ff.FromField + "=" + ToDR[ff.ToField].ToString() + " AND ";
}
}
}
SELECTSQL = SELECTSQL.Substring(0, SELECTSQL.Length - 5);
//SELECTSQL+=this.FromWhere;
result = 0;
switch (this.FromDBUrl)
{
case DA.DBUrlType.AppCenterDSN:
result = DBAccess.RunSQLReturnCOUNT(SELECTSQL);
break;
case DA.DBUrlType.DBAccessOfMSMSSQL:
result = DBAccess.RunSQLReturnCOUNT(SELECTSQL);
break;
case DA.DBUrlType.DBAccessOfOLE:
result = DBAccessOfOLE.RunSQLReturnCOUNT(SELECTSQL);
break;
case DA.DBUrlType.DBAccessOfOracle:
result = DBAccessOfOracle.RunSQL(SELECTSQL);
break;
case DA.DBUrlType.DBAccessOfODBC:
result = DBAccessOfODBC.RunSQLReturnCOUNT(SELECTSQL);
break;
default:
break;
}
if (result != 1)
{
//delete
DeleteSQL = "delete FROM " + this.ToTable + " WHERE ";
foreach (FF ff in this.FFs)
{
if (ff.IsPK == false)
{
continue;
}
if (ff.DataType == DataType.AppString)
{
DeleteSQL += ff.ToField + "='" + ToDR[ff.ToField].ToString() + "' AND ";
}
else
{
DeleteSQL += ff.ToField + "=" + ToDR[ff.ToField].ToString() + " AND ";
}
}
DeleteSQL = DeleteSQL.Substring(0, DeleteSQL.Length - 5);
switch (this.ToDBUrl)
{
case DA.DBUrlType.AppCenterDSN:
DBAccess.RunSQL(DeleteSQL);
break;
case DA.DBUrlType.DBAccessOfMSMSSQL:
DBAccess.RunSQL(DeleteSQL);
break;
case DA.DBUrlType.DBAccessOfOLE:
DBAccessOfOLE.RunSQL(DeleteSQL);
break;
case DA.DBUrlType.DBAccessOfOracle:
DBAccessOfOracle.RunSQL(DeleteSQL);
break;
case DA.DBUrlType.DBAccessOfODBC:
DBAccessOfODBC.RunSQL(DeleteSQL);
break;
default:
break;
}
continue;
}
else if (result > 1)
{
throw new Exception("目的数据异常错误＋表名；关键字" + this.ToTable + "关键字" + ToDR[0].ToString());
}
}
#endregion
if (this.UPDATEsql != null)
{
switch (this.ToDBUrl)
{
case DA.DBUrlType.AppCenterDSN:
DBAccess.RunSQL(UPDATEsql);
break;
case DA.DBUrlType.DBAccessOfMSMSSQL:
DBAccess.RunSQL(UPDATEsql);
break;
case DA.DBUrlType.DBAccessOfOLE:
DBAccessOfOLE.RunSQL(UPDATEsql);
break;
case DA.DBUrlType.DBAccessOfOracle:
DBAccessOfOracle.RunSQL(UPDATEsql);
break;
case DA.DBUrlType.DBAccessOfODBC:
DBAccessOfODBC.RunSQL(UPDATEsql);
break;
default:
break;
}
}
this.DoAfter();
}
/// <summary>
/// 删除之后插入, 用于数据量不太大,更新频率不太频繁的数据处理.
/// </summary>
public void DeleteInsert()
{
this.DoBefore(); //调用业务处理。
// 得到源表.
DataTable FromDataTable = this.GetFromDataTable();
this.DeleteObjData();
#region 遍历源表 插入操作
string InsertSQL = "";
foreach (DataRow FromDR in FromDataTable.Rows)
{
InsertSQL = "INSERT INTO " + this.ToTable + "(";
foreach (FF ff in this.FFs)
{
InsertSQL += ff.ToField.ToString() + ",";
}
InsertSQL = InsertSQL.Substring(0, InsertSQL.Length - 1);
InsertSQL += ") values(";
foreach (FF ff in this.FFs)
{
if (ff.DataType == DataType.AppString || ff.DataType == DataType.AppDateTime)
{
InsertSQL += "'" + FromDR[ff.FromField].ToString() + "',";
}
else
{
InsertSQL += FromDR[ff.FromField].ToString() + ",";
}
}
InsertSQL = InsertSQL.Substring(0, InsertSQL.Length - 1);
InsertSQL += ")";
switch (this.ToDBUrl)
{
case DA.DBUrlType.AppCenterDSN:
DBAccess.RunSQL(InsertSQL);
break;
case DA.DBUrlType.DBAccessOfMSMSSQL:
DBAccessOfMSMSSQL.RunSQL(InsertSQL);
break;
case DA.DBUrlType.DBAccessOfOLE:
DBAccessOfOLE.RunSQL(InsertSQL);
break;
case DA.DBUrlType.DBAccessOfOracle:
DBAccessOfOracle.RunSQL(InsertSQL);
break;
case DA.DBUrlType.DBAccessOfODBC:
DBAccessOfODBC.RunSQL(InsertSQL);
break;
default:
break;
}
}
#endregion
this.DoAfter(); // 调用业务处理。
}
/// <summary>
/// 增量调度：
/// 比如： 纳税人的纳税信息。
/// 特点：1， 数据与时间成增量的增加。
/// 2， 月份以前的数据不变化。
/// </summary>
public void Incremental()
{
/*
* 实现步骤：
* 1，组成sql.
* 2，执行更新。
*
* */
this.DoBefore(); // 调用，更新前的业务逻辑处理。
#region 得到要更新的数据源。
DataTable FromDataTable = this.GetFromDataTable();
#endregion
#region 开始执行更新。
string isExitSql = "";
string InsertSQL = "";
//遍历 数据源表.
foreach (DataRow FromDR in FromDataTable.Rows)
{
#region 判断是否存在．
/* 判断是否存在，如果存在continue. 不存在就 insert. */
isExitSql = "SELECT * FROM " + this.ToTable + " WHERE ";
foreach (FF ff in this.FFs)
{
if (ff.IsPK == false)
{
continue;
}
isExitSql += ff.ToField + "='" + FromDR[ff.FromField] + "' AND ";
}
isExitSql = isExitSql.Substring(0, isExitSql.Length - 5);
if (DBAccess.IsExits(isExitSql)) //如果不存在就 insert .
{
continue;
}
#endregion 判断是否存在
#region 执行插入操作
InsertSQL = "INSERT INTO " + this.ToTable + "(";
foreach (FF ff in this.FFs)
{
InsertSQL += ff.ToField.ToString() + ",";
}
InsertSQL = InsertSQL.Substring(0, InsertSQL.Length - 1);
InsertSQL += ") values(";
foreach (FF ff in this.FFs)
{
if (ff.DataType == DataType.AppString || ff.DataType == DataType.AppDateTime)
{
InsertSQL += "'" + FromDR[ff.FromField].ToString() + "',";
}
else
{
InsertSQL += FromDR[ff.FromField].ToString() + ",";
}
}
InsertSQL = InsertSQL.Substring(0, InsertSQL.Length - 1);
InsertSQL += ")";
switch (this.ToDBUrl)
{
case DA.DBUrlType.AppCenterDSN:
DBAccess.RunSQL(InsertSQL);
break;
case DA.DBUrlType.DBAccessOfMSMSSQL:
DBAccessOfOLE.RunSQL(InsertSQL);
break;
case DA.DBUrlType.DBAccessOfOLE:
DBAccessOfOLE.RunSQL(InsertSQL);
break;
case DA.DBUrlType.DBAccessOfOracle:
DBAccessOfOracle.RunSQL(InsertSQL);
break;
case DA.DBUrlType.DBAccessOfODBC:
DBAccessOfODBC.RunSQL(InsertSQL);
break;
default:
break;
}
#endregion 执行插入操作
}
#endregion 结束,开始执行更新
this.DoAfter(); // 调用，更新之后的业务处理。
}
