利用webService上传数据(SQL Server 上传到oracle数据库)

 客户端代码

首先定义客户端的数据库(SQL Server) 中一个表

ChangeLogForSyncID uniqueidentifier Unchecked
EventID int Unchecked (自增)
IsUpload bit Unchecked
OperateType int Checked (增删改)
BusinessType int Checked (具体业务)
BusinessID varchar(100) Checked (业务ID)
OperateTime datetime Unchecked

 

WebService 端定义上传数据格式(也是一个树形结构表)

public class TransportDataTable { public BusinessType BusinessType { get; set; } public string TableName { get; set; } public TableOperateType tableOperateType; private string[] keyFields = new string[0]; public string[] KeyFields { get { return keyFields; } set { keyFields = value; } } private string[] parentFields = new string[0]; public string[] ParentFields { get { return parentFields; } set { parentFields = value; } } private TransportColumn[] columns = new TransportColumn[0]; public TransportColumn[] Columns { get { return columns; } set { columns = value; } } private TransportDataRow[] rows = new TransportDataRow[0]; public TransportDataRow[] Rows { get { return rows; } set { rows = value; } } private TransportDataTable[] children = new TransportDataTable[0]; public TransportDataTable[] Children { get { return children; } set { children = value; } } public TransportDataTable() { } }

具体实现

客户端通过配置 表名,列名,主键,字表,业务类型(这里只做两种类型,同步(插入和修改是一种操作)和删除),主键在字表的名称(这里可能就是主键在字表的名称会发生变化) protected TransportDataTable CreateTable(BaseUploadConfig config) { TransportDataTable table = new TransportDataTable(); table.KeyFields = config.KeyFields; table.TableName = config.TableName.ToLower(); string columns = string.Join(",", config.ColumnName); string dbcolumns = "'" + string.Join("','", config.ColumnName) + "'"; table.Columns = GetTransportColumn(config.TableName, dbcolumns); if (config.OperateType == BusinessOperateType.Delete) table.tableOperateType = TableOperateType.Delete; else table.tableOperateType = TableOperateType.Sync; string[] businessIDArr = config.BusinessID.Split(','); string[] KeyFieldsArr = config.KeyFields; StringBuilder sb = new StringBuilder(); for (int i = 0; i < KeyFieldsArr.Length; i++) { sb.AppendFormat("{0}='{1}'", KeyFieldsArr[i], businessIDArr[i]); } string SQL = "select top 1 {0} from {1} where {2} "; SQL = string.Format(SQL, columns, config.TableName, sb.ToString()); DataTable dt = SQLHelper.ExecuteTable(SQL, null); table.Rows = GeDataRows(dt,config,table); return table; } private TransportDataTable[] CreateChildren(BaseUploadConfig[] children, ArrayList parentValueList) { ArrayList arr = new ArrayList(); StringBuilder sb = null; string SQL = string.Empty; string columns = string.Empty; foreach (BaseUploadConfig config in children) { TransportDataTable table = new TransportDataTable(); sb = new StringBuilder(); table.KeyFields = config.KeyFields; table.ParentFields = config.ParentFields; table.TableName = config.TableName.ToLower(); columns = string.Join(",", config.ColumnName); string[] parentFieldArr = config.ParentFields; string[] businessIDArr = (string[])parentValueList.ToArray(typeof(string)); for (int i = 0; i < businessIDArr.Length; i++) { sb.AppendFormat("{0}='{1}'", parentFieldArr[i], businessIDArr[i]); } SQL = "select {0} from {1} where {2} "; SQL = string.Format(SQL, columns, config.TableName, sb.ToString()); DataTable dt = SQLHelper.ExecuteTable(SQL, null); string dbcolumns = "'" + string.Join("','", config.ColumnName) + "'"; table.Columns = GetTransportColumn(config.TableName, dbcolumns); table.Rows = GeDataRows(dt, config,table); arr.Add(table); } return (TransportDataTable[])arr.ToArray(typeof(TransportDataTable)); } private TransportDataRow[] GeDataRows(DataTable dt, BaseUploadConfig parentConfig, TransportDataTable parentTable) { List<string> keyFields = parentTable.KeyFields.ToList(); ArrayList parentList = null; ArrayList arr = new ArrayList(); for (int i = 0; i < dt.Rows.Count; i++) { parentList = new ArrayList(); TransportDataRow dr = new TransportDataRow(); FeGIS.BLL.ExternalDataUploadService.DictionaryEntry[] de = new FeGIS.BLL.ExternalDataUploadService.DictionaryEntry[dt.Columns.Count]; for (int j = 0; j < dt.Columns.Count; j++) { de[j] = new FeGIS.BLL.ExternalDataUploadService.DictionaryEntry(); de[j].Key = dt.Columns[j].ColumnName.ToLower(); de[j].Value = dt.Rows[i][j] == DBNull.Value ? "" : dt.Rows[i][j]; bool flag = keyFields.Exists(delegate(string str) { return str.ToLower() == de[j].Key.ToString().ToLower(); }); if (flag) parentList.Add(de[j].Value); } dr.FieldValues = de; arr.Add(dr); if (parentConfig.Children != null && parentConfig.Children.Length > 0) { parentTable.Children = CreateChildren(parentConfig.Children, parentList); } } return (TransportDataRow[])arr.ToArray(typeof(TransportDataRow)); }

自动构造树形数据;构造树形数据代码在此;

 

需要注意的是oracle 和sql  日期处理不同

这里根据数据库字典查询某一个表里的所有列

---

private TransportColumn[] GetTransportColumn(string tableName, string columns) { ArrayList arr = new ArrayList(); string SQL = "select o.name as tbname, c.name as columnname, t.name as columntype" + " from sys.tables as o" + " join sys.columns as c" + " on o.object_id=c.object_id" + " join sys.types as t" + " on c.system_type_id=t.system_type_id where o.name='" + tableName + "' and c.name in (" + columns.ToLower() + ")"; using (SqlDataReader sdr = SQLHelper.ExecuteReader(CommandType.Text, SQL, null)) { while (sdr.Read()) { TransportColumn col = new TransportColumn(); col.fieldName = sdr["columnname"].ToString().ToLower(); col.fieldType = GetColumnType(sdr["columntype"].ToString(),col.fieldName); arr.Add(col); } } return (TransportColumn[])arr.ToArray(typeof(TransportColumn)); }