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