Example #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();

		}
        /// <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);
            }
        }
	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();

	}
Example #4
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;
 }
Example #5
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();
                }
            }
        }
Example #6
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 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();
		}
        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;
        }
Example #9
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;
    }
        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;
        }
        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_);
        }
Example #12
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();
     }
 }
Example #13
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;
        }
Example #14
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;
        }
Example #15
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();
     }
 }
Example #16
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();
                }
            }
        }
        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();
        }
Example #18
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 [email protected]");
                        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;
        }
Example #19
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;
 }
Example #20
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;
 }
        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();
                }
            }
        }
Example #22
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;
        }
Example #23
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;
        }
Example #24
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;
     }
 }
Example #25
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();
		}
        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;
        }
Example #27
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;
        }
        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 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;
        }
Example #30
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;
        }