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