示例#1
0
		public void run()
		{
			Exception exp = null;

			OleDbConnection con = new OleDbConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
			OleDbTransaction txn = null;

			try
			{
				BeginCase("BeginTransaction - connection close");
				try
				{
					txn = con.BeginTransaction();
				}
				catch (Exception ex) {exp = ex;}
				Compare(exp.GetType().FullName ,typeof(InvalidOperationException).FullName );
				exp=null;
			} 
			catch(Exception ex){exp = ex;}
			finally{EndCase(exp); exp = null;}

			con.Open();

			try
			{
				BeginCase("BeginTransaction - connection close");
				txn = con.BeginTransaction();
				Compare(txn == null,false );
			} 
			catch(Exception ex){exp = ex;}
			finally{EndCase(exp); exp = null;}

			if (con.State == ConnectionState.Open) con.Close();

		}
示例#2
0
        /// <summary>
        /// 公有方法,执行一组Sql语句。
        /// </summary>
        /// <param name="SqlStrings">Sql语句组</param>
        /// <returns>是否成功</returns>
        public bool ExecuteSQL(ArrayList SqlStrings)
        {
            ////System.Windows.Forms.MessageBox.Show("SqlStrings:" + SqlStrings);
            bool success = true;

            Open();
            OleDbCommand     cmd   = new OleDbCommand();
            OleDbTransaction trans = Connection.BeginTransaction();

            cmd.Connection  = Connection;
            cmd.Transaction = trans;
            try
            {
                foreach (String str in SqlStrings)
                {
                    cmd.CommandText = str;
                    cmd.ExecuteNonQuery();
                }
                trans.Commit();
            }
            catch (Exception ex)
            {
                success = false;
                MessageBox.Show(ex.Message);
                trans.Rollback();
            }
            finally
            {
                Close();
            }
            return(success);
        }
        /// <summary>
        /// Test OleDbTransaction class
        /// </summary>    
        public static void Test_Transaction()
        {
            using (OleDbConnection conn = new OleDbConnection())
            {
                OleDbTransaction transaction = null;
                conn.ConnectionString = TestCases.connString;
                conn.Open();

                TestCases.ExecuteSQL("drop table if exists t", conn);

                transaction = conn.BeginTransaction();

                string sql = "create table t(idx integer)";
                using (OleDbCommand command = new OleDbCommand(sql, conn))
                {
                    command.Transaction = transaction;
                    command.ExecuteNonQuery();
                }

                int tablesCount = TestCases.GetTablesCount("t", conn, transaction);
                Assert.IsTrue(tablesCount == 1);

                transaction.Rollback();

                //Verify the table does not exist
                tablesCount = GetTablesCount("t", conn, transaction);
                Assert.IsTrue(tablesCount == 0);

                transaction = conn.BeginTransaction();

                sql = "create table t(idx integer)";
                using (OleDbCommand command = new OleDbCommand(sql, conn))
                {
                    command.Transaction = transaction;
                    command.ExecuteNonQuery();
                }

                tablesCount = GetTablesCount("t", conn, transaction);
                Assert.IsTrue(tablesCount == 1);

                transaction.Commit();

                tablesCount = GetTablesCount("t", conn, transaction);
                Assert.IsTrue(tablesCount == 1);

                transaction = conn.BeginTransaction();

                TestCases.ExecuteSQL("drop table t", conn, transaction);

                transaction.Commit();

                tablesCount = GetTablesCount("t", conn);
                Assert.IsTrue(tablesCount == 0);
            }
        }
示例#4
0
 /// <summary>
 /// 执行多条SQL语句,实现数据库事务。
 /// </summary>
 /// <param name="SQLStringList">多条SQL语句</param>		
 public static void ExecuteSqlTran(ArrayList SQLStringList)
 {
     using (OleDbConnection conn = new OleDbConnection(connectionString))
     {
         conn.Open();
         OleDbCommand cmd = new OleDbCommand();
         cmd.Connection = conn;
         OleDbTransaction tx = conn.BeginTransaction();
         cmd.Transaction = tx;
         try
         {
             for (int n = 0; n < SQLStringList.Count; n++)
             {
                 string strsql = SQLStringList[n].ToString();
                 if (strsql.Trim().Length > 1)
                 {
                     cmd.CommandText = strsql;
                     cmd.ExecuteNonQuery();
                 }
             }
             tx.Commit();
         }
         catch (System.Data.OleDb.OleDbException E)
         {
             tx.Rollback();
             throw new Exception(E.Message);
         }
     }
 }
        public override void Delete()
        {
            System.Data.OleDb.OleDbConnection con = Db.Connection;
            OleDbTransaction trans = con.BeginTransaction();

            try
            {
                OleDbCommand cmd = con.CreateCommand();
                cmd.Transaction = trans;
                cmd.CommandText = @"delete from  " + GetDbTableName() +
                                  @" where id = " + m_Id.ToString();
                cmd.ExecuteNonQuery();

                if (GetForeignDocField() != null)
                {
                    cmd.CommandText = @"update documents set " + GetForeignDocField()
                                      + " = NULL where " + GetForeignDocField() + " = " + m_Id.ToString();
                    cmd.ExecuteNonQuery();
                }
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
        }
        public int product_service(string service_id, string product_id, int customer_id)
        {
            int row=0;

               cn = new OleDbConnection("Provider=MSDAORA;Data Source=192.168.0.217/orcl;Persist Security Info=True;User ID=hr;Password=hr;");

            cmd = cn.CreateCommand();
            try
            {
                cn.Open();

                string s = "update product_services set product_id='" + product_id + "' where service_id=(select service_id from customer_services where customer_id=" + customer_id + " and service_id='" + service_id + "')";
                Trans = cn.BeginTransaction();
                cmd = new OleDbCommand(s, cn,Trans);
                cmd.Transaction = Trans;
                row= cmd.ExecuteNonQuery();
                Trans.Commit();

            }
            catch (Exception e)
            {
                string s = e.Message;
                Trans.Rollback();
            }
            finally
            {
                cn.Close();
            }
            return row;
        }
        public void exitTopic(TUser tUser)
        {
            //--Data Base Access Variables--
            System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
            System.Data.OleDb.OleDbCommand    dbCommand    = new OleDbCommand();
            dbCommand.Connection = dbConnection;
            System.Data.OleDb.OleDbTransaction dbTransaction;
            //-----------------------------

            dbConnection.Close();
            dbConnection.Open();
            dbTransaction         = dbConnection.BeginTransaction();
            dbCommand.Transaction = dbTransaction;
            try
            {
                //Begin Transaction
                dbCommand.CommandText = "UPDATE User_Topic SET finishDateTime = '" + DateTime.Now.ToString() + "' WHERE topic_id = " + tUser.topic.id.ToString();
                dbCommand.ExecuteNonQuery();
                tUser.topic = null;
                dbTransaction.Commit();
                //End Transaction
            }
            catch
            {
                dbTransaction.Rollback();
                dbCommand.Transaction = null;
                dbTransaction         = null;
                dbConnection.Close();
            }

            dbCommand.Transaction = null;
            dbTransaction         = null;
            dbConnection.Close();
        }
        public bool Execute(string qry)
        {
            ConnectionString cnString = new ConnectionString();
            IDbConnection cn = new OleDbConnection(cnString.GetConnString());
            IDbCommand cmd = new OleDbCommand(qry, (OleDbConnection)cn);
            cmd.CommandType = CommandType.StoredProcedure;

            try
            {
                cn.Open();
                IDbTransaction tran = cn.BeginTransaction();
                cmd.Transaction = tran;

                int affectedRows = cmd.ExecuteNonQuery();
                Console.WriteLine(affectedRows);
                if (affectedRows > 0)
                {
                    tran.Commit();
                    return true;
                }
                else
                {
                    tran.Rollback();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                cn.Close();
            }
            return false;
        }
		public void run()
		{
			OleDbConnection con = null;
			OleDbTransaction txn;
			Exception exp = null;
			try
			{
				BeginCase("OleDbTransaction Rollback");

				//
				//prepare data
				base.PrepareDataForTesting(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);

				string Result = "";
				con = new OleDbConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
				con.Open();
				txn = con.BeginTransaction();
				OleDbCommand cmd = new OleDbCommand("Update Employees Set LastName = 'StamLastName' Where EmployeeID = 100", con, txn);
				cmd.ExecuteNonQuery();
				txn.Rollback();

				//
				//
				cmd = new OleDbCommand("Select LastName From Employees Where EmployeeID = 100", con);
				Result = cmd.ExecuteScalar().ToString();
				Compare(Result,"Last100" );
				this.Log(Result);

			} 
			catch(Exception ex){exp = ex;}
			finally{EndCase(exp); exp = null;}

			if (con.State == ConnectionState.Open) con.Close();
		}
示例#10
0
	public void run()
	{

		Exception exp = null;
		OleDbConnection con = new OleDbConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
		con.Open();
		OleDbTransaction txn = con.BeginTransaction();
		OleDbCommand cmd = new OleDbCommand("Select * From Employees", con);

		try
		{
			BeginCase("check Transaction property - default");
			Compare(cmd.Transaction==null , true);
		} 
		catch(Exception ex){exp = ex;}
		finally{EndCase(exp); exp = null;}

		cmd.Transaction = txn;

		try
		{
			BeginCase("check Transaction property");
			Compare(cmd.Transaction , txn);
		} 
		catch(Exception ex){exp = ex;}
		finally{EndCase(exp); exp = null;}
		
		if (con.State == ConnectionState.Open) con.Close();

	}
示例#11
0
        public override bool Delete()
        {
            var sqlStatement = "Delete From Path ";
            using (var conn = new OleDbConnection(this.ConnectionString))
            {
                conn.Open();
                var trans = conn.BeginTransaction();
                try
                {
                    AccessHelper.ExecuteNonQuery(trans, sqlStatement);
                    trans.Commit();

                    return true;
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    Logger.Error(ex.Message, ex);
                    return false;
                }
                finally
                {
                    conn.Close();
                }
            }
        }
示例#12
0
        public void setMessageDeletedByStudent(string message_id)
        {
            //--Data Base Access Variables--
            System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
            System.Data.OleDb.OleDbCommand    dbCommand    = new OleDbCommand();
            dbCommand.Connection = dbConnection;
            System.Data.OleDb.OleDbTransaction dbTransaction;
            //-----------------------------

            dbConnection.Close();
            dbConnection.Open();
            dbTransaction         = dbConnection.BeginTransaction();
            dbCommand.Transaction = dbTransaction;
            try
            {
                dbCommand.CommandText = "UPDATE Messages SET deletedByStudent = true WHERE id = " + message_id;
                dbCommand.ExecuteNonQuery();
                dbTransaction.Commit();
                //End Transaction
            }
            catch
            {
                dbTransaction.Rollback();
                dbCommand.Transaction = null;
                dbTransaction         = null;
                dbConnection.Close();
            }

            dbCommand.Transaction = null;
            dbTransaction         = null;
            dbConnection.Close();
        }
示例#13
0
        public void finishTopic(TTopic tTopic)
        {
            //--Data Base Access Variables--
            System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
            System.Data.OleDb.OleDbCommand    dbCommand    = new OleDbCommand();
            dbCommand.Connection = dbConnection;
            System.Data.OleDb.OleDbTransaction dbTransaction;
            //-----------------------------

            dbConnection.Close();
            dbConnection.Open();
            dbTransaction         = dbConnection.BeginTransaction();
            dbCommand.Transaction = dbTransaction;
            try
            {
                //---Begin Transaction---
                tTopic.finishDateTime = DateTime.Now;
                //UPDATE Topic
                dbCommand.CommandText = "UPDATE Topics SET finishDateTime = '" + tTopic.finishDateTime.ToString() + "' WHERE id = " + tTopic.id.ToString();
                dbCommand.ExecuteNonQuery();;
                dbTransaction.Commit();
                //----End Transaction----
            }
            catch
            {
                dbTransaction.Rollback();
                dbCommand.Transaction = null;
                dbTransaction         = null;
                dbConnection.Close();
            }

            dbCommand.Transaction = null;
            dbTransaction         = null;
            dbConnection.Close();
        }
示例#14
0
 public RowCounts ApplyTableChanges(TableConf table, TableConf archiveTable, string dbName, long CTID, string CTDBName, bool isConsolidated)
 {
     var cmds = new List<InsertDelete>();
     cmds.Add(BuildApplyCommand(table, dbName, CTDBName, CTID));
     if (archiveTable != null) {
         cmds.Add(BuildApplyCommand(archiveTable, dbName, CTDBName, CTID));
     }
     var connStr = buildConnString(dbName);
     var rowCounts = new RowCounts(0, 0);
     using (var conn = new OleDbConnection(connStr)) {
         conn.Open();
         var trans = conn.BeginTransaction();
         foreach (var id in cmds) {
             id.delete.Transaction = trans;
             id.delete.Connection = conn;
             id.delete.CommandTimeout = Config.QueryTimeout;
             logger.Log(id.delete.CommandText, LogLevel.Trace);
             int deleted = id.delete.ExecuteNonQuery();
             logger.Log(new { Table = table.Name, message = "Rows deleted: " + deleted }, LogLevel.Info);
             id.insert.Transaction = trans;
             id.insert.Connection = conn;
             id.insert.CommandTimeout = Config.QueryTimeout;
             logger.Log(id.insert.CommandText, LogLevel.Trace);
             int inserted = id.insert.ExecuteNonQuery();
             logger.Log(new { Table = table.Name, message = "Rows inserted: " + inserted }, LogLevel.Info);
             rowCounts = new RowCounts(rowCounts.Inserted + inserted, rowCounts.Deleted + deleted);
         }
         trans.Commit();
     }
     return rowCounts;
 }
示例#15
0
 public static void ExecuteSqlTran(Hashtable SQLStringList)
 {
     using (System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(DbHelperOleDb.connectionString))
     {
         oleDbConnection.Open();
         using (System.Data.OleDb.OleDbTransaction oleDbTransaction = oleDbConnection.BeginTransaction())
         {
             System.Data.OleDb.OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand();
             try
             {
                 foreach (DictionaryEntry dictionaryEntry in SQLStringList)
                 {
                     string cmdText = dictionaryEntry.Key.ToString();
                     System.Data.OleDb.OleDbParameter[] cmdParms = (System.Data.OleDb.OleDbParameter[])dictionaryEntry.Value;
                     DbHelperOleDb.PrepareCommand(oleDbCommand, oleDbConnection, oleDbTransaction, cmdText, cmdParms);
                     int num = oleDbCommand.ExecuteNonQuery();
                     oleDbCommand.Parameters.Clear();
                     oleDbTransaction.Commit();
                 }
             }
             catch
             {
                 oleDbTransaction.Rollback();
                 throw;
             }
         }
     }
 }
示例#16
0
 public static void ExecuteSqlTran(ArrayList SQLStringList)
 {
     using (System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(DbHelperOleDb.connectionString))
     {
         oleDbConnection.Open();
         System.Data.OleDb.OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand();
         oleDbCommand.Connection = oleDbConnection;
         System.Data.OleDb.OleDbTransaction oleDbTransaction = oleDbConnection.BeginTransaction();
         oleDbCommand.Transaction = oleDbTransaction;
         try
         {
             for (int i = 0; i < SQLStringList.Count; i++)
             {
                 string text = SQLStringList[i].ToString();
                 if (text.Trim().Length > 1)
                 {
                     oleDbCommand.CommandText = text;
                     oleDbCommand.ExecuteNonQuery();
                 }
             }
             oleDbTransaction.Commit();
         }
         catch (System.Data.OleDb.OleDbException ex)
         {
             oleDbTransaction.Rollback();
             throw new Exception(ex.Message);
         }
     }
 }
示例#17
0
        public void activeTopic(int topic_id)
        {
            //--Data Base Access Variables--
            System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
            System.Data.OleDb.OleDbCommand    dbCommand    = new OleDbCommand();
            dbCommand.Connection = dbConnection;
            System.Data.OleDb.OleDbTransaction dbTransaction;
            //-----------------------------

            dbConnection.Close();
            dbConnection.Open();
            dbTransaction         = dbConnection.BeginTransaction();
            dbCommand.Transaction = dbTransaction;
            try
            {
                //---Begin Transaction---
                //UPDATE Topic
                dbCommand.CommandText = "UPDATE Topics SET finishDateTime = NULL WHERE id = " + topic_id;
                dbCommand.ExecuteNonQuery();;
                dbTransaction.Commit();
                //----End Transaction----
            }
            catch
            {
                dbTransaction.Rollback();
                dbCommand.Transaction = null;
                dbTransaction         = null;
                dbConnection.Close();
            }

            dbCommand.Transaction = null;
            dbTransaction         = null;
            dbConnection.Close();
        }
示例#18
0
    public bool DeleteContentType(string id)
    {
        String strCmd1 = string.Format("DELETE FROM ContentType WHERE ID = {0}" , id);

        String strCmd2 = string.Format("DELETE FROM Contents WHERE TypeCode = (SELECT TypeCode FROM ContentType WHERE ID = {0})",id);

        OleDbConnection conn = new OleDbConnection(StrConn);
        OleDbTransaction transaction = null;
        bool flag = false;
        try
        {
            conn.Open();
            transaction = conn.BeginTransaction();

            OleDbCommand contentCommand = new OleDbCommand(strCmd2, conn, transaction);
            contentCommand.ExecuteNonQuery();

            OleDbCommand typeCommand = new OleDbCommand(strCmd1, conn,transaction);
            typeCommand.ExecuteNonQuery();

            transaction.Commit();
            flag = true;
        }
        catch (Exception ex)
        {
            flag = false;
            transaction.Rollback();
        }
        finally
        {
            conn.Close();
            conn.Dispose();
        }
        return flag;
    }
        protected void BtnInsertJjd_Click(object sender, EventArgs e)
        {
            string jjdNo_;
            string psQty_;
            string reqObjid_;
            string reqVer_ = "";
            string reqMsg_;

            jjdNo_ = TxtJjdNo.Text;
            //�򽻽ӵ����� jjd_line,�� ����jjd_no����jjd_line
            using (OleDbConnection conn = new OleDbConnection(DBHelper.OleConnectionString))
            {
                conn.Open();
                OleDbTransaction tr = conn.BeginTransaction();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Transaction = tr;
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;

                if (jjdNo_ == "")
                {
                    Misc.Message(this.GetType(),ClientScript, "err01 �������ӵ�ʧ��!");
                    return;
                }

                cmd.CommandText = "jp_jjd_line_api.new_";
                cmd.Parameters.Clear();

                cmd.Parameters.Add("v_jjd_no", OleDbType.VarChar).Value = jjdNo_;
                cmd.Parameters.Add("v_req_objid", OleDbType.VarChar);
                cmd.Parameters.Add("v_req_rowversion", OleDbType.VarChar);
                cmd.Parameters.Add("v_qty", OleDbType.Single);
                cmd.Parameters.Add("v_msg", OleDbType.VarChar, 1000).Direction = ParameterDirection.Output;

                foreach (GridViewRow grv in GVReqData.Rows)
                {
                    psQty_ = ((TextBox)(grv.FindControl("TxtJjdLineQty"))).Text;
                    if (psQty_ == "") continue;
                    reqObjid_ = grv.Cells[13].Text;
                    reqVer_ = grv.Cells[14].Text;
                    cmd.Parameters["v_req_objid"].Value = reqObjid_;
                    cmd.Parameters["v_req_rowversion"].Value = reqVer_;
                    cmd.Parameters["v_qty"].Value = Convert.ToSingle(psQty_);

                    cmd.ExecuteNonQuery();

                    reqMsg_ = cmd.Parameters["v_msg"].Value.ToString();

                    if (reqMsg_ != "1")
                    {
                        Misc.Message(this.GetType(),ClientScript, string.Format("err02 �������ӵ�ʧ�ܣ�{0}", reqMsg_));
                        return;
                    }
                }
                tr.Commit();
            }

            DisplayJjd(jjdNo_);
        }
示例#20
0
 /// <summary>
 /// Initializes a new instance.
 /// </summary>
 /// <param name="connectionString">OleDB Connection string</param>
 /// <param name="useTransaction">True if you want to use a transaction</param>
 public SqlHelper(string connectionString, bool useTransaction) {
     _connection = new OleDbConnection(connectionString);
     _connection.Open();
  
     if (useTransaction) {
         _transaction = _connection.BeginTransaction();
     }
 }
示例#21
0
        /// <summary>
        /// 增加一条数据,及其子表数据
        /// </summary>
        public int Add(Model.manager_role model)
        {
            int newId;
            using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString))
            {
                conn.Open();
                using (OleDbTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        StringBuilder strSql = new StringBuilder();
                        strSql.Append("insert into dt_manager_role(");
                        strSql.Append("role_name,role_type)");
                        strSql.Append(" values (");
                        strSql.Append("@role_name,@role_type)");

                        OleDbParameter[] parameters = {
					            new OleDbParameter("@role_name", OleDbType.VarChar,100),
					            new OleDbParameter("@role_type", OleDbType.TinyInt,1)};
                        parameters[0].Value = model.role_name;
                        parameters[1].Value = model.role_type;

                        DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters);
                        //取得新插入的ID
                        newId = GetMaxId(conn, trans);

                        StringBuilder strSql2;
                        foreach (Model.manager_role_value models in model.manager_role_values)
                        {
                            strSql2 = new StringBuilder();
                            strSql2.Append("insert into dt_manager_role_value(");
                            strSql2.Append("role_id,channel_name,channel_id,action_type)");
                            strSql2.Append(" values (");
                            strSql2.Append("@role_id,@channel_name,@channel_id,@action_type)");
                            OleDbParameter[] parameters2 = {
						            new OleDbParameter("@role_id", OleDbType.Integer,4),
						            new OleDbParameter("@channel_name", OleDbType.VarChar,255),
						            new OleDbParameter("@channel_id", OleDbType.Integer,4),
						            new OleDbParameter("@action_type", OleDbType.VarChar,100)};
                            parameters2[0].Value = newId;
                            parameters2[1].Value = models.channel_name;
                            parameters2[2].Value = models.channel_id;
                            parameters2[3].Value = models.action_type;

                            DbHelperOleDb.ExecuteSql(conn, trans, strSql2.ToString(), parameters2);
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        return -1;
                    }
                }
            }
            return newId;
        }
示例#22
0
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Model.manager_role model)
        {
            using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString))
            {
                conn.Open();
                using (OleDbTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        StringBuilder strSql = new StringBuilder();
                        strSql.Append("insert into " + databaseprefix + "manager_role(");
                        strSql.Append("role_name,role_type,is_sys)");
                        strSql.Append(" values (");
                        strSql.Append("@role_name,@role_type,@is_sys)");
                        OleDbParameter[] parameters = {
                                new OleDbParameter("@role_name", OleDbType.VarChar,100),
                                new OleDbParameter("@role_type", OleDbType.Integer,4),
                                new OleDbParameter("@is_sys", OleDbType.Integer,4)};
                        parameters[0].Value = model.role_name;
                        parameters[1].Value = model.role_type;
                        parameters[2].Value = model.is_sys;
                        DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters);
                        //取得新插入的ID
                        model.id = GetMaxId(conn, trans);

                        StringBuilder strSql2;
                        if (model.manager_role_values != null)
                        {
                            foreach (Model.manager_role_value modelt in model.manager_role_values)
                            {
                                strSql2 = new StringBuilder();
                                strSql2.Append("insert into " + databaseprefix + "manager_role_value(");
                                strSql2.Append("role_id,nav_name,action_type)");
                                strSql2.Append(" values (");
                                strSql2.Append("@role_id,@nav_name,@action_type)");
                                OleDbParameter[] parameters2 = {
                                        new OleDbParameter("@role_id", OleDbType.Integer,4),
                                        new OleDbParameter("@nav_name", OleDbType.VarChar,100),
                                        new OleDbParameter("@action_type", OleDbType.VarChar,50)};
                                parameters2[0].Value = model.id;
                                parameters2[1].Value = modelt.nav_name;
                                parameters2[2].Value = modelt.action_type;
                                DbHelperOleDb.ExecuteSql(conn, trans, strSql2.ToString(), parameters2);
                            }
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        return -1;
                    }
                }
            }
            return model.id;
        }
示例#23
0
 void Generate(OleDbConnection connection, DateTime startDate, int daysCount) {
     using(var transaction = connection.BeginTransaction()) {
         for(int n = 0; n < daysCount; n++) {
             Console.Write("{0} of {1}\r", n + 1, daysCount);
             GenerateDay(connection, transaction, n, startDate.AddDays(n));
             RaiseProgress((double)n / (double)daysCount);
         }
         transaction.Commit();
     }
 }
示例#24
0
        protected void BtnSave_Click(object sender, EventArgs e)
        {
            string strObjid;
            if (DdlProject.SelectedValue == "0")
            {
                Misc.Message(this.GetType(), ClientScript, "����ʧ�ܣ���ѡ����Ŀ��");
                return;
            }
            if (TxtPackageNo.Text.Trim() == "")
            {
                Misc.Message(this.GetType(), ClientScript, " ����ʧ�ܣ������������ʱ��롣");
            }
            if (TxtPackageName.Text.Trim() == "")
            {
                Misc.Message(this.GetType(), ClientScript, " ����ʧ�ܣ�������������������");
            }

            using (OleDbConnection conn = new OleDbConnection(DBHelper.OleConnectionString))
            {
                if (conn.State != ConnectionState.Open) conn.Open();
                OleDbTransaction tr = conn.BeginTransaction();
                OleDbCommand cmd = new OleDbCommand("gen_part_package_api.new_", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Transaction = tr;
                cmd.Parameters.Clear();
                cmd.Parameters.Add("v_project_id", OleDbType.VarChar, 20).Value = DdlProject.SelectedValue;
                cmd.Parameters.Add("v_Package_no", OleDbType.VarChar, 20).Value = this.TxtPackageNo.Text;
                cmd.Parameters.Add("v_Package_name", OleDbType.VarChar, 500).Value = this.TxtPackageName.Text;
                cmd.Parameters.Add("v_objid", OleDbType.VarChar, 50).Direction = ParameterDirection.Output;
                try
                {
                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    strObjid = cmd.Parameters["v_objid"].Value.ToString();
                    GVBaleEditDataBind_(strObjid);

                }
                catch (Exception ex)
                {
                    tr.Rollback();
                    if (Misc.CheckIsDBCustomException(ex))
                    {
                        Misc.Message(this.GetType(), ClientScript, Misc.GetDBCustomException(ex));
                    }
                    else
                    {
                        throw;
                    }
                }
                finally
                {
                    conn.Close();
                }
            }
        }
示例#25
0
        /// <summary>執行 Transaction</summary>
        /// <param name="alSQL">欲執行交易的 ArrayList (內含 SQL 指令)</param>
        /// <param name="OleDbConn">OleDbConnection連線物件</param>
        /// <returns>Transaction是否成功</returns>
        /// <remarks></remarks>
        public static bool raiseOleDbTransaction(ArrayList alSQL, System.Data.OleDb.OleDbConnection OleDbConn)
        {
            if (alSQL == null)
            {
                return(true);
            }
            if (alSQL.Count == 0)
            {
                return(true);
            }
            if (OleDbConn == null)
            {
                OleDbConn = createOleDbConnection();
            }
            System.Data.OleDb.OleDbTransaction OleDbTrans = null;
            if (!(OleDbConn.State == ConnectionState.Open))
            {
                OleDbConn.Open();
            }
            System.Data.OleDb.OleDbCommand cmd = OleDbConn.CreateCommand();
            StringBuilder strSQL = new StringBuilder("");

            OleDbTrans = OleDbConn.BeginTransaction();
            try
            {
                cmd.Transaction = OleDbTrans;
                for (int i = 0; i <= alSQL.Count - 1; i++)
                {
                    if (!string.IsNullOrEmpty(alSQL[i].ToString()))
                    {
                        strSQL.AppendLine(alSQL[i].ToString());
                    }
                }
                cmd.CommandText = strSQL.ToString();
                cmd.ExecuteNonQuery();
                OleDbTrans.Commit();
                return(true);
            }
            catch (Exception Ex)
            {
                if ((OleDbTrans != null))
                {
                    OleDbTrans.Rollback();
                }
                //Message.alertMessage("C0002", null, Ex.Message.ToString(), null);
                return(false);
            }
            finally
            {
                if (!(OleDbConn.State == ConnectionState.Closed))
                {
                    OleDbConn.Close();
                }
            }
        }
示例#26
0
        protected void BtnSave_Click(object sender, EventArgs e)
        {
            if (!ReqQtyValidation()) return;

            TextBox txtBox_ = new TextBox();
            DropDownList ddl_lack_type_ = new DropDownList(); //ming.li ����ȱ����������
            double reqQty;
            using (OleDbConnection conn = new OleDbConnection(Lib.DBHelper.OleConnectionString))
            {
                if (conn.State != ConnectionState.Open) conn.Open();
                OleDbTransaction tr = conn.BeginTransaction();
                OleDbCommand cmd = new OleDbCommand("jp_demand_api.new_", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Transaction = tr;
                cmd.Parameters.Clear();
                cmd.Parameters.Add("v_demand_id", OleDbType.VarChar,20).Direction = ParameterDirection.Output;
                cmd.Parameters.Add("v_matr_seq_no", OleDbType.VarChar);
                cmd.Parameters.Add("v_matr_seq_line_no", OleDbType.Numeric);
                cmd.Parameters.Add("v_require_qty", OleDbType.Numeric);
                cmd.Parameters.Add("v_project_block", OleDbType.VarChar);
                cmd.Parameters.Add("v_project_system", OleDbType.VarChar).Value=TxtSystem.Text;
                cmd.Parameters.Add("v_work_content", OleDbType.VarChar).Value = TxtWorkContent.Text;
                cmd.Parameters.Add("v_place", OleDbType.VarChar).Value = DdlProdSite.SelectedValue;
                cmd.Parameters.Add("v_receiver", OleDbType.VarChar).Value = DdlReceiptPerson.SelectedItem.Text;
                cmd.Parameters.Add("v_receiver_ic", OleDbType.VarChar).Value = TxtIC.Text;
                cmd.Parameters.Add("v_receive_date", OleDbType.VarChar).Value = TxtDate.Text;
                cmd.Parameters.Add("v_receiver_contact", OleDbType.VarChar).Value = TxtContact.Text;
                cmd.Parameters.Add("v_receipt_dept", OleDbType.VarChar).Value = DdlReceiptDept.SelectedValue;
                cmd.Parameters.Add("v_crane", OleDbType.VarChar).Value = ChkDz.Checked == true ? "1" : "0";
                cmd.Parameters.Add("v_recorder", OleDbType.VarChar).Value = ((Authentication.LOGININFO)Session["USERINFO"]).UserID;
                cmd.Parameters.Add("v_req_group", OleDbType.VarChar).Value = ((Authentication.LOGININFO)Session["USERINFO"]).GroupID;
                cmd.Parameters.Add("v_lack_type", OleDbType.VarChar);
                foreach (GridViewRow gvr in GVRation.Rows)
                {
                    txtBox_ = (TextBox)(gvr.FindControl("TxtReqQty"));
                    if (txtBox_.Text == "" || txtBox_.Text == null) continue;
                    reqQty = Convert.ToDouble(txtBox_.Text);
                    ddl_lack_type_ = (DropDownList)(gvr.FindControl("DDL_QH")); //ming.li ����ȱ����������
                    if (reqQty > 0)
                    {
                        cmd.Parameters["v_matr_seq_no"].Value = gvr.Cells[1].Text;
                        cmd.Parameters["v_matr_seq_line_no"].Value = gvr.Cells[2].Text;
                        cmd.Parameters["v_require_qty"].Value = reqQty;
                        cmd.Parameters["v_project_block"].Value = gvr.Cells[13].Text;
                        cmd.Parameters["v_lack_type"].Value = ddl_lack_type_.SelectedValue;
                        cmd.ExecuteNonQuery();
                        ReqIDSessionHandler("ADD", cmd.Parameters["v_demand_id"].Value.ToString());//ming.li 20130327
                    }
                }
                tr.Commit();
            }
            GVRationDataBind();
            RecoverPageView();
            GVDataBind();
        }
示例#27
0
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Model.point_log model)
        {
            int newId;
            using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString))
            {
                conn.Open();
                using (OleDbTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        StringBuilder strSql = new StringBuilder();
                        strSql.Append("insert into dt_point_log(");
                        strSql.Append("user_id,user_name,[value],remark,add_time)");
                        strSql.Append(" values (");
                        strSql.Append("@user_id,@user_name,@value,@remark,@add_time)");
                        OleDbParameter[] parameters = {
					    new OleDbParameter("@user_id", OleDbType.Integer,4),
					    new OleDbParameter("@user_name", OleDbType.VarChar,100),
					    new OleDbParameter("@value", OleDbType.Integer,4),
					    new OleDbParameter("@remark", OleDbType.VarChar,500),
					    new OleDbParameter("@add_time", OleDbType.Date)};
                        parameters[0].Value = model.user_id;
                        parameters[1].Value = model.user_name;
                        parameters[2].Value = model.value;
                        parameters[3].Value = model.remark;
                        parameters[4].Value = model.add_time;

                        DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters);
                        //取得新插入的ID
                        newId = GetMaxId(conn, trans);

                        StringBuilder strSql2 = new StringBuilder();
                        strSql2.Append("update dt_users set point=point+" + model.value);
                        if (model.value > 0)
                        {
                            strSql2.Append(",exp=exp+" + model.value);
                        }
                        strSql2.Append(" where id=@id");
                        OleDbParameter[] parameters2 = {
                         new OleDbParameter("@id", OleDbType.Integer,4)};
                        parameters2[0].Value = model.user_id;

                        DbHelperOleDb.ExecuteSql(conn, trans, strSql2.ToString(), parameters2);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        return -1;
                    }
                }
            }
            return newId;
        }
示例#28
0
 /// <summary>
 /// 增加一条数据
 /// </summary>
 public int Add(Model.user_recharge model)
 {
     int newId;
     using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString))
     {
         conn.Open();
         using (OleDbTransaction trans = conn.BeginTransaction())
         {
             try
             {
                 StringBuilder strSql=new StringBuilder();
                 strSql.Append("insert into " + databaseprefix + "user_recharge(");
                 strSql.Append("user_id,user_name,recharge_no,payment_id,amount,status,add_time,complete_time)");
                 strSql.Append(" values (");
                 strSql.Append("@user_id,@user_name,@recharge_no,@payment_id,@amount,@status,@add_time,@complete_time)");
                 OleDbParameter[] parameters = {
                         new OleDbParameter("@user_id", OleDbType.Integer,4),
                         new OleDbParameter("@user_name", OleDbType.VarChar,100),
                         new OleDbParameter("@recharge_no", OleDbType.VarChar,100),
                         new OleDbParameter("@payment_id", OleDbType.Integer,4),
                         new OleDbParameter("@amount", OleDbType.Decimal,5),
                         new OleDbParameter("@status", OleDbType.Integer,4),
                         new OleDbParameter("@add_time", OleDbType.Date),
                         new OleDbParameter("@complete_time", OleDbType.Date)};
                 parameters[0].Value = model.user_id;
                 parameters[1].Value = model.user_name;
                 parameters[2].Value = model.recharge_no;
                 parameters[3].Value = model.payment_id;
                 parameters[4].Value = model.amount;
                 parameters[5].Value = model.status;
                 parameters[6].Value = model.add_time;
                 if (model.complete_time != null)
                 {
                     parameters[7].Value = model.complete_time;
                 }
                 else
                 {
                     parameters[7].Value = DBNull.Value;
                 }
                 DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters);
                 //取得新插入的ID
                 newId = GetMaxId(conn, trans);
                 trans.Commit();
             }
             catch
             {
                 trans.Rollback();
                 return -1;
             }
         }
     }
     return newId;
 }
示例#29
0
 /// <summary>
 /// 增加一条数据
 /// </summary>
 public int Add(Model.user_message model)
 {
     int newId;
     using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString))
     {
         conn.Open();
         using (OleDbTransaction trans = conn.BeginTransaction())
         {
             try
             {
                 StringBuilder strSql = new StringBuilder();
                 strSql.Append("insert into " + databaseprefix + "user_message(");
                 strSql.Append("[type],post_user_name,accept_user_name,is_read,title,content,post_time,read_time)");
                 strSql.Append(" values (");
                 strSql.Append("@type,@post_user_name,@accept_user_name,@is_read,@title,@content,@post_time,@read_time)");
                 OleDbParameter[] parameters = {
                         new OleDbParameter("@type", OleDbType.Integer,4),
                         new OleDbParameter("@post_user_name", OleDbType.VarChar,100),
                         new OleDbParameter("@accept_user_name", OleDbType.VarChar,100),
                         new OleDbParameter("@is_read", OleDbType.Integer,4),
                         new OleDbParameter("@title", OleDbType.VarChar,100),
                         new OleDbParameter("@content", OleDbType.VarChar),
                         new OleDbParameter("@post_time", OleDbType.Date),
                         new OleDbParameter("@read_time", OleDbType.Date)};
                 parameters[0].Value = model.type;
                 parameters[1].Value = model.post_user_name;
                 parameters[2].Value = model.accept_user_name;
                 parameters[3].Value = model.is_read;
                 parameters[4].Value = model.title;
                 parameters[5].Value = model.content;
                 parameters[6].Value = model.post_time;
                 if (model.read_time != null)
                 {
                     parameters[7].Value = model.read_time;
                 }
                 else
                 {
                     parameters[7].Value = DBNull.Value;
                 }
                 DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters);
                 //取得新插入的ID
                 newId = GetMaxId(conn, trans);
                 trans.Commit();
             }
             catch
             {
                 trans.Rollback();
                 return -1;
             }
         }
     }
     return newId;
 }
示例#30
0
 public DbTransaction GetTransction()
 {
     //throw new NotImplementedException();
     if (_connection != null)
     {
         if (_connection.State == ConnectionState.Closed)
         {
             _connection.Open();
         }
         return((DbTransaction)_connection.BeginTransaction());
     }
     return(null);
 }
示例#31
0
        public override int  Create()
        {
            Validate();
            System.Data.OleDb.OleDbConnection con = Db.Connection;

            OleDbTransaction trans = con.BeginTransaction();
            OleDbCommand     cmd   = con.CreateCommand();

            cmd.Transaction = trans;
            try
            {
                cmd.CommandText = @"insert into users (Login, Password, FirstName, LastName, Email, Role) values (?,?,?,?,?,?)";

                cmd.Parameters.Add(new OleDbParameter("Login", m_Login));
                cmd.Parameters.Add(new OleDbParameter("Password", m_Password));
                cmd.Parameters.Add(new OleDbParameter("FirstName", m_FirstName));
                cmd.Parameters.Add(new OleDbParameter("LastName", m_LastName));
                cmd.Parameters.Add(new OleDbParameter("Email", m_Email));
                cmd.Parameters.Add(new OleDbParameter("Role", m_Role));
                cmd.ExecuteNonQuery();
                cmd.CommandText = "select @@identity";
                Decimal oid = (Decimal)cmd.ExecuteScalar();
                m_Id = Convert.ToInt32(oid);


                if (m_Groups != null)
                {
                    cmd.CommandText = @"insert into usergroups (UserId, GroupId) values (?,?)";
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add(new OleDbParameter("UserId", typeof(int)));
                    cmd.Parameters.Add(new OleDbParameter("GroupId", typeof(int)));


                    //cmd.Prepare();
                    foreach (int groupId in m_Groups)
                    {
                        cmd.Parameters["UserId"].Value  = m_Id;
                        cmd.Parameters["GroupId"].Value = groupId;
                        cmd.ExecuteNonQuery();
                    }
                }

                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
            return(m_Id);
        }
        protected void ButtSave_Click(object sender, EventArgs e)
        {
            string v_out;

            using (OleDbConnection conn = new OleDbConnection(DBHelper.OleConnectionString))
            {
                if (conn.State != ConnectionState.Open) conn.Open();
                OleDbCommand cmd = new OleDbCommand("gen_part_package_item_api.Modify_", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                OleDbTransaction tr = conn.BeginTransaction();
                cmd.Transaction = tr;

                cmd.Parameters.Clear();
                cmd.Parameters.Add("v_objid", OleDbType.VarChar, 20).Value = HiddenObjId.Value;
                cmd.Parameters.Add("v_rowversion", OleDbType.VarChar, 20).Value = HiddenRowversion.Value;
                cmd.Parameters.Add("v_Part_name", OleDbType.VarChar, 500).Value = TxtPartName.Text;
                cmd.Parameters.Add("v_Part_name_e", OleDbType.VarChar, 500).Value = TxtPartNameE.Text;
                cmd.Parameters.Add("v_Part_spec", OleDbType.VarChar, 100).Value = TxtPartSpec.Text;
                cmd.Parameters.Add("v_UNIT", OleDbType.VarChar, 20).Value = DdlUnit.SelectedValue =="0"?"":DdlUnit.SelectedValue;
                cmd.Parameters.Add("v_Dec_no", OleDbType.VarChar, 100).Value = TxtDecNo.Text;
                cmd.Parameters.Add("v_Contract_no", OleDbType.VarChar, 100).Value = TxtContractNo.Text;
                cmd.Parameters.Add("v_po_no", OleDbType.VarChar).Value = TxtPO.Text;
                cmd.Parameters.Add("v_pay_flag", OleDbType.VarChar).Value = ChkPayFlag.Checked ? "1" : "0";

                try
                {
                    cmd.ExecuteNonQuery();
                    tr.Commit();
                    Misc.RegisterClientScript(this.GetType(),"part_refresh",ClientScript,
                        "<script type='text/javascript'>alert('���ݸ��³ɹ���');window.dialogArguments.refresh();window.close();</script>");
                }
                catch (Exception ex)
                {
                    tr.Rollback();
                    if (Misc.CheckIsDBCustomException(ex))
                    {
                        Misc.Message(this.GetType(), ClientScript, Misc.GetDBCustomException(ex));
                    }
                    else
                    {
                        throw;
                    }
                    //Misc.Message(this.GetType(),ClientScript,string.Format("���ݸ���ʧ�ܣ�{0}",ex.Message));
                    //return;
                }
                finally
                {
                    conn.Close();
                }
            }
        }
示例#33
0
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Model.manager model)
        {
            int newId;
            using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString))
            {
                conn.Open();
                using (OleDbTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        StringBuilder strSql = new StringBuilder();
                        strSql.Append("insert into " + databaseprefix + "manager(");
                        strSql.Append("role_id,role_type,user_name,[password],salt,real_name,telephone,email,is_lock,add_time)");
                        strSql.Append(" values (");
                        strSql.Append("@role_id,@role_type,@user_name,@password,@salt,@real_name,@telephone,@email,@is_lock,@add_time)");
                        OleDbParameter[] parameters = {
                                new OleDbParameter("@role_id", OleDbType.Integer,4),
                                new OleDbParameter("@role_type", OleDbType.Integer,4),
                                new OleDbParameter("@user_name", OleDbType.VarChar,100),
                                new OleDbParameter("@password", OleDbType.VarChar,100),
                                new OleDbParameter("@salt", OleDbType.VarChar,20),
                                new OleDbParameter("@real_name", OleDbType.VarChar,50),
                                new OleDbParameter("@telephone", OleDbType.VarChar,30),
                                new OleDbParameter("@email", OleDbType.VarChar,30),
                                new OleDbParameter("@is_lock", OleDbType.Integer,4),
                                new OleDbParameter("@add_time", OleDbType.Date)};
                        parameters[0].Value = model.role_id;
                        parameters[1].Value = model.role_type;
                        parameters[2].Value = model.user_name;
                        parameters[3].Value = model.password;
                        parameters[4].Value = model.salt;
                        parameters[5].Value = model.real_name;
                        parameters[6].Value = model.telephone;
                        parameters[7].Value = model.email;
                        parameters[8].Value = model.is_lock;
                        parameters[9].Value = model.add_time;

                        DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters);
                        //取得新插入的ID
                        newId = GetMaxId(conn, trans);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        return -1;
                    }
                }
            }
            return newId;
        }
示例#34
0
        public override void Update()
        {
            Validate();


            System.Data.OleDb.OleDbConnection con = Db.Connection;

            OleDbTransaction trans = con.BeginTransaction();
            OleDbCommand     cmd   = con.CreateCommand();

            cmd.Transaction = trans;
            try
            {
                cmd.CommandText = @"update users set Login=?, Password=?, FirstName=?, LastName=?, Email=?, Role=? where id=" + m_Id.ToString();
                cmd.Parameters.Add(new OleDbParameter("Login", m_Login));
                cmd.Parameters.Add(new OleDbParameter("Password", m_Password));
                cmd.Parameters.Add(new OleDbParameter("FirstName", m_FirstName));
                cmd.Parameters.Add(new OleDbParameter("LastName", m_LastName));
                cmd.Parameters.Add(new OleDbParameter("Email", m_Email));
                cmd.Parameters.Add(new OleDbParameter("Role", m_Role));
                cmd.ExecuteNonQuery();

                cmd.CommandText = @"delete from usergroups where userid=" + m_Id.ToString();
                cmd.ExecuteNonQuery();


                if (m_Groups != null)
                {
                    cmd.CommandText = @"insert into usergroups (UserId, GroupId) values (?,?)";
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add(new OleDbParameter("UserId", typeof(int)));
                    cmd.Parameters.Add(new OleDbParameter("GroupId", typeof(int)));


                    //cmd.Prepare();
                    foreach (int groupId in m_Groups)
                    {
                        cmd.Parameters["UserId"].Value  = m_Id;
                        cmd.Parameters["GroupId"].Value = groupId;
                        cmd.ExecuteNonQuery();
                    }
                }

                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
        }
示例#35
0
        public bool Execute(DbUser alteredUser)
        {
            // update user details
            GetStreamID id = new GetStreamID();
            string stream_id = id.Read(alteredUser.Stream);

            GetTStatusID id2 = new GetTStatusID();
            string tstatus_id = id2.Read(alteredUser.TStatus);

            ConnectionString cnString = new ConnectionString();
            IDbConnection cn = new OleDbConnection(cnString.GetConnString());

            IDbCommand cmd = new OleDbCommand("sp_update_profile", (OleDbConnection)cn);
            cmd.CommandType = CommandType.StoredProcedure;

            try
            {
                cn.Open();
                IDbTransaction tran = cn.BeginTransaction();
                cmd.Transaction = tran;

                // Add job
                cmd.Parameters.Add(new OleDbParameter("@userLocation", alteredUser.Location));
                cmd.Parameters.Add(new OleDbParameter("@userID", alteredUser.UserId));
                cmd.Parameters.Add(new OleDbParameter("@userStream", stream_id));
                cmd.Parameters.Add(new OleDbParameter("@tstatID", tstatus_id));
                cmd.Parameters.Add(new OleDbParameter("@userDegree", alteredUser.Degree));
                cmd.Parameters.Add(new OleDbParameter("@userModules", alteredUser.Modules));

                int affectedRows = cmd.ExecuteNonQuery();

                if (affectedRows > 0)
                {
                    tran.Commit();
                    return true;
                }
                else
                {
                    tran.Rollback();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                cn.Close();
            }
            return false;
        }
示例#36
0
 /// <summary>
 /// opens a new connection then starts transaction on that connection
 /// </summary>
 public bool BeginNewTransaction()
 {
     try
     {
         _conn = getNewConnection();
         _tran = _conn.BeginTransaction();
         return true;
     }
     catch (Exception e)
     {
         Debug.WriteLine(e.Message);
         return false;
     }
 }
示例#37
0
		public void run()
		{

			Exception exp = null;

			OleDbConnection con = new OleDbConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
			con.Open();

			/*********************************************************
			 * OLEDB Provider for SQL Server does not allow nested transactions
			 * http://support.microsoft.com/kb/177138/EN-US/
			 * http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q316872&
			*/
			if ((ConnectedDataProvider.GetDbType(con) == DataBaseServer.SQLServer) ||
				(ConnectedDataProvider.GetDbType(con) == DataBaseServer.Oracle) ||
				(ConnectedDataProvider.GetDbType(con) == DataBaseServer.PostgreSQL) ||
				(ConnectedDataProvider.GetDbType(con) == DataBaseServer.DB2))
			{
				Log(string.Format("Test skipped, nested transactions are not supported in {0}", ConnectedDataProvider.GetDbType(con)));
				return;
			}

			// How To Implement Nested Transactions with Oracle
			// http://support.microsoft.com/kb/187289/EN-US/

			OleDbTransaction txnOuter = null;
			OleDbTransaction txnInner = null;
		
			try
			{
				BeginCase("Check Outer Transaction Isoloation Level");
			
				txnOuter = con.BeginTransaction();
				txnInner = txnOuter.Begin();

				Compare(txnOuter.IsolationLevel,IsolationLevel.ReadCommitted);
			} 
			catch(Exception ex){exp = ex;}
			finally{EndCase(exp); exp = null;}

			try
			{
				BeginCase("Check Inner Transaction Isoloation Level");
				Compare(txnOuter.IsolationLevel,IsolationLevel.RepeatableRead);
			} 
			catch(Exception ex){exp = ex;}
			finally{EndCase(exp); exp = null;}

			if (con.State == ConnectionState.Open) con.Close();
		}
示例#38
0
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Model.payment model)
        {
            int newId;
            using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString))
            {
                conn.Open();
                using (OleDbTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        StringBuilder strSql = new StringBuilder();
                        strSql.Append("insert into dt_payment(");
                        strSql.Append("title,img_url,remark,[type],poundage_type,poundage_amount,sort_id,is_lock,api_path)");
                        strSql.Append(" values (");
                        strSql.Append("@title,@img_url,@remark,@type,@poundage_type,@poundage_amount,@sort_id,@is_lock,@api_path)");
                        OleDbParameter[] parameters = {
					            new OleDbParameter("@title", OleDbType.VarChar,100),
					            new OleDbParameter("@img_url", OleDbType.VarChar,255),
					            new OleDbParameter("@remark", OleDbType.VarChar,500),
					            new OleDbParameter("@type", OleDbType.TinyInt,1),
					            new OleDbParameter("@poundage_type", OleDbType.TinyInt,1),
					            new OleDbParameter("@poundage_amount", OleDbType.Decimal,5),
					            new OleDbParameter("@sort_id", OleDbType.Integer,4),
					            new OleDbParameter("@is_lock", OleDbType.TinyInt,1),
					            new OleDbParameter("@api_path", OleDbType.VarChar,100)};
                        parameters[0].Value = model.title;
                        parameters[1].Value = model.img_url;
                        parameters[2].Value = model.remark;
                        parameters[3].Value = model.type;
                        parameters[4].Value = model.poundage_type;
                        parameters[5].Value = model.poundage_amount;
                        parameters[6].Value = model.sort_id;
                        parameters[7].Value = model.is_lock;
                        parameters[8].Value = model.api_path;

                        DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters);
                        //取得新插入的ID
                        newId = GetMaxId(conn, trans);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        return -1;
                    }
                }
            }
            return newId;
        }
示例#39
0
        public bool GetCommand(int recommender_id, int recommended_id, int job_id, string reason)
        {
            if (DetermineIfExists(recommender_id, recommended_id, job_id))
            {
                return true;
            }
            else
            {
                ConnectionString cnString = new ConnectionString();
                IDbConnection cn = new OleDbConnection(cnString.GetConnString());

                IDbCommand cmd = new OleDbCommand("sp_add_recommendation", (OleDbConnection)cn);
                cmd.CommandType = CommandType.StoredProcedure;

                try
                {
                    cn.Open();
                    IDbTransaction tran = cn.BeginTransaction();
                    cmd.Transaction = tran;

                    cmd.Parameters.Add(new OleDbParameter("@userId", recommender_id));
                    cmd.Parameters.Add(new OleDbParameter("@user_Id", recommended_id));
                    cmd.Parameters.Add(new OleDbParameter("@jobId", job_id));
                    cmd.Parameters.Add(new OleDbParameter("@reason", reason));

                    int affectedRows = cmd.ExecuteNonQuery();

                    if (affectedRows > 0)
                    {
                        tran.Commit();
                        return true;
                    }
                    else
                    {
                        tran.Rollback();
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                    return false;
                }
                finally
                {
                    cn.Close();
                }
                return true;
            }
        }
        public int CreateCustomer(Customer newCustomer)
        {
            int result = -1;
            try
            {
                Conn = db.openConnAccess();
                tr = Conn.BeginTransaction();

                sb = new StringBuilder();

                sb.Remove(0, sb.Length);
                sb.Append("INSERT INTO tbCustomer(CCode,CName,Address,PRVID,Phone,Discount)");
                sb.Append(" VALUES (@CCode,@CName,@Address,@PRVID,@Phone,@Discount)");

                string sqlSave;
                sqlSave = sb.ToString();

                com = new OleDbCommand();
                com.Connection = Conn;
                com.CommandText = sqlSave;
                com.Transaction = tr;
                com.Parameters.Clear();
                com.Parameters.Add("@CCode", OleDbType.VarChar).Value = newCustomer.CCode;
                com.Parameters.Add("@CName", OleDbType.VarChar).Value = newCustomer.CName;
                com.Parameters.Add("@Address", OleDbType.VarChar).Value = newCustomer.Address;
                com.Parameters.Add("@PRVID", OleDbType.VarChar).Value = newCustomer.PRVID;
                com.Parameters.Add("@Phone", OleDbType.VarChar).Value = newCustomer.Phone;
                com.Parameters.Add("@Discount", OleDbType.VarChar).Value = newCustomer.Discount;
                com.ExecuteNonQuery();
                tr.Commit();

                result = 1;

            }
            catch (Exception ex)
            {
                tr.Rollback();
                Conn.Close();
                return result;
                throw ex;

            }
            finally
            {
                Conn.Close();
            }

            return result;
        }
示例#41
0
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Model.user_groups model)
        {
            int newId;
            using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString))
            {
                conn.Open();
                using (OleDbTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        StringBuilder strSql = new StringBuilder();
                        strSql.Append("insert into dt_user_groups(");
                        strSql.Append("title,grade,upgrade_exp,amount,point,discount,is_default,is_upgrade,is_lock)");
                        strSql.Append(" values (");
                        strSql.Append("@title,@grade,@upgrade_exp,@amount,@point,@discount,@is_default,@is_upgrade,@is_lock)");
                        OleDbParameter[] parameters = {
					            new OleDbParameter("@title", OleDbType.VarChar,100),
					            new OleDbParameter("@grade", OleDbType.Integer,4),
					            new OleDbParameter("@upgrade_exp", OleDbType.Integer,4),
					            new OleDbParameter("@amount", OleDbType.Decimal,5),
					            new OleDbParameter("@point", OleDbType.Integer,4),
					            new OleDbParameter("@discount", OleDbType.Integer,4),
					            new OleDbParameter("@is_default", OleDbType.TinyInt,1),
					            new OleDbParameter("@is_upgrade", OleDbType.TinyInt,1),
					            new OleDbParameter("@is_lock", OleDbType.TinyInt,1)};
                        parameters[0].Value = model.title;
                        parameters[1].Value = model.grade;
                        parameters[2].Value = model.upgrade_exp;
                        parameters[3].Value = model.amount;
                        parameters[4].Value = model.point;
                        parameters[5].Value = model.discount;
                        parameters[6].Value = model.is_default;
                        parameters[7].Value = model.is_upgrade;
                        parameters[8].Value = model.is_lock;

                        DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters);
                        //取得新插入的ID
                        newId = GetMaxId(conn, trans);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        return -1;
                    }
                }
            }
            return newId;
        }
        public int CreateSupiler(Supiler newSupiler)
        {
            int result = -1;
            try
            {
                Conn = db.openConnAccess();
                tr = Conn.BeginTransaction();

                sb = new StringBuilder();

                sb.Remove(0, sb.Length);
                sb.Append("INSERT INTO tbSupiler(SPCode,SPName,Address,PersonContact,Phone,Fax)");
                sb.Append(" VALUES (@SPCode,@SPName,@Address,@PersonContact,@Phone,@Fax)");

                string sqlSave;
                sqlSave = sb.ToString();

                com = new OleDbCommand();
                com.Connection = Conn;
                com.CommandText = sqlSave;
                com.Transaction = tr;
                com.Parameters.Clear();
                com.Parameters.Add("@SPCode", OleDbType.VarChar).Value = newSupiler.SPCode;
                com.Parameters.Add("@SPName", OleDbType.VarChar).Value = newSupiler.SPName;
                com.Parameters.Add("@Address", OleDbType.VarChar).Value = newSupiler.Address;
                com.Parameters.Add("@PersonContact", OleDbType.VarChar).Value = newSupiler.PersonContact;
                com.Parameters.Add("@Phone", OleDbType.VarChar).Value = newSupiler.Phone;
                com.Parameters.Add("@Fax", OleDbType.VarChar).Value = newSupiler.Fax;
                com.ExecuteNonQuery();
                tr.Commit();

                result = 1;

            }
            catch (Exception ex)
            {
                tr.Rollback();
                Conn.Close();
                return result;
                throw ex;

            }
            finally
            {
                Conn.Close();
            }

            return result;
        }
示例#43
0
        public TTopic createTopic(TUser tUserStarter, TCourse tCourse, string title)
        {
            //--Data Base Access Variables--
            System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
            System.Data.OleDb.OleDbCommand    dbCommand    = new OleDbCommand();
            dbCommand.Connection = dbConnection;
            System.Data.OleDb.OleDbDataReader  dbDataReader;
            System.Data.OleDb.OleDbTransaction dbTransaction;
            //-----------------------------

            dbConnection.Close();
            dbConnection.Open();
            dbTransaction         = dbConnection.BeginTransaction();
            dbCommand.Transaction = dbTransaction;
            TTopic tTopic = new TTopic(tCourse, title);

            try
            {
                //Begin Transaction
                tTopic.startDateTime  = DateTime.Now;
                tTopic.starterTeacher = tUserStarter;
                dbCommand.CommandText = "INSERT INTO Topics (course_id, group_id, title, startDateTime, starterTeacher) VALUES('" + tTopic.tCourse.id + "', '" + tTopic.tCourse.groupId + "', '" + tTopic.title + "', '" + tTopic.startDateTime.ToString() + "', '" + tUserStarter.id + "')";
                dbCommand.ExecuteNonQuery();
                dbCommand.CommandText = "SELECT * FROM Topics WHERE (course_id = '" + tTopic.tCourse.id + "') AND (group_id = '" + tTopic.tCourse.groupId + "') AND (title = '" + tTopic.title + "') AND (startDateTime = '" + tTopic.startDateTime.ToString() + "')";
                dbDataReader          = dbCommand.ExecuteReader();
                dbDataReader.Read();
                tTopic.id = Convert.ToInt32(dbDataReader["id"]);
                dbDataReader.Close();
                dbTransaction.Commit();
                //End Transaction
            }
            catch
            {
                dbTransaction.Rollback();
                dbCommand.Transaction = null;
                dbTransaction         = null;
                dbConnection.Close();
                return(null);
            }

            dbCommand.Transaction = null;
            dbTransaction         = null;
            dbDataReader.Close();
            dbConnection.Close();
            return(tTopic);
        }
示例#44
0
        public void enterTopic(TUser tUser, TTopic tTopic)
        {
            //--Data Base Access Variables--
            System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
            System.Data.OleDb.OleDbCommand    dbCommand    = new OleDbCommand();
            dbCommand.Connection = dbConnection;
            System.Data.OleDb.OleDbTransaction dbTransaction;
            System.Data.OleDb.OleDbDataReader  dbDataReader;
            //-----------------------------

            dbConnection.Close();
            dbConnection.Open();
            dbTransaction         = dbConnection.BeginTransaction();
            dbCommand.Transaction = dbTransaction;
            try
            {
                //Begin Transaction
                //Verify if the user is already on-line
                dbCommand.CommandText = "SELECT * FROM User_Topic WHERE user_id = '" + tUser.id + "' AND finishDateTime IS NULL";
                dbDataReader          = dbCommand.ExecuteReader();
                if (dbDataReader.HasRows)
                {
                    dbDataReader.Close();
                    dbCommand.CommandText = "UPDATE User_Topic SET finishDateTime = '" + DateTime.Now.ToString() + "' WHERE user_id = '" + tUser.id + "' AND finishDateTime IS NULL";
                    dbCommand.ExecuteNonQuery();
                }
                dbDataReader.Close();
                dbCommand.CommandText = "INSERT INTO User_Topic (user_id, topic_id, startDateTime) VALUES ('" + tUser.id + "', '" + tTopic.id + "', '" + DateTime.Now.ToString() + "')";
                dbCommand.ExecuteNonQuery();
                tUser.topic = tTopic;
                dbTransaction.Commit();
                //End Transaction
            }
            catch
            {
                dbTransaction.Rollback();
                dbCommand.Transaction = null;
                dbTransaction         = null;
                dbConnection.Close();
            }

            dbCommand.Transaction = null;
            dbTransaction         = null;
            dbConnection.Close();
        }
示例#45
0
        public void setPositionOfNewContentItem(string contentItemGroup)
        {
            //--Data Base Access Variables--
            System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
            System.Data.OleDb.OleDbCommand    dbCommand    = new OleDbCommand();
            dbCommand.Connection = dbConnection;
            System.Data.OleDb.OleDbTransaction dbTransaction;
            System.Data.OleDb.OleDbDataReader  dbDataReader;
            //-----------------------------

            dbConnection.Close();
            dbConnection.Open();
            dbTransaction         = dbConnection.BeginTransaction();
            dbCommand.Transaction = dbTransaction;
            try
            {
                //Begin Transaction
                //Verify if the user is already on-line
                dbCommand.CommandText = "SELECT MAX(id) FROM ContentItems WHERE item_group = '" + contentItemGroup + "'";
                dbDataReader          = dbCommand.ExecuteReader();
                if (dbDataReader.HasRows)
                {
                    dbDataReader.Read();
                    Int64 maxId = dbDataReader.GetInt32(0); //MAX id is the new item
                    dbDataReader.Close();
                    dbCommand.CommandText = "UPDATE ContentItems SET [position] = " + maxId.ToString() + " WHERE (id = " + maxId.ToString() + ") AND (item_group = 'A')";
                    dbCommand.ExecuteNonQuery();
                }
                dbTransaction.Commit();
                //End Transaction
            }
            catch
            {
                dbTransaction.Rollback();
                dbCommand.Transaction = null;
                dbTransaction         = null;
                dbConnection.Close();
            }

            dbCommand.Transaction = null;
            dbTransaction         = null;
            dbConnection.Close();
        }
示例#46
0
        public override void Delete()
        {
            System.Data.OleDb.OleDbConnection con = Db.Connection;
            OleDbTransaction trans = con.BeginTransaction();
            OleDbCommand     cmd   = con.CreateCommand();

            cmd.Transaction = trans;
            try
            {
                cmd.CommandText = @"delete from  usergroups where userid = " + m_Id.ToString();
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"delete from  users where id = " + m_Id.ToString();
                cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
        }
示例#47
0
        public void  DbDelete()
        {
            if (m_Id < 1)
            {
                throw new BipFatalException("Internal Error");
            }

            System.Data.OleDb.OleDbConnection con = Db.Connection;
            OleDbTransaction trans = con.BeginTransaction();
            OleDbCommand     cmd   = con.CreateCommand();

            cmd.Transaction = trans;
            try
            {
                cmd.CommandText = @"delete from DocGroups where DocId = " + m_Id.ToString();
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"delete from DocRefRelated where DocId = " + m_Id.ToString();
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"delete from UserFavoriteDocs where DocId = " + m_Id.ToString();
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"delete from UserReadDocs where DocId = " + m_Id.ToString();
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"update documents set ParentId = null where ParentId =" + m_Id.ToString();
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"update documents set PreviousVersionId = null where PreviousVersionId =" + m_Id.ToString();
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"delete from Documents where Id = " + m_Id.ToString();
                cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
        }
示例#48
0
        public int  DbCreate(out OleDbTransaction trans)
        {
            Validate();
            System.Data.OleDb.OleDbConnection con = Db.Connection;

            trans = con.BeginTransaction();
            OleDbCommand cmd = con.CreateCommand();

            cmd.Transaction = trans;
            try
            {
                CmdParams cp = new CmdParams(cmd);
                m_CreationTime  = DateTime.Now;
                cmd.CommandText = @"
				insert into documents
				(
					CreationTime,
					FileType, 
					[FileName],
					DateReceived, 
					DocumentDate, 
					IncomingNumber, 
					OutgoingNumber,
					Subject, 
					Header, 
					ArchiveFileNames, 
					DocTypeId, 
					DocSourceId, 
					DocCategoryId, 
					ParentId, 
					PreviousVersionId, 
					OwnerUserId, 
					IsPublic
				)values ( "                 +
                                  cp.Add(m_CreationTime) +
                                  cp.Add(m_FileTypeId) +
                                  cp.Add(m_FileName) +
                                  cp.Add(m_DateReceived) +
                                  cp.Add(m_DocumentDate) +
                                  cp.Add(m_IncomingNumber) +
                                  cp.Add(m_OutgoingNumber) +
                                  cp.Add(m_Subject) +
                                  cp.Add(m_Header) +
                                  cp.Add(m_ArchiveFileNames) +
                                  cp.Add(m_DocTypeId) +
                                  cp.Add(m_DocSourceId) +
                                  cp.Add(m_DocCategoryId) +
                                  cp.Add(m_ParentId) +
                                  cp.Add(m_PreviousVersionId) +
                                  cp.Add(m_OwnerUserId) +
                                  cp.Add(m_IsPublic) + " ) ";


                cmd.ExecuteNonQuery();
                cmd.CommandText = "select @@identity";
                Decimal oid = (Decimal)cmd.ExecuteScalar();
                m_Id = Convert.ToInt32(oid);

                StoreDocRefs(trans, "DocGroups", "GroupId", Groups);
                StoreDocRefs(trans, "DocRefRelated", "RelatedDocId", RefDocuments);
                cmd.Parameters.Clear();
                cmd.CommandText = "update Documents set StorageFileName = '" + StorageFileName + "' where Id = " + m_Id.ToString();
                cmd.ExecuteNonQuery();
                //trans.Commit();

                //.DEV. isRead
            }
            catch (Exception ex)
            {
                trans.Rollback();
                trans = null;
                throw ex;
            }

            MarkAsRead();

            return(m_Id);
        }
示例#49
0
        public static bool ImportReportsFromDB(string fileName)
        {
            if (File.Exists(fileName))// Open the input file for input
            {
                System.Data.OleDb.OleDbConnection connImport      = new System.Data.OleDb.OleDbConnection(VWA4Common.VWACommon.GetConnectionString(fileName));
                System.Data.OleDb.OleDbConnection connTransaction = new System.Data.OleDb.OleDbConnection(VWA4Common.AppContext.WasteConnectionString);
                connTransaction.Open();
                System.Data.OleDb.OleDbTransaction transaction = connTransaction.BeginTransaction();

                try
                {
                    Hashtable OldToNewReports = new Hashtable();
                    // import all reports
                    DataTable dtReports = VWA4Common.DB.Retrieve("SELECT * FROM ReportMemorized", connImport, null);
                    if (dtReports.Rows.Count > 0)
                    {
                        foreach (DataRow rowReport in dtReports.Rows)
                        {
                            string date_time   = VWA4Common.VWACommon.DateToString(DateTime.Now);
                            string oldReportID = rowReport["ID"].ToString();
                            string title       = rowReport["Title"].ToString();

                            if (VWA4Common.DB.Retrieve("SELECT * FROM ReportMemorized WHERE Title = '" + title + "'").Rows.Count > 0)
                            {
                                title = "Imported " + date_time + " " + title;
                            }
                            string newReportID = VWA4Common.DB.Insert("INSERT INTO ReportMemorized(Title, ReportType, ConfigXML, CreatedDate, ModifiedDate) " +
                                                                      " VALUES('" + title + "','" + rowReport["ReportType"].ToString() + "','" + rowReport["ConfigXML"].ToString() + "', #" +
                                                                      date_time + "#, #" + date_time + "#)", connTransaction, transaction).ToString();

                            OldToNewReports[oldReportID] = newReportID;
                            //System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
                            //cmd.CommandText = "INSERT INTO ReportMemorized (ConfigXML, ReportType, Title, CreatedDate, ModifiedDate) " +
                            //        "VALUES(@ConfigXML, @ReportType, @Title, @CreatedDate, @ModifiedDate)";

                            //cmd.Parameters.Add("@ConfigXML", OleDbType.Binary);
                            //cmd.Parameters.Add("@ReportType", OleDbType.VarChar, 50, "ReportType");
                            //cmd.Parameters.Add("@Title", OleDbType.VarChar, 255, "Title");

                            //cmd.Parameters.Add("@CreatedDate", OleDbType.Date, 50, "CreatedDate");
                            //cmd.Parameters.Add("@ModifiedDate", OleDbType.Date, 50, "ModifiedDate");

                            //cmd.Parameters["@ConfigXML"].Value = System.Text.Encoding.UTF8.GetBytes(arr);
                            //cmd.Parameters["@ReportType"].Value = reportType;
                            //cmd.Parameters["@Title"].Value = name;

                            //cmd.Parameters["@CreatedDate"].Value = DateTime.Now;
                            //cmd.Parameters["@ModifiedDate"].Value = DateTime.Now;

                            //cmd.Connection = conn;
                            //if (cmd.ExecuteNonQuery() <= 0)
                            //    MessageBox.Show(null, "Error saving report - report was not saved", "Error saving report", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            //if (isNew)
                            //    cmd.CommandText = "SELECT @@Identity";
                            //else
                            //    cmd.CommandText = "SELECT ID FROM ReportMemorized WHERE ReportMemorized.Title = '" + name + "'";
                            //id = (int)cmd.ExecuteScalar();

                            // import all report parameters
                            DataTable dtReportParameters = VWA4Common.DB.Retrieve("SELECT * FROM ReportParam WHERE ReportMemorized = " + oldReportID, connImport, null);
                            if (dtReportParameters.Rows.Count > 0)
                            {
                                foreach (DataRow rowReportParam in dtReportParameters.Rows)
                                {
                                    VWA4Common.DB.Insert("INSERT INTO ReportParam(ParamName, ParamValue, ParamDisplayValue, ParamType, " +
                                                         "ParamValueType, AssignType, GlobalName, ReportMemorized) " +
                                                         " VALUES('" + rowReportParam["ParamName"].ToString().Replace("'", "''") + "', '" +
                                                         rowReportParam["ParamValue"].ToString().Replace("'", "''") + "', '" +
                                                         rowReportParam["ParamDisplayValue"].ToString().Replace("'", "''") + "', '" +
                                                         rowReportParam["ParamType"].ToString().Replace("'", "''") + "', '" +
                                                         rowReportParam["ParamValueType"].ToString().Replace("'", "''") + "', '" +
                                                         rowReportParam["AssignType"].ToString().Replace("'", "''") + "', '" +
                                                         rowReportParam["GlobalName"].ToString().Replace("'", "''") + "', " +
                                                         newReportID + ")",
                                                         connTransaction, transaction).ToString();
                                }
                            }
                        }
                    }

                    // import all series
                    DataTable dtSeries = VWA4Common.DB.Retrieve("SELECT * FROM ReportSeries", connImport, null);
                    if (dtSeries.Rows.Count > 0)
                    {
                        foreach (DataRow rowSerie in dtSeries.Rows)
                        {
                            string date_time  = VWA4Common.VWACommon.DateToString(DateTime.Now);
                            string oldSerieID = rowSerie["ID"].ToString();
                            // Mila todo: what to do with SiteID?
                            string newSerieID = VWA4Common.DB.Insert("INSERT INTO ReportSeries(SerieName, SiteID, CreatedDate, ModifiedDate) " +
                                                                     " VALUES('" + rowSerie["SerieName"].ToString() + "', " + rowSerie["SiteID"].ToString() + ", #" +
                                                                     date_time + "#, #" + date_time + "#)",
                                                                     connTransaction, transaction).ToString();

                            // import all report sets
                            DataTable dtReportSets = VWA4Common.DB.Retrieve("SELECT * FROM ReportSet WHERE SerieID = " + oldSerieID, connImport, null);
                            if (dtReportSets.Rows.Count > 0)
                            {
                                foreach (DataRow rowReportSet in dtReportSets.Rows)
                                {
                                    string oldReportSetID = rowSerie["ID"].ToString();
                                    string newReportSetID = VWA4Common.DB.Insert("INSERT INTO ReportSet(ReportMemorized, [Order], Expression, SerieID) " +
                                                                                 " VALUES(" + OldToNewReports[rowReportSet["ReportMemorized"].ToString()] + ", " +
                                                                                 rowReportSet["Order"] + ", '" + rowReportSet["Expression"].ToString().Replace("'", "''") + "', " + rowReportSet["SerieID"] + ")",
                                                                                 connTransaction, transaction).ToString();
                                }
                            }
                        }
                    }
                    transaction.Commit();
                    return(true);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error occured during importing Reports with message : " + ex.Message, "VWA Import Reports Error",
                                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                    transaction.Rollback();
                }
                finally
                {
                    if (connTransaction != null && connTransaction.State != ConnectionState.Closed)
                    {
                        connTransaction.Close();
                    }
                }
            }
            return(false);
        }
示例#50
0
        public void changePositionOfContentItems(int oldPositionIndex, int newPositionIndex, string owner)
        {
            //--Data Base Access Variables--
            System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
            System.Data.OleDb.OleDbCommand    dbCommand    = new OleDbCommand();
            dbCommand.Connection = dbConnection;
            System.Data.OleDb.OleDbTransaction dbTransaction;
            System.Data.OleDb.OleDbDataReader  dbDataReader;
            //-----------------------------

            dbConnection.Close();
            dbConnection.Open();
            dbTransaction         = dbConnection.BeginTransaction();
            dbCommand.Transaction = dbTransaction;
            try
            {
                //--Begin Transaction--

                //ID of the oldPosition
                dbCommand.CommandText = "SELECT id, [position] FROM ContentItems WHERE (item_group = 'A') AND (owner = '" + owner + "') ORDER BY [position]";
                dbDataReader          = dbCommand.ExecuteReader();
                for (int count = 0; count <= oldPositionIndex; count++)
                {
                    dbDataReader.Read();
                }
                Int64 oldPositionId = dbDataReader.GetInt32(0); //id of the oldPosition
                Int64 oldPosition   = dbDataReader.GetInt32(1); //position of the oldPositionIndex
                dbDataReader.Close();

                //ID of the newPosition
                dbCommand.CommandText = "SELECT id, [position] FROM ContentItems WHERE (item_group = 'A') AND (owner = '" + owner + "') ORDER BY [position]";
                dbDataReader          = dbCommand.ExecuteReader();
                for (int count = 0; count <= newPositionIndex; count++)
                {
                    dbDataReader.Read();
                }
                Int64 newPositionId = dbDataReader.GetInt32(0); //id of the newPosition
                Int64 newPosition   = dbDataReader.GetInt32(1); //position of the newPositionIndex
                dbDataReader.Close();

                //UPDATE of the oldPosition
                dbCommand.CommandText = "UPDATE ContentItems SET [position] = " + newPosition.ToString() + " WHERE (id = " + oldPositionId.ToString() + ") AND (item_group = 'A')";
                dbCommand.ExecuteNonQuery();

                //UPDATE of the newPosition
                dbCommand.CommandText = "UPDATE ContentItems SET [position] = " + oldPosition.ToString() + " WHERE (id = " + newPositionId.ToString() + ") AND (item_group = 'A')";
                dbCommand.ExecuteNonQuery();

                dbTransaction.Commit();

                //--End Transaction--
            }
            catch
            {
                dbTransaction.Rollback();
                dbCommand.Transaction = null;
                dbTransaction         = null;
                dbConnection.Close();
            }

            dbCommand.Transaction = null;
            dbTransaction         = null;
            dbConnection.Close();
        }
示例#51
0
        public void  DbUpdate()
        {
            UserIdentity user = UserIdentity.Current;

            Validate();
            System.Data.OleDb.OleDbConnection con = Db.Connection;

            OleDbTransaction trans = con.BeginTransaction();
            OleDbCommand     cmd   = con.CreateCommand();

            cmd.Transaction = trans;
            try
            {
                CmdParams cp = new CmdParams(cmd, false);
                m_CreationTime  = DateTime.Now;
                cmd.CommandText = @"
				update documents
				set	FileType = "                     + cp.Add(m_FileTypeId) +
                                  ", [FileName] = " + cp.Add(m_FileName) +
                                  ", DateReceived = " + cp.Add(m_DateReceived) +
                                  ", DocumentDate = " + cp.Add(m_DocumentDate) +
                                  ", IncomingNumber = " + cp.Add(m_IncomingNumber) +
                                  ", OutgoingNumber = " + cp.Add(m_OutgoingNumber) +
                                  ", Subject = " + cp.Add(m_Subject) +
                                  ", Header = " + cp.Add(m_Header) +
                                  ", ArchiveFileNames = " + cp.Add(m_ArchiveFileNames) +
                                  ", DocTypeId = " + cp.Add(m_DocTypeId) +
                                  ", DocSourceId = " + cp.Add(m_DocSourceId) +
                                  ", DocCategoryId = " + cp.Add(m_DocCategoryId) +
                                  ", ParentId = " + cp.Add(m_ParentId) +
                                  ", PreviousVersionId = " + cp.Add(m_PreviousVersionId) +
                                  ", IsPublic = " + cp.Add(m_IsPublic) +
                                  ", StorageFileName = '" + StorageFileName + "' " +
                                  " where id = " + m_Id.ToString();
                cmd.ExecuteNonQuery();

                StoreDocRefs(trans, "DocGroups", "GroupId", Groups);
                StoreDocRefs(trans, "DocRefRelated", "RelatedDocId", RefDocuments);

                cmd.Parameters.Clear();
                cmd.CommandText = "delete from UserReadDocs where DocId=" + m_Id.ToString() + " and UserId=" + user.UserId.ToString();
                cmd.ExecuteNonQuery();
                cmd.CommandText = "delete from UserFavoriteDocs where DocId=" + m_Id.ToString() + " and UserId=" + user.UserId.ToString();
                cmd.ExecuteNonQuery();
                if (IsRead)
                {
                    cmd.CommandText = "insert into UserReadDocs (DocId, UserId) values (" + m_Id.ToString() + ", " + user.UserId.ToString() + " ) ";
                    cmd.ExecuteNonQuery();
                }

                if (IsFavorite)
                {
                    cmd.CommandText = "insert into UserFavoriteDocs (DocId, UserId) values (" + m_Id.ToString() + ", " + user.UserId.ToString() + " ) ";
                    cmd.ExecuteNonQuery();
                }

                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
        }