小数据表的同步方法（通过生成拼接sql方式进行）

                strSql = "select * from t_jcj_CJD";
                loacalDt = DbService.Instance.QueryLoacal(strSql);
                UpLoacaTable("CJDBH", "T_JCJ_CJD", loacalDt, null);

  /// <summary>
        /// 同步数据表
        /// </summary>
        /// <param name="keyColumnName">表主键，更新用</param>
        /// <param name="tbName">表名</param>
        /// <param name="dt">内存数据表</param>
        /// <param name="noIncludeCloumns">不需要同步的列 集合</param>
        /// <returns></returns>
        int UpLoacaTable(string keyColumnName, string tbName, DataTable dt, List<string> noIncludeCloumns)
        {
            int scjs = 0;
            int ret = 0;

            if (null == dt || string.IsNullOrEmpty(tbName))
                return ret;

            bool noInclude = false;
            int iNoIncludeCloumnCount = 0;
            if (null != noIncludeCloumns && noIncludeCloumns.Count > 0)
                iNoIncludeCloumnCount = noIncludeCloumns.Count;

            string strfiled = string.Empty, strfields = string.Empty, strInsertValues = string.Empty, strUpdateValues = string.Empty;
            try
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    strfiled = dt.Columns[i].ColumnName;

                    #region 检查是列是否需要参与数据处理
                    noInclude = false;
                    if (null != noIncludeCloumns)
                    {
                        for (int j = 0; j < noIncludeCloumns.Count; j++)
                        {
                            if (noIncludeCloumns[j].ToUpper() == strfiled.ToUpper())
                            {
                                noInclude = true;
                                break;
                            }
                        }
                    }
                    if (noInclude)
                        continue;

                    #endregion

                    strfields += strfiled + ",";
                    strInsertValues += "?,";
                    strUpdateValues += strfiled + "=?,";
                }

                if (strfields != "")
                {
                    strfields = strfields.Substring(0, strfields.LastIndexOf(","));
                    strInsertValues = strInsertValues.Substring(0, strInsertValues.LastIndexOf(","));
                    strUpdateValues = strUpdateValues.Substring(0, strUpdateValues.LastIndexOf(","));
                }

                //组成sql语句
                string strInsertSql = string.Empty, strUpdateSql = string.Empty;

                strInsertSql = "insert into " + tbName + " ("
                            + strfields
                            + ")  values ("
                            + strInsertValues
                            + ")";

                strUpdateSql = "update " + tbName + " set " + strUpdateValues + " where " + keyColumnName + "=?";

                DbParameter[] parametersInsert = new DbParameter[dt.Columns.Count - iNoIncludeCloumnCount];
                DbParameter[] parametersUpdate = new DbParameter[dt.Columns.Count - iNoIncludeCloumnCount + 1];
                string fieldValue = string.Empty;
                string keyColumnValue = string.Empty;

                noInclude = false;

                for (int r = 0; r < dt.Rows.Count; r++)
                {
                    int columnIndex = 0;
                    for (int c = 0; c < dt.Columns.Count; c++)
                    {
                        strfiled = dt.Columns[c].ColumnName;

                        #region 检查是列是否需要参与数据处理
                        noInclude = false;
                        if (null != noIncludeCloumns)
                        {
                            for (int j = 0; j < noIncludeCloumns.Count; j++)
                            {
                                if (noIncludeCloumns[j].ToUpper() == strfiled.ToUpper())
                                {
                                    noInclude = true;
                                    break;
                                }
                            }

                        }
                        if (noInclude)
                            continue;

                        #endregion

                        fieldValue = dt.Rows[r][c].ToString();

                        //本地库删除依据值
                        if (keyColumnName == strfiled)
                            keyColumnValue = fieldValue;

                        parametersInsert[columnIndex] = DbService.Instance.CreateParameter(dt.Columns[c].ColumnName, DbType.String, ParameterDirection.Input, fieldValue = string.IsNullOrEmpty(fieldValue) ? "" : fieldValue);
                        parametersUpdate[columnIndex] = DbService.Instance.CreateParameter(dt.Columns[c].ColumnName, DbType.String, ParameterDirection.Input, fieldValue = string.IsNullOrEmpty(fieldValue) ? "" : fieldValue);

                        columnIndex++;
                    }

                    parametersUpdate[parametersUpdate.Length - 1] = DbService.Instance.CreateParameter(keyColumnName, DbType.String, ParameterDirection.Input, dt.Rows[r][0].ToString());

                    //查找记录是否存在
                    string sqlExist = "select " + keyColumnName + " from " + tbName + " where " + keyColumnName + " =?";
                    DbParameter[] existParameters = new DbParameter[1];
                    existParameters[0] = DbService.Instance.CreateParameter(keyColumnName, DbType.String, ParameterDirection.Input, dt.Rows[r][0].ToString());
                    DbDataReader reader = DbService.Instance.Execute(sqlExist, existParameters);
                    //中心数据库是否存在该条记录
                    bool bRecExist = false;
                    bRecExist = (null != reader && reader.HasRows);
                    if (reader != null) reader.Dispose();//by李平20171016
                    int iRet = 0;//数据库操作结果
                    if (!bRecExist)//不存在，写入
                    {
                        iRet = DbService.Instance.ExecuteNoQuery(strInsertSql, parametersInsert);
                        if (iRet==1) scjs++;
                    }

                    //by lgh 2016-01-04 不用更新
                    /*
                    if (bRecExist)
                        iRet = DbService.Instance.ExecuteNoQuery(strUpdateSql, parametersUpdate);
                    */
                    if (bRecExist) iRet = 1;

                    //删除上传的值
                    if (iRet == 1)
                    {
                        string sqlStr = "delete from " + tbName + " where " + keyColumnName + "='' or " + keyColumnName + "=?";
                        DbParameter[] pmts = new DbParameter[1];
                        pmts[0] = DbService.Instance.CreateParameter(keyColumnName, DbType.String, ParameterDirection.Input, keyColumnValue);
                        int delRet = DbService.Instance.ExecuteNoQueryLoacal(sqlStr, pmts);
                    }
                }

                return ret;
            }
            catch
            {
                return ret;
            }
            finally
            {
              
                LogService.Instance.Info("完成表"+tbName+"数据上传，共上传"+scjs+"条数据");         
            }
        }

  /// <summary>         上传本地数据表         </summary>  
        /// <param name="keyColumnName">关键列</param>    
        /// <param name="tbName">表名</param>   
        /// <param name="dt">内在数据表</param>   
        /// <param name="noIncludeCloumns">不包含列 集合</param>   
        /// <returns></returns>        
        int UpLoacaTable(string keyColumnName, string tbName, DataTable dt, List<string> noIncludeCloumns)
        {
            int scjs = 0; int ret = 0;
            if (null == dt || string.IsNullOrEmpty(tbName)) return ret;
            bool noInclude = false; int iNoIncludeCloumnCount = 0; if (null != noIncludeCloumns && noIncludeCloumns.Count > 0) iNoIncludeCloumnCount = noIncludeCloumns.Count;
            string strfiled = string.Empty, strfields = string.Empty, strInsertValues = string.Empty, strUpdateValues = string.Empty; try
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    strfiled = dt.Columns[i].ColumnName;
                    #region 检查是列是否需要参与数据处理     
                    noInclude = false;
                    if (null != noIncludeCloumns)
                    {
                        for (int j = 0; j < noIncludeCloumns.Count; j++)
                        {
                            if (noIncludeCloumns[j].ToUpper() == strfiled.ToUpper())
                            {
                                noInclude = true;
                                break;
                            }
                        }
                    }
                    if (noInclude)
                        continue;
                    #endregion
                    strfields += strfiled + ","; strInsertValues += "?,"; strUpdateValues += strfiled + "=?,";
                }
                if (strfields != "")
                {
                    strfields = strfields.Substring(0, strfields.LastIndexOf(","));
                    strInsertValues = strInsertValues.Substring(0, strInsertValues.LastIndexOf(","));
                    strUpdateValues = strUpdateValues.Substring(0, strUpdateValues.LastIndexOf(","));
                }
                //组成sql语句               
                string strInsertSql = string.Empty, strUpdateSql = string.Empty;
                strInsertSql = "insert into " + tbName + " (" + strfields + ")  values (" + strInsertValues + ")";
                strUpdateSql = "update " + tbName + " set " + strUpdateValues + " where " + keyColumnName + "=?";
                DbParameter[] parametersInsert = new DbParameter[dt.Columns.Count - iNoIncludeCloumnCount];
                DbParameter[] parametersUpdate = new DbParameter[dt.Columns.Count - iNoIncludeCloumnCount + 1];
                string fieldValue = string.Empty; string keyColumnValue = string.Empty;
                noInclude = false;
                for (int r = 0; r < dt.Rows.Count; r++)
                {
                    int columnIndex = 0; for (int c = 0; c < dt.Columns.Count; c++)
                    {
                        strfiled = dt.Columns[c].ColumnName;
                        #region 检查是列是否需要参与数据处理                    
                        noInclude = false;
                        if (null != noIncludeCloumns)
                        {
                            for (int j = 0; j < noIncludeCloumns.Count; j++)
                            {
                                if (noIncludeCloumns[j].ToUpper() == strfiled.ToUpper())
                                {
                                    noInclude = true;
                                    break;
                                }
                            }
                        }
                        if (noInclude) continue;
                        #endregion
                        fieldValue = dt.Rows[r][c].ToString();
                        //本地库删除依据值        
                        if (keyColumnName == strfiled)
                            keyColumnValue = fieldValue;
                        parametersInsert[columnIndex] = DbService.Instance.CreateParameter(dt.Columns[c].ColumnName, DbType.String, ParameterDirection.Input, fieldValue = string.IsNullOrEmpty(fieldValue) ? "" : fieldValue);
                        parametersUpdate[columnIndex] = DbService.Instance.CreateParameter(dt.Columns[c].ColumnName, DbType.String, ParameterDirection.Input, fieldValue = string.IsNullOrEmpty(fieldValue) ? "" : fieldValue);
                        columnIndex++;
                    }
                    parametersUpdate[parametersUpdate.Length - 1] = DbService.Instance.CreateParameter(keyColumnName, DbType.String, ParameterDirection.Input, dt.Rows[r][0].ToString());
                    //查找记录是否存在                
                    string sqlExist = "select " + keyColumnName + " from " + tbName + " where " + keyColumnName + " =?";
                    DbParameter[] existParameters = new DbParameter[1];
                    existParameters[0] = DbService.Instance.CreateParameter(keyColumnName, DbType.String, ParameterDirection.Input, dt.Rows[r][0].ToString());
                    DbDataReader reader = DbService.Instance.Execute(sqlExist, existParameters);
                    //中心数据库是否存在该条记录                  
                    bool bRecExist = false;
                    bRecExist = (null != reader && reader.HasRows);
                    if (reader != null) reader.Dispose();
                    int iRet = 0;//数据库操作结果          
                    if (!bRecExist)//不存在，写入         
                    {
                        iRet = DbService.Instance.ExecuteNoQuery(strInsertSql, parametersInsert);
                        if (iRet == 1) scjs++;
                    }

                    if (bRecExist) iRet = 1;
                    //删除上传的值                  
                    if (iRet == 1)
                    {
                        string sqlStr = "delete from " + tbName + " where " + keyColumnName + "='' or " + keyColumnName + "=?";
                        DbParameter[] pmts = new DbParameter[1];
                        pmts[0] = DbService.Instance.CreateParameter(keyColumnName, DbType.String, ParameterDirection.Input, keyColumnValue);
                        int delRet = DbService.Instance.ExecuteNoQueryLoacal(sqlStr, pmts);
                    }
                }
                return ret;
            }
            catch
            {
                return ret;
            }
            finally
            {
                LogService.Instance.Info("完成表" + tbName + "数据上传，共上传" + scjs + "条数据");
            }
        }