将Sqlite数据写入Sqlserver

<summary> /// 上传本地信息至服务器 /// </summary> public static void UploadDataToServer() { try { #region 1.读取本地数据 StringBuilder sbSelect = new StringBuilder(); foreach (string tblName in CMMConstants.UPLOADTABLENAMES) { // 查询未上传的数据 sbSelect.Append(string.Format(" SELECT * FROM {0} WHERE IsUpload=‘0‘ OR IsUpload IS NULL;", tblName)); } SQLiteDBManager _SQLiteDBManager = new SQLiteDBManager(); DataSet ds = _SQLiteDBManager.GetDataSet(sbSelect.ToString(), null); for (int i = 0; i < CMMConstants.UPLOADTABLENAMES.Length; i++) { // 设置DataTableName ds.Tables[i].TableName = CMMConstants.UPLOADTABLENAMES[i]; } #endregion #region 2.上传至服务器 // 服务器数据访问 DBManager _DBManager = new DBManager(); // 获取服务器表的字段列表 DataSet dsColumns = _DBManager.GetColumns(CMMConstants.UPLOADTABLENAMES.ToList()); List<SqlBulkCopyColumnMapping> mappingCollection = null; DataTable dtTemp = null; DataTable dtData = null; DataTable dtCopy = null; string sColumnName = null; // 转换guid列类型及复制数据表数据 Func<DataTable> setType = () => { // 需要设置成guid类型的列名集合 string[] columnNames = new string[] { "ShiYongID", "ShiYanShiID", "ZuoWeiID", "ShiYongMingXiID", "ShuKaJiLuID" }; DataTable dtSetType = dtData.Clone(); foreach (string columnName in columnNames) { if (dtSetType.Columns.Contains(columnName)) { // 设置列类型 dtSetType.Columns[columnName].DataType = typeof(Guid); } } // 填充数据 dtSetType.Load(dtData.CreateDataReader()); return dtSetType; }; // 循环多张表 foreach (string tblName in CMMConstants.UPLOADTABLENAMES) { dtData = ds.Tables[tblName]; dtTemp = dsColumns.Tables[tblName]; mappingCollection = new List<SqlBulkCopyColumnMapping>(); if (dtData.Rows.Count > 0) { // 循环表中的列集合 foreach (DataRow row in dtTemp.Rows) { sColumnName = row["name"].ToString(); if (dtData.Columns.Contains(sColumnName)) { // 添加映射 mappingCollection.Add(new SqlBulkCopyColumnMapping(sColumnName, sColumnName)); } } // 获取转换列类型后的数据表 dtCopy = setType(); // 将数据更新到服务器 _DBManager.BulkCopy(tblName, dtCopy, mappingCollection); // 更新本地数据上传状态 string sKeyName = CMMConstants.UPLOADTABLENAMES_PrimaryKey[tblName]; string sInStr = InStr(dtCopy, sKeyName); string sUpdateSql = string.Format("UPDATE {0} SET IsUpload=‘{1}‘ where {2} IN({3})", tblName, "1", sKeyName, sInStr); int iResult = _SQLiteDBManager.ExeSql(sUpdateSql); } } #endregion } catch (Exception ex) { throw ex; } }

ps:
未加func处理datatable前，报string转guid异常，直接修改datatable列类型报异常有数据时无法更改列类型，
func处理也许并不是最好的，将bulkcopy改为使用自定义表类型，将数据传递到服务器再进行写入也许更好。

 

将Sqlite数据写入Sqlserver

标签：复制   服务   manager   adt   foreach   data   length   ppi   date