/// <summary> /// /// </summary> /// <param name="connectionName"></param> /// <param name="dbTableName"></param> /// <param name="table"></param> /// <returns></returns> public static List<SqlCommandObject> GeneratorCommand(string connectionName, string dbTableName, DataTable table, DataRowState state) { if (connectionName.IsEmpty()) throw new Exception("GeneratorCommand方法中的connectionName为空."); List<SqlCommandObject> cmds = new List<SqlCommandObject>(); if (table.IsEmpty()) return cmds; InsertCommandGenerator insertCommandGenerator = new InsertCommandGenerator(); DeleteCommandGenerator deleteCommandGenerator = new DeleteCommandGenerator(); UpdateCommandGenerator updateCommandGenerator = new UpdateCommandGenerator(); SqlCommandObject cmd = null; if (table != null) { DataTable dtChanges = null; if (state == DataRowState.Unchanged) { dtChanges = table.GetChanges(); } else { dtChanges = table.GetChanges(state); } if (dtChanges == null) return cmds; if (dbTableName.IsEmpty()) throw new Exception("GeneratorCommand方法中的tableName为空."); foreach (DataRow dr in dtChanges.Rows) { switch (dr.RowState) { case DataRowState.Deleted: cmd = deleteCommandGenerator.GenerateCommand(connectionName, dbTableName, dr); break; case DataRowState.Modified: cmd = updateCommandGenerator.GenerateCommand(connectionName, dbTableName, dr); break; case DataRowState.Added: cmd = insertCommandGenerator.GenerateCommand(connectionName, dbTableName, dr); break; default: cmd = null; break; } if (cmd != null) { cmds.Add(cmd); } } } return cmds; }
public int Update(DataTable data) { int result = 0; if (data.GetChanges() != null) { adapter.UpdateCommand = GetUpdateCommand(); adapter.InsertCommand = GetInsertCommand(); adapter.DeleteCommand = GetDeleteCommand(); result = adapter.Update(data.GetChanges()); data.AcceptChanges(); } return result; }
public static DataTable CompareTwoDataTable(DataTable dt1, DataTable dt2) { dt1.Merge(dt2); DataTable d3 = dt2.GetChanges(); return(d3); }
/// <summary> /// 保存附件 /// </summary> /// <param name="storage">附件数据</param> public void SaveAttachedFile(DataTable storage) { DataTable temp = storage.GetChanges(); //获取修改的记录 if (temp != null)//有新增或修改附件,保存数据 { IBridge_DataDict bridge = BridgeFactory.CreateDataDictBridge(typeof(tb_sys_AttachFile)); DataSet ds = new DataSet(); ds.Tables.Add(temp); bridge.Update(ds); } }
private void btnOK_Click(object sender, EventArgs e) { if (MenuSet.pwdSet == '0') { MessageBox.Show("没有设置该权限!", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } string strPwd = txtPwd.Text.Trim(); string strSel = string.Format("SELECT * FROM rhdbs.user_info WHERE user_id='{0}' AND pwd='{1}'", TheToken.LoginUser, strPwd); DataTable dt = new DataTable(); MySqlConnection conn = new MySqlConnection(TheToken.DataConnStr); conn.Open(); MySqlDataAdapter da = new MySqlDataAdapter(strSel, conn); try { da.Fill(dt); } catch { MessageBox.Show("密码错误!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } try { if (txtPwdNew.Text != txtPwdNew2.Text) { MessageBox.Show("两次输入密码不一致!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } dt.Rows[0].BeginEdit(); dt.Rows[0]["pwd"] = txtPwdNew.Text; dt.Rows[0].EndEdit(); MySqlCommandBuilder scb = new MySqlCommandBuilder(da); scb.ReturnGeneratedIdentifiers = false; da.Update(dt.GetChanges()); dt.AcceptChanges(); } catch { MessageBox.Show("密码错误!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } MessageBox.Show("密码修改成功。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); }
public bool Add(int taskID, int userID, string message, List<DataModel.Modal.CommentAttachment> commentAttachments) { int retunvalue; try { var datatable = new DataTable(); datatable.Columns.Add("Name", typeof(string)); datatable.Columns.Add("Path", typeof(string)); if (commentAttachments != null) { foreach (var commentAttachment in commentAttachments) { var row = datatable.NewRow(); row["Name"] = commentAttachment.FileResource.name; row["Path"] = commentAttachment.FileResource.path; datatable.Rows.Add(row); } } using (SqlConnection con = new SqlConnection("Data Source=DESKTOP-7QFA5C9\\MSSQLSERVER_2012;Initial Catalog=SimplePlatformTemp;user id=sa;password=sa;")) { con.Open(); using (var command = new SqlCommand()) { command.Connection = con; command.CommandText = "[dbo].[sproc_SimplePlatForm_AddComment]"; command.CommandType = CommandType.StoredProcedure; command.Parameters.Add(new SqlParameter { ParameterName = "@taksID", SqlDbType = SqlDbType.Int, Value = taskID }); command.Parameters.Add(new SqlParameter { ParameterName = "@userID", SqlDbType = SqlDbType.Int, Value = userID }); command.Parameters.Add(new SqlParameter { ParameterName = "@message", SqlDbType = SqlDbType.VarChar, Value = message }); var dataTableParameter = command.Parameters.AddWithValue("@Attachments", datatable.GetChanges()); dataTableParameter.SqlDbType = SqlDbType.Structured; dataTableParameter.TypeName = "[dbo].[Attachments]"; command.Parameters.Add(new SqlParameter { ParameterName = "@Status", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.ReturnValue }); command.ExecuteNonQuery(); retunvalue = (int)command.Parameters["@Status"].Value; } con.Close(); } return retunvalue == 1; } catch (Exception ex) { return false; } }
/// <summary> /// 检测是否有未保存的更改(用于窗体关闭时) /// </summary> /// <param name="table">用于检测是否有更改的DataTable</param> /// <returns></returns> public static bool IsChanged(SD.DataTable table) { try { if (table.GetChanges() != null) { return(true); } else { return(false); } } catch (Exception) { return(false); } }
private void butLuu_Click(object sender, System.EventArgs e) { Cursor = Cursors.WaitCursor; try { System.Data.DataTable dttmp = dt.GetChanges(); int i_vat = 0; foreach (DataRow r in dttmp.Rows) { d.upd_eve_tables(itable, i_userid, "upd"); d.upd_eve_upd_del(itable, i_userid, "upd", d.fields(user + ".d_dmbd", "id=" + decimal.Parse(r["id"].ToString()))); if (r["vat"].ToString() != "") { i_vat = int.Parse(r["vat"].ToString()); } else { i_vat = 0; } string s_sql = "update " + user + ".d_dmbd set giaban=" + decimal.Parse(r["giaban_vat"].ToString()) + "," +//Sua giaban =>giaban_vat : aLinh noi gia ban trong d_dmbd la gia da co vat roi "vat=" + i_vat + "," + "dongia=" + decimal.Parse(r["giamua"].ToString()) + "," + "gia_bh=" + decimal.Parse(r["gia_bh"].ToString()) + ",gia_phuthu=" + decimal.Parse(r["gia_phuthu"].ToString()) + ",gia_bh_novat=" + decimal.Parse(r["gia_bh_novat"].ToString()) + ""; if (bGiaban_noi_ngoai) { s_sql += ",giaban2=" + decimal.Parse(r["giaban2"].ToString()); } else { s_sql += ",giaban2=giaban"; } //s_sql += ",ngayud=now(),gia_cucduoc=" + decimal.Parse(r["gia_cucduoc"].ToString()).ToString() + " where id=" + decimal.Parse(r["id"].ToString());//Thuy 30.01.2013 ko update ngayud=>A Binh yêu cầu s_sql += ",gia_cucduoc=" + decimal.Parse(r["gia_cucduoc"].ToString()).ToString() + " where id=" + decimal.Parse(r["id"].ToString());//Thuy 30.01.2013 ko update ngayud=>A Binh yêu cầu d.execute_data(s_sql); } } catch (Exception ex) { } Cursor = Cursors.Default; MessageBox.Show(lan.Change_language_MessageText("Đã cập nhật thành công !"), d.Msg); }
public int UpdateDataTable1(string sql, System.Data.DataTable dt, System.Data.OracleClient.OracleDataAdapter sda) { int i = 0; try { System.Data.OracleClient.OracleCommandBuilder builder = new System.Data.OracleClient.OracleCommandBuilder(sda); sda.UpdateCommand = builder.GetUpdateCommand(); if (dt.GetChanges() != null) { i = sda.Update(dt); dt = GetDataTable(sql); } } catch (Exception ex) { MessageBox.Show("Error:" + ex.Message.ToString()); return(-1); } return(i); }
/// <summary> /// Gengerates the specified table. /// </summary> /// <param name="table">The table.</param> /// <returns><c>true</c> if XXXX, <c>false</c> otherwise</returns> /// <exception cref="System.NullReferenceException">DbHelper can't be null!</exception> public override bool Gengerate(DataTable table) { if (_Helper == null) throw new NullReferenceException("DbHelper can't be null!"); if(String.IsNullOrEmpty(_TableName) || String.IsNullOrEmpty(_Id) || table.Columns.Contains(_Id) == false ) return false; DataTable adds = table.GetChanges(DataRowState.Added); if(adds == null) return true; long iStart; object rtn = _Helper.ExcuteForUnique("select max(" + _Id + ") from " + _TableName); iStart = rtn == null ? 0 : Convert.ToInt64(rtn); foreach (DataRow row in table.Rows) { if (row.RowState == DataRowState.Added) { row.SetField<long>(_Id, ++iStart); } } return true; }
public DataTable SetTable(DataTable dt) { try { DataTable dtChanges = dt.GetChanges(); if (dtChanges == null) throw new Exception("There are no changes to be saved!"); CheckPKExists(dt); //Open and query if (_oleConn ==null) Open(); if (_oleConn.State != ConnectionState.Open) throw new Exception("Connection cannot open error."); if (SetSheetQuerySelect()==false) return null; //Fill table OleDbDataAdapter oleAdapter = SetSheetQueryAdapter(dtChanges); oleAdapter.Update(dtChanges); //Clean up _oleCmdSelect.Dispose(); _oleCmdSelect=null; oleAdapter.Dispose(); oleAdapter=null; if (KeepConnectionOpen==false) Close(); return dt; } catch (Exception ex) { throw ex; } }
private string GetTableChangeAml(DataTable table) { var arasProxy = _proxy as ArasEditorProxy; var context = arasProxy == null ? ElementFactory.Local.LocalizationContext : arasProxy.Connection.AmlContext.LocalizationContext; var changes = table.GetChanges(DataRowState.Added | DataRowState.Deleted | DataRowState.Modified); if (changes == null) return string.Empty; var settings = new System.Xml.XmlWriterSettings(); settings.OmitXmlDeclaration = true; settings.Indent = true; settings.IndentChars = " "; var types = table.AsEnumerable() .Select(r => r.CellValue(Extensions.AmlTable_TypeName).ToString()) .Where(t => !string.IsNullOrEmpty(t)) .Distinct().ToList(); var singleType = types.Count == 1 ? types[0] : null; object newValue; using (var writer = new System.IO.StringWriter()) using (var xml = XmlWriter.Create(writer, settings)) { xml.WriteStartElement("AML"); foreach (var row in changes.AsEnumerable()) { xml.WriteStartElement("Item"); xml.WriteAttributeString("type", singleType ?? row.CellValue(Extensions.AmlTable_TypeName).ToString()); xml.WriteAttributeString("id", row.CellIsNull("id") ? Guid.NewGuid().ToString("N").ToUpperInvariant() : row.CellValue("id").ToString()); switch (row.RowState) { case DataRowState.Added: xml.WriteAttributeString("action", "add"); foreach (var column in changes.Columns.OfType<DataColumn>()) { if (!column.ColumnName.Contains('/') && column.ColumnName != Extensions.AmlTable_TypeName && !row.IsNull(column)) { xml.WriteElementString(column.ColumnName, context.Format(row[column])); } } break; case DataRowState.Deleted: xml.WriteAttributeString("action", "delete"); break; case DataRowState.Modified: xml.WriteAttributeString("action", "edit"); foreach (var column in changes.Columns.OfType<DataColumn>()) { if (!column.ColumnName.Contains('/') && column.ColumnName != Extensions.AmlTable_TypeName && IsChanged(row, column, out newValue)) { xml.WriteElementString(column.ColumnName, context.Format(newValue)); } } break; } xml.WriteEndElement(); } xml.WriteEndElement(); xml.Flush(); return writer.ToString() ?? string.Empty; } }
private void UpdateData(DataTable table) { DataTable deleted = table.GetChanges(DataRowState.Unchanged); if (deleted != null) { DataColumn[] columns = table.PrimaryKey; if (columns != null && columns.Length != 0) { foreach (DataRow row in deleted.Rows) { object[] keys = GetPrimaryKeyValue(row, columns); DataRow found = table.Rows.Find(keys); if (found != null) table.Rows.Remove(found); } } } table.AcceptChanges(); }
/// <summary> /// 多表操作用该方法 /// </summary> /// <param name="pkColumn">Update时的主键,请在数据库表中设置相应的主键</param> /// <param name="dsUpdate">需要操作的数据表集合</param> /// <returns></returns> internal bool UpdateDB (string pkColumn, DataSet dsUpdate) { bool bRst = false; int haveDt = dsUpdate.Tables.Count; DataTable dtSoucre = new DataTable(); DataTable dtSubmit = new DataTable(); if (haveDt > 0) { DBConn cn = new DBConn(this.appName); using (cn.sqlCon) { cn.sqlCon.Open(); SqlTransaction sqlTransaction = cn.sqlCon.BeginTransaction(); try { //处理表情况 for (int i = 0; i < dsUpdate.Tables.Count; i++) { string tabName = dsUpdate.Tables[i].TableName; string RowStatus = dsUpdate.Tables[i].Rows[0].RowState.ToString(); string select = "SELECT TOP 0 * FROM "; select += tabName + " WHIT(NOLOCK)"; DataTable dt = dsUpdate.Tables[i].Copy(); if (RowStatus == "Modified") { string soucre = @"SELECT * FROM " + tabName + " WITH(NOLOCK) "; dtSoucre = GetData(soucre, new object[0]); for (int j = 0; j < dt.Rows.Count; j++) { string updatePK = dt.Rows[j][pkColumn].ToString(); for (int k = 0; k < dtSoucre.Rows.Count; k++) { string sourePK = dtSoucre.Rows[k][pkColumn].ToString(); if (updatePK == sourePK) { for (int l = 0; l < dt.Columns.Count; l++) { string ColName = dt.Columns[l].ColumnName; dtSoucre.Rows[k][ColName] = dt.Rows[j][ColName]; }; }; }; }; }; SqlCommand cmd = new SqlCommand(select, cn.sqlCon); cmd.Transaction = sqlTransaction; SqlDataAdapter adapter = new SqlDataAdapter(cmd); if (RowStatus == "Added") { adapter.Fill(dt); dtSubmit = dt.Copy(); } else if (RowStatus == "Modified") { adapter.Fill(dtSubmit); dtSubmit = dtSoucre.GetChanges(); //设置主键 ManufacturePrimaryKey(pkColumn, dtSubmit); }; SqlCommandBuilder builder = new SqlCommandBuilder(adapter); adapter.Update(dtSubmit); }; sqlTransaction.Commit(); cn.sqlCon.Close(); bRst = true; } catch (Exception) { bRst = false; sqlTransaction.Rollback(); cn.sqlCon.Close(); }; }; }; return bRst; }
/// <summary> /// 枚举树视图,生成保存用的临时数据。 /// 思路:枚举树视图中所有树结点,跟据树结点的操作状态(新增,修改)生成资料行(DataRow) /// 新增:结点没绑定DataRow且结点打勾. 为新增权限 /// 修改: 结点有绑定DataRow且结点没有打勾. 为删除权限 /// </summary> /// <param name="auths">树视图绑定的权限数据</param> /// <returns></returns> private DataTable GetGroupAuthorityChanges(DataTable auths) { foreach (TreeNode node in _treeAuthority.Nodes) { AuthNodeTag tag = node.Tag as AuthNodeTag; //结点没绑定DataRow且结点打勾. 为新增权限 if (tag.DataRow == null && node.Checked) AddAuthority(auths, node); //结点有绑定DataRow且结点没有打勾. 为删除权限 else if (tag.DataRow != null && !node.Checked) tag.DataRow.Delete(); //递归处理子结点 if (node.Nodes.Count > 0) GetGroupAuthorityChangesChild(node, auths); } //返回修改的记录 DataTable ret = auths.GetChanges(); return ret == null ? auths.Clone() : ret; }
public int UpdateDataBase(DbDataAdapter da, DataTable dt) { int updateCount = 0; try { DataTable dtChanges = dt.GetChanges(); if (dtChanges != null && dtChanges.Rows.Count >= 0) { updateCount = da.Update(dtChanges); dt.AcceptChanges(); } } catch (Exception) { dt.RejectChanges(); throw; } return updateCount; }
/// <summary> /// Merge all changes in DataTable sourceData to the imported DataSet /// </summary> /// <param name="sourceData">DataTable that contains changes</param> public void MergeChanges(DataTable sourceData) { string connection_string; string query; DataTable DestinationDataTable = new DataTable(); query = "SELECT * FROM (" + sourceData.TableName + ");"; connection_string = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + dbpath + ";"; OleDbConnection Connection = new OleDbConnection(connection_string); OleDbDataAdapter accessOleAdapter = new OleDbDataAdapter(query, Connection); OleDbCommandBuilder myCB = new OleDbCommandBuilder(accessOleAdapter); Connection.Open(); accessOleAdapter.Fill(DestinationDataTable); DestinationDataTable.TableName = sourceData.TableName; DestinationDataTable.Merge(sourceData, true, MissingSchemaAction.Ignore); try { accessOleAdapter.Update(DestinationDataTable.GetChanges()); DestinationDataTable.AcceptChanges(); sourceData.AcceptChanges(); } catch (Exception E) { MessageBox.Show(E.Message); } Connection.Close(); }
/// <summary> /// Saves changes made to table data to the database. /// </summary> /// <param name="table">DataTable with changes.</param> /// <param name="selectQuery">SELECT SQL query which was used to read this table.</param> /// <returns>Returns true if save was successful and returns false otherwise.</returns> public bool UpdateTable(DataTable table, string selectQuery) { // Validate inputs if (table == null) throw new ArgumentNullException("table"); if (String.IsNullOrEmpty(selectQuery)) throw new ArgumentException(Resources.Error_EmptyString, "selectQuery"); Debug.Assert(Connection != null, "Connection is not initialized!"); // Extract changes from table DataTable changes = table.GetChanges(); // If no changes detected, return true if (changes == null) return true; // Retrieving connection DbConnection conn = GetConnection(); DbTransaction transaction = null; try { // Ensure the connection is open EnsureConnectionIsOpen(); // Start transaction transaction = conn.BeginTransaction(); // Create command object DbCommand comm = conn.CreateCommand(); comm.CommandText = selectQuery; comm.Transaction = transaction; // Create a data adapter and attach it to command DbDataAdapter adapter = CreateDataAdapter(); adapter.SelectCommand = comm; // Create a command builder and attach it to adapter DbCommandBuilder builder = CreateCommandBuilder(adapter); // Build update commands // If there are deleted rows, create delete command DataRow[] selection = table.Select(String.Empty, String.Empty, DataViewRowState.Deleted); if (selection != null && selection.Length > 0) adapter.DeleteCommand = builder.GetDeleteCommand(); // If there are modified rows, create update command selection = table.Select(String.Empty, String.Empty, DataViewRowState.ModifiedCurrent); if (selection != null && selection.Length > 0) adapter.UpdateCommand = builder.GetUpdateCommand(); // If there are new rows, create insert command selection = table.Select(String.Empty, String.Empty, DataViewRowState.Added); if (selection != null && selection.Length > 0) adapter.InsertCommand = builder.GetInsertCommand(); // Attach adapter commands to transactions if (adapter.UpdateCommand != null) adapter.UpdateCommand.Transaction = transaction; if (adapter.InsertCommand != null) adapter.InsertCommand.Transaction = transaction; if (adapter.DeleteCommand != null) adapter.DeleteCommand.Transaction = transaction; // Saves data adapter.Update(changes); // Release resources builder.Dispose(); adapter.Dispose(); // Commit transaction transaction.Commit(); // Return results return true; } catch { // Try to ping connection after error to force socket recreation TryToPingConnection(conn); // On any error rolback transaction if any if (transaction != null) transaction.Rollback(); throw; } finally { Connection.UnlockProviderObject(); } }
/// <summary> /// 利用DataTable来更新数据 /// </summary> /// <param name="item">要更新的数据项目</param> /// <param name="tab">要更新的数据DataTable</param> /// <returns>更新是否成功</returns> public bool Update(string item,DataTable tab) { if(item!=this._item) this.SetItem(item); if(this._item.Length<1) throw(new Exception("设置更新规则失败!")); if (tab.HasErrors) { throw(new Exception("违反数据校验!")); } SqlTransaction trans=null; SqlCommand cmd; this._adpCascade.SelectCommand=null; this._adpCascade.InsertCommand=null; this._adpCascade.UpdateCommand=null; this._adpCascade.DeleteCommand=null; DataTable tab_cascade=tab.GetChanges(DataRowState.Modified|DataRowState.Deleted); if(tab_cascade==null) tab_cascade=new DataTable(); DataRow[] drs_ins=tab_cascade.Select("","",DataViewRowState.Added); DataRow[] drs_mody=tab_cascade.Select("","",DataViewRowState.ModifiedCurrent); DataRow[] drs_del=tab_cascade.Select("","",DataViewRowState.Deleted); try { //先更新级联的数据再更新本身项目数据 _conn.Open(); trans=_conn.BeginTransaction(); //首选更新新增和修改的 if(null!=this._adapter.UpdateCommand) this._adapter.UpdateCommand.Transaction=trans; if(null!=this._adapter.InsertCommand) this._adapter.InsertCommand.Transaction=trans; this._adapter.Update(tab.Select("","",DataViewRowState.Added|DataViewRowState.ModifiedCurrent)); //级联增加 for(int i=0;i<this._listCmdCasInsert.Count;i++) { cmd=this._listCmdCasInsert[i] as SqlCommand; if(cmd==null) continue; cmd.Transaction=trans; this._adpCascade.InsertCommand=cmd; this._adpCascade.Update(drs_ins); } this._adpCascade.InsertCommand=null; //级联更新 for(int i=0;i<this._listCmdCasUpdate.Count;i++) { cmd=this._listCmdCasUpdate[i] as SqlCommand; if(cmd==null) continue; cmd.Transaction=trans; this._adpCascade.UpdateCommand=cmd; this._adpCascade.Update(drs_mody); } this._adpCascade.UpdateCommand=null; //级联删除 for(int i=0;i<this._listCmdCasDelete.Count;i++) { cmd=this._listCmdCasDelete[i] as SqlCommand; if(cmd==null) continue; cmd.Transaction=trans; this._adpCascade.DeleteCommand=cmd; this._adpCascade.Update(drs_del); } this._adpCascade.DeleteCommand=null; //最后更新删除的 if(null!=this._adapter.DeleteCommand) this._adapter.DeleteCommand.Transaction=trans; this._adapter.Update(tab.Select("","",DataViewRowState.Deleted)); trans.Commit(); tab.AcceptChanges(); } catch ( Exception ex ) { if(_conn.State==ConnectionState.Open && trans!=null) trans.Rollback(); NameValueCollection errInfo = new NameValueCollection(); ex.Data["数据源"] = this._item; errInfo["数据文件"] = this._xmlfile; ExceptionManager.Publish(ex, errInfo); _conn.Close(); return false; } finally { _conn.Close(); } return true; }
public void UpdateDBDataset() { try { dt = ds.Tables[0]; DataTable changes = dt.GetChanges(); da.Update(changes); dt.AcceptChanges(); } catch (MySqlException ex) { err += "Error: " + ex.Message.ToString(); errNumber = ex.Number; } }
public void UpdateGiftDetail(DataTable Dt_Gift, DbTransaction RootDBT, string UID) { bool IsRootTranscation = false; try { DBO.MKT_GiftDBO dbo = new MKT_GiftDBO(ref USEDB); //判斷是否有傳入Root Transcation IsRootTranscation = (RootDBT == null) ? true : false; #region 啟動交易或指定RootTranscation if (IsRootTranscation) { //獨立呼叫啟動Transcation Conn = USEDB.CreateConnection(); Conn.Open(); DBT = Conn.BeginTransaction(); } else { DBT = RootDBT; } #endregion //DataTable DtD_A = Dt_Gift.GetChanges(DataRowState.Added); DataTable DtD_M = Dt_Gift.GetChanges(DataRowState.Modified); DataTable DtD_D = Dt_Gift.GetChanges(DataRowState.Deleted); ArrayList arrayList = new ArrayList(); #region Delete if (DtD_D != null) { for (int i = 0; i <= DtD_D.Rows.Count - 1; i++) { arrayList.Clear(); arrayList.Add(DtD_D.Rows[i]["ITEM", DataRowVersion.Original].ToString()); arrayList.Add(DtD_D.Rows[i]["PERIOD", DataRowVersion.Original].ToString()); arrayList.Add(DtD_D.Rows[i]["VIRTUAL_CODE", DataRowVersion.Original].ToString()); arrayList.Add(DtD_D.Rows[i]["UPDATEDATE", DataRowVersion.Original].ToString()); arrayList.Add(DtD_D.Rows[i]["UPDATEUID", DataRowVersion.Original].ToString()); arrayList.Add(DateTime.Now); arrayList.Add(UID); dbo.DeleteGift(arrayList, DBT); } DtD_D.Clear(); } #endregion #region Insert //if (DtD_A != null) //{ // for (int i = 0; i <= DtD_A.Rows.Count - 1; i++) // { // arrayList.Clear(); // arrayList.Add(M_ID); // arrayList.Add(DtD_A.Rows[i]["FULLBARCODE"]); // arrayList.Add(DtD_A.Rows[i]["BARCODE"]); // arrayList.Add(DtD_A.Rows[i]["PERIOD_BARCODE"]); // arrayList.Add(DtD_A.Rows[i]["ITEM"]); // arrayList.Add(DtD_A.Rows[i]["ITEM_NAME"]); // arrayList.Add(DtD_A.Rows[i]["PERIOD"]); // arrayList.Add(DtD_A.Rows[i]["ITEM_MAP"]); // arrayList.Add(int.Parse(DtD_A.Rows[i]["P_QTY"].ToString())); // arrayList.Add(DtD_A.Rows[i]["P_CLASS"]); // arrayList.Add(DtD_A.Rows[i]["P_NOTES"]); // arrayList.Add(UID); // dbo.InsertDetail(arrayList, DBT); // } // DtD_A.Clear(); //} #endregion #region Update if (DtD_M != null) { for (int i = 0; i <= DtD_M.Rows.Count - 1; i++) { arrayList.Clear(); arrayList.Add(DtD_M.Rows[i]["ITEM", DataRowVersion.Original].ToString()); arrayList.Add(DtD_M.Rows[i]["PERIOD", DataRowVersion.Original].ToString()); arrayList.Add(DtD_M.Rows[i]["VIRTUAL_CODE", DataRowVersion.Original].ToString()); arrayList.Add(DtD_M.Rows[i]["VIRTUAL_NAME", DataRowVersion.Original].ToString()); arrayList.Add(DtD_M.Rows[i]["PLAN_QTY", DataRowVersion.Original].ToString()); arrayList.Add(DtD_M.Rows[i]["UPDATEDATE", DataRowVersion.Original].ToString()); arrayList.Add(DtD_M.Rows[i]["UPDATEUID", DataRowVersion.Original].ToString()); arrayList.Add(DtD_M.Rows[i]["VIRTUAL_NAME", DataRowVersion.Current].ToString()); arrayList.Add(DtD_M.Rows[i]["PLAN_QTY", DataRowVersion.Current].ToString()); arrayList.Add(DateTime.Now); arrayList.Add(UID); dbo.UpdateGift(arrayList, DBT); } DtD_M.Clear(); } #endregion #region 交易成功 if (IsRootTranscation) { //獨立呼叫Transcation成立 DBT.Commit(); } #endregion } catch (Exception ex) { #region 交易失敗 //if (IsRootTranscation) //{ //獨立呼叫Transcation失敗 DBT.Rollback(); //} #endregion throw ex; } finally { #region 判斷是否關閉交易連線 if (IsRootTranscation) { //獨立呼叫Transcation,關閉連線 if (Conn.State == ConnectionState.Connecting) { Conn.Close(); } } #endregion } }
public void Changes () //To test GetChanges and RejectChanges { DataTable table = new DataTable (); DataColumn col = new DataColumn (); col.ColumnName = "Id"; col.DataType = Type.GetType ("System.Int32"); table.Columns.Add (col); UniqueConstraint uc = new UniqueConstraint ("UK1", table.Columns[0] ); table.Constraints.Add (uc); col = new DataColumn (); col.ColumnName = "Name"; col.DataType = Type.GetType ("System.String"); table.Columns.Add (col); DataRow row = table.NewRow (); row ["Id"] = 147; row ["name"] = "Abc"; table.Rows.Add (row); table.AcceptChanges (); row = table.NewRow (); row ["Id"] = 47; row ["name"] = "Efg"; table.Rows.Add (row); //Testing GetChanges DataTable changesTable = table.GetChanges (); AssertEquals ("#A01", 1 ,changesTable.Rows.Count); AssertEquals ("#A02","Efg" ,changesTable.Rows[0]["Name"]); table.AcceptChanges (); changesTable = table.GetChanges (); try { int cnt = changesTable.Rows.Count; } catch(Exception e) { if (e.GetType () != typeof (AssertionException)) AssertEquals ("#A03",typeof(NullReferenceException) ,e.GetType ()); else Console.WriteLine (e); } //Testing RejectChanges row = table.NewRow (); row ["Id"] = 247; row ["name"] = "Hij"; table.Rows.Add (row); (table.Rows [0])["Name"] = "AaBbCc"; table.RejectChanges (); AssertEquals ("#A03", "Abc" , (table.Rows [0]) ["Name"]); AssertEquals ("#A04", 2, table.Rows.Count); }
static void Main(string[] args) { //Należy skonfigurować połączenie. SqlConnection connection = new SqlConnection("Data Source=XYZ-COMP\\SQLEXPRESS; Initial Catalog=animal; Integrated Security=true;"); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = new SqlCommand("SELECT * FROM Zwierz", connection); da.InsertCommand = new SqlCommand("INSERT INTO Zwierz (ID, Nazwa_pl, Nazwa_ang) VALUES(@ID, @Nazwa_pl, @Nazwa_ang)", connection); da.UpdateCommand = new SqlCommand("UPDATE Zwierz SET ID=@ID, Nazwa_pl=@Nazwa_pl, Nazwa_ang=@Nazwa_ang WHERE ID=@ID_prev", connection); da.DeleteCommand = new SqlCommand("DELETE FROM Zwierz WHERE ID=@ID", connection); da.InsertCommand.Parameters.Add("@ID", SqlDbType.Int, 0, "ID").SourceVersion = DataRowVersion.Original; da.InsertCommand.Parameters.Add("@Nazwa_pl", SqlDbType.VarChar, 30, "Nazwa_pl").SourceVersion = DataRowVersion.Original; da.InsertCommand.Parameters.Add("@Nazwa_ang", SqlDbType.VarChar, 30, "Nazwa_ang").SourceVersion = DataRowVersion.Original; da.UpdateCommand.Parameters.Add("@ID", SqlDbType.Int, 0, "ID").SourceVersion = DataRowVersion.Current; da.UpdateCommand.Parameters.Add("@Nazwa_pl", SqlDbType.VarChar, 30, "Nazwa_pl").SourceVersion = DataRowVersion.Current; da.UpdateCommand.Parameters.Add("@Nazwa_ang", SqlDbType.VarChar, 50, "Nazwa_ang").SourceVersion = DataRowVersion.Current; da.UpdateCommand.Parameters.Add("@ID_Prev", SqlDbType.Int, 0, "ID").SourceVersion = DataRowVersion.Original; da.DeleteCommand.Parameters.Add("@ID", SqlDbType.Int, 0, "ID").SourceVersion = DataRowVersion.Current; DataTable dt = new DataTable("Zwierz"); da.Fill(dt); //var start-------------- int ID; String Nazwa_pl, Nazwa_ang; String wybor; //opcja uzytkownika int indeks; //indeks wiersza do usuniecia DataRowCollection itemColumns = dt.Rows; DataTable dtChanges = dt.GetChanges(); //var end----------------- while (true) //dopoki uzytkownik nie zakonczy programu { Console.WriteLine("Wybierz 1 - dodaj zwierza"); Console.WriteLine("Wybierz 2 - usun zwierza"); Console.WriteLine("Wybierz 3 - modyfikuj zwierza"); Console.WriteLine("Wybierz 4 - listuj"); Console.WriteLine("Wybierz 5 - zakoncz"); wybor = Console.ReadLine(); //pobranie opcji if (wybor.Equals("1")) { Console.WriteLine("Podaj w kolejnych 3 wierszach wartosci: ID, Nazwa_pl, Nazwa_ang"); ID = Convert.ToInt32(Console.ReadLine()); Nazwa_pl = Console.ReadLine(); Nazwa_ang = Console.ReadLine(); DataRow newRow = dt.NewRow(); newRow["ID"] = ID; newRow["Nazwa_pl"] = Nazwa_pl; newRow["Nazwa_ang"] = Nazwa_ang; dt.Rows.Add(newRow); dtChanges = dt.GetChanges(); if (dtChanges != null) { da.Update(dtChanges); dt.AcceptChanges(); } } else if (wybor.Equals("2")) { Console.WriteLine("Podaj numer wiersza tabeli, ktory chcesz usunac (numerujac wiersze od zera):"); indeks = Convert.ToInt32(Console.ReadLine()); itemColumns[indeks].Delete(); dtChanges = dt.GetChanges(); if (dtChanges != null) { da.Update(dtChanges); dt.AcceptChanges(); } } else if (wybor.Equals("3")) { Console.WriteLine("Podaj numer wiersza tabeli, ktory chcesz zmodyfikowac (numerujac wiersze od zera):"); indeks = Convert.ToInt32(Console.ReadLine()); Console.WriteLine("Podaj w kolejnych 3 wierszach nowe wartosci: ID, Nazwa_pl, Nazwa_ang"); ID = Convert.ToInt32(Console.ReadLine()); Nazwa_pl = Console.ReadLine(); Nazwa_ang = Console.ReadLine(); itemColumns[indeks]["ID"] = ID; itemColumns[indeks]["Nazwa_pl"] = Nazwa_pl; itemColumns[indeks]["Nazwa_ang"] = Nazwa_ang; dtChanges = dt.GetChanges(); if (dtChanges != null) { da.Update(dtChanges); dt.AcceptChanges(); } } else if (wybor.Equals("4")) { Console.WriteLine("---------------------------------------------------------"); Console.WriteLine("Tabela Zwierz:"); if (dt != null) { PrintDataTable(dt); } else Console.WriteLine("Nie wprowadzono zmian"); } else if (wybor.Equals("5")) { Environment.Exit(0); } } //koniec main }
public Hashtable SaveData(string p_brid, string p_user, string p_terminal, ref DataTable p_dt, out string p_err) { #region variable Declaration int _intNumRecords = 0, tempSrno = 0; p_err = null; Hashtable _htSave = new Hashtable(); _htSave.Add("TIMESTAMP", _strErrMsg); #endregion try { _base.Connect(); DataTable _dtMas = p_dt.GetChanges(); foreach (DataRow _drRow in _dtMas.Rows) { switch (_drRow.RowState) { case DataRowState.Added: _base.BeginTransaction(IsolationLevel.ReadCommitted); _htSave = Add(p_brid, p_user, p_terminal, _drRow); if (_htSave["p_flg"].ToString().ToUpper() == "N") { _strInsertErrMsg += "Exam Id " + _drRow["SRNO"].ToString() + ""; _base.Rollback(); continue; } else if (_htSave["p_flg"].ToString().ToUpper() == "Y") { tempSrno = int.Parse(_htSave["SRNO"].ToString()); } break; case DataRowState.Modified: _base.BeginTransaction(IsolationLevel.ReadCommitted); _htSave = Update(p_brid, p_user, p_terminal, _drRow); if (_htSave["p_flg"].ToString().ToUpper() == "T") { _strTimeStampErrMsg += "Srno. = " + _drRow["SRNO"].ToString() + ""; _base.Rollback(); continue; } else if (_htSave["p_flg"].ToString().ToUpper() == "N") { _strUpdateErrMsg += "Srno. = " + _drRow["SRNO"].ToString() + ""; _base.Rollback(); continue; } else if (_htSave["p_flg"].ToString().ToUpper() == "Y") { tempSrno = int.Parse(_drRow["SRNO"].ToString()); } break; case DataRowState.Deleted: _base.BeginTransaction(IsolationLevel.ReadCommitted); _htSave = Delete(p_brid, p_user, p_terminal, _drRow); if (_htSave["p_flg"].ToString().ToUpper() == "T") { _strTimeStampErrMsg += "Srno. = " + _drRow["SRNO", DataRowVersion.Original].ToString() + ""; _base.Rollback(); continue; } else if (_htSave["p_flg"].ToString().ToUpper() == "N") { _strTimeStampErrMsg += "Srno. = " + _drRow["SRNO", DataRowVersion.Original].ToString() + ""; _base.Rollback(); continue; } break; } DataRow[] _drRows = null; if (_drRow.RowState != DataRowState.Deleted) { _drRows = p_dt.Select("SRNO = " + _drRow["SRNO"]); if (_drRow.RowState == DataRowState.Added) { p_dt.Rows[p_dt.Rows.IndexOf(_drRows[0])]["SRNO"] = tempSrno; } p_dt.Rows[p_dt.Rows.IndexOf(_drRows[0])]["TIME_STAMP"] = _htSave["p_time_stamp"].ToString(); } if (_drRow.RowState == DataRowState.Deleted) { _drRows = p_dt.Select("SRNO =" + _drRow["SRNO", DataRowVersion.Original], "", DataViewRowState.Deleted); } p_dt.Rows[p_dt.Rows.IndexOf(_drRows[0])].AcceptChanges(); _intNumRecords++; _base.Commit(); } GetTransactionSummary(_strInsertErrMsg, _strUpdateErrMsg, _strDeleteErrMsg, _strTimeStampErrMsg, out _strErrMsg); _htSave.Add("RESULT", "true"); _htSave["TIMESTAMP"] = _strErrMsg; _htSave["SAVERECORD"] = _intNumRecords; } catch (Exception ex) { _base.Rollback(); p_err = ex.Message.ToString(); } finally { _base.Disconnect(); } return _htSave; }
public void UpdateDataSet() { execSQL("DROP TABLE IF EXISTS Test"); execSQL("CREATE TABLE Test (id INT NOT NULL, blob1 LONGBLOB, text1 LONGTEXT, PRIMARY KEY(id))"); execSQL("INSERT INTO Test VALUES( 1, NULL, 'Text field' )"); try { MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn); MySqlCommandBuilder cb = new MySqlCommandBuilder(da); DataTable dt = new DataTable(); da.Fill(dt); string s = (string)dt.Rows[0][2]; Assert.AreEqual("Text field", s); byte[] inBuf = Utils.CreateBlob(512); dt.Rows[0].BeginEdit(); dt.Rows[0]["blob1"] = inBuf; dt.Rows[0].EndEdit(); DataTable changes = dt.GetChanges(); da.Update(changes); dt.AcceptChanges(); dt.Clear(); da.Fill(dt); cb.Dispose(); byte[] outBuf = (byte[])dt.Rows[0]["blob1"]; Assert.AreEqual(inBuf.Length, outBuf.Length, "checking length of updated buffer"); for (int y = 0; y < inBuf.Length; y++) Assert.AreEqual(inBuf[y], outBuf[y], "checking array data"); } catch (Exception ex) { Assert.Fail(ex.Message); } }
public Hashtable SaveData(ref DataTable p_dtParameter) { int _intNumRecords = 0; string _strCriteria = ""; Hashtable _htSave = new Hashtable(); string _strTimeStampErrMsg = "Timestamp Error : \n"; string _strInsertErrMsg = "Problem In Inserting Record : \n"; string _strUpdateErrMsg = "Problem In Updating Record : \n"; string _strDeleteErrMsg = "Record can't deleted due to child record exist : \n"; string _strErrMsg = ""; _htSave.Add("TIMESTAMP", _strErrMsg); try { wrap.GetConnection(); DataTable _dtTemp = p_dtParameter.GetChanges(); DataRow[] _drRows; _strCriteria = ""; foreach (DataRow _drRow in _dtTemp.Rows) { if (_drRow.RowState != DataRowState.Deleted) { bool _isCheckDuplicate = true; if (_drRow.RowState == DataRowState.Modified) { if ((GetValueOfObject(_drRow["SHIFT_NAME", DataRowVersion.Current])) == (GetValueOfObject(_drRow["SHIFT_NAME", DataRowVersion.Original]))) { _isCheckDuplicate = false; } if (_isCheckDuplicate) { if (CheckExistUserName(_drRow["UserName", DataRowVersion.Original].ToString(),_drRow["Password",DataRowVersion.Original].ToString(),"Other") == 'Y') { _htSave.Add("RESULT", "true"); } } }//if(modified) } switch (_drRow.RowState) { case DataRowState.Added: _htSave = AddData(_drRow); _strCriteria = FetchCriteria(_drRow, DataRowVersion.Current, out p_err); _drRows = p_dtParameter.Select(_strCriteria); if (_drRows.Length > 0) { if (_drRow.RowState == DataRowState.Added) { p_dtParameter.Rows[p_dtParameter.Rows.IndexOf(_drRows[0])]["SHIFT_ID"] = _htSave["p_shift_id"].ToString(); } p_dtParameter.Rows[p_dtParameter.Rows.IndexOf(_drRows[0])]["TIME_STAMP"] = _htSave["p_time_stamp"].ToString(); } if (_htSave["p_flg"].ToString().ToUpper() == "N") _strInsertErrMsg += "Shift Name = " + _drRow["SHIFT_NAME"] + "\n"; break; case DataRowState.Modified: _htSave = UpdateData(p_entTerm, p_entUser, _drRow, out p_err); if (_htSave["p_flg"].ToString().ToUpper() == "T") { _strTimeStampErrMsg += "Shift Name = " + _drRow["SHIFT_NAME"] + "\n"; continue; } if (_htSave["p_flg"].ToString().ToUpper() == "N") _strUpdateErrMsg += "Shift Name = " + _drRow["SHIFT_NAME"] + "\n"; break; case DataRowState.Deleted: _htSave = DeleteData(p_entUser, p_entTerm, _drRow, out p_err); _strCriteria = FetchCriteria(_drRow, DataRowVersion.Original, out p_err); _drRows = p_dtParameter.Select(_strCriteria); if (_drRows.Length > 0) { if (_drRow.RowState == DataRowState.Deleted) { p_dtParameter.Rows[p_dtParameter.Rows.IndexOf(_drRows[0])]["SHIFT_ID"] = _htSave["p_shift_id"].ToString(); } p_dtParameter.Rows[p_dtParameter.Rows.IndexOf(_drRows[0])]["TIME_STAMP"] = _htSave["p_time_stamp"].ToString(); } if (_htSave["p_flg"].ToString().ToUpper() == "T") { _strTimeStampErrMsg += "Shift Name = " + _drRow["SHIFT_NAME", DataRowVersion.Original] + "\n"; continue; } if (_htSave["p_flg"].ToString().ToUpper() == "N") _strDeleteErrMsg += "Shift = " + _drRow["SHIFT_NAME", DataRowVersion.Original] + "\n"; break; } //if(deleted) } if (_strTimeStampErrMsg != "Timestamp Error : \n") _strErrMsg = _strTimeStampErrMsg + "\n \n"; if (_strInsertErrMsg != "Problem In Inserting Record : \n") _strErrMsg = _strInsertErrMsg + "\n \n"; if (_strUpdateErrMsg != "Problem In Updating Record : \n") _strErrMsg = _strUpdateErrMsg + "\n \n"; if (_strDeleteErrMsg != "Record can't deleted due to child record exist : \n") _strErrMsg = _strDeleteErrMsg + "\n \n"; _htSave.Add("RESULT", "true"); _htSave["TIMESTAMP"] = _strErrMsg; return _htSave; } catch (Exception ex) { throw new Exception(ex.Message); } finally { wrap.CloseConnection(); } return null; }
public void MultiUpdate() { execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(100), dt DATETIME, tm TIME, `multi word` int, PRIMARY KEY(id))"); execSQL("INSERT INTO Test (id, name) VALUES (1, 'test1')"); execSQL("INSERT INTO Test (id, name) VALUES (2, 'test2')"); execSQL("INSERT INTO Test (id, name) VALUES (3, 'test3')"); MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn); MySqlCommandBuilder cb = new MySqlCommandBuilder(da); DataTable dt = new DataTable(); da.Fill(dt); dt.Rows[0]["id"] = 4; dt.Rows[0]["name"] = "test4"; dt.Rows[1]["id"] = 5; dt.Rows[1]["name"] = "test5"; dt.Rows[2]["id"] = 6; dt.Rows[2]["name"] = "test6"; DataTable changes = dt.GetChanges(); da.Update(changes); dt.AcceptChanges(); dt.Rows[0]["id"] = 7; dt.Rows[0]["name"] = "test7"; dt.Rows[1]["id"] = 8; dt.Rows[1]["name"] = "test8"; dt.Rows[2]["id"] = 9; dt.Rows[2]["name"] = "test9"; changes = dt.GetChanges(); da.Update(changes); dt.AcceptChanges(); cb.Dispose(); }
private void wrtiteData() { DataTable dwt = new DataTable(); NpgsqlConnection conn = DBExchange.Inst.connectDb; dwt.Columns.Add("ticket_id", System.Type.GetType("System.Int64")); dwt.Columns.Add("template_id", System.Type.GetType("System.Int32")); int patid = this.searchPatientBox1.pIdN; int docin = DBExchange.Inst.dbUsrId; int serviceid = rl[this.comboBox1.SelectedIndex].ResearchId; string _command = "Insert into ticket_radio (pat_id, doc_in, type_id, date_app, status) values " + "('" + patid + "','" + docin + "','" + serviceid + "', '" + this.dateTimePicker1.Value.ToShortDateString() + "', 'false') RETURNING ticket_id ;"; NpgsqlCommand writeTicket = new NpgsqlCommand(_command, conn); DataTable ds = new DataTable(); NpgsqlDataAdapter da = new NpgsqlDataAdapter("Select ticket_id, template_id from lab_results_radio where ticket_id = (Select max(ticket_id) from lab_results_radio);", conn); da.InsertCommand = new NpgsqlCommand("insert into lab_results_radio (ticket_id, template_id ) " + " values (:a, :b)", conn); da.InsertCommand.Parameters.Add(new NpgsqlParameter("a", NpgsqlDbType.Bigint)); da.InsertCommand.Parameters.Add(new NpgsqlParameter("b", NpgsqlDbType.Integer)); da.InsertCommand.Parameters[0].Direction = ParameterDirection.Input; da.InsertCommand.Parameters[1].Direction = ParameterDirection.Input; da.InsertCommand.Parameters[0].SourceColumn = "ticket_id"; da.InsertCommand.Parameters[1].SourceColumn = "template_id"; try { Int64 gotInt6 = (Int64)writeTicket.ExecuteScalar(); foreach (int i in checkedListBox1.CheckedIndices) { DataRow dwtNewRo = dwt.NewRow(); dwtNewRo["ticket_id"] = gotInt6; dwtNewRo["template_id"] = zl[i].ZoneId; dwt.Rows.Add(dwtNewRo); } da.Fill(ds); ds = dwt; DataTable ds2 = ds.GetChanges(); da.Update(ds2); ds.Merge(ds2); ds.AcceptChanges(); foreach (int i in checkedListBox1.CheckedIndices) { checkedListBox1.SetItemChecked(i, false); } dwt.Clear(); } catch (Exception exception) { Warnings.WarnLog log = new Warnings.WarnLog(); log.writeLog(MethodBase.GetCurrentMethod().Name, exception.Message.ToString(), exception.StackTrace.ToString()); } }
static void Main(string[] args) { //creating programers table DataTable programmers = new DataTable("programmers"); DataColumn programmerId = new DataColumn("ID", typeof(int)); programmerId.Unique = true; DataColumn programmerName = new DataColumn("Name", typeof(String)); DataColumn programmerSurname = new DataColumn("Surname", typeof(String)); DataColumn teamId = new DataColumn("team ID", typeof(int)); programmers.Columns.Add(programmerId); programmers.Columns.Add(programmerName); programmers.Columns.Add(programmerSurname); programmers.Columns.Add(teamId); programmers.PrimaryKey = new DataColumn[] { programmerId }; //creating technologies table DataTable technologies = new DataTable("technologies"); DataColumn technologyId = new DataColumn("ID", typeof(int)); technologyId.Unique = true; DataColumn programId = new DataColumn("programmer Id", typeof(int)); DataColumn technologyName = new DataColumn("Name", typeof(String)); technologies.Columns.Add(technologyId); technologies.Columns.Add(technologyName); technologies.Columns.Add(programId); technologies.PrimaryKey = new DataColumn[] { technologyId }; //creating skills table DataTable skills = new DataTable("skills"); DataColumn skillId = new DataColumn("ID", typeof(int)); skillId.Unique = true; DataColumn skillType = new DataColumn("Type", typeof(String)); skills.Columns.Add(skillId); skills.Columns.Add(skillType); skills.PrimaryKey = new DataColumn[] { skillId }; //creating DataSet DataSet team = new DataSet("Team"); team.Tables.Add(programmers); team.Tables.Add(technologies); team.Tables.Add(skills); team.Relations.Add("programmers-technologies", programmers.Columns["ID"], technologies.Columns["programmer Id"]); //<addInfo> //filling "programmers" programmers.Rows.Add(1, "asfafs", "asfasf", 1); programmers.Rows.Add(2, "afwq", "asfqwe", 1); programmers.Rows.Add(3, "qwgqg", "qweqwe", 2); programmers.Rows.Add(4, "grrgr", "gqgqeg", 3); programmers.Rows.Add(5, "nenre", "nernernt", 3); //filling "technologies" technologies.Rows.Add(1, "fdgdfgdf", 1); technologies.Rows.Add(2, "dfgdfgd", 2); technologies.Rows.Add(3, "gerterg", 1); technologies.Rows.Add(4, "htohtmh", 2); technologies.Rows.Add(5, "htohtmh", 3); //filling "skills" skills.Rows.Add(1, "hgfnlmfn"); skills.Rows.Add(2, "utrurtut"); skills.Rows.Add(3, "hgfnjrtjlmfn"); skills.Rows.Add(4, "itrirti"); skills.Rows.Add(5, "kyktktyku"); //</addInfo> programmers.AcceptChanges(); technologies.AcceptChanges(); skills.AcceptChanges(); //printing tables Console.WriteLine("Таблиця <programmers> :"); PrintDataTable(programmers); Console.WriteLine(); Console.WriteLine("Таблиця <technologies> :"); PrintDataTable(technologies); Console.WriteLine(); Console.WriteLine("Таблиця <skills> :"); PrintDataTable(skills); Console.WriteLine(); DataView programmersView = new DataView(programmers); programmersView.Sort = "ID DESC"; programmersView.RowFilter = "ID > 3"; Console.WriteLine("Вiдсортована i вiдфiльтрована таблиця <programmers> : "); PrintDataTable(programmersView.ToTable()); //adding programmers.Rows.Add(6, "Oleh", "Kurnenkov", 6); //modifying technologies.Rows[0][0] = 6; technologies.Rows[0][1] = "Some Technology"; technologies.Rows[0][2] = 6; //deleting DataRow deleted = skills.Rows[2]; deleted.Delete(); Console.WriteLine(); Console.WriteLine("Стани змiнених рядкiв : "); Console.WriteLine(programmers.Rows[5].RowState); Console.WriteLine(technologies.Rows[0].RowState); Console.WriteLine(skills.Rows[2].RowState); // dataset with modified data DataSet modified = new DataSet("Modified DBes"); modified.Tables.Add(technologies.GetChanges()); //added modified.Tables.Add(programmers.GetChanges()); //modified //modified.Tables.Add(users.GetChanges()); //deleted Console.WriteLine(); Console.WriteLine("Записи змiн : "); for (int i = 0; i < modified.Tables.Count; i++) { PrintDataTable(modified.Tables[i]); } Console.ReadLine(); }
/// <summary> /// 【功能】:更新店群折扣維護資料 /// /// 【傳入值】: /// 〔@參考web ui spec欄位〕 /// /// 【回傳值】: /// 〔@更新是否成功〕 /// /// 【主要TABLE】: /// VDS_STM_PRICEDISCOUNT_SG /// /// 【檢查】: /// 1.屬於同一店群內的折扣設定檢查 /// IF DISCOUNT_TYPE=1 THEN //店群對大分類 /// 如果更新的ROOT_NO||PMA已經存在,則檢查【有效開始日】要小於上一筆資料的【有效結束日】,否則顯示錯誤訊息,《有效開始日期輸入有誤,應小於相同大分類折扣 /// 的有效結束日》。 /// /// ELSE IF DISCOUNT_TYPE=2 THEN //店群對中分類 /// 如果更新的ROOT_NO||PMA||CATEGORY已經存在,則檢查【有效開始日】要小於上一筆資料的【有效結束日】,否則顯示錯誤訊息,《有效開始日期輸入有誤, /// 應小於相同折扣的有效結束日》。 /// /// ELSE DISCOUNT_TYPE=4 //店群對單品 /// 如果更新的ITEM已經存在,則檢查【有效開始日】要小於上一筆資料的【有效結束日】,否則顯示錯誤訊息,《有效開始日期輸入有誤,應小於相同大分類折扣的有效結束日》。 /// /// END IF /// /// 【說明】: /// 1.必填的欄位如果是空值,則INSERT空白,長度參照Table Schema。 /// 2.如果檢查無誤,則正確更新資料,如有錯誤則要回傳錯誤訊息。 /// 3.如果群分類=2(圖書),PERIOD期別欄位='000000' /// </summary> public void UpdateStoreDiscount(DataTable dtA, DataTable dtB, DataTable dtC, string UID, DbTransaction RootDBT) { bool IsRootTranscation = false; try { DBO.STM_STM19_DBO dbo = new STM_STM19_DBO(ref USEDB); //判斷是否有傳入Root Transcation IsRootTranscation = (RootDBT == null) ? true : false; #region 啟動交易或指定RootTranscation if (IsRootTranscation) { //獨立呼叫啟動Transcation Conn = USEDB.CreateConnection(); Conn.Open(); DBT = Conn.BeginTransaction(); } else { DBT = RootDBT; } #endregion DataTable dtA_i = dtA.GetChanges(DataRowState.Added); DataTable dtA_m = dtA.GetChanges(DataRowState.Modified); DataTable dtA_d = dtA.GetChanges(DataRowState.Deleted); ArrayList arrayList = new ArrayList(); int N_ID; string V_CODE; if (dtA_i != null) { for (int i = 0; i < dtA_i.Rows.Count; i++) { arrayList.Clear(); arrayList = this.GetArrayList(dtA_i.Rows[i], UID); dbo.doCreateStoreDiscount(arrayList, DBT, out N_ID, out V_CODE); } } if (dtA_m != null) { for (int i = 0; i <= dtA_m.Rows.Count - 1; i++)//店群折扣 -> 修改 { arrayList.Clear(); arrayList.Add(Convert.ToInt32(dtA_m.Rows[i]["ID"].ToString())); arrayList.Add(DateTime.Parse(dtA_m.Rows[i]["CREATEDATE", DataRowVersion.Original].ToString())); arrayList.Add(dtA_m.Rows[i]["CREATEUID", DataRowVersion.Original].ToString()); arrayList.Add(DateTime.Parse(dtA_m.Rows[i]["UPDATEDATE", DataRowVersion.Original].ToString())); arrayList.Add(dtA_m.Rows[i]["UPDATEUID", DataRowVersion.Original].ToString()); arrayList.Add(dtA_m.Rows[i]["CODE"].ToString());//V_NEW_CODE arrayList.Add(dtA_m.Rows[i]["STORE"].ToString());//V_NEW_GROUP_NO arrayList.Add(Convert.ToDateTime(dtA_m.Rows[i]["EFF_START"].ToString()));//D_NEW_EFF_START arrayList.Add(Convert.ToDateTime(dtA_m.Rows[i]["EFF_END"].ToString()));//D_NEW_EFF_END arrayList.Add(Convert.ToInt32(dtA_m.Rows[i]["DISCOUNT_KIND"].ToString()));//N_NEW_DISCOUNT_KIND arrayList.Add(GetValueSetParameter(dtA_m.Rows[i]["ROOT_NO"].ToString(), "string", false));//V_NEW_ROOT_NO arrayList.Add(GetValueSetParameter(dtA_m.Rows[i]["PMA"].ToString(), "string", false));//V_NEW_PMA arrayList.Add(GetValueSetParameter(dtA_m.Rows[i]["CATEGORY"].ToString(), "string", false));//V_NEW_CATEGORY arrayList.Add(GetValueSetParameter(dtA_m.Rows[i]["ITEM"].ToString(), "string", false));//V_NEW_ITEM arrayList.Add(GetValueSetParameter(dtA_m.Rows[i]["PERIOD"].ToString(), "string", false));//V_NEW_PERIOD arrayList.Add(GetValueSetParameter(dtA_m.Rows[i]["PERIOD_END"].ToString(), "string", false));//V_NEW_PERIOD_END arrayList.Add(GetValueSetParameter(dtA_m.Rows[i]["PRICE_DISCOUNT"].ToString(), "float", false));//N_NEW_PRICE_DISCOUNT arrayList.Add(GetValueSetParameter(dtA_m.Rows[i]["FIX_PRICE_DISCOUNT"].ToString(), "float", false));//N_NEW_FIX_PRICE_DISCOUNT arrayList.Add(GetValueSetParameter(dtA_m.Rows[i]["VENDOR_SUBSIDY_PERCENT"].ToString(), "float", false));//N_NEW_VENDOR_SUBSIDY_PERCENT arrayList.Add(GetValueSetParameter(dtA_m.Rows[i]["COST_PERCENT"].ToString(), "float", false));//N_NEW_COST_PERCENT arrayList.Add(Convert.ToInt32(dtA_m.Rows[i]["CONFIRM_CODE"].ToString()));//N_NEW_CONFIRM_CODE arrayList.Add(System.DateTime.Now);//D_NEW_UPDATEDATE arrayList.Add(dtA_m.Rows[i]["UPDATEUID"].ToString());//V_NEW_UPDATEUID dbo.doUpdate(arrayList, DBT); } dtA_m.Clear(); } if (dtA_d != null) { for (int i = 0; i <= dtA_d.Rows.Count - 1; i++)//店群折扣 -> 修改 { arrayList.Clear(); arrayList.Add(Convert.ToInt32(dtA_d.Rows[i]["ID", DataRowVersion.Original].ToString())); arrayList.Add(DateTime.Parse(dtA_d.Rows[i]["CREATEDATE", DataRowVersion.Original].ToString())); arrayList.Add(dtA_d.Rows[i]["CREATEUID", DataRowVersion.Original].ToString()); arrayList.Add(DateTime.Parse(dtA_d.Rows[i]["UPDATEDATE", DataRowVersion.Original].ToString())); arrayList.Add(dtA_d.Rows[i]["UPDATEUID", DataRowVersion.Original].ToString()); arrayList.Add(System.DateTime.Now);//D_NEW_UPDATEDATE arrayList.Add(UID);//V_NEW_UPDATEUID dbo.doDelete(arrayList, DBT); } dtA_d.Clear(); } DataTable dtB_i = dtB.GetChanges(DataRowState.Added); DataTable dtB_m = dtB.GetChanges(DataRowState.Modified); DataTable dtB_d = dtB.GetChanges(DataRowState.Deleted); if (dtB_i != null) { for (int i = 0; i < dtB_i.Rows.Count; i++) { arrayList.Clear(); arrayList = this.GetArrayList(dtB_i.Rows[i], UID); dbo.doCreateStoreDiscount(arrayList, DBT, out N_ID, out V_CODE); } } if (dtB_m != null) { for (int i = 0; i <= dtB_m.Rows.Count - 1; i++)//店群折扣 -> 修改 { arrayList.Clear(); arrayList.Add(Convert.ToInt32(dtB_m.Rows[i]["ID"].ToString())); arrayList.Add(DateTime.Parse(dtB_m.Rows[i]["CREATEDATE", DataRowVersion.Original].ToString())); arrayList.Add(dtB_m.Rows[i]["CREATEUID", DataRowVersion.Original].ToString()); arrayList.Add(DateTime.Parse(dtB_m.Rows[i]["UPDATEDATE", DataRowVersion.Original].ToString())); arrayList.Add(dtB_m.Rows[i]["UPDATEUID", DataRowVersion.Original].ToString()); arrayList.Add(dtB_m.Rows[i]["CODE"].ToString());//V_NEW_CODE arrayList.Add(dtB_m.Rows[i]["STORE"].ToString());//V_NEW_GROUP_NO arrayList.Add(Convert.ToDateTime(dtB_m.Rows[i]["EFF_START"].ToString()));//D_NEW_EFF_START arrayList.Add(Convert.ToDateTime(dtB_m.Rows[i]["EFF_END"].ToString()));//D_NEW_EFF_END arrayList.Add(Convert.ToInt32(dtB_m.Rows[i]["DISCOUNT_KIND"].ToString()));//N_NEW_DISCOUNT_KIND arrayList.Add(GetValueSetParameter(dtB_m.Rows[i]["ROOT_NO"].ToString(), "string", false));//V_NEW_ROOT_NO arrayList.Add(GetValueSetParameter(dtB_m.Rows[i]["PMA"].ToString(), "string", false));//V_NEW_PMA arrayList.Add(GetValueSetParameter(dtB_m.Rows[i]["CATEGORY"].ToString(), "string", false));//V_NEW_CATEGORY arrayList.Add(GetValueSetParameter(dtB_m.Rows[i]["ITEM"].ToString(), "string", false));//V_NEW_ITEM arrayList.Add(GetValueSetParameter(dtB_m.Rows[i]["PERIOD"].ToString(), "string", false));//V_NEW_PERIOD arrayList.Add(GetValueSetParameter(dtB_m.Rows[i]["PERIOD_END"].ToString(), "string", false));//V_NEW_PERIOD_END arrayList.Add(GetValueSetParameter(dtB_m.Rows[i]["PRICE_DISCOUNT"].ToString(), "float", false));//N_NEW_PRICE_DISCOUNT arrayList.Add(GetValueSetParameter(dtB_m.Rows[i]["FIX_PRICE_DISCOUNT"].ToString(), "float", false));//N_NEW_FIX_PRICE_DISCOUNT arrayList.Add(GetValueSetParameter(dtB_m.Rows[i]["VENDOR_SUBSIDY_PERCENT"].ToString(), "float", false));//N_NEW_VENDOR_SUBSIDY_PERCENT arrayList.Add(GetValueSetParameter(dtB_m.Rows[i]["COST_PERCENT"].ToString(), "float", false));//N_NEW_COST_PERCENT arrayList.Add(Convert.ToInt32(dtB_m.Rows[i]["CONFIRM_CODE"].ToString()));//N_NEW_CONFIRM_CODE arrayList.Add(System.DateTime.Now);//D_NEW_UPDATEDATE arrayList.Add(dtB_m.Rows[i]["UPDATEUID"].ToString());//V_NEW_UPDATEUID dbo.doUpdate(arrayList, DBT); } dtB_m.Clear(); } if (dtB_d != null) { for (int i = 0; i <= dtB_d.Rows.Count - 1; i++)//店群折扣 -> 修改 { arrayList.Clear(); arrayList.Add(Convert.ToInt32(dtB_d.Rows[i]["ID", DataRowVersion.Original].ToString())); arrayList.Add(DateTime.Parse(dtB_d.Rows[i]["CREATEDATE", DataRowVersion.Original].ToString())); arrayList.Add(dtB_d.Rows[i]["CREATEUID", DataRowVersion.Original].ToString()); arrayList.Add(DateTime.Parse(dtB_d.Rows[i]["UPDATEDATE", DataRowVersion.Original].ToString())); arrayList.Add(dtB_d.Rows[i]["UPDATEUID", DataRowVersion.Original].ToString()); arrayList.Add(System.DateTime.Now);//D_NEW_UPDATEDATE arrayList.Add(UID);//V_NEW_UPDATEUID dbo.doDelete(arrayList, DBT); } dtB_d.Clear(); } if (dtC != null) { DataTable dtC_i = dtC.GetChanges(DataRowState.Added); DataTable dtC_m = dtC.GetChanges(DataRowState.Modified); DataTable dtC_d = dtC.GetChanges(DataRowState.Deleted); if (dtC_i != null) { for (int i = 0; i < dtC_i.Rows.Count; i++) { arrayList.Clear(); arrayList = this.GetArrayList(dtC_i.Rows[i], UID); dbo.doCreateStoreDiscount(arrayList, DBT, out N_ID, out V_CODE); } } if (dtC_m != null) { for (int i = 0; i <= dtC_m.Rows.Count - 1; i++)//店群折扣 -> 修改 { arrayList.Clear(); arrayList.Add(Convert.ToInt32(dtC_m.Rows[i]["ID"].ToString())); arrayList.Add(DateTime.Parse(dtC_m.Rows[i]["CREATEDATE", DataRowVersion.Original].ToString())); arrayList.Add(dtC_m.Rows[i]["CREATEUID", DataRowVersion.Original].ToString()); arrayList.Add(DateTime.Parse(dtC_m.Rows[i]["UPDATEDATE", DataRowVersion.Original].ToString())); arrayList.Add(dtC_m.Rows[i]["UPDATEUID", DataRowVersion.Original].ToString()); arrayList.Add(dtC_m.Rows[i]["CODE"].ToString());//V_NEW_CODE arrayList.Add(dtC_m.Rows[i]["STORE"].ToString());//V_NEW_GROUP_NO arrayList.Add(Convert.ToDateTime(dtC_m.Rows[i]["EFF_START"].ToString()));//D_NEW_EFF_START arrayList.Add(Convert.ToDateTime(dtC_m.Rows[i]["EFF_END"].ToString()));//D_NEW_EFF_END arrayList.Add(Convert.ToInt32(dtC_m.Rows[i]["DISCOUNT_KIND"].ToString()));//N_NEW_DISCOUNT_KIND arrayList.Add(GetValueSetParameter(dtC_m.Rows[i]["ROOT_NO"].ToString(), "string", false));//V_NEW_ROOT_NO arrayList.Add(GetValueSetParameter(dtC_m.Rows[i]["PMA"].ToString(), "string", false));//V_NEW_PMA arrayList.Add(GetValueSetParameter(dtC_m.Rows[i]["CATEGORY"].ToString(), "string", false));//V_NEW_CATEGORY arrayList.Add(GetValueSetParameter(dtC_m.Rows[i]["ITEM"].ToString(), "string", false));//V_NEW_ITEM arrayList.Add(GetValueSetParameter(dtC_m.Rows[i]["PERIOD"].ToString(), "string", false));//V_NEW_PERIOD arrayList.Add(GetValueSetParameter(dtC_m.Rows[i]["PERIOD_END"].ToString(), "string", false));//V_NEW_PERIOD_END arrayList.Add(GetValueSetParameter(dtC_m.Rows[i]["PRICE_DISCOUNT"].ToString(), "float", false));//N_NEW_PRICE_DISCOUNT arrayList.Add(GetValueSetParameter(dtC_m.Rows[i]["FIX_PRICE_DISCOUNT"].ToString(), "float", false));//N_NEW_FIX_PRICE_DISCOUNT arrayList.Add(GetValueSetParameter(dtC_m.Rows[i]["VENDOR_SUBSIDY_PERCENT"].ToString(), "float", false));//N_NEW_VENDOR_SUBSIDY_PERCENT arrayList.Add(GetValueSetParameter(dtC_m.Rows[i]["COST_PERCENT"].ToString(), "float", false));//N_NEW_COST_PERCENT arrayList.Add(Convert.ToInt32(dtC_m.Rows[i]["CONFIRM_CODE"].ToString()));//N_NEW_CONFIRM_CODE arrayList.Add(System.DateTime.Now);//D_NEW_UPDATEDATE arrayList.Add(dtC_m.Rows[i]["UPDATEUID"].ToString());//V_NEW_UPDATEUID dbo.doUpdate(arrayList, DBT); } dtC_m.Clear(); } if (dtC_d != null) { for (int i = 0; i <= dtC_d.Rows.Count - 1; i++)//店群折扣 -> 修改 { arrayList.Clear(); arrayList.Add(Convert.ToInt32(dtC_d.Rows[i]["ID", DataRowVersion.Original].ToString())); arrayList.Add(DateTime.Parse(dtC_d.Rows[i]["CREATEDATE", DataRowVersion.Original].ToString())); arrayList.Add(dtC_d.Rows[i]["CREATEUID", DataRowVersion.Original].ToString()); arrayList.Add(DateTime.Parse(dtC_d.Rows[i]["UPDATEDATE", DataRowVersion.Original].ToString())); arrayList.Add(dtC_d.Rows[i]["UPDATEUID", DataRowVersion.Original].ToString()); arrayList.Add(System.DateTime.Now);//D_NEW_UPDATEDATE arrayList.Add(UID);//V_NEW_UPDATEUID dbo.doDelete(arrayList, DBT); } dtC_d.Clear(); } } #region 交易成功 if (IsRootTranscation) { //獨立呼叫Transcation成立 DBT.Commit(); } #endregion } catch (Exception ex) { #region 交易失敗 //if (IsRootTranscation) //{ //獨立呼叫Transcation失敗 DBT.Rollback(); //} #endregion throw ex; } finally { #region 判斷是否關閉交易連線 if (IsRootTranscation) { //獨立呼叫Transcation,關閉連線 if (Conn.State == ConnectionState.Connecting) { Conn.Close(); } } #endregion } }
/// <summary> /// Updates the child nodes of "parentNode" by using the fields from a datatable. /// </summary> /// <param name="parentNode"></param> /// <param name="table"></param> /// <param name="keyField">The column name of the datatable that acts as a primary key.</param> /// <remarks> /// The child nodes that will be updated must have attribute fields that correspond to /// the DataTable. The "keyField" will be used to identify the attribute that serves as /// an identifier of the rows. The datatable can have less fields than the nodes so /// you have the chance to update smaller subsets. /// Make sure that you did not call "AcceptChanges" before passing the datatable or this /// function will not find any change. /// </remarks> public static void UpdateChildNodesWithDataTable(XmlNode parentNode, DataTable table, string keyField) { if (parentNode == null) { throw new ArgumentNullException("Unable to update child nodes because parentNode is null"); } if (parentNode.HasChildNodes) { XmlNode firstNode = parentNode.ChildNodes[0]; // // Verify that the fields of first child node match the fields in the data table // note that it's ok if the datatable has fewer fields than the nodes. string missingFields = ""; StringCollection columnNames = new StringCollection(); foreach (DataColumn col in table.Columns) { if (firstNode.Attributes[col.ColumnName] == null) { if (missingFields.Length == 0) missingFields = col.ColumnName; else missingFields += ", " + col.ColumnName; } else columnNames.Add(col.ColumnName); } if (missingFields.Length > 0) { throw new Exception("Unable to update nodes with datatable because the nodes are missing the fields: "+missingFields); } /// /// Remove nodes that got deleted from datatable /// DataTable currTable = table.GetChanges(DataRowState.Deleted); if (currTable != null) { //since there is no way to tell which rows got deleted then just remove all nodes //that are not present in the datatable. XmlNode nodeToDelete; Trace.WriteLine("Rows Deleted:"); foreach (DataRow row in table.Rows) { string keyValue = row[keyField].ToString(); nodeToDelete = SelectNode(parentNode, keyField, keyValue); Trace.WriteLine(keyValue); if (nodeToDelete != null) { parentNode.RemoveChild(nodeToDelete); } } } /// /// Update nodes with changes made on the datatable /// currTable = table.GetChanges(DataRowState.Modified); if (currTable != null) { XmlNode nodeToUpdate; Trace.WriteLine("Rows Changed:"); foreach (DataRow row in currTable.Rows) { string keyValue = row[keyField].ToString(); Trace.WriteLine(keyValue); nodeToUpdate = SelectNode(parentNode, keyField, keyValue); if (nodeToUpdate == null) throw new Exception("Unable to update node with '"+keyField+"="+keyValue+"' because it doesn't exist"); string valueToSet; foreach (string colName in columnNames) { if (colName == keyField) continue; valueToSet = row[colName].ToString(); SetAttributeValue(nodeToUpdate, colName, valueToSet); } } } /// /// Add new nodes to match new rows added to datatable /// currTable = table.GetChanges(DataRowState.Added); if (currTable != null) { XmlNode newNode; string keyValue; XmlDocument doc = parentNode.OwnerDocument; Trace.WriteLine("Rows Added:"); foreach (DataRow row in currTable.Rows) { keyValue = row[keyField].ToString(); Trace.WriteLine(keyValue); if (SelectNode(parentNode, keyField, keyValue) == null) { newNode = doc.CreateElement(firstNode.Name); CopyAttributes(row, newNode); parentNode.AppendChild(newNode); } else { System.Windows.Forms.MessageBox.Show("Can not add duplicate nodes. Row with '"+keyField+"="+keyValue+" was not added.", "Error Updating Nodes from Table"); } } } table.AcceptChanges(); } }
/// <summary> /// Saves the table to the DB. /// </summary> /// <param name="table">database table.</param> public void Save(DataTable table) { try { // prepares select command. string query = "SELECT * FROM " + table.TableName; command.CommandType = CommandType.Text; command.CommandText = query; command.Connection = connection; // opens connection. connection.Open(); // gets transaction context. SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.RepeatableRead); command.Transaction = transaction; // sets the SqlCommandBuilder that constructs update, delete, insert commands. SqlDataAdapter dataAdapter = new SqlDataAdapter(); dataAdapter.SelectCommand = command; SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter); try { DataTable changes; changes = table.GetChanges(DataRowState.Deleted); if(changes != null) dataAdapter.Update(changes); changes = table.GetChanges(DataRowState.Modified); if(changes != null) dataAdapter.Update(changes); changes = table.GetChanges(DataRowState.Added); if(changes != null) dataAdapter.Update(changes); transaction.Commit(); } catch { transaction.Rollback(); throw; } } catch(Exception e) { Log.WriteErrorToLog(e.Message); throw; } }
public void Changes () //To test GetChanges and RejectChanges { DataTable table = new DataTable (); DataColumn col = new DataColumn (); col.ColumnName = "Id"; col.DataType = typeof (int); table.Columns.Add (col); UniqueConstraint uc = new UniqueConstraint ("UK1", table.Columns[0] ); table.Constraints.Add (uc); col = new DataColumn (); col.ColumnName = "Name"; col.DataType = typeof (string); table.Columns.Add (col); DataRow row = table.NewRow (); row ["Id"] = 147; row ["name"] = "Abc"; table.Rows.Add (row); table.AcceptChanges (); row = table.NewRow (); row ["Id"] = 47; row ["name"] = "Efg"; table.Rows.Add (row); //Testing GetChanges DataTable changesTable = table.GetChanges (); Assert.AreEqual (1, changesTable.Rows.Count, "#A01"); Assert.AreEqual ("Efg", changesTable.Rows[0]["Name"], "#A02"); table.AcceptChanges (); changesTable = table.GetChanges (); try { int cnt = changesTable.Rows.Count; Assert.Fail (); } catch (NullReferenceException) { } //Testing RejectChanges row = table.NewRow (); row ["Id"] = 247; row ["name"] = "Hij"; table.Rows.Add (row); (table.Rows [0])["Name"] = "AaBbCc"; table.RejectChanges (); Assert.AreEqual ("Abc" , (table.Rows [0]) ["Name"], "#A03"); Assert.AreEqual (2, table.Rows.Count, "#A04"); }
//---------------------------------------------------------------------------------------------------- /// <summary> /// 테이블 갱신(INSERT,UPDATE,DELETE...) /// </summary> /// <param name="table">테이블 객체</param> /// <param name="adapter">어답터</param> /// <returns>성공유무</returns> //---------------------------------------------------------------------------------------------------- public bool UpdateTable( DataTable table, DbDataAdapter adapter ) { DataTable changed_table = table.GetChanges(); if( changed_table==null ) return true; // 업데이트 DbTransaction tran = (DbTransaction)m_connection.BeginTransaction(); try { //adapter.SelectCommand.Transaction = tran; adapter.InsertCommand.Transaction = tran; adapter.UpdateCommand.Transaction = tran; adapter.DeleteCommand.Transaction = tran; adapter.Update(changed_table); tran.Commit(); } catch( Exception ex ) { tran.Rollback(); Log( ex.ToString() ); m_error = ex.Message; return false; } table.AcceptChanges(); return true; }