#region 下载时重构insert（数据带null处理） public void DownDataInsert(DataTable _dt, string TableName,DBHelper dbhelper) { List<string> _List = new List<string>(); try { if (_dt == null) { throw new Exception("datatable不可为空！"); } if (string.IsNullOrEmpty(TableName)) { throw new Exception("表名不可为空！"); } for (int i = 0; i < _dt.Rows.Count; i++) { string sqlstring = string.Empty; string sqlString = "insert into " + TableName + " ({0}) VALUES ({1})"; string Names = string.Empty; string values = string.Empty; foreach (DataColumn c in _dt.Columns) { if (_dt.Rows[i][c.ColumnName] != null && !string.IsNullOrEmpty(_dt.Rows[i][c.ColumnName].ToString())) { Names += (string.IsNullOrEmpty(Names) ? "" : ",") + c.ColumnName; if (IsType(c.DataType, "System.Nullable`1[System.Int16]") || IsType(c.DataType, "System.Nullable`1[System.Int32]") || IsType(c.DataType, "System.Nullable`1[System.Int64]") || IsType(c.DataType, "System.Nullable`1[System.Double]") || IsType(c.DataType, "System.Nullable`1[System.Decimal]") || c.DataType == typeof(System.Int16) || c.DataType == typeof(System.Int32) || c.DataType == typeof(System.Int64) || c.DataType == typeof(System.Double) || c.DataType == typeof(System.Decimal)) { values += (string.IsNullOrEmpty(values) ? "" : ",") + _dt.Rows[i][c.ColumnName].ToString(); } if (IsType(c.DataType, "System.Nullable`1[System.DateTime]") || c.DataType == typeof(System.DateTime)) { values += (string.IsNullOrEmpty(values) ? "" : ",") + "DateTime('" + _dt.Rows[i][c.ColumnName].ToString() + "')"; } if (IsType(c.DataType, "System.String") || c.DataType == typeof(System.String)) { values += (string.IsNullOrEmpty(values) ? "" : ",") + "'" + _dt.Rows[i][c.ColumnName].ToString() + "'"; } } } if (!string.IsNullOrEmpty(Names) && !string.IsNullOrEmpty(values)) { sqlstring = string.Format(sqlString, Names, values); _List.Add(sqlstring); } } dbhelper.ExecuteSqlTran(_List); } catch (Exception ex) { throw ex; } finally { _List = null; } } #endregion #region 比较两个datatable,值不一样的相应的修改下载处理 public void CompareToDataTable(DataTable SourTable, DataTable TargetTable, string TableName) { string SelectString = string.Empty; List<string> _list = new List<string>(); DataTable dtInsert = null; DataTable drUpdate = null; try { dtInsert = SourTable.Clone(); drUpdate = SourTable.Clone(); foreach (DataRow dr in SourTable.Rows) { if (TableName == "t_sellworker") { SelectString = "serverpartcode='" + dr["serverpartcode"].ToString() + "' and sellworkercode='" + dr["sellworkercode"].ToString() + "'"; } else if (TableName == "t_salespromote") { SelectString = "salespromote_id=" + dr["salespromote_id"].ToString() + ""; // SelectString = "salespromote_startdate='" + dr["salespromote_startdate"].ToString() + "' and salespromote_enddate='" + dr["salespromote_enddate"].ToString() + "' " + // " and salespromote_type='" + dr["salespromote_type"].ToString() + "' and salespromote_name='" + dr["salespromote_name"].ToString() + "' and commodity_code='" + dr["commodity_code"].ToString() + "'"; } else if (TableName == "t_commodityex") { SelectString = "commodity_code='" + dr["commodity_code"].ToString() + "' and commodity_barcode='" + dr["commodity_barcode"].ToString() + "' "; // and serverpartshop_id=" + dr["serverpartshop_id"].ToString() + " } DataRow[] dr2 = TargetTable.Select(SelectString); if (dr2 != null && dr2.Length > 0) { bool flag = false; foreach (DataRow dr3 in dr2) { foreach (DataColumn c in SourTable.Columns) { if (dr3[c.ColumnName] != null && dr[c.ColumnName] != null && dr3[c.ColumnName].ToString() != dr[c.ColumnName].ToString()) { flag = true; break; } } } if (flag) { drUpdate.Rows.Add(dr.ItemArray); } } else { dtInsert.Rows.Add(dr.ItemArray); //添加数据行 } } if (drUpdate != null && drUpdate.Rows.Count > 0) { if(TableName=="t_sellworker") { UpdateData(drUpdate, "t_sellworker", new string[] { "serverpartcode", "sellworkercode" }, TargetDb); } else if (TableName == "t_salespromote") { UpdateData(drUpdate, "t_salespromote", new string[] { "salespromote_startdate", "salespromote_enddate", "salespromote_type", "salespromote_name", "commodity_code"}, TargetDb); } else if (TableName == "t_commodityex") { UpdateData(drUpdate, "t_commodityex", new string[] { "serverpartcode", "commodity_barcode", "serverpartshop_id" }, TargetDb); } MessageBox.Show("更新成功！"); } if (dtInsert != null && dtInsert.Rows.Count > 0) { DownDataInsert(dtInsert, TableName, TargetDb);//插入 MessageBox.Show("下载成功！"); } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { dtInsert = null; drUpdate = null; } } #endregion #region 下载时重构update语句(处理数据空的情况) public void UpdateData(DataTable table, string TableName, string[] keys,DBHelper dbhelper) { List<string> _List = null; Dictionary<string, string> keyList = null; try { if (_List == null) { _List = new List<string>(); } else { _List.Clear(); } if (table == null) { throw new Exception("行不可为空！"); } if (string.IsNullOrEmpty(TableName)) { throw new Exception("表名不可为空！"); } for (int i = 0; i < table.Rows.Count; i++) { if (keyList == null) { keyList = new Dictionary<string, string>(); } else { keyList.Clear(); } string sqlstring = string.Empty; string sqlString = "Update " + TableName + " set {0} where {1}"; string values = string.Empty; foreach (DataColumn c in table.Columns) { if (table.Rows[i][c.ColumnName] != null && !string.IsNullOrEmpty(table.Rows[i][c.ColumnName].ToString())) { if (IsType(c.DataType, "System.Nullable`1[System.Int16]") || IsType(c.DataType, "System.Nullable`1[System.Int32]") || IsType(c.DataType, "System.Nullable`1[System.Int64]") || IsType(c.DataType, "System.Nullable`1[System.Double]") || IsType(c.DataType, "System.Nullable`1[System.Decimal]") || c.DataType == typeof(System.Int16) || c.DataType == typeof(System.Int32) || c.DataType == typeof(System.Int64) || c.DataType == typeof(System.Double) || c.DataType == typeof(System.Decimal)) { values += ((string.IsNullOrEmpty(values) ? "" : ",") + c.ColumnName + "=" + table.Rows[i][c.ColumnName].ToString()); if (keys.Contains(c.ColumnName.ToUpper()) || keys.Contains(c.ColumnName.ToLower())) { keyList.Add(c.ColumnName, table.Rows[i][c.ColumnName].ToString()); } } if (IsType(c.DataType, "System.Nullable`1[System.DateTime]") || c.DataType == typeof(System.DateTime)) { values += ((string.IsNullOrEmpty(values) ? "" : ",") + c.ColumnName + "=" + "DateTime('" + table.Rows[i][c.ColumnName].ToString() + "')"); if (keys.Contains(c.ColumnName.ToUpper()) || keys.Contains(c.ColumnName.ToLower())) { keyList.Add(c.ColumnName, "DateTime('" + table.Rows[i][c.ColumnName].ToString() + "')"); } } if (IsType(c.DataType, "System.String") || c.DataType == typeof(System.String)) { values += (string.IsNullOrEmpty(values) ? "" : ",") + c.ColumnName + "=" + "'" + table.Rows[i][c.ColumnName].ToString() + "'"; if (keys.Contains(c.ColumnName.ToUpper()) || keys.Contains(c.ColumnName.ToLower())) { keyList.Add(c.ColumnName, "'" + table.Rows[i][c.ColumnName].ToString() + "'"); } } } } if (!string.IsNullOrEmpty(values) && keyList != null && keyList.Count == keys.Length) { string strKeys = string.Empty; foreach (KeyValuePair<string, string> kvp in keyList) { strKeys += ((string.IsNullOrEmpty(strKeys) ? "" : " AND ") + kvp.Key + " = " + kvp.Value); } if (!string.IsNullOrEmpty(strKeys)) { sqlstring = string.Format(sqlString, values, strKeys); _List.Add(sqlstring); } } } dbhelper.ExecuteSqlTran(_List); } catch (Exception ex) { throw ex; } finally { _List = null; keyList = null; } } #endregion #region 上传时重构目标数据表insert（数据中带null的处理） public void InsertTableData(DataTable dt, string TableName, DBHelper dbhelper) { try { List<string> _List = new List<string>(); if (dt == null) { throw new Exception("datatable不可为空！"); } if (string.IsNullOrEmpty(TableName)) { throw new Exception("表名不可为空！"); } for (int i = 0; i < dt.Rows.Count; i++) { string sqlstring = string.Empty; string sqlString = "insert into " + TableName + " ({0}) VALUES ({1})"; string Names = string.Empty; string values = string.Empty; foreach (DataColumn c in dt.Columns) { if (dt.Rows[i][c.ColumnName] != null && !string.IsNullOrEmpty(dt.Rows[i][c.ColumnName].ToString())) { Names += (string.IsNullOrEmpty(Names) ? "" : ",") + c.ColumnName; if (IsType(c.DataType, "System.Nullable`1[System.Int16]") || IsType(c.DataType, "System.Nullable`1[System.Int32]") || IsType(c.DataType, "System.Nullable`1[System.Int64]") || IsType(c.DataType, "System.Nullable`1[System.Double]") || IsType(c.DataType, "System.Nullable`1[System.Decimal]") || c.DataType == typeof(System.Int16) || c.DataType == typeof(System.Int32) || c.DataType == typeof(System.Int64) || c.DataType == typeof(System.Double) || c.DataType == typeof(System.Decimal)) { values += (string.IsNullOrEmpty(values) ? "" : ",") + dt.Rows[i][c.ColumnName].ToString(); } if (IsType(c.DataType, "System.Nullable`1[System.DateTime]") || c.DataType == typeof(System.DateTime)) { values += (string.IsNullOrEmpty(values) ? "" : ",") + "DateTime('" + dt.Rows[i][c.ColumnName].ToString() + "')"; } if (IsType(c.DataType, "System.String") || c.DataType == typeof(System.String)) { values += (string.IsNullOrEmpty(values) ? "" : ",") + "'" + dt.Rows[i][c.ColumnName].ToString() + "'"; } } } if (!string.IsNullOrEmpty(Names) && !string.IsNullOrEmpty(values)) { sqlstring = string.Format(sqlString, Names, values); _List.Add(sqlstring); } } dbhelper.ExecuteSqlTran(_List); } catch (Exception ex) { throw ex; } } #endregion
DBHelper SourceDb = new DBHelper("192.168.11.197", "pos1101", "dba", "sql");//源数据库 DBHelper TargetDb = new DBHelper("127.0.0.1", "pos012", "dba", "sql"); //目标
public class DBHelper { #region 构造函数 public DBHelper(string ConnStr) { connstring = ConnStr; } public DBHelper(string Host, string Server, string UserId, string Password) { connstring = "host=" + Host + ";server=" + Server + ";userid=" + UserId + ";password=" + Password + ";"; } public DBHelper() { } #endregion #region 属性信息 private string connstring = null; public string ConnStr { set { connstring = value; } get { return connstring; } } #endregion public DataSet QueryOdbc(string SqlString) { using (SAConnection conn = new SAConnection(connstring)) { SACommand cmd = new SACommand(SqlString, conn); try { conn.Open(); SADataAdapter adp = new SADataAdapter(cmd); DataSet ds = new DataSet(); adp.Fill(ds); conn.Close(); return ds; } catch (Exception ex) { throw new Exception(ex.Message); } } } /// <summary> /// 保存数据 /// </summary> /// <param name="SQLStringList"></param> public void ExecuteSqlTran(List<string> SQLStringList) { using (SAConnection conn = new SAConnection(connstring)) { conn.Open(); SACommand cmd = new SACommand { Connection = conn }; SATransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { for (int i = 0; i < SQLStringList.Count; i++) { string strsql = SQLStringList[i].ToString(); if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; cmd.ExecuteNonQuery(); } } tx.Commit(); } catch (Exception ex) { tx.Rollback(); throw new Exception(ex.Message); } finally { conn.Close(); } } } /// <summary> /// 保存数据 /// </summary> /// <param name="SQLStringList"></param> public void ExecuteSqlTran(string SQLString) { using (SAConnection conn = new SAConnection(connstring)) { conn.Open(); SACommand cmd = new SACommand { Connection = conn }; SATransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { if (SQLString.Trim().Length > 1) { cmd.CommandText = SQLString; cmd.ExecuteNonQuery(); } tx.Commit(); } catch (Exception E) { tx.Rollback(); throw new Exception(E.Message); } finally { conn.Close(); } } } /// <summary> /// 获取最大主键ID /// </summary> /// <param name="SqlString"></param> /// <returns></returns> public int OdbcGetMaxPKID(string SqlString) { using (SAConnection conn = new SAConnection(connstring)) { SACommand cmd = new SACommand(SqlString, conn); try { conn.Open(); int max_id = cmd.ExecuteScalar().ToString().Equals("") ? 0 : int.Parse(cmd.ExecuteScalar().ToString()); conn.Close(); return max_id; } catch (Exception ex) { throw new Exception(ex.Message); } } } ///// <summary> ///// 测试连接 ///// </summary> //public static void TestConn() //{ // using (SAConnection Conn = new SAConnection(connstring)) // { // Conn.Open(); // SACommand cmd = new SACommand(); // cmd.Connection = Conn; // Conn.Close(); // } //} }
dbhelper类