GetChanges() public method

public GetChanges ( ) : DataTable
return DataTable
        /// <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;
 }
Beispiel #3
0
        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);
     }
 }
Beispiel #5
0
        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;
     }
 }
Beispiel #7
0
 /// <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);
     }
 }
Beispiel #8
0
 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);
 }
Beispiel #9
0
        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;
        }
Beispiel #11
0
        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;
      }
    }
Beispiel #13
0
 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();
 }
Beispiel #14
0
        /// <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;
        }
Beispiel #17
0
        /// <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();
        }
Beispiel #18
0
        /// <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;
		}
Beispiel #20
0
 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;
     }
 }
Beispiel #21
0
        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);
		}
Beispiel #23
0
        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;
        }
Beispiel #25
0
        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);
            }
        }
Beispiel #26
0
        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();
        }
Beispiel #28
0
        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());

            }
        }
Beispiel #29
0
        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

            }
        }
Beispiel #31
0
		/// <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;
			}
		}
Beispiel #33
0
		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");
		}
Beispiel #34
0
        //----------------------------------------------------------------------------------------------------
        /// <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;
        }