private DataTable dt; //表以及对应的描述信息
public OracleDAL(string conn)
{
help = new DbHelp(DbProviderFactories.GetFactory("System.Data.OracleClient"), conn);
var strSql = @"select ROWNUM 序号 ,ut.table_name 表名,utc.comments 表说明
from user_tables ut
left join user_tab_comments utc on ut.table_name = utc.table_name
where utc.table_name not like '%$%'
order by ut.table_name
";
help.BeginTransation();
//查询所有表名
dt = help.ExecuteSql(strSql);
//Oracle 主键关系表，使用临时表来管理
var keyTempTable = @"CREATE GLOBAL TEMPORARY TABLE table_key_constraints as
SELECT col.table_name, col.column_name from user_constraints con,user_cons_columns col
WHERE con.constraint_name=col.constraint_name and con.constraint_type='P' AND 1<>1";
var dropTempTable = @"DROP TABLE table_key_constraints";
var exportTmpTable = @"INSERT INTO table_key_constraints SELECT col.table_name, col.column_name from user_constraints con,user_cons_columns col
WHERE con.constraint_name=col.constraint_name and con.constraint_type='P' ";
//删除临时表
try
{
help.ExecuteNonQuery(dropTempTable);
}
catch (Exception)
{
help.BeginTransation();
}
//创建临时表
help.ExecuteNonQuery(keyTempTable);
//导入临时表数据
help.ExecuteNonQuery(exportTmpTable);
strSql = @" select row_number()over( partition by utc.table_name order by utc.COLUMN_ID, ROWNUM ) as 序号,
utc.table_name as 表名,
utc.column_name as 列名,
utc.data_type as 数据类型,
utc.data_length as 长度,
utc.data_precision as 精度,
utc.data_Scale 小数位数,
case ( select count(*) from table_key_constraints where column_name = utc.COLUMN_NAME and table_name=upper(utc.table_name) )
when 0 then '' else '√'
end as 主键,
case when utc.nullable = 'Y' then '√' else '' end as 允许空,
utc.data_default as 默认值,
ucc.comments as 列说明
from
user_tab_columns utc,user_col_comments ucc
where utc.table_name = ucc.table_name and utc.column_name = ucc.column_name
and utc.table_name not like '%$%' and utc.table_name not like '%plsql%'
order by utc.table_name,序号";
dtStruct = help.ExecuteSql(strSql);
//删除临时表
help.ExecuteNonQuery(dropTempTable);
help.Commit();
}
/// <summary>
/// 删除事件
/// </summary>
protected void gvInfo_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
DbHelp db = new DbHelp();
string strID = gvInfo.DataKeys[e.RowIndex].Value.ToString();//获取主键列的值
int num = db.ExecuteNonQuery("delete from tb_email where id = '" + strID + "'");
if (num > 0)
{
string script = "";
script += "<script language='javascript'>";
script += "alert('数据保存成功!');";
script += " opener.__doPostBack('" + this + "','');";
script += " this.close();";
script += "</script>";
Page.RegisterStartupScript("RefreshSourceWindowAndCloseMe", script);
setDataSource();
}
}
public int deleteTaskByUser(string userid)
{
DbHelp db = new DbHelp();
return(db.ExecuteNonQuery("delete [sde].[sde].sdtask where userid = '" + userid + "'"));
}
public int deleteTask(string factoryId)
{
DbHelp db = new DbHelp();
return(db.ExecuteNonQuery("delete [sde].[sde].sdtask where factoryId = '" + factoryId + "'"));
}
/// <summary>
/// 点击保存或者添加
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnSave_Click(object sender, EventArgs e)
{
string Code = this.txtCode.Text.Trim();
string ProductCode = this.txtProductCode.Text.Trim();
string Name = this.txtName.Text.Trim();
string Unit = this.txtUnit.Text.Trim();
string producer = this.txtproducer.Text.Trim();
DbHelp db = new DbHelp();
Dictionary <string, string> dataMap = new Dictionary <string, string>();
dataMap.Add("Code", Code);
dataMap.Add("ProductCode", ProductCode);
dataMap.Add("Name", Name);
dataMap.Add("Unit", Unit);
dataMap.Add("producer", producer);
bool isok = false;
if (strID.Length < 1)//添加
{
//重复的不添加
DataSet ds = db.Query("select * from tb_dishesFuding where name ='" + Name + "' and IsAction = 1");
if (null != ds && ds.Tables[0].Rows.Count > 0)
{
string script = "";
script += "<script language='javascript'>";
script += "alert('菜品已经存在,不能添加!');";
script += "</script>";
Page.RegisterStartupScript("", script);
return;
}
else
{
//没有激活就激活
DataSet ds2 = db.Query("select * from tb_dishesFuding where name ='" + Name + "' and IsAction = 0");
if (null != ds2 && ds2.Tables[0].Rows.Count > 0)
{
string id = ds2.Tables[0].Rows[0]["id"].ToString();
dataMap.Add("IsAction", "1");
isok = db.UpdateDataWkt("tb_dishesFuding", dataMap, "where id ='" + id + "'");
}
else
{
string ColName = PinYinConverter.Get(Name);
dataMap.Add("id", Guid.NewGuid().ToString());
dataMap.Add("IsAction", "1");
dataMap.Add("ColName", ColName);
int num = db.InsertData("tb_dishesFuding", dataMap);
if (num > 0)
{
isok = true;
//添加列到数据表中
StringBuilder strbld = new StringBuilder();
strbld.Append("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tb_orderFuding]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)");
strbld.Append("if (NOT exists ( select * from dbo.syscolumns where name = '" + ColName + "' and id in ");
strbld.Append("(select id from dbo.sysobjects where id = object_id(N'[dbo].[tb_orderFuding]') and OBJECTPROPERTY(id, N'IsUserTable') = 1))");
strbld.Append(") ");
strbld.Append("ALTER TABLE tb_orderFuding ADD " + ColName + " int NULL");
string sql = strbld.ToString();
int num2 = db.ExecuteNonQuery(sql);
}
}
}
}
else
{
//编辑
isok = db.UpdateDataWkt("tb_dishesFuding", dataMap, "where id ='" + strID + "'");
}
if (isok)
{
string script = "";
script += "<script language='javascript'>";
script += "alert('数据保存成功!');";
script += " opener.__doPostBack('" + "btnRefresh" + "','');";
script += " this.close();";
script += "</script>";
Page.RegisterStartupScript("RefreshSourceWindowAndCloseMe", script);
}
else
{
string script = "";
script += "<script language='javascript'>";
script += "alert('数据保存失败!');";
script += " opener.__doPostBack('" + "btnRefresh" + "','');";
script += " this.close();";
script += "</script>";
Page.RegisterStartupScript("RefreshSourceWindowAndCloseMe", script);
}
}