利用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,sb.ToString()); DataTable dt = SQLHelper.ExecuteTable(SQL,null); string dbcolumns = "'" + string.Join("',config.ColumnName) + "'"; table.Columns = GetTransportColumn(config.TableName,dbcolumns); table.Rows = GeDataRows(dt,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)); }??