using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
namespace Interface
{
public class InOut
{
public int sqlBatch = 500;
private DataSet InterFaceDs;
private string MCDBConnectString = "Provider=Sybase.ASEOLEDBProvider.2;Initial Catalog=dbname;User ID=uid;Password=ps;Server Name=192.168.110.5,5000;LoginTimeOut=240;ClientHostName=導入導出";
private List insertSQL;
private List updateSQL;
private List deleteSQL;
private DataView headerData; //符合過濾條件ProjectID='{0}' and SouTable='{1}' and Type&1=1的結果集
private DataView bodyData; //符號過濾條件TableID={0}的結果集
private List fieldMap; //根據數據源表的列順序生成源數據列與目標表列的對應關係,源表的每列會産生一行
public delegate void DealSQL(object sender);
public event DealSQL DealInsertSQL; //通過事件處理生成的Insert SQL語句
public event DealSQL DealUpdateSQL; //通過事件處理生成的Update SQL語句
public event DealSQL DealDeleteSQL; //通過事件處理生成的Delete SQL語句
public List InsertSql
{
get { return this.insertSQL; }
}
public List UpdateSql
{
get { return this.updateSQL; }
}
public List DeleteSql
{
get { return this.deleteSQL; }
}
private struct TempBody
{
public bool existRec;
public string souField;
public string desField;
public string dataType;
public string defaultValue;
public bool isPrimaryKey;
public TempBody(bool _existRec, string _souField, string _desField, string _dataType, string _defaultValue, bool _isPrimaryKey)
{
existRec = _existRec;
souField = _souField;
desField = _desField;
dataType = _dataType;
defaultValue = _defaultValue;
isPrimaryKey = _isPrimaryKey;
}
}
public InOut(string PID)
{
OleDbConnection MCDB = new OleDbConnection(MCDBConnectString);
string SQL = "select TableID,SouTable,DesTable,Type,FileFormat,FileName,ProjectID from InterfaceH where ProjectID='{0}' at isolation 0" +
" select TableID,SouField,DesField,DataType,DefaultValue,IsPrimKey from InterfaceB where ProjectID='{0}' at isolation 0";
OleDbDataAdapter InterDA = new OleDbDataAdapter(string.Format(SQL, PID), MCDB);
this.InterFaceDs = new DataSet();
InterDA.Fill(this.InterFaceDs);
}
private void GetStruct(DataTable fromTable, string PID)
{
if (fromTable == null)
{
throw new Exception("數據源表不能為NULL");
}
if (fromTable.Rows.Count < 1) return;
string headerfilter = string.Format("ProjectID='{0}' and SouTable='{1}' and Type=1", PID, fromTable.TableName.Trim());
headerData = this.InterFaceDs.Tables[0].DefaultView;
headerData.RowFilter = headerfilter;
if (headerData.Count != 1)
{
throw new Exception(string.Format("符合" + headerfilter + "條件的InterfaceH表記錄數不為1,現等於{0}", headerData.Count));
}
string bodyfilter = string.Format("TableID={0}", headerData.Table.Rows[0][0].ToString().Trim());
bodyData = this.InterFaceDs.Tables[1].DefaultView;
bodyData.RowFilter = bodyfilter;
if (bodyData.Count < 1)
{
throw new Exception("符合" + bodyfilter + "條件的InterfaceB表記錄數小於1");
}
fieldMap = new List();
for (int i = 0; i < fromTable.Columns.Count; i++)
{
int rocNO = -1;
for (int r = 0; r < bodyData.Table.Rows.Count; r++)
{
if (fromTable.Columns[i].ColumnName == bodyData[r]["SouField"].ToString())
{
rocNO = r;
break;
}
}
if (rocNO == -1)
{
fieldMap.Add(new TempBody(false, null, null, null, null, false));
}
else
{
fieldMap.Add(new TempBody(true, bodyData[rocNO]["SouField"].ToString(), bodyData[rocNO]["DesField"].ToString(), bodyData[rocNO]["DataType"].ToString(), bodyData[rocNO]["DefaultValue"].ToString(), (bodyData[rocNO]["IsPrimKey"].ToString() == "Y" ? true : false)));
}
}
int souPrimKey = 0;
foreach (TempBody tb in fieldMap)
{
if (tb.existRec == true && tb.isPrimaryKey == true)
souPrimKey++;
}
int desPrimKey = 0;
foreach (DataRow dr in bodyData.Table.Rows)
{
if (dr["IsPrimKey"].ToString() == "Y") desPrimKey++;
}
if (souPrimKey != desPrimKey)
{
throw new Exception("源數據表不能為目標表的每個主健指定值!");
}
}
public void CreateInsert(DataTable fromTable, string PID)
{
this.GetStruct(fromTable, PID);
StringBuilder insertHeader = new StringBuilder(); //保存Insert語句的前半部份,"values("之前
insertHeader.Append("insert into " + headerData[0]["DesTable"].ToString() + " (");
bool firstField = true;
for (int i = 0; i < fieldMap.Count; i++)
{
if (fieldMap[i].existRec == true)
{
insertHeader.Append((firstField == true) ? fieldMap[i].desField : "," + fieldMap[i].desField);
firstField = false;
}
}
insertHeader.Append(") values (");
StringBuilder insertData = new StringBuilder(); //保存Insert語句的數據部分
insertSQL = new List();
for (int i = 0; i < fromTable.Rows.Count; i++)
{
firstField = true;
StringBuilder tmpData = new StringBuilder();
for (int fn = 0; fn < fieldMap.Count; fn++)
{
if (fieldMap[fn].existRec == true)
{
string firstChar = (firstField == true) ? "" : ",";
switch (fieldMap[fn].dataType.ToLower().Trim())
{
case "string":
case "date":
case "datetime":
tmpData.Append(firstChar + (DBNull.Value.Equals(fromTable.Rows[i][fn]) ? ((fieldMap[fn].defaultValue.ToString() != "") ? "'" + fieldMap[fn].defaultValue.ToString() + "'" : "NULL") : "'" + fromTable.Rows[i][fn].ToString() + "'"));
break;
default:
tmpData.Append(firstChar + (DBNull.Value.Equals(fromTable.Rows[i][fn]) ? ((fieldMap[fn].defaultValue.ToString() != "") ? fieldMap[fn].defaultValue.ToString() : "NULL") : fromTable.Rows[i][fn].ToString()));
break;
}
firstField = false;
}
}
tmpData.Append(")");
this.insertSQL.Add(insertHeader.ToString() + tmpData.ToString());
//分批執行事件
if (this.insertSQL.Count == this.sqlBatch && this.DealInsertSQL != null)
{
this.DealInsertSQL(this);
this.insertSQL = new List();
}
}
if (this.insertSQL.Count > 0 && this.DealInsertSQL != null)
this.DealInsertSQL(this);
this.insertSQL = null;
this.headerData = null;
this.bodyData = null;
this.fieldMap = null;
}
public void CreateUpdate(DataTable fromTable, string PID)
{
this.GetStruct(fromTable, PID);
StringBuilder updateHeader = new StringBuilder(); //保存update語句的前半部份
updateHeader.Append("update " + headerData[0]["DesTable"].ToString() + " set ");
StringBuilder updateData = new StringBuilder(); //保存update語句的數據部分
updateSQL = new List();
for (int i = 0; i < fromTable.Rows.Count; i++)
{
bool firstField = true;
bool firstWhere = true;
StringBuilder tmpData = new StringBuilder();
StringBuilder tmpWhere = new StringBuilder();
for (int fn = 0; fn < fieldMap.Count; fn++)
{
if (fieldMap[fn].existRec == true)
{
string firstChar = ((firstField == true) ? (fieldMap[fn].desField + "=") : ("," + fieldMap[fn].desField + "="));
switch (fieldMap[fn].dataType.ToLower().Trim())
{
case "string":
case "date":
case "datetime":
tmpData.Append(firstChar + (DBNull.Value.Equals(fromTable.Rows[i][fn]) ? ((fieldMap[fn].defaultValue.ToString() != "") ? "'" + fieldMap[fn].defaultValue.ToString() + "'" : "NULL") : "'" + fromTable.Rows[i][fn].ToString() + "'"));
break;
default:
tmpData.Append(firstChar + (DBNull.Value.Equals(fromTable.Rows[i][fn]) ? ((fieldMap[fn].defaultValue.ToString() != "") ? fieldMap[fn].defaultValue.ToString() : "NULL") : fromTable.Rows[i][fn].ToString()));
break;
}
firstField = false;
if (fieldMap[fn].isPrimaryKey == true)
{
if (firstWhere == true)
{
tmpWhere.Append(" where " + fieldMap[fn].desField + "=");
firstWhere = false;
}
else
{
tmpWhere.Append(" and " + fieldMap[fn].desField + "=");
}
switch (fieldMap[fn].dataType.ToLower().Trim())
{
case "string":
case "date":
case "datetime":
tmpWhere.Append(DBNull.Value.Equals(fromTable.Rows[i][fn]) ? ((fieldMap[fn].defaultValue.ToString() != "") ? "'" + fieldMap[fn].defaultValue.ToString() + "'" : "NULL") : "'" + fromTable.Rows[i][fn].ToString() + "'");
break;
default:
tmpWhere.Append(DBNull.Value.Equals(fromTable.Rows[i][fn]) ? ((fieldMap[fn].defaultValue.ToString() != "") ? fieldMap[fn].defaultValue.ToString() : "NULL") : fromTable.Rows[i][fn].ToString());
break;
}
firstWhere = false;
}
}
}
this.updateSQL.Add(updateHeader.ToString() + tmpData.ToString() + tmpWhere.ToString());
if (this.updateSQL.Count == this.sqlBatch && this.DealUpdateSQL != null)
{
this.DealUpdateSQL(this);
this.updateSQL = new List();
}
}
if (this.updateSQL.Count > 0 && this.DealUpdateSQL != null)
this.DealUpdateSQL(this);
this.updateSQL = null;
this.headerData = null;
this.bodyData = null;
this.fieldMap = null;
}
public void CreateDelete(DataTable fromTable, string PID)
{
this.GetStruct(fromTable, PID);
StringBuilder deleteHeader = new StringBuilder(); //保存Delete語句的前半部份
deleteHeader.Append("delete " + headerData[0]["DesTable"].ToString() + " where ");
deleteSQL = new List();
for (int i = 0; i < fromTable.Rows.Count; i++)
{
bool firstWhere = true;
StringBuilder tmpWhere = new StringBuilder(); //保存Delete語句的where部份
for (int fn = 0; fn < fieldMap.Count; fn++)
{
if (fieldMap[fn].existRec == true && fieldMap[fn].isPrimaryKey == true)
{
if (firstWhere == true)
{
tmpWhere.Append(fieldMap[fn].desField + "=");
firstWhere = false;
}
else
{
tmpWhere.Append(" and " + fieldMap[fn].desField + "=");
}
switch (fieldMap[fn].dataType.ToLower().Trim())
{
case "string":
case "date":
case "datetime":
tmpWhere.Append(DBNull.Value.Equals(fromTable.Rows[i][fn]) ? ((fieldMap[fn].defaultValue.ToString() != "") ? "'" + fieldMap[fn].defaultValue.ToString() + "'" : "NULL") : "'" + fromTable.Rows[i][fn].ToString() + "'");
break;
default:
tmpWhere.Append(DBNull.Value.Equals(fromTable.Rows[i][fn]) ? ((fieldMap[fn].defaultValue.ToString() != "") ? fieldMap[fn].defaultValue.ToString() : "NULL") : fromTable.Rows[i][fn].ToString());
break;
}
firstWhere = false;
}
}
this.deleteSQL.Add(deleteHeader.ToString() + tmpWhere.ToString());
if (this.deleteSQL.Count == this.sqlBatch && this.DealDeleteSQL != null)
{
this.DealDeleteSQL(this);
this.deleteSQL = new List();
}
}
if (this.deleteSQL.Count > 0 && this.DealDeleteSQL != null)
this.DealDeleteSQL(this);
this.deleteSQL = null;
this.headerData = null;
this.bodyData = null;
this.fieldMap = null;
}
}
}
阅读(1146) | 评论(0) | 转发(0) |
