public bool ExecuteSqlString(string sqlString) { bool resultState; OleDbTransaction myTrans = null; try { _connection.Open(); myTrans = _connection.BeginTransaction(); var command = new OleDbCommand(sqlString, _connection, myTrans); command.ExecuteNonQuery(); myTrans.Commit(); resultState = true; } catch { myTrans?.Rollback(); resultState = false; } finally { _connection.Close(); } return(resultState); }
private void Form1_Load(object sender, EventArgs e) { ds = new DataSet("emp"); string conStr = "Provider=ORAOLEDB.ORACLE;data source=topcredu;User ID = scott; Password = tiger"; using (OleDbConnection connection = new OleDbConnection(conStr)) { OleDbCommand command = new OleDbCommand(); OleDbTransaction tr = null; try { connection.Open(); tr = connection.BeginTransaction(); command.Connection = connection; command.Transaction = tr; command.CommandText = "insert into emp (empno, ename) values (9699, '3434 길동')"; int i = command.ExecuteNonQuery(); Console.WriteLine(i + "건 Inserted!"); command.CommandText = "insert into emp (empno, ename) values (8997, '3524 길동')"; i = command.ExecuteNonQuery(); tr.Commit(); adapter = new OleDbDataAdapter("select * from emp", connection); adapter.Fill(ds, "EMP"); dataGridView1.DataSource = ds.Tables["EMP"]; adapter.Fill(ds, "EMP"); } catch (Exception ex) { tr?.Rollback(); MessageBox.Show(ex.Message, "emp Table Loading Error"); } finally { connection.Close(); } } }
private void Form1_Load(object sender, EventArgs e) { ds = new DataSet("emp"); //아래 onj 는 $ORACLE_HOME/network/admin 의 tnsnames.ora 파일에 정의된 TNS 이름 //string conStr = "Provider=ORAOLEDB.ORACLE;data source=topcredu;UserID = scott; Password = tiger"; //string conStr = "Provider=MSDAORA;data source=topcredu;User ID=sscott;Password=tiger"; string conStr = @"Data Source=(DESCRIPTION = ( ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.27)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = topcredu) ) ) ;User Id=scott;Password=tiger; Provider=OraOleDB.Oracle"; using (OleDbConnection connection = new OleDbConnection(conStr)) { OleDbCommand command = new OleDbCommand(); OleDbTransaction tr = null; try { connection.Open(); tr = connection.BeginTransaction(); command.Connection = connection; command.Transaction = tr; command.CommandText = "insert into emp (empno, ename)" + "values(3499, '3000길동')"; int i = command.ExecuteNonQuery(); Console.WriteLine(i + "건 Inserted!"); command.CommandText = "insert into emp (empno, ename)" + "values(7899,'3000길동')"; i = command.ExecuteNonQuery(); tr.Commit(); adapter = new OleDbDataAdapter("select * from emp", connection); adapter.Fill(ds, "EMP"); dataGridView1.DataSource = ds.Tables["EMP"]; adapter.Fill(ds, "EMP"); } catch (Exception ex) { tr?.Rollback(); MessageBox.Show(ex.Message, "emp Table Loading Error"); } finally { connection.Close(); } } }
private void Form1_Load(object sender, EventArgs e) { ds = new DataSet("emp"); //오라클 topcredu는 $ORACLE_HOME/network/admin의 tnsnames.ora 파일에 정의된 TNS이름 //string conStr = "Provider=ORAOLEDB.ORACLE;data source=topcredu;User ID=scott;Password=tiger"; //string conStr = "Provider=OraOLEDB.ORACLE;data source=topcredu;User ID=scott;Password=tiger"; // SQL Server, 프로젝트 속성에서 Target Pltform을 64비트라고 할 필요없다. string conStr = @"Provider=SQLOLEDB;Data Source=localhost\OJC;Initial Catalog=test;Integrated Security=SSPI"; using (OleDbConnection connection = new OleDbConnection(conStr)) { OleDbCommand command = new OleDbCommand(); OleDbTransaction tr = null; try { connection.Open(); tr = connection.BeginTransaction(); command.Connection = connection; command.Transaction = tr; command.CommandText = "insert into emp (empno, ename)" + " values (3456, '3000길동')"; int i = command.ExecuteNonQuery(); Console.WriteLine(i + "건 Inserted!"); command.CommandText = "insert into emp (empno, ename)" + " values (7890, '3000길동')"; i = command.ExecuteNonQuery(); tr.Commit(); adapter = new OleDbDataAdapter("select * from emp", connection); adapter.Fill(ds, "EMP"); dataGridView1.DataSource = ds.Tables["EMP"]; adapter.Fill(ds, "EMP"); } catch (Exception ex) { tr?.Rollback(); MessageBox.Show(ex.Message, "emp Table Loading Error"); } finally { connection.Close(); } } }
private void Form1_Load(object sender, EventArgs e) { ds = new DataSet("emp"); //아래 onj 는 $ORACLE_HOME/network/admin 의 tnsnames.ora 파일에 정의된 TNS 이름 string conStr = "Provider=ORAOLEDB.ORACLE;data source=topcredu;User ID = scott; Password = tiger"; //string conStr = "Provider=MSDAORA;data source=topcredu;User ID=scott;Password=tiger"; using (OleDbConnection connection = new OleDbConnection(conStr)) { OleDbCommand command = new OleDbCommand(); OleDbTransaction tr = null; try { connection.Open(); tr = connection.BeginTransaction(); command.Connection = connection; command.Transaction = tr; command.CommandText = "insert into emp (empno, ename)" + " values (3456, '3000 길동')"; int i = command.ExecuteNonQuery(); Console.WriteLine(i + "건 Inserted!"); command.CommandText = "insert into emp (empno, ename)" + " values (7890, '3000 길동')"; i = command.ExecuteNonQuery(); tr.Commit(); //영구히 DB에 저장해라 adapter = new OleDbDataAdapter("select * from emp", connection); adapter.Fill(ds, "EMP"); dataGridView1.DataSource = ds.Tables["EMP"]; adapter.Fill(ds, "EMP"); } catch (Exception ex) { tr?.Rollback(); // 취소 MessageBox.Show(ex.Message, "emp Table Loading Error"); } finally { connection.Close(); } } }
/// <summary> /// 删除一条数据 /// </summary> public bool Delete(int id) { //取得Model信息 Model.article_attribute_field model = GetModel(id); //开始删除 using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString)) { conn.Open(); using (OleDbTransaction trans = conn.BeginTransaction()) { try { //查找关联的频道ID,得到后以备使用 StringBuilder strSql1 = new StringBuilder(); strSql1.Append("select channel_id,field_id from " + databaseprefix + "channel_field "); strSql1.Append(" where field_id=@field_id"); OleDbParameter[] parameters1 = { new OleDbParameter("@field_id", OleDbType.Integer, 4) }; parameters1[0].Value = id; DataTable dt = DbHelperOleDb.Query(conn, trans, strSql1.ToString(), parameters1).Tables[0]; //删除频道关联的字段表 StringBuilder strSql2 = new StringBuilder(); strSql2.Append("delete from " + databaseprefix + "channel_field"); strSql2.Append(" where field_id=@field_id"); OleDbParameter[] parameters2 = { new OleDbParameter("@field_id", OleDbType.Integer, 4) }; parameters2[0].Value = id; DbHelperOleDb.ExecuteSql(conn, trans, strSql2.ToString(), parameters2); //重建对应频道的视图 if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { Model.channel modelt = new DAL.channel(databaseprefix).GetModel(conn, trans, int.Parse(dr["channel_id"].ToString())); if (modelt != null) { new DAL.channel(databaseprefix).RehabChannelViews(conn, trans, modelt, modelt.name); } } } //删除主表 StringBuilder strSql = new StringBuilder(); strSql.Append("delete from " + databaseprefix + "article_attribute_field "); strSql.Append(" where id=@id"); OleDbParameter[] parameters = { new OleDbParameter("@id", OleDbType.Integer, 4) }; parameters[0].Value = id; DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters); //删除扩展字段表中一列 DbHelperOleDb.ExecuteSql(conn, trans, "alter table " + databaseprefix + "article_attribute_value drop column " + model.name); //没有错误确认事务 trans.Commit(); } catch { trans.Rollback(); return(false); } } } return(true); }
/// <summary> /// 增加一条数据 /// </summary> public int Add(Model.article_attribute_field 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 + "article_attribute_field("); strSql.Append("[name],title,control_type,data_type,data_length,data_place,item_option,default_value,is_required,is_password,is_html,editor_type,valid_tip_msg,valid_error_msg,valid_pattern,sort_id,is_sys)"); strSql.Append(" values ("); strSql.Append("@name,@title,@control_type,@data_type,@data_length,@data_place,@item_option,@default_value,@is_required,@is_password,@is_html,@editor_type,@valid_tip_msg,@valid_error_msg,@valid_pattern,@sort_id,@is_sys)"); OleDbParameter[] parameters = { new OleDbParameter("@name", OleDbType.VarChar, 100), new OleDbParameter("@title", OleDbType.VarChar, 100), new OleDbParameter("@control_type", OleDbType.VarChar, 50), new OleDbParameter("@data_type", OleDbType.VarChar, 50), new OleDbParameter("@data_length", OleDbType.Integer, 4), new OleDbParameter("@data_place", OleDbType.Integer, 4), new OleDbParameter("@item_option", OleDbType.VarChar), new OleDbParameter("@default_value", OleDbType.VarChar), new OleDbParameter("@is_required", OleDbType.Integer, 4), new OleDbParameter("@is_password", OleDbType.Integer, 4), new OleDbParameter("@is_html", OleDbType.Integer, 4), new OleDbParameter("@editor_type", OleDbType.Integer, 4), new OleDbParameter("@valid_tip_msg", OleDbType.VarChar, 255), new OleDbParameter("@valid_error_msg", OleDbType.VarChar, 255), new OleDbParameter("@valid_pattern", OleDbType.VarChar, 500), new OleDbParameter("@sort_id", OleDbType.Integer, 4), new OleDbParameter("@is_sys", OleDbType.Integer, 4) }; parameters[0].Value = model.name; parameters[1].Value = model.title; parameters[2].Value = model.control_type; parameters[3].Value = model.data_type; parameters[4].Value = model.data_length; parameters[5].Value = model.data_place; parameters[6].Value = model.item_option; parameters[7].Value = model.default_value; parameters[8].Value = model.is_required; parameters[9].Value = model.is_password; parameters[10].Value = model.is_html; parameters[11].Value = model.editor_type; parameters[12].Value = model.valid_tip_msg; parameters[13].Value = model.valid_error_msg; parameters[14].Value = model.valid_pattern; parameters[15].Value = model.sort_id; parameters[16].Value = model.is_sys; DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters); //取得新插入的ID newId = GetMaxId(conn, trans); //增加扩展字段表中一列 StringBuilder strSql2 = new StringBuilder(); strSql2.Append("alter table " + databaseprefix + "article_attribute_value add " + model.name + " " + model.data_type); OleDbParameter[] parameters2 = { }; DbHelperOleDb.ExecuteSql(conn, trans, strSql2.ToString(), parameters2); trans.Commit(); } catch { trans.Rollback(); return(-1); } } } return(newId); }
private void BatchBallot(HttpContext context, OleDbConnection conn) { Dal.Models.UserInfo user = (Dal.Models.UserInfo)context.Session["UserInfo"]; Dal.Models.Expert expert = BLL.Expert.GetExpertByUserID(user.UserID.Value, conn); int iPrizeID = 0; int.TryParse(context.Request["PrizeID"], out iPrizeID); string strVoteCode = context.Request["VoteCode"]; string strLevelCode = context.Request["LevelCode"]; if (strLevelCode == "0404") { strLevelCode = ""; } Dal.Models.Prize prize = BLL.Prize.GetPrize(iPrizeID, conn); if (prize == null || prize.IsStopVote) { context.Response.Write("当前奖项及轮次已结束投票。"); return; } OleDbTransaction tran = conn.BeginTransaction(); try { //根据专家和奖项获评奖项目 List <Dal.Models.Vote> lstVote = BLL.Vote.GetVoteDeclarationList(expert, prize, conn, tran); //获取每个奖项的已评奖数量 Dictionary <string, int> dicVote = new Dictionary <string, int>(); //获取每个奖项未评奖数量 Dictionary <string, int> dicRemain = new Dictionary <string, int>(); GetLimit(prize, lstVote, ref dicVote, ref dicRemain); //if (strVoteCode == "0601" && (dicRemain["Recommended"] == 0 // || (strLevelCode == "0401" && dicRemain["FirstPrize"] == 0) // || (strLevelCode == "0402" && dicRemain["SecondPrize"] == 0) // || (strLevelCode == "0403" && dicRemain["ThirdPrize"] == 0))) //{ // throw new Exception("所投选项名额已满,请重新投票!"); //} Dal.Models.Vote vote = new Dal.Models.Vote() { UserID = user.UserID.Value, Votor = expert.ExpertID.Value, ActivityID = prize.ActivityID.Value, MultipleVoteRound = prize.MultipleVoteRound ?? 1, VoteRound = prize.MultipleVoteRound ?? 1, IsSubmit = false, PrizeID = prize.PrizeID.Value, VoteCode = strVoteCode, PrizeLevelCode = strLevelCode, VoteType = "0502" }; BLL.Vote.BatchBallot(vote, conn, tran); tran.Commit(); context.Response.Write(JsonConvert.SerializeObject("投票成功!")); } catch (Exception ex) { tran.Rollback(); context.Response.Write(JsonConvert.SerializeObject(ex.Message)); } }
/// <summary> /// 更新一条数据 /// </summary> public bool Update(Model.article_attribute_field model) { using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString)) { conn.Open(); using (OleDbTransaction trans = conn.BeginTransaction()) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("update " + databaseprefix + "article_attribute_field set "); strSql.Append("[name]=@name,"); strSql.Append("title=@title,"); strSql.Append("control_type=@control_type,"); strSql.Append("data_type=@data_type,"); strSql.Append("data_length=@data_length,"); strSql.Append("data_place=@data_place,"); strSql.Append("item_option=@item_option,"); strSql.Append("default_value=@default_value,"); strSql.Append("is_required=@is_required,"); strSql.Append("is_password=@is_password,"); strSql.Append("is_html=@is_html,"); strSql.Append("editor_type=@editor_type,"); strSql.Append("valid_tip_msg=@valid_tip_msg,"); strSql.Append("valid_error_msg=@valid_error_msg,"); strSql.Append("valid_pattern=@valid_pattern,"); strSql.Append("sort_id=@sort_id,"); strSql.Append("is_sys=@is_sys"); strSql.Append(" where id=@id"); OleDbParameter[] parameters = { new OleDbParameter("@name", OleDbType.VarChar, 100), new OleDbParameter("@title", OleDbType.VarChar, 100), new OleDbParameter("@control_type", OleDbType.VarChar, 50), new OleDbParameter("@data_type", OleDbType.VarChar, 50), new OleDbParameter("@data_length", OleDbType.Integer, 4), new OleDbParameter("@data_place", OleDbType.Integer, 4), new OleDbParameter("@item_option", OleDbType.VarChar), new OleDbParameter("@default_value", OleDbType.VarChar), new OleDbParameter("@is_required", OleDbType.Integer, 4), new OleDbParameter("@is_password", OleDbType.Integer, 4), new OleDbParameter("@is_html", OleDbType.Integer, 4), new OleDbParameter("@editor_type", OleDbType.Integer, 4), new OleDbParameter("@valid_tip_msg", OleDbType.VarChar, 255), new OleDbParameter("@valid_error_msg", OleDbType.VarChar, 255), new OleDbParameter("@valid_pattern", OleDbType.VarChar, 500), new OleDbParameter("@sort_id", OleDbType.Integer, 4), new OleDbParameter("@is_sys", OleDbType.Integer, 4), new OleDbParameter("@id", OleDbType.Integer, 4) }; parameters[0].Value = model.name; parameters[1].Value = model.title; parameters[2].Value = model.control_type; parameters[3].Value = model.data_type; parameters[4].Value = model.data_length; parameters[5].Value = model.data_place; parameters[6].Value = model.item_option; parameters[7].Value = model.default_value; parameters[8].Value = model.is_required; parameters[9].Value = model.is_password; parameters[10].Value = model.is_html; parameters[11].Value = model.editor_type; parameters[12].Value = model.valid_tip_msg; parameters[13].Value = model.valid_error_msg; parameters[14].Value = model.valid_pattern; parameters[15].Value = model.sort_id; parameters[16].Value = model.is_sys; parameters[17].Value = model.id; DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters); //修改扩展字段表中一列 StringBuilder strSql2 = new StringBuilder(); strSql2.Append("alter table " + databaseprefix + "article_attribute_value alter column " + model.name + " " + model.data_type); DbHelperOleDb.ExecuteSql(conn, trans, strSql2.ToString()); //没有错误确认事务 trans.Commit(); } catch { trans.Rollback(); return(false); } } } return(true); }
protected void Add_Task_Click(object sender, EventArgs e) { if (TB_Task.Text == "") { Tooltips_Task.Text = "事项名称不能为空!"; Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script> ShowDialog('Add_Task');</script>"); } else { ArrayList sqllist = new ArrayList(); if ((bool)ViewState["isedit"]) { string sql = "Update PMTask Set beizhu='" + Input.Inputadd(TB_Task_Beizhu.Text) + "',[lastdate]=#" + DateTime.Now.ToLocalTime() + "# where ID in (" + ViewState["sqlid"].ToString() + ")"; if (CB_Task.Checked) { sql = "Update PMTask Set status='Close',beizhu='" + Input.Inputadd(TB_Task_Beizhu.Text) + "',[lastdate]=#" + DateTime.Now.ToLocalTime() + "# where ID in (" + ViewState["sqlid"].ToString() + ")"; } sqllist.Add(sql); } else { if (DD_EQ.SelectedIndex != 0) { string sql = "Insert into PMTask ([eq],[unit],[taskname],[status],[beizhu],[eng]) values ('" + DD_EQ.SelectedValue + "','" + DD_Unit.SelectedValue + "','" + TB_Task.Text + "','Open','" + Input.Inputadd(TB_Task_Beizhu.Text) + "','" + Session["name"].ToString() + "')"; sqllist.Add(sql); } else { for (int i = 0; i < 16; i++) { string sql = "Insert into PMTask ([eq],[unit],[taskname],[status],[beizhu],[eng]) values ('" + DD_EQ.Items[i + 1].Text + "','" + DD_Unit.SelectedValue + "','" + TB_Task.Text + "','Open','" + Input.Inputadd(TB_Task_Beizhu.Text) + "','" + Session["name"].ToString() + "')"; sqllist.Add(sql); } } } if (sqllist.Count > 0) { string ConnectionString = ConfigurationManager.ConnectionStrings["mdbcon"].ConnectionString; OleDbConnection conn = new OleDbConnection(ConnectionString); conn.Open(); OleDbCommand cmd = new OleDbCommand(); OleDbTransaction tx = conn.BeginTransaction(); cmd.Connection = conn; cmd.Transaction = tx; try { for (int i = 0; i < sqllist.Count; i++) { cmd.CommandText = sqllist[i].ToString(); cmd.ExecuteNonQuery(); } tx.Commit(); LB_OK.Text = "发布成功!"; Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script> ShowDialog('dlg_ok');</script>"); Load_Changed(sender, e); } catch { tx.Rollback(); LB_OK.Text = "发布失败!"; Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script> ShowDialog('dlg_ok');</script>"); Load_Changed(sender, e); } finally { tx.Dispose(); cmd.Dispose(); conn.Dispose(); } } } }
private void Update(OleDbTransaction tran, Receipt receipt) { OleDbConnection conn; var newTran = (tran == null); if (newTran) { conn = new OleDbConnection(this.ConnectionString); conn.Open(); tran = conn.BeginTransaction(); } else { conn = tran.Connection; } try { var query = new QueryContext(null, @"UPDATE [Receipts] SET [VesselName] = ?, [Time] = ?, [ReceiptFor] = ?, [PortOfShipment] = ?, [PortOfDestination] = ?, [KindOfGoodsUId] = ?, [VesselStatus] = ?, [TotalOfVolumeOfStandard] = ?, [TotalOfVolume] = ?, [TotalOfVolumeOfWater] = ?, [TotalOfMass] = ?, [TotalOfVolumeOfPipes] = ?, [OperaterName] = ?, [AgentName] = ?, [ShipperName] = ?, [ConsignerName] = ?, [ConsigneeName] = ?, [TimeSaved] = ?, [ReceiptType] = ? WHERE [No] = ?") { Connection = conn, Transaction = tran }; query.Parameters.AddWithValue(receipt.VesselName); query.Parameters.AddWithValue(receipt.Time).OleDbType = OleDbType.Date; query.Parameters.AddWithValue(receipt.ReceiptFor); query.Parameters.AddWithValue(receipt.PortOfShipment); query.Parameters.AddWithValue(receipt.PortOfDestination); query.Parameters.AddWithValue(receipt.KindOfGoods != null ? receipt.KindOfGoods.UId : null); query.Parameters.AddWithValue(receipt.VesselStatus != null ? receipt.VesselStatus.ToString() : VesselStatus.Empty.ToString()); query.Parameters.AddWithValue(receipt.TotalOfVolumeOfStandard); query.Parameters.AddWithValue(receipt.TotalOfVolume); query.Parameters.AddWithValue(receipt.TotalOfVolumeOfWater); query.Parameters.AddWithValue(receipt.TotalOfMass); query.Parameters.AddWithValue(receipt.TotalOfVolumeOfPipes); query.Parameters.AddWithValue(receipt.OperaterName); query.Parameters.AddWithValue(receipt.AgentName); query.Parameters.AddWithValue(receipt.ShipperName); query.Parameters.AddWithValue(receipt.ConsignerName); query.Parameters.AddWithValue(receipt.ConsigneeName); query.Parameters.AddWithValue(DateTime.Now).OleDbType = OleDbType.Date; query.Parameters.AddWithValue(receipt.ReceiptType.ToString()); query.Parameters.AddWithValue(receipt.No); query.ExecuteNonQuery(); this.RemoveReceiptTankDetails(tran, receipt.No); this.Add(tran, receipt.No, receipt.ReceiptTankDetails); if (newTran) { tran.Commit(); } if (this.all != null) { this.all.RemoveAll(r => r.No.Equals(receipt.No)); this.all.Add(receipt); } } catch (Exception ex) { if (newTran) { tran.Rollback(); } throw ex; } finally { if (newTran) { tran.Dispose(); conn.Dispose(); } } }
/// <summary> /// 删除一条数据 /// </summary> public bool Delete(int id) { //取得频道的名称 string channel_name = GetChannelName(id); if (string.IsNullOrEmpty(channel_name)) { return(false); } //取得要删除的所有导航ID //string navIds = new navigation(databaseprefix).GetIds("channel_" + channel_name); using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString)) { conn.Open(); using (OleDbTransaction trans = conn.BeginTransaction()) { try { //删除导航主表 //if (!string.IsNullOrEmpty(navIds)) //{ DbHelperOleDb.ExecuteSql(conn, trans, "delete from " + databaseprefix + "navigation where channel_id =" + id + ""); //} //删除视图 StringBuilder strSql1 = new StringBuilder(); //strSql1.Append("if exists (select 1 from sysobjects where id = object_id('view_channel_" + channel_name + "') and type = 'V')"); strSql1.Append("drop view view_channel_" + channel_name); DbHelperOleDb.ExecuteSql(conn, trans, strSql1.ToString()); //删除频道扩展字段表 StringBuilder strSql2 = new StringBuilder(); strSql2.Append("delete from " + databaseprefix + "channel_field "); strSql2.Append(" where channel_id=@channel_id "); OleDbParameter[] parameters2 = { new OleDbParameter("@channel_id", OleDbType.Integer, 4) }; parameters2[0].Value = id; DbHelperOleDb.ExecuteSql(conn, trans, strSql2.ToString(), parameters2); //删除频道表 StringBuilder strSql3 = new StringBuilder(); strSql3.Append("delete from " + databaseprefix + "channel "); strSql3.Append(" where id=@id "); OleDbParameter[] parameters3 = { new OleDbParameter("@id", OleDbType.Integer, 4) }; parameters3[0].Value = id; DbHelperOleDb.ExecuteSql(conn, trans, strSql3.ToString(), parameters3); trans.Commit(); } catch { trans.Rollback(); return(false); } } } return(true); }
public int Add(Rain.Model.article_attribute_field model) { int maxId; using (OleDbConnection oleDbConnection = new OleDbConnection(DbHelperOleDb.connectionString)) { oleDbConnection.Open(); using (OleDbTransaction trans = oleDbConnection.BeginTransaction()) { try { StringBuilder stringBuilder1 = new StringBuilder(); stringBuilder1.Append("insert into " + this.databaseprefix + "article_attribute_field("); stringBuilder1.Append("[name],title,control_type,data_type,data_length,data_place,item_option,default_value,is_required,is_password,is_html,editor_type,valid_tip_msg,valid_error_msg,valid_pattern,sort_id,is_sys)"); stringBuilder1.Append(" values ("); stringBuilder1.Append("@name,@title,@control_type,@data_type,@data_length,@data_place,@item_option,@default_value,@is_required,@is_password,@is_html,@editor_type,@valid_tip_msg,@valid_error_msg,@valid_pattern,@sort_id,@is_sys)"); OleDbParameter[] oleDbParameterArray1 = new OleDbParameter[17] { new OleDbParameter("@name", OleDbType.VarChar, 100), new OleDbParameter("@title", OleDbType.VarChar, 100), new OleDbParameter("@control_type", OleDbType.VarChar, 50), new OleDbParameter("@data_type", OleDbType.VarChar, 50), new OleDbParameter("@data_length", OleDbType.Integer, 4), new OleDbParameter("@data_place", OleDbType.Integer, 4), new OleDbParameter("@item_option", OleDbType.VarChar), new OleDbParameter("@default_value", OleDbType.VarChar), new OleDbParameter("@is_required", OleDbType.Integer, 4), new OleDbParameter("@is_password", OleDbType.Integer, 4), new OleDbParameter("@is_html", OleDbType.Integer, 4), new OleDbParameter("@editor_type", OleDbType.Integer, 4), new OleDbParameter("@valid_tip_msg", OleDbType.VarChar, (int)byte.MaxValue), new OleDbParameter("@valid_error_msg", OleDbType.VarChar, (int)byte.MaxValue), new OleDbParameter("@valid_pattern", OleDbType.VarChar, 500), new OleDbParameter("@sort_id", OleDbType.Integer, 4), new OleDbParameter("@is_sys", OleDbType.Integer, 4) }; oleDbParameterArray1[0].Value = (object)model.name; oleDbParameterArray1[1].Value = (object)model.title; oleDbParameterArray1[2].Value = (object)model.control_type; oleDbParameterArray1[3].Value = (object)model.data_type; oleDbParameterArray1[4].Value = (object)model.data_length; oleDbParameterArray1[5].Value = (object)model.data_place; oleDbParameterArray1[6].Value = (object)model.item_option; oleDbParameterArray1[7].Value = (object)model.default_value; oleDbParameterArray1[8].Value = (object)model.is_required; oleDbParameterArray1[9].Value = (object)model.is_password; oleDbParameterArray1[10].Value = (object)model.is_html; oleDbParameterArray1[11].Value = (object)model.editor_type; oleDbParameterArray1[12].Value = (object)model.valid_tip_msg; oleDbParameterArray1[13].Value = (object)model.valid_error_msg; oleDbParameterArray1[14].Value = (object)model.valid_pattern; oleDbParameterArray1[15].Value = (object)model.sort_id; oleDbParameterArray1[16].Value = (object)model.is_sys; DbHelperOleDb.ExecuteSql(oleDbConnection, trans, stringBuilder1.ToString(), oleDbParameterArray1); maxId = this.GetMaxId(oleDbConnection, trans); StringBuilder stringBuilder2 = new StringBuilder(); stringBuilder2.Append("alter table " + this.databaseprefix + "article_attribute_value add " + model.name + " " + model.data_type); OleDbParameter[] oleDbParameterArray2 = new OleDbParameter[0]; DbHelperOleDb.ExecuteSql(oleDbConnection, trans, stringBuilder2.ToString(), oleDbParameterArray2); trans.Commit(); } catch { trans.Rollback(); return(-1); } } } return(maxId); }
protected void Add_Item_Click(object sender, EventArgs e) { ArrayList sqllist = new ArrayList(); if (TB_Cycle.Text == "") { ToolTips.Text = "更新的周期不能为空"; } else { int cycle = Convert.ToInt32(TB_Cycle.Text); if (cycle > 0) { if (DD_Item_Option.SelectedIndex == 0 && DD_Position_Option.SelectedIndex == 0) { if (DD_EQ.SelectedIndex == 0) { sqllist.Add("Update PMState Set [zhouqi]='" + cycle + "' where [unit]='" + DD_Unit.SelectedValue + "' AND [item]='" + DD_Items.SelectedValue + "' AND [position]='" + DD_Position.SelectedValue + "'"); } else { sqllist.Add("Update PMState Set [zhouqi]='" + cycle + "' where eq='" + DD_EQ.SelectedValue + "' AND [unit]='" + DD_Unit.SelectedValue + "' AND [item]='" + DD_Items.SelectedValue + "' AND [position]='" + DD_Position.SelectedValue + "'"); } } else { if (DD_Item_Option.SelectedIndex == 0) { if (TB_New_Position.Text == "") { ToolTips.Text = "新位置不能为空"; } else { DateTime last = Convert.ToDateTime("2015/01/01"); if (TB_Date.Text != "") { last = Convert.ToDateTime(TB_Date.Text); } DateTime duedate = last.AddDays(cycle); if (DD_EQ.SelectedIndex == 0) { if (DD_Unit.SelectedValue != "DB") { for (int i = 0; i < 16; i++) { sqllist.Add("Insert into PMState (eq,[item],[unit],[position],[zhouqi],[last],[duedate]) Values ('" + DD_EQ.Items[i + 1].Value + "','" + DD_Items.SelectedValue + "','" + DD_Unit.SelectedValue + "','" + TB_New_Position.Text + "','" + cycle + "',#" + last.ToLocalTime() + "#,#" + duedate.ToLocalTime() + "#)"); } } else { for (int i = 0; i < 16; i++) { if (IsWithDB(i)) { sqllist.Add("Insert into PMState (eq,[item],[unit],[position],[zhouqi],[last],[duedate]) Values ('" + DD_EQ.Items[i + 1].Value + "','" + DD_Items.SelectedValue + "','" + DD_Unit.SelectedValue + "','" + TB_New_Position.Text + "','" + cycle + "',#" + last.ToLocalTime() + "#,#" + duedate.ToLocalTime() + "#)"); } } } } else { sqllist.Add("Insert into PMState (eq,[item],[unit],[position],[zhouqi],[last],[duedate]) Values ('" + DD_EQ.SelectedValue + "','" + DD_Items.SelectedValue + "','" + DD_Unit.SelectedValue + "','" + TB_New_Position.Text + "','" + cycle + "',#" + last.ToLocalTime() + "#,#" + duedate.ToLocalTime() + "#)"); } } } else { if ((TB_New_Item.Text == "") || (TB_New_Position.Text == "")) { ToolTips.Text = "新项目或新位置不能为空"; } else { DateTime last = Convert.ToDateTime("2015/01/01"); if (TB_Date.Text != "") { last = Convert.ToDateTime(TB_Date.Text); } DateTime duedate = last.AddDays(cycle); if (DD_EQ.SelectedIndex == 0) { if (DD_Unit.SelectedValue != "DB") { for (int i = 0; i < 16; i++) { sqllist.Add("Insert into PMState (eq,[item],[unit],[position],[zhouqi],[last],[duedate]) Values ('" + DD_EQ.Items[i + 1].Value + "','" + TB_New_Item.Text + "','" + DD_Unit.SelectedValue + "','" + TB_New_Position.Text + "','" + cycle + "',#" + last.ToLocalTime() + "#,#" + duedate.ToLocalTime() + "#)"); } } else { for (int i = 0; i < 16; i++) { if (IsWithDB(i)) { sqllist.Add("Insert into PMState (eq,[item],[unit],[position],[zhouqi],[last],[duedate]) Values ('" + DD_EQ.Items[i + 1].Value + "','" + TB_New_Item.Text + "','" + DD_Unit.SelectedValue + "','" + TB_New_Position.Text + "','" + cycle + "',#" + last.ToLocalTime() + "#,#" + duedate.ToLocalTime() + "#)"); } } } } else { sqllist.Add("Insert into PMState (eq,[item],[unit],[position],[zhouqi],[last],[duedate]) Values ('" + DD_EQ.SelectedValue + "','" + TB_New_Item.Text + "','" + DD_Unit.SelectedValue + "','" + TB_New_Position.Text + "','" + cycle + "',#" + last.ToLocalTime() + "#,#" + duedate.ToLocalTime() + "#)"); } } } } if (sqllist.Count > 0) { string ConnectionString = ConfigurationManager.ConnectionStrings["mdbcon"].ConnectionString; OleDbConnection conn = new OleDbConnection(ConnectionString); conn.Open(); OleDbCommand cmd = new OleDbCommand(); OleDbTransaction tx = conn.BeginTransaction(); cmd.Connection = conn; cmd.Transaction = tx; try { for (int i = 0; i < sqllist.Count; i++) { cmd.CommandText = sqllist[i].ToString(); cmd.ExecuteNonQuery(); } tx.Commit(); string myscript = @"alert('发布成功!');window.location.href='NewPMItem.aspx';"; Page.ClientScript.RegisterStartupScript(this.GetType(), "myscript", myscript, true); } catch { tx.Rollback(); conn.Close(); ToolTips.Text = "发布失败!"; } } } else { ToolTips.Text = "更新的周期必须大于0"; } } }
/// <summary> /// 更新一条数据 /// </summary> public bool Update(Model.channel model) { Model.channel oldModel = GetModel(model.id); //旧的数据 //取得站点对应的导航ID int parent_id = 1; // new DAL.channel_site(databaseprefix).GetSiteNavId(model.site_id); //if (parent_id == 0) //{ // return false; //} using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString)) { conn.Open(); using (OleDbTransaction trans = conn.BeginTransaction()) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("update " + databaseprefix + "channel set "); strSql.Append("site_id=@site_id,"); strSql.Append("[name]=@name,"); strSql.Append("title=@title,"); strSql.Append("is_albums=@is_albums,"); strSql.Append("is_attach=@is_attach,"); strSql.Append("is_spec=@is_spec,"); strSql.Append("sort_id=@sort_id"); strSql.Append(" where id=@id "); OleDbParameter[] parameters = { new OleDbParameter("@site_id", OleDbType.Integer, 4), new OleDbParameter("@name", OleDbType.VarChar, 50), new OleDbParameter("@title", OleDbType.VarChar, 100), new OleDbParameter("@is_albums", OleDbType.Integer, 4), new OleDbParameter("@is_attach", OleDbType.Integer, 4), new OleDbParameter("@is_spec", OleDbType.Integer, 4), new OleDbParameter("@sort_id", OleDbType.Integer, 4), new OleDbParameter("@id", OleDbType.Integer, 4) }; parameters[0].Value = model.site_id; parameters[1].Value = model.name; parameters[2].Value = model.title; parameters[3].Value = model.is_albums; parameters[4].Value = model.is_attach; parameters[5].Value = model.is_spec; parameters[6].Value = model.sort_id; parameters[7].Value = model.id; DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters); //删除已移除的扩展字段 FieldDelete(conn, trans, model.channel_fields, model.id); //添加扩展字段 if (model.channel_fields != null) { StringBuilder strSql2; foreach (Model.channel_field modelt in model.channel_fields) { strSql2 = new StringBuilder(); Model.channel_field fieldModel = null; if (oldModel.channel_fields != null) { fieldModel = oldModel.channel_fields.Find(p => p.field_id == modelt.field_id); //查找是否已经存在 } if (fieldModel == null) //如果不存在则添加 { strSql2.Append("insert into " + databaseprefix + "channel_field("); strSql2.Append("channel_id,field_id)"); strSql2.Append(" values ("); strSql2.Append("@channel_id,@field_id)"); OleDbParameter[] parameters2 = { new OleDbParameter("@channel_id", OleDbType.Integer, 4), new OleDbParameter("@field_id", OleDbType.Integer, 4) }; parameters2[0].Value = modelt.channel_id; parameters2[1].Value = modelt.field_id; DbHelperOleDb.ExecuteSql(conn, trans, strSql2.ToString(), parameters2); } } } //删除旧视图重建新视图 RehabChannelViews(conn, trans, model, oldModel.name); //修改对应的导航 new DAL.navigation(databaseprefix).Update(conn, trans, "channel_" + oldModel.name + "_list", 1, "channel_" + model.name + "_list", model.title, model.sort_id); new DAL.navigation(databaseprefix).Update(conn, trans, "channel_" + oldModel.name + "_category", 143, "channel_" + model.name + "_category", model.title + "栏目类别", model.sort_id); //内容管理 //new DAL.navigation(databaseprefix).Update(conn, trans, "channel_" + oldModel.name + "_list", "channel_" + model.name + "_list"); //内容管理 //new DAL.navigation(databaseprefix).Update(conn, trans, "channel_" + oldModel.name + "_category", "channel_" + model.name + "_category"); //栏目类别 //new DAL.navigation(databaseprefix).Update(conn, trans, "channel_" + oldModel.name + "_comment", "channel_" + model.name + "_comment"); //评论管理 trans.Commit(); } catch { trans.Rollback(); return(false); } } } return(true); }
private string execProc(DataSet ds, ref string s) { OleDbTransaction trunsaction = null; trunsaction = com.Connection.BeginTransaction(IsolationLevel.ReadCommitted); string ret = "0"; int judgeflag = 1; try { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { proc.Parameters["iv_dispensary"].Value = ds.Tables[0].Rows[i]["iv_dispensary"].ToString();// "5101"; proc.Parameters["iv_patient_id"].Value = ds.Tables[0].Rows[i]["iv_patient_id"].ToString(); proc.Parameters["in_visit_id"].Value = ds.Tables[0].Rows[i]["in_visit_id"].ToString(); proc.Parameters["in_baby_no"].Value = ds.Tables[0].Rows[i]["in_baby_no"].ToString(); proc.Parameters["in_order_no"].Value = ds.Tables[0].Rows[i]["in_order_no"].ToString(); proc.Parameters["in_order_sub_no"].Value = ds.Tables[0].Rows[i]["in_order_sub_no"].ToString(); proc.Parameters["iv_drug_code"].Value = ds.Tables[0].Rows[i]["iv_drug_code"].ToString(); proc.Parameters["iv_drug_spec"].Value = ds.Tables[0].Rows[i]["iv_drug_spec"].ToString(); proc.Parameters["iv_drug_units"].Value = ds.Tables[0].Rows[i]["iv_drug_units"].ToString(); proc.Parameters["iv_firm_id"].Value = ds.Tables[0].Rows[i]["iv_firm_id"].ToString(); proc.Parameters["in_dispense_amount"].Value = ds.Tables[0].Rows[i]["in_dispense_amount"].ToString(); proc.Parameters["iv_DISPENSING_PROVIDER"].Value = ds.Tables[0].Rows[i]["iv_DISPENSING_PROVIDER"].ToString(); proc.Parameters["in_order_sub_sub_no"].Value = ds.Tables[0].Rows[i]["in_order_sub_sub_no"].ToString(); proc.Parameters["iv_bar_code"].Value = ds.Tables[0].Rows[i]["iv_bar_code"].ToString(); proc.Parameters["id_PREFORM_DATETIME"].Value = DateTime.Now; proc.Parameters["in_SINA_MOUNT"].Value = "1"; proc.Parameters["iv_ADD_NURSE"].Value = ds.Tables[0].Rows[0]["iv_ADD_NURSE"].ToString(); proc.Parameters["in_stock_flag"].Value = ds.Tables[0].Rows[i]["in_stock_flag"].ToString(); proc.Parameters["on_price"].Value = ""; proc.Parameters["on_charges"].Value = ""; proc.Parameters["od_DISPENSING_DATE_TIME"].Value = DateTime.Now; proc.Parameters["on_exec_val"].Value = ""; proc.Parameters["ov_exec_txt"].Value = ""; proc.Connection.Open(); proc.ExecuteNonQuery(); if (proc.Parameters["on_exec_val"].Value.ToString() == "1") { proc.Connection.Close(); judgeflag = 1; continue; } else { proc.Connection.Close(); s = proc.Parameters["ov_exec_txt"].Value.ToString(); ret = proc.Parameters["on_exec_val"].Value.ToString(); judgeflag = 0; //com.CommandText = "rollback"; //com.ExecuteNonQuery(); return(ret); } } s = proc.Parameters["ov_exec_txt"].Value.ToString(); ret = proc.Parameters["on_exec_val"].Value.ToString(); if (judgeflag == 1) { trunsaction.Commit(); } else { trunsaction.Rollback(); } //com.CommandText = "commit"; //com.ExecuteNonQuery(); com.Connection.Close(); return(ret); } catch (System.Exception ex) { //com.CommandText = "rollback"; //com.ExecuteNonQuery(); trunsaction.Rollback(); com.Connection.Close(); s = ex.Message; return("0"); } finally { proc.Connection.Close(); } }
public void rechaza() { trans.Rollback(); }
protected void BtnSave_Click(object sender, EventArgs e) { using (OleDbConnection conn = new OleDbConnection(DBHelper.OleConnectionString)) { OleDbCommand cmd = new OleDbCommand("jp_demand_api.Modify_demand", conn); cmd.CommandType = CommandType.StoredProcedure; conn.Open(); OleDbTransaction tr = conn.BeginTransaction(); cmd.Transaction = tr; cmd.Parameters.Add("v_require_qty", OleDbType.Decimal).Value = Convert.ToDecimal(TxtRequireQty.Text); cmd.Parameters.Add("project_block", OleDbType.VarChar).Value = TxtBlock.Text; cmd.Parameters.Add("v_project_system", OleDbType.VarChar).Value = TxtSystem.Text; cmd.Parameters.Add("v_place", OleDbType.VarChar).Value = DdlProdSite.SelectedValue; cmd.Parameters.Add("v_place_description", OleDbType.VarChar).Value = DdlProdSite.SelectedItem.Text.ToString(); 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_crance", 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_psflag", OleDbType.VarChar).Value = ChkPS.Checked ? "1" : "0"; cmd.Parameters.Add("v_work_content", OleDbType.VarChar).Value = TxtWorkContent.Text; cmd.Parameters.Add("v_objid", OleDbType.VarChar).Value = objid.Value; cmd.Parameters.Add("v_rowversion", OleDbType.VarChar).Value = rowversion.Value; cmd.Parameters.Add("v_lack_type", OleDbType.VarChar).Value = DDL_QH.SelectedValue; cmd.Parameters.Add("v_msg", OleDbType.VarChar, 500).Direction = ParameterDirection.Output; try { cmd.ExecuteNonQuery(); tr.Commit(); Misc.RegisterClientScript(this.GetType(), "refresh", ClientScript, "<script>alert('数据更新成功!');window.opener.__doPostBack('BtnQuery','');window.close();</script>"); } catch (Exception ex) { tr.Rollback(); if (Misc.CheckIsDBCustomException(ex)) { Misc.RegisterClientScript(this.GetType(), "refresh", ClientScript, "<script>alert('" + Misc.GetDBCustomException(ex) + "');window.opener.__doPostBack('BtnQuery','');window.close();</script>"); //Misc.Message(this.GetType(), ClientScript, Misc.GetDBCustomException(ex)); } else { throw ex; } } finally { conn.Close(); } } //using (OleDbConnection conn = new OleDbConnection(DBHelper.OleConnectionString)) //{ // OleDbCommand cmd = new OleDbCommand(); // cmd.CommandText = string.Format("update jp_requisition set require_qty = to_number('{0}')," + // "project_block = '{1}'," + // "project_system = '{2}'," + // "place = '{3}'," + // "place_description = JP_RECEIPT_PLACE_API.get_name('{3}')," + // "receiver = '{4}'," + // "receiver_ic = '{5}'," + // "receive_date = to_date('{6}','yyyy-mm-dd')," + // "receiver_contact = '{7}'," + // "crane = '{8}'," + // "recorder = '{9}'," + // "receipt_dept='{10}'," + // "req_group='{11}'," + // "record_time = sysdate " + // " where rowstate='init' and requisition_id ='{12}'", // TxtRequireQty.Text, // TxtBlock.Text, // TxtSystem.Text, // DdlProdSite.SelectedValue, // DdlReceiptPerson.SelectedItem.Text, // TxtIC.Text, // TxtDate.Text, // TxtContact.Text, // ChkDz.Checked == true ? "1" : "0", // ((Authentication.LOGININFO)Session["USERINFO"]).UserID, // DdlReceiptDept.SelectedValue, // ((Authentication.LOGININFO)Session["USERINFO"]).GroupID, // req_id.Value); // cmd.Connection = conn; // cmd.CommandType = CommandType.Text; // if (conn.State != ConnectionState.Open) conn.Open(); // try // { // cmd.ExecuteNonQuery(); // this.ClientScript.RegisterStartupScript(this.GetType(), "refresh", "<script>alert('数据更新成功!');window.opener.__doPostBack('BtnQuery','');window.close();</script>"); // } // catch (Exception) // { // throw; // } // finally // { // conn.Close(); // } //} }
public int Add(Rain.Model.orders model) { using (OleDbConnection oleDbConnection = new OleDbConnection(DbHelperOleDb.connectionString)) { oleDbConnection.Open(); using (OleDbTransaction trans = oleDbConnection.BeginTransaction()) { try { StringBuilder stringBuilder1 = new StringBuilder(); stringBuilder1.Append("insert into " + this.databaseprefix + "orders("); stringBuilder1.Append("order_no,trade_no,user_id,user_name,payment_id,payment_fee,payment_status,payment_time,express_id,express_no,express_fee,express_status,express_time,accept_name,post_code,telphone,mobile,email,area,address,message,remark,is_invoice,invoice_title,invoice_taxes,payable_amount,real_amount,order_amount,point,status,add_time,confirm_time,complete_time)"); stringBuilder1.Append(" values ("); stringBuilder1.Append("@order_no,@trade_no,@user_id,@user_name,@payment_id,@payment_fee,@payment_status,@payment_time,@express_id,@express_no,@express_fee,@express_status,@express_time,@accept_name,@post_code,@telphone,@mobile,@email,@area,@address,@message,@remark,@is_invoice,@invoice_title,@invoice_taxes,@payable_amount,@real_amount,@order_amount,@point,@status,@add_time,@confirm_time,@complete_time)"); OleDbParameter[] oleDbParameterArray1 = new OleDbParameter[33] { new OleDbParameter("@order_no", OleDbType.VarChar, 100), new OleDbParameter("@trade_no", OleDbType.VarChar, 100), new OleDbParameter("@user_id", OleDbType.Integer, 4), new OleDbParameter("@user_name", OleDbType.VarChar, 100), new OleDbParameter("@payment_id", OleDbType.Integer, 4), new OleDbParameter("@payment_fee", OleDbType.Decimal, 5), new OleDbParameter("@payment_status", OleDbType.Integer, 4), new OleDbParameter("@payment_time", OleDbType.Date), new OleDbParameter("@express_id", OleDbType.Integer, 4), new OleDbParameter("@express_no", OleDbType.VarChar, 100), new OleDbParameter("@express_fee", OleDbType.Decimal, 5), new OleDbParameter("@express_status", OleDbType.Integer, 4), new OleDbParameter("@express_time", OleDbType.Date), new OleDbParameter("@accept_name", OleDbType.VarChar, 50), new OleDbParameter("@post_code", OleDbType.VarChar, 20), new OleDbParameter("@telphone", OleDbType.VarChar, 30), new OleDbParameter("@mobile", OleDbType.VarChar, 20), new OleDbParameter("@email", OleDbType.VarChar, 30), new OleDbParameter("@area", OleDbType.VarChar, 100), new OleDbParameter("@address", OleDbType.VarChar, 500), new OleDbParameter("@message", OleDbType.VarChar, 500), new OleDbParameter("@remark", OleDbType.VarChar, 500), new OleDbParameter("@is_invoice", OleDbType.Integer, 4), new OleDbParameter("@invoice_title", OleDbType.VarChar, 100), new OleDbParameter("@invoice_taxes", OleDbType.Decimal, 5), new OleDbParameter("@payable_amount", OleDbType.Decimal, 5), new OleDbParameter("@real_amount", OleDbType.Decimal, 5), new OleDbParameter("@order_amount", OleDbType.Decimal, 5), new OleDbParameter("@point", OleDbType.Integer, 4), new OleDbParameter("@status", OleDbType.Integer, 4), new OleDbParameter("@add_time", OleDbType.Date), new OleDbParameter("@confirm_time", OleDbType.Date), new OleDbParameter("@complete_time", OleDbType.Date) }; oleDbParameterArray1[0].Value = (object)model.order_no; oleDbParameterArray1[1].Value = (object)model.trade_no; oleDbParameterArray1[2].Value = (object)model.user_id; oleDbParameterArray1[3].Value = (object)model.user_name; oleDbParameterArray1[4].Value = (object)model.payment_id; oleDbParameterArray1[5].Value = (object)model.payment_fee; oleDbParameterArray1[6].Value = (object)model.payment_status; DateTime?nullable = model.payment_time; if (nullable.HasValue) { oleDbParameterArray1[7].Value = (object)model.payment_time; } else { oleDbParameterArray1[7].Value = (object)DBNull.Value; } oleDbParameterArray1[8].Value = (object)model.express_id; oleDbParameterArray1[9].Value = (object)model.express_no; oleDbParameterArray1[10].Value = (object)model.express_fee; oleDbParameterArray1[11].Value = (object)model.express_status; nullable = model.express_time; if (nullable.HasValue) { oleDbParameterArray1[12].Value = (object)model.express_time; } else { oleDbParameterArray1[12].Value = (object)DBNull.Value; } oleDbParameterArray1[13].Value = (object)model.accept_name; oleDbParameterArray1[14].Value = (object)model.post_code; oleDbParameterArray1[15].Value = (object)model.telphone; oleDbParameterArray1[16].Value = (object)model.mobile; oleDbParameterArray1[17].Value = (object)model.email; oleDbParameterArray1[18].Value = (object)model.area; oleDbParameterArray1[19].Value = (object)model.address; oleDbParameterArray1[20].Value = (object)model.message; oleDbParameterArray1[21].Value = (object)model.remark; oleDbParameterArray1[22].Value = (object)model.is_invoice; oleDbParameterArray1[23].Value = (object)model.invoice_title; oleDbParameterArray1[24].Value = (object)model.invoice_taxes; oleDbParameterArray1[25].Value = (object)model.payable_amount; oleDbParameterArray1[26].Value = (object)model.real_amount; oleDbParameterArray1[27].Value = (object)model.order_amount; oleDbParameterArray1[28].Value = (object)model.point; oleDbParameterArray1[29].Value = (object)model.status; oleDbParameterArray1[30].Value = (object)model.add_time; nullable = model.confirm_time; if (nullable.HasValue) { oleDbParameterArray1[31].Value = (object)model.confirm_time; } else { oleDbParameterArray1[31].Value = (object)DBNull.Value; } nullable = model.complete_time; if (nullable.HasValue) { oleDbParameterArray1[32].Value = (object)model.complete_time; } else { oleDbParameterArray1[32].Value = (object)DBNull.Value; } DbHelperOleDb.ExecuteSql(oleDbConnection, trans, stringBuilder1.ToString(), oleDbParameterArray1); model.id = this.GetMaxId(oleDbConnection, trans); if (model.order_goods != null) { foreach (order_goods orderGood in model.order_goods) { StringBuilder stringBuilder2 = new StringBuilder(); stringBuilder2.Append("insert into " + this.databaseprefix + "order_goods("); stringBuilder2.Append("article_id,order_id,goods_no,goods_title,img_url,spec_text,goods_price,real_price,quantity,point)"); stringBuilder2.Append(" values ("); stringBuilder2.Append("@article_id,@order_id,@goods_no,@goods_title,@img_url,@spec_text,@goods_price,@real_price,@quantity,@point)"); OleDbParameter[] oleDbParameterArray2 = new OleDbParameter[10] { new OleDbParameter("@article_id", OleDbType.Integer, 4), new OleDbParameter("@order_id", OleDbType.Integer, 4), new OleDbParameter("@goods_no", OleDbType.VarChar, 50), new OleDbParameter("@goods_title", OleDbType.VarChar, 100), new OleDbParameter("@img_url", OleDbType.VarChar, (int)byte.MaxValue), new OleDbParameter("@spec_text", (object)SqlDbType.Text), new OleDbParameter("@goods_price", OleDbType.Decimal, 5), new OleDbParameter("@real_price", OleDbType.Decimal, 5), new OleDbParameter("@quantity", OleDbType.Integer, 4), new OleDbParameter("@point", OleDbType.Integer, 4) }; oleDbParameterArray2[0].Value = (object)orderGood.article_id; oleDbParameterArray2[1].Value = (object)model.id; oleDbParameterArray2[2].Value = (object)orderGood.goods_no; oleDbParameterArray2[3].Value = (object)orderGood.goods_title; oleDbParameterArray2[4].Value = (object)orderGood.img_url; oleDbParameterArray2[5].Value = (object)orderGood.spec_text; oleDbParameterArray2[6].Value = (object)orderGood.goods_price; oleDbParameterArray2[7].Value = (object)orderGood.real_price; oleDbParameterArray2[8].Value = (object)orderGood.quantity; oleDbParameterArray2[9].Value = (object)orderGood.point; DbHelperOleDb.ExecuteSql(oleDbConnection, trans, stringBuilder2.ToString(), oleDbParameterArray2); StringBuilder stringBuilder3 = new StringBuilder(); stringBuilder3.Append("update " + this.databaseprefix + "article_attribute_value set "); stringBuilder3.Append("stock_quantity=stock_quantity-@stock_quantity where article_id=@article_id"); OleDbParameter[] oleDbParameterArray3 = new OleDbParameter[2] { new OleDbParameter("@stock_quantity", OleDbType.Integer, 4), new OleDbParameter("@article_id", OleDbType.Integer, 4) }; oleDbParameterArray3[0].Value = (object)orderGood.quantity; oleDbParameterArray3[1].Value = (object)orderGood.article_id; DbHelperOleDb.ExecuteSql(oleDbConnection, trans, stringBuilder3.ToString(), oleDbParameterArray3); } } trans.Commit(); } catch { trans.Rollback(); return(-1); } } } return(model.id); }
/// <summary> /// 增加一条数据及其子表 /// </summary> public int Add(Model.channel model) { //取得站点对应的导航ID int parent_id = 1;// new DAL.channel_site(databaseprefix).GetSiteNavId(model.site_id); //if (parent_id == 0) //{ // return 0; //} using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString)) { conn.Open(); using (OleDbTransaction trans = conn.BeginTransaction()) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into " + databaseprefix + "channel("); strSql.Append("site_id,[name],title,is_albums,is_attach,is_spec,sort_id)"); strSql.Append(" values ("); strSql.Append("@site_id,@name,@title,@is_albums,@is_attach,@is_spec,@sort_id)"); OleDbParameter[] parameters = { new OleDbParameter("@site_id", OleDbType.Integer, 4), new OleDbParameter("@name", OleDbType.VarChar, 50), new OleDbParameter("@title", OleDbType.VarChar, 100), new OleDbParameter("@is_albums", OleDbType.Integer, 4), new OleDbParameter("@is_attach", OleDbType.Integer, 4), new OleDbParameter("@is_spec", OleDbType.Integer, 4), new OleDbParameter("@sort_id", OleDbType.Integer, 4) }; parameters[0].Value = model.site_id; parameters[1].Value = model.name; parameters[2].Value = model.title; parameters[3].Value = model.is_albums; parameters[4].Value = model.is_attach; parameters[5].Value = model.is_spec; parameters[6].Value = model.sort_id; DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters); //取得新插入的ID model.id = GetMaxId(conn, trans); //扩展字段 if (model.channel_fields != null) { StringBuilder strSql2; foreach (Model.channel_field modelt in model.channel_fields) { strSql2 = new StringBuilder(); strSql2.Append("insert into " + databaseprefix + "channel_field("); strSql2.Append("channel_id,field_id)"); strSql2.Append(" values ("); strSql2.Append("@channel_id,@field_id)"); OleDbParameter[] parameters2 = { new OleDbParameter("@channel_id", OleDbType.Integer, 4), new OleDbParameter("@field_id", OleDbType.Integer, 4) }; parameters2[0].Value = model.id; parameters2[1].Value = modelt.field_id; DbHelperOleDb.ExecuteSql(conn, trans, strSql2.ToString(), parameters2); } } //添加视图 StringBuilder strSql3 = new StringBuilder(); strSql3.Append("CREATE VIEW view_channel_" + model.name + " as"); strSql3.Append(" SELECT " + databaseprefix + "article.*"); if (model.channel_fields != null) { foreach (Model.channel_field modelt in model.channel_fields) { Model.article_attribute_field fieldModel = new DAL.article_attribute_field(databaseprefix).GetModel(modelt.field_id); if (fieldModel != null) { strSql3.Append("," + databaseprefix + "article_attribute_value." + fieldModel.name); } } } strSql3.Append(" FROM " + databaseprefix + "article_attribute_value INNER JOIN"); strSql3.Append(" " + databaseprefix + "article ON " + databaseprefix + "article_attribute_value.article_id = " + databaseprefix + "article.id"); strSql3.Append(" WHERE " + databaseprefix + "article.channel_id=" + model.id); DbHelperOleDb.ExecuteSql(conn, trans, strSql3.ToString()); //添加导航菜单 //int newNavId = new DAL.navigation(databaseprefix).Add(conn, trans, parent_id, "channel_" + model.name, model.title, "", model.sort_id, model.id, "Show"); new DAL.navigation(databaseprefix).Add(conn, trans, parent_id, "channel_" + model.name + "_list", model.title, "article/article_list.aspx", model.sort_id, model.id, "Show,View,Add,Edit,Delete,Audit"); new DAL.navigation(databaseprefix).Add(conn, trans, 143, "channel_" + model.name + "_category", model.title + "栏目类别", "article/category_list.aspx", model.sort_id, model.id, "Show,View,Add,Edit,Delete"); //new DAL.navigation(databaseprefix).Add(conn, trans, newNavId, "channel_" + model.name + "_comment", "评论管理", "article/comment_list.aspx", 101, model.id, "Show,View,Delete,Reply"); trans.Commit(); } catch { trans.Rollback(); return(0); } } } return(model.id); }
public bool Update(Rain.Model.article_attribute_field model) { using (OleDbConnection connection = new OleDbConnection(DbHelperOleDb.connectionString)) { connection.Open(); using (OleDbTransaction trans = connection.BeginTransaction()) { try { StringBuilder stringBuilder1 = new StringBuilder(); stringBuilder1.Append("update " + this.databaseprefix + "article_attribute_field set "); stringBuilder1.Append("[name]=@name,"); stringBuilder1.Append("title=@title,"); stringBuilder1.Append("control_type=@control_type,"); stringBuilder1.Append("data_type=@data_type,"); stringBuilder1.Append("data_length=@data_length,"); stringBuilder1.Append("data_place=@data_place,"); stringBuilder1.Append("item_option=@item_option,"); stringBuilder1.Append("default_value=@default_value,"); stringBuilder1.Append("is_required=@is_required,"); stringBuilder1.Append("is_password=@is_password,"); stringBuilder1.Append("is_html=@is_html,"); stringBuilder1.Append("editor_type=@editor_type,"); stringBuilder1.Append("valid_tip_msg=@valid_tip_msg,"); stringBuilder1.Append("valid_error_msg=@valid_error_msg,"); stringBuilder1.Append("valid_pattern=@valid_pattern,"); stringBuilder1.Append("sort_id=@sort_id,"); stringBuilder1.Append("is_sys=@is_sys"); stringBuilder1.Append(" where id=@id"); OleDbParameter[] oleDbParameterArray = new OleDbParameter[18] { new OleDbParameter("@name", OleDbType.VarChar, 100), new OleDbParameter("@title", OleDbType.VarChar, 100), new OleDbParameter("@control_type", OleDbType.VarChar, 50), new OleDbParameter("@data_type", OleDbType.VarChar, 50), new OleDbParameter("@data_length", OleDbType.Integer, 4), new OleDbParameter("@data_place", OleDbType.Integer, 4), new OleDbParameter("@item_option", OleDbType.VarChar), new OleDbParameter("@default_value", OleDbType.VarChar), new OleDbParameter("@is_required", OleDbType.Integer, 4), new OleDbParameter("@is_password", OleDbType.Integer, 4), new OleDbParameter("@is_html", OleDbType.Integer, 4), new OleDbParameter("@editor_type", OleDbType.Integer, 4), new OleDbParameter("@valid_tip_msg", OleDbType.VarChar, (int)byte.MaxValue), new OleDbParameter("@valid_error_msg", OleDbType.VarChar, (int)byte.MaxValue), new OleDbParameter("@valid_pattern", OleDbType.VarChar, 500), new OleDbParameter("@sort_id", OleDbType.Integer, 4), new OleDbParameter("@is_sys", OleDbType.Integer, 4), new OleDbParameter("@id", OleDbType.Integer, 4) }; oleDbParameterArray[0].Value = (object)model.name; oleDbParameterArray[1].Value = (object)model.title; oleDbParameterArray[2].Value = (object)model.control_type; oleDbParameterArray[3].Value = (object)model.data_type; oleDbParameterArray[4].Value = (object)model.data_length; oleDbParameterArray[5].Value = (object)model.data_place; oleDbParameterArray[6].Value = (object)model.item_option; oleDbParameterArray[7].Value = (object)model.default_value; oleDbParameterArray[8].Value = (object)model.is_required; oleDbParameterArray[9].Value = (object)model.is_password; oleDbParameterArray[10].Value = (object)model.is_html; oleDbParameterArray[11].Value = (object)model.editor_type; oleDbParameterArray[12].Value = (object)model.valid_tip_msg; oleDbParameterArray[13].Value = (object)model.valid_error_msg; oleDbParameterArray[14].Value = (object)model.valid_pattern; oleDbParameterArray[15].Value = (object)model.sort_id; oleDbParameterArray[16].Value = (object)model.is_sys; oleDbParameterArray[17].Value = (object)model.id; DbHelperOleDb.ExecuteSql(connection, trans, stringBuilder1.ToString(), oleDbParameterArray); StringBuilder stringBuilder2 = new StringBuilder(); stringBuilder2.Append("alter table " + this.databaseprefix + "article_attribute_value alter column " + model.name + " " + model.data_type); DbHelperOleDb.ExecuteSql(connection, trans, stringBuilder2.ToString()); trans.Commit(); } catch { trans.Rollback(); return(false); } } } return(true); }
public void SetAwardCount(HttpContext context, OleDbConnection conn) { int iPrizeID = 0; Int32.TryParse(context.Request["PrizeID"].ToString(), out iPrizeID); Dal.Models.Prize prize = BLL.Prize.GetPrize(iPrizeID, conn); OleDbTransaction tran = conn.BeginTransaction(); try { int iAwardCount = 0; if (prize.PrizeTypeCode == "0302" || prize.PrizeTypeCode == "0303") { if (int.TryParse(context.Request["AwardCount"], out iAwardCount)) { BLL.Prize.SetPrizeLevel( new Dal.Models.PrizeLevel() { LevelCode = prize.PrizeTypeCode == "0302" ? "0404" : "0405", LevelLimitNumber = iAwardCount, PrizeID = prize.PrizeID } , conn, tran); } else { throw new Exception("请正确输入获奖数量!"); } } else { if (int.TryParse(context.Request["FirstPrize"], out iAwardCount)) { prize.FirstPrize = iAwardCount; BLL.Prize.SetPrizeLevel( new Dal.Models.PrizeLevel() { LevelCode = "0401", LevelLimitNumber = iAwardCount, PrizeID = prize.PrizeID } , conn, tran); } else { throw new Exception("请正确输入获奖数量!"); } if (int.TryParse(context.Request["SecondPrize"], out iAwardCount)) { BLL.Prize.SetPrizeLevel( new Dal.Models.PrizeLevel() { LevelCode = "0402", LevelLimitNumber = iAwardCount, PrizeID = prize.PrizeID } , conn, tran); } else { throw new Exception("请正确输入获奖数量!"); } if (int.TryParse(context.Request["ThirdPrize"], out iAwardCount)) { BLL.Prize.SetPrizeLevel( new Dal.Models.PrizeLevel() { LevelCode = "0403", LevelLimitNumber = iAwardCount, PrizeID = prize.PrizeID } , conn, tran); } else { throw new Exception("请正确输入获奖数量!"); } tran.Commit(); context.Response.Write("获奖数量设定成功!"); } } catch (Exception ex) { tran.Rollback(); context.Response.Write(ex.Message); } }
public static bool UpdateLocalTable() { DataSet ds = null; try { //获取数据 ds = WSCommon.GetLocalCache(); if (ds == null || ds.Tables.Count < 2) { throw new Exception("获取数据失败!"); } else { OleDbConnection con = new OleDbConnection(localConStr); con.Open(); OleDbTransaction otran = con.BeginTransaction(); try { //otran.Begin(); String strSQL = "DELETE FROM COMPANY"; OleDbCommand cmd = new OleDbCommand(strSQL, con); cmd.Transaction = otran; cmd.ExecuteNonQuery(); //更新COMPANY表 foreach (DataRow dr in ds.Tables["COMPANY"].Rows) { strSQL = "INSERT INTO COMPANY (NAME,CODE,SHORTNAME,TXNTYPE)VALUES(@NAME,@CODE,@SHORTNAME,@TXNTYPE)"; cmd = new OleDbCommand(strSQL, con); cmd.Parameters.Add(new OleDbParameter("@CODE", dr["CODE"])); cmd.Parameters.Add(new OleDbParameter("@NAME", dr["NAME"])); cmd.Parameters.Add(new OleDbParameter("@SHORTNAME", dr["SHORTNAME"])); cmd.Parameters.Add(new OleDbParameter("@TXNTYPE", dr["TXNTYPE"])); cmd.Transaction = otran; cmd.ExecuteNonQuery(); } //更新CLASS表 strSQL = "DELETE FROM CLASS"; cmd = new OleDbCommand(strSQL, con); cmd.Transaction = otran; cmd.ExecuteNonQuery(); foreach (DataRow dr in ds.Tables["CLASS"].Rows) { strSQL = "INSERT INTO CLASS (CLSCODE,CLSNAME)VALUES(@CLSCODE,@CLSNAME)"; cmd = new OleDbCommand(strSQL, con); cmd.Parameters.Add(new OleDbParameter("@CLSCODE", dr["CLSCODE"])); cmd.Parameters.Add(new OleDbParameter("@CLSNAME", dr["CLSNAME"])); cmd.Transaction = otran; cmd.ExecuteNonQuery(); } otran.Commit(); con.Close(); return(true); } catch (Exception ex) { otran.Rollback(); return(false); } finally { if (con.State == ConnectionState.Open) { con.Close(); } } } } catch (Exception ex) { throw new Exception("ERROR:" + ex.Message); } return(false); }
/// <summary> /// 回滚一个事务 /// </summary> public void RollbackTrans() { trans.Rollback(); }
protected void btnNext_Click(object sender, EventArgs e) { Dal.Models.UserInfo user = (Dal.Models.UserInfo)Session["UserInfo"]; // 创建申报项目 Dal.Models.Declaration decl = null; bool bChanged = false; string strTemplateCode = ""; DateTime dtm; if (!DateTime.TryParse(txtDeclarateDate.Text, out dtm)) { hdMsg.Value = "项目申报时间格式错误!"; return; } int iPrizeID = Convert.ToInt32(Request["rdlPrize"]); if (iPrizeID == 0 && rdlPrize.SelectedValue != "") { iPrizeID = Convert.ToInt32(rdlPrize.SelectedValue); } if (iPrizeID == 0) { hdMsg.Value = "请选择奖项!"; return; } OleDbConnection conn = new OleDbConnection(Dal.OleDbHlper.ConnectionString); conn.Open(); string strActivityType = Request.Cookies["ActivityType"].Value; Dal.Models.Activity actActivated = BLL.Activity.GetActivity(strActivityType, conn); string strDeclarationCode = ""; if (ViewState["DeclarationID"] != null) { decl = BLL.Declaration.GetDeclaration(Convert.ToInt32(ViewState["DeclarationID"]), conn); strDeclarationCode = decl.DeclarationCode; } else { decl = new Dal.Models.Declaration(); decl.UserID = user.UserID; decl.ActivityID = actActivated.ActivityID; } if (decl.DeclareDate == null || decl.DeclareDate.Value != dtm) { bChanged = true; decl.DeclareDate = dtm; } if (string.IsNullOrEmpty(decl.RecommendFrom) || decl.RecommendFrom != ChooseAssociation.SelectedValue) { bChanged = true; decl.RecommendFrom = ChooseAssociation.SelectedValue; } if (decl.PrizeID == null || decl.PrizeID != iPrizeID) { bChanged = true; decl.PrizeID = iPrizeID; } if (string.IsNullOrEmpty(decl.DeclarationName) || decl.DeclarationName != txtProjectName.Text.Trim()) { bChanged = true; decl.DeclarationName = txtProjectName.Text.Trim(); } Dal.Models.Prize prize = BLL.Prize.GetPrize(decl.PrizeID.Value, conn); strTemplateCode = prize.TemplateCode; int iSpecilID = prize.SpecialtyID.Value; int actID = prize.ActivityID.Value; Dal.Models.Schedule dSchedule = BLL.Schedule.GetSpecialtySchedule(iSpecilID, conn); if (dSchedule == null || !dSchedule.IsDeclarationStart.Value) { hdMsg.Value = "未在专业申报时间内,不能创建!"; conn.Close(); return; } string strSituation = ""; if (!string.IsNullOrEmpty(Request["Situation"])) { strSituation = Request["Situation"].Replace("'", "").Replace("\"", ""); } if (bChanged == true && (strSituation == Dal.Models.EnumSituation.Declaration.ToString() || strSituation == "")) { OleDbTransaction trans = conn.BeginTransaction(); try { BLL.Declaration.Declarate(ref decl, conn, trans); string strImportDeclarationID = ImportDeclarationData.SelectedValue; if (strImportDeclarationID != "0" && strImportDeclarationID != "") { string strDeclarationCode2 = BLL.Declaration.GetDeclaration(Convert.ToInt32(strImportDeclarationID), conn, trans).DeclarationCode; BLL.Declaration.ImportDeclarate(decl.DeclarationID.Value, Convert.ToInt32(strImportDeclarationID), conn, trans); BLL.Declaration.ImportUploadFile(decl.DeclarationID.Value, Convert.ToInt32(strImportDeclarationID), decl.DeclarationCode, strDeclarationCode2, conn, trans); } trans.Commit(); ViewState["DeclarationID"] = decl.DeclarationID.Value; } catch (Exception ex) { ViewState.Remove("DeclarationID"); trans.Rollback(); hdMsg.Value = ex.Message; return; } } // 获取申报项目的模板首页 string strPartCode = strTemplateCode.Substring(2, 2) + "01"; // 根据首页获取模板 TempInfo = BLL.Declaration.GetTemplateInfo(strPartCode, conn); TempInfo.DeclarationID = (int)ViewState["DeclarationID"]; TempInfo.DeclarationCode = strDeclarationCode; if (!string.IsNullOrEmpty(strSituation)) { TempInfo.Situation = (Dal.Models.EnumSituation)Enum.Parse(typeof(Dal.Models.EnumSituation), strSituation, true); } else { TempInfo.Situation = (Dal.Models.EnumSituation)Enum.Parse(typeof(Dal.Models.EnumSituation), "Declara", true); } conn.Close(); if (TempInfo != null) { Session.Add("TemplateInfo", TempInfo); // 跳转到模板页 if (strSituation == "ExpertReview" || strSituation == "Delete" || strSituation == "Auditing") { Server.Transfer(TempInfo.NewTemplateUrl); } else { Server.Transfer(TempInfo.TemplateUrl); } } }
public void Rollback() { Transaction.Rollback(); }
private void btnSave_Click(object sender, EventArgs e) { OleDbTransaction transObj = null; string DiscountInput; try { if (tbxQuantity.Text == "0") { tbxQuantity.BackColor = Color.Red; tbxQuantity.Focus(); throw new Exception("Please Enter Item Quantity"); } if (tbxQuantity.BackColor == Color.Red) { tbxQuantity.Focus(); throw new Exception("Only Digits are allowed for Quantity. Try again"); } if (tbxUnitPrice.Text == "0") { tbxUnitPrice.BackColor = Color.Red; tbxUnitPrice.Focus(); throw new Exception("Please Set Unit Price"); } transObj = DatabaseConnectionObject.connectionObj_Global.BeginTransaction(); // DataObjectsInitializer PurchaseTableDataObj = new DataObjectsInitializer("Purchases", DatabaseConnectionObject.datasetObject_Global, transObj); string prefix = DatabaseConnectionObject.datasetObject_Global.Tables["TableIDNumbers"].Rows.Find("TabPurchases")["Table Prefix"].ToString(); string num = DatabaseConnectionObject.datasetObject_Global.Tables["TableIDNumbers"].Rows.Find("TabPurchases")["Next ID Number"].ToString(); string SelectedUnit = UnitIDDictionary[cbxUnitID.SelectedItem.ToString()]; //string unitPrice = DatabaseConnectionObject.datasetObject_Global.Tables["Units"].Rows.Find(SelectedUnit)["Unit Price"].ToString(); Dictionary <String, Object> Dic2Use = new Dictionary <string, object> { }; string finalPrice = removeUnwantedCharacters(lblFinalPrice.Text, new char[] { ',' }); //DataRow NewRow_sales = DatabaseConnectionObject.datasetObject_Global.Tables["Purchases"].NewRow(); Dic2Use.Add("PurchaseID", prefix + num); Dic2Use.Add("Supplier Contact", tbxSuppliersContact.Text); Dic2Use.Add("Item ID", DeleteDictionary[cbxItemID.SelectedItem.ToString()]); Dic2Use.Add("Date", new DateTimeUtility().ToEpochFormat(DateTime.Now.ToLocalTime())); Dic2Use.Add("Unit ID/ Unit Price", cbxUnitID.SelectedItem.ToString() + "/" + tbxUnitPrice.Text); Dic2Use.Add("Quantity", tbxQuantity.Text); Dic2Use.Add("Supplier Name", tbxSupplierName.Text); Dic2Use.Add("Cost", finalPrice); new DataTableManager("Purchases", DatabaseConnectionObject.datasetObject_Global, transObj, GlobalVariables.CurrentSessionID).CreateRow(Dic2Use, Dic2Use["PurchaseID"].ToString()); String NextIDNmber = DatabaseConnectionObject.datasetObject_Global.Tables["TableIDNumbers"].Rows.Find("TabPurchases")["Next ID Number"].ToString(); NextIDNmber = (long.Parse(NextIDNmber) + 1).ToString(); Dic2Use.Clear(); Dic2Use.Add("Next ID Number", NextIDNmber); new DataTableManager("TableIDNumbers", DatabaseConnectionObject.datasetObject_Global, transObj, GlobalVariables.CurrentSessionID).EditRow("TabPurchases", Dic2Use); transObj.Commit(); MessageBox.Show("Purchase Successfully Saved", "Success"); List <string> ItemIdList = new List <string> { }; DeleteDictionary = new Dictionary <string, string> { }; ItemIdList.Add("< Select Item ID >"); foreach (DataRow dtRow in DatabaseConnectionObject.datasetObject_Global.Tables["Items"].Rows) { ItemIdList.Add(dtRow["Item ID"].ToString() + " - " + dtRow["Item Name"].ToString()); DeleteDictionary.Add(dtRow["Item ID"].ToString() + " - " + dtRow["Item Name"].ToString(), dtRow["Item ID"].ToString()); } cbxItemID.DataSource = ItemIdList; DeleteList = ItemIdList; List <string> UnitIdList = new List <string> { }; UnitIDDictionary = new Dictionary <string, string> { }; UnitIdList.Add("< Select Item Unit >"); cbxUnitID.DataSource = UnitIdList; UnitIDList = UnitIdList; btnSave.Enabled = false; cbxUnitID.Enabled = false; tbxQuantity.Enabled = false; tbxSupplierName.Enabled = false; tbxSuppliersContact.Enabled = false; tbxUnitPrice.Enabled = false; tbxUnitPrice.Text = "0"; tbxQuantity.Enabled = false; tbxQuantity.Text = "0"; btnClear.Enabled = false; btnSave.Enabled = false; lblFinalPrice.Text = "0"; btnSave.Enabled = false; } catch (Exception ex) { if (transObj != null) { transObj.Rollback(); } MessageBox.Show("Error Saving Sale. " + ex.Message.ToString(), "Error Occured"); } }
//public void FinishVote(HttpContext context, OleDbConnection conn) //{ // Dal.Models.UserInfo user = (Dal.Models.UserInfo)context.Session["UserInfo"]; // //Issubmit="1",是投票结果的状态为提交,专家不能再更改投票结果,投票时,此字段默认值为0 // string strIsSubmit = "1"; // //获取当前活动类型编码 // string strActivityType = context.Session["ActivityType"].ToString(); // //获取当前活动ID // int iActivityID = BLL.Activity.GetActivity(strActivityType, conn).ActivityID.Value; // //获取当前活动时间节点 // Dal.Models.Schedule schedule = BLL.Schedule.GetActivitySchedule(iActivityID, conn); // //获取所有本次活动的奖项 // List<Dal.Models.Prize> lstPrize = BLL.Prize.GetPrizeList(user, strActivityType, "", conn); // //修改Schedule的投票阶段 // BLL.Vote.UpdateScheduleVoteFinish(schedule.ScheduleID.Value, false, conn); // foreach (var item in lstPrize) // { // List<Dal.Models.Vote> lstVote = BLL.Vote.GetActivityPrizeVoteList(item.PrizeID.Value, conn); // foreach (var Vote in lstVote) // { // //修改Vote为已经提交 // BLL.Vote.SubmitVote(Vote.VoteID.Value, strIsSubmit, conn); // } // } // context.Response.Write("投票已提交"); //} private void Ballot(HttpContext context, OleDbConnection conn) { Dal.Models.UserInfo user = (Dal.Models.UserInfo)context.Session["UserInfo"]; Dal.Models.Expert expert = BLL.Expert.GetExpertByUserID(user.UserID.Value, conn); OleDbTransaction tran = conn.BeginTransaction(); try { int iDeclarationID = 0; int iPrizeID = 0; int iMultipleVoteRound = 0; if (!int.TryParse(context.Request["DeclarationID"], out iDeclarationID) || !int.TryParse(context.Request["PrizeID"], out iPrizeID) || !int.TryParse(context.Request["MultipleVoteRound"], out iMultipleVoteRound)) { throw new Exception("参数错误!"); } Dal.Models.Prize prize = BLL.Prize.GetPrize(iPrizeID, conn, tran); if (prize == null || prize.IsStopVote) { throw new Exception("当前奖项及轮次已结束投票。"); } string strVoteCode = context.Request["VoteCode"]; string strLevelCode = context.Request["LevelCode"]; if (strLevelCode == "0404") { strLevelCode = ""; } Dal.Models.Declaration decl = BLL.Declaration.GetDeclaration(iDeclarationID, conn, tran); if (decl.PrizeID != iPrizeID) { throw new Exception("参数错误!"); } prize = BLL.Prize.GetPrize(decl.AdjustedPrize.Value, conn, tran); if (prize.MultipleVoteRound != iMultipleVoteRound) { throw new Exception("当前已进入新一轮投票,请重新进入本页面进行投票!"); } Dal.Models.Vote vote = new Dal.Models.Vote() { UserID = user.UserID.Value, Votor = expert.ExpertID.Value, DeclarationID = iDeclarationID, ActivityID = decl.ActivityID.Value, MultipleVoteRound = prize.MultipleVoteRound ?? 1, RevoteCode = prize.RevoteCode ?? "", RevoteRound = prize.RevoteRound ?? 0, IsSubmit = false, PrizeID = prize.PrizeID.Value, VoteCode = strVoteCode, PrizeLevelCode = strLevelCode, VoteType = "0502" }; BLL.Vote.Ballot(vote, conn, tran); //根据专家和奖项获评奖项目 List <Dal.Models.Vote> lstVote = BLL.Vote.GetVoteDeclarationList(expert, prize, conn, tran); //获取每个奖项的已评奖数量 Dictionary <string, int> dicVote = new Dictionary <string, int>(); //获取每个奖项未评奖数量 Dictionary <string, int> dicRemain = new Dictionary <string, int>(); GetLimit(prize, lstVote, ref dicVote, ref dicRemain); string Message = ""; //if (strVoteCode == "0601" && (dicRemain["Recommended"] == 0 // || (strLevelCode == "0401" && dicRemain["FirstPrize"] == 0) // || (strLevelCode == "0402" && dicRemain["SecondPrize"] == 0) // || (strLevelCode == "0403" && dicRemain["ThirdPrize"] == 0))) //{ // Message = "所投选项名额已满,请注意!"; //} //else //{ Message = "投票成功!"; //} tran.Commit(); object[] obj = { prize, lstVote, dicVote, dicRemain, Message }; context.Response.Write(JsonConvert.SerializeObject(obj)); } catch (Exception ex) { tran.Rollback(); context.Response.Write(JsonConvert.SerializeObject(new object[] { ex.Message })); } }
private static void RollbackTransaction() { myTransaction?.Rollback(); myTransaction = null; }
/// <summary> /// 执行多条sql语句或特殊语句 /// </summary> /// <param name="SQL">sql脚本</param> /// <param name="IsTransAction">是否开启事务 默认true</param> /// <returns>受影响条数</returns> public int DBOther(string SQL, bool IsTransAction = true) { var num = 0; if (!string.IsNullOrEmpty(SQL) && DBABase != null) { DBTypeEnum.DBType dbtype = (DBTypeEnum.DBType)DBTypeValue; switch (dbtype) { case DBTypeEnum.DBType.Oracle: var tupOracle = DBABase.GetOracleConnnect(SQL); using (var connection = tupOracle.Item1) { try { var cmd = tupOracle.Item2; var adapt = tupOracle.Item3; if (IsTransAction) { using (OracleTransaction orclTrans = connection.BeginTransaction(IsolationLevel.ReadCommitted)) { try { num = cmd.ExecuteNonQuery(); orclTrans.Commit(); } catch (Exception ex) { orclTrans.Rollback(); throw ex; } } } else { num = cmd.ExecuteNonQuery(); } connection.Close(); } catch (Exception ex) { throw ex; } finally { if (connection.State != ConnectionState.Closed) { connection.Close(); } } } break; case DBTypeEnum.DBType.Access: var tupAccess = DBABase.GetAccessConnnect(SQL); using (var connection = tupAccess.Item1) { try { var cmd = tupAccess.Item2; var adapt = tupAccess.Item3; if (IsTransAction) { using (OleDbTransaction orclTrans = connection.BeginTransaction(IsolationLevel.ReadCommitted)) { try { num = cmd.ExecuteNonQuery(); orclTrans.Commit(); } catch (Exception ex) { orclTrans.Rollback(); throw ex; } } } else { num = cmd.ExecuteNonQuery(); } connection.Close(); } catch (Exception ex) { throw ex; } finally { if (connection.State != ConnectionState.Closed) { connection.Close(); } } } break; case DBTypeEnum.DBType.SqlServer: var tupSqlServer = DBABase.GetSqlServerConnnect(SQL); using (var connection = tupSqlServer.Item1) { try { var cmd = tupSqlServer.Item2; var adapt = tupSqlServer.Item3; if (IsTransAction) { using (SqlTransaction orclTrans = connection.BeginTransaction(IsolationLevel.ReadCommitted)) { try { num = cmd.ExecuteNonQuery(); orclTrans.Commit(); } catch (Exception ex) { orclTrans.Rollback(); throw ex; } } } else { num = cmd.ExecuteNonQuery(); } connection.Close(); } catch (Exception ex) { throw ex; } finally { if (connection.State != ConnectionState.Closed) { connection.Close(); } } } break; case DBTypeEnum.DBType.Sqlite: var tupSqlite = DBABase.GetSqliteConnnect(SQL); using (var connection = tupSqlite.Item1) { try { var cmd = tupSqlite.Item2; var adapt = tupSqlite.Item3; if (IsTransAction) { using (SQLiteTransaction orclTrans = connection.BeginTransaction(IsolationLevel.ReadCommitted)) { try { num = cmd.ExecuteNonQuery(); orclTrans.Commit(); } catch (Exception ex) { orclTrans.Rollback(); throw ex; } } } else { num = cmd.ExecuteNonQuery(); } connection.Close(); } catch (Exception ex) { throw ex; } finally { if (connection.State != ConnectionState.Closed) { connection.Close(); } } } break; } } return(num); }