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(); }
private DataTable dt; //表以及对应的描述信息 public SqlDAL(string conn, int type = 2012) { help = new DbHelp(DbProviderFactories.GetFactory("System.Data.SqlClient"), conn); var strSql = @" SELECT TOP 100 PERCENT d.name as 表名, a.colorder AS 序号, a.name AS 列名, b.name AS 数据类型, a.length AS 长度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数, CASE WHEN COLUMNPROPERTY(a.id,a.name, 'IsIdentity') = 1 THEN '是' ELSE '' END AS 标识, CASE WHEN EXISTS (SELECT 1 FROM dbo.sysindexes si INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK' WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主键, CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允许空, ISNULL(e.text, '') AS 默认值, ISNULL(g.[value], '') AS 列说明 FROM dbo.syscolumns a LEFT OUTER JOIN dbo.systypes b ON a.xtype = b.xusertype INNER JOIN dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.status >= 0 LEFT OUTER JOIN dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id AND g.name = 'MS_Description' LEFT OUTER JOIN sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0 AND f.name = 'MS_Description' ORDER BY d.name, 序号 "; dtStruct = help.ExecuteSql(strSql); // if (type == 2012) // strSql = @"select Row_Number() over ( order by getdate() ) as 序号, t1.name as 表名, // case when t2.minor_id = 0 then isnull(t2.value, '') else '' end as 表说明 //from sysobjects t1 //left join sys.extended_properties t2 on t1.id=t2.major_id //where type='u' and ( minor_id=0 or minor_id is null )"; // else if (type == 2008 || type == 2005) strSql = @"SELECT Row_Number() over ( order by getdate() ) as 序号, case when a.colorder = 1 then d.name else '' end as 表名, case when a.colorder = 1 then isnull(f.value, '') else '' end as 表说明 FROM syscolumns a inner join sysobjects d on a.id = d.id and d.xtype = 'U' and d.name <> 'sys.extended_properties' left join sys.extended_properties f on a.id = f.major_id and f.minor_id = 0 where a.colorder = 1 and d.name<>'sysdiagrams'"; dt = help.ExecuteSql(strSql); //保存数据 }
protected void GridView1_Command(object sender, System.Web.UI.WebControls.GridViewCommandEventArgs e) { string id = e.CommandArgument.ToString(); DbHelp dbmanage = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); if (e.CommandName.Equals("ViewDetail")) { string cmd = "<script>winPW('../MenuManage/search.aspx?CustomerID=" + id + "&Onlyview=true','',700,1200)</script>"; Page.ClientScript.RegisterStartupScript(ClientScript.GetType(), "winPW", cmd); } if (e.CommandName.Equals("InvalidataRecord")) { string cmd = "<script>winPW('../MenuManage/ADRwuxiao.aspx?CustomerID=" + id + "','',400,300)</script>"; Page.ClientScript.RegisterStartupScript(ClientScript.GetType(), "winPW", cmd); } if (e.CommandName.Equals("RevertRecord")) { string strsql = "update BaseInfo set State='需修改' where ID=" + id; int num = dbmanage.ExecuteSql(strsql); Response.Write("<script>alert('修改成功')</script>"); strbind = ViewState["strsql_ADRAudit"].ToString(); binddata(strbind); } if (e.CommandName.Equals("DeleteItem")) { string strSql = "delete from BaseInfo where ID =" + id; int num = dbmanage.ExecuteSql(strSql); Response.Write("<script>alert('修改成功')</script>"); strbind = ViewState["strsql_ADRAudit"].ToString(); binddata(strbind); } if (e.CommandName.Equals("AddToUser")) { string strSql = "select * from BaseInfo where ID =" + id + " and UserID is null"; DataSet ds = dbmanage.QueryToDs(strSql); int count = ds.Tables[0].Rows.Count; if (count == 1) { string strdatemonth = DateTime.Now.ToString("yyyy-MM"); strSql = "update BaseInfo temp set UserID='" + Session["UserID"].ToString() + "',OwnedService='" + Session["UserName"].ToString() + "',AssignTime='" + strdatemonth + "',State='未处理' where ID=" + id; dbmanage.ExecuteSql(strSql); ClientScript.RegisterStartupScript(ClientScript.GetType(), "showalert", "<script>showalert('添加成功')</script>"); } else { ClientScript.RegisterStartupScript(ClientScript.GetType(), "showalert", "<script>showalert('该客户已经添加')</script>"); } strbind = ViewState["strsql_ADRAudit"].ToString(); binddata(strbind); } }
protected void DeleteRole(object sender, EventArgs e) { if (listRole.SelectedIndex == 0) { return; } string deleterole = listRole.SelectedItem.Text; string strsql = "delete from Role where Authority='" + deleterole + "'"; DbHelp dbmanage = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); dbmanage.ExecuteSql(strsql); strsql = "update AccountInf set Authority='undefine' where Authority='" + deleterole + "'"; dbmanage.ExecuteSql(strsql); Response.Redirect(Request.Url.ToString()); }
protected void DeleteButtonClick(object sender, EventArgs e) { for (int i = 0; i < this.GridView1.Rows.Count; i++) { CheckBox ckb = (CheckBox)this.GridView1.Rows[i].Cells[0].FindControl("chkRow"); //OleDbConnection cn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("..//DataBase//CustomerBaseInfo.accdb")); DbHelp dbmanage = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); if (ckb.Checked) { GridViewRow gvr = GridView1.Rows[i]; string strAssignTime = gvr.Cells[4].Text; string userid = GridView1.DataKeys[i].Value.ToString(); string strSql = "update BaseInfo set UserID=null,OwnedService=null,AssignTime=null,State=null where UserID = " + userid + " and State='未处理' and AssignTime ='" + strAssignTime + "'"; //OleDbCommand myCmd = new OleDbCommand(strSql, cn); //myCmd.Parameters.Add("@UserID", OleDbType.VarChar, 11).Value = GridView1.DataKeys[i].Value.ToString(); //cn.Open(); //myCmd.ExecuteNonQuery(); //cn.Close(); dbmanage.ExecuteSql(strSql); } } GetRecordToGridView(); //string strSql = "delete from FenPeiDeleteTable where Owner= @Owner"; //OleDbConnection cn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("..//DataBase//FenPeiDeleteInfo.accdb")); //OleDbCommand myCmd = new OleDbCommand(strSql, cn); //myCmd.Parameters.Add("@Owner", OleDbType.VarChar, 11).Value = GridView1.DataKeys[e.RowIndex].Value.ToString(); ////string test = GridView1.DataKeys[e.RowIndex].Value.ToString(); //cn.Open(); //myCmd.ExecuteNonQuery(); ////Response.Write("<script > alert(id) </script>"); //cn.Close(); //binddata(); }
protected void SaveBtnClick(object sender, EventArgs e) { DbHelp dbmanage; dbmanage = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); string strsql = "select * from Role where Authority='" + txtRoleName.Text + "'"; try { object firstobject; bool num = dbmanage.GetSingle(strsql, out firstobject); if (null != firstobject) { Response.Write("<script>alert('角色已存在!')</script> "); } else { strsql = "insert into Role (Authority,AuthorityDescription) Values ('" + txtRoleName.Text + "','" + txtRoleDesc.Text + "')"; dbmanage.ExecuteSql(strsql); Response.Write("<script>alert('角色创建成功!')</script> "); } } catch (System.Exception ex) { throw; } }
protected void OnEnsure(object sender, EventArgs e) { string invalidate1 = ""; if (ch.Value == "1") { invalidate1 += "错号,"; } if (kh.Value == "1") { invalidate1 += "空号,"; } if (gh.Value == "1") { invalidate1 += "过户"; } string customerid = Request.QueryString["CustomerID"]; string nowTime = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); DbHelp dbmanage = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); string strsql = "update BaseInfo set [State]='" + "无效" + "',CommitTime='" + nowTime + "',InvalidateReason='" + invalidate1 + "',OtherInvalidateReason='" + txtwuxiao.Value + "' where ID=" + customerid; int num = dbmanage.ExecuteSql(strsql); if (num > 0) { Response.Write("<script>alert('修改成功')</script>"); Response.Write("<script language=javascript>opener.location.href=opener.location.href;window.close();</script>"); } else { Response.Write("<script>alert('num<0')</script>"); } }
protected void FailButtonClick(object sender, EventArgs e) { string customerid = Request.QueryString["CustomerID"]; string strsql = "update BaseInfo set State='失败' where ID=" + customerid; DbHelp dbmanage = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); int num = dbmanage.ExecuteSql(strsql); ClientScript.RegisterStartupScript(ClientScript.GetType(), "Showalert", "<script>Showalert()</script>"); }
protected void ArrangeCustomers(object sender, EventArgs e) { string strsql = ViewState["SearchSql"].ToString(); DbHelp dbmanage = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); DataSet ds = dbmanage.QueryToDs(strsql); int count = ds.Tables[0].Rows.Count; int checknum = 0; for (int i = 0; i < this.GridView1.Rows.Count; i++) { CheckBox ckb = (CheckBox)this.GridView1.Rows[i].Cells[0].FindControl("CheckBox1"); if (ckb.Checked) { checknum++; } } int countarrange; if (txtfps.Text == "" && checknum != 0) { countarrange = count / checknum; } else { countarrange = int.Parse(txtfps.Text); } int index = 0; for (int i = 0; i < this.GridView1.Rows.Count; i++) { CheckBox ckb = (CheckBox)this.GridView1.Rows[i].Cells[0].FindControl("CheckBox1"); string userid = GridView1.DataKeys[i].Value.ToString(); //strsql = "select Deptment from AccountInf where ID=" + userid; //DbHelp dbmanage1 = new DbHelp(EnumDbType.DbOleDb, "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("..//database//AccountInf.accdb")); //DataSet ds1 = dbmanage1.QueryToDs(strsql); //strdep = ds1.Tables[0].Rows.; string username = GridView1.Rows[i].Cells[1].Text; string strdatemonth = DateTime.Now.ToString("yyyy-MM"); if (ckb.Checked) { for (int j = 0; j < countarrange; j++) { if (index >= count) { return; } strsql = "update BaseInfo set UserID='" + userid + "',OwnedService='" + username + "',AssignTime='" + strdatemonth + "',State='未处理' where ID=" + ds.Tables[0].Rows[index]["ID"]; dbmanage.ExecuteSql(strsql); index++; } } } Response.Write("<script>alert('分配成功!')</script>"); Response.Redirect(Request.Url.ToString()); }
protected void ResetDataClick(object sender, EventArgs e) { string ExpTime = System.DateTime.Now.AddDays(-240.0).ToString("yyyy-MM-dd HH:mm:ss"); string currentDate = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); DbHelp dbmanage = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); string strSql = "update BaseInfo set State=NULL,UserID=NULL,CommitTime='"; strSql += currentDate + "' where CommitTime <= '"; strSql += ExpTime + "'"; int refCount = dbmanage.ExecuteSql(strSql); }
protected void OnClickConfirm(object sender, EventArgs e) { string OperNo = Request.QueryString["OperNo"]; string Tableid = Request.QueryString["ID"]; DbHelp dbmanage; string strsex; bool bAddUser; if (OperNo == "ADD") { bAddUser = true; } else { bAddUser = false; } if (RadioButtonSex0.Checked) { strsex = "Man"; } else { strsex = "Female"; } dbmanage = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); string strSQL = null; if (bAddUser) { strSQL = "Insert into AccountInf(UserName,UserIdentifier,Sex,Deptment,PhoneNum,MobileNumber,Address,IdenfierID,Email) Values ('" + txtLoginName.Text + "', '" + txtUserName.Text + "', '" + strsex + "', '" + listDept.SelectedItem.Text + "', '" + txtphone.Text + "', '" + txtMobile.Text + "', '" + txtAddress.Text + "','" + txtCardID.Text + "', '" + txtMail.Text + "')"; } else { strSQL = "update AccountInf set UserName='******',UserIdentifier='" + txtUserName.Text + "',Sex='" + strsex + "',Deptment='" + listDept.SelectedItem.Text + "',PhoneNum='" + txtphone.Text + "',MobileNumber='" + txtMobile.Text + "',Address='" + txtAddress.Text + "',IdenfierID='" + txtCardID.Text + "',Email='" + txtMail.Text + "',AccountMemo='" + txtMark.Text + "'" + " where ID=" + Tableid; } dbmanage.ExecuteSql(strSQL); string script_str = "<script>alert('保存成功!')</script>"; Response.Write(script_str); }
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e) { string userid = GridView1.DataKeys[e.RowIndex].Value.ToString(); string strSql = "delete from BaseInfo where ID = " + userid; DbHelp dbmanage = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); dbmanage.ExecuteSql(strSql); string SearchStr = ViewState["SearchSql"].ToString(); binddata(SearchStr); }
protected void OnSaveBaseInf(object sender, EventArgs e) { string customerid = Request.QueryString["CustomerID"]; string strsql = "update BaseInfo set PhoneNum='" + txtshouj.Value + "',RegistrationTime='" + txtdengj.Value + "',JiaoQiangTine='" + txtjiaoqiang.Value + "',InsuranceTime='" + txtshangyexian.Value + "',Expiration='" + txtdaoqi.Value + "',InsuranceCompany='" + drtb.Value + "',setNumber='" + txtzuowei.Value + "',CustomerType='" + txtkhlx.Value + "',Address='" + txtlxdz.Value + "',Addition='" + txtbz.Value + "' where ID=" + customerid; DbHelp dbmanage = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); int count = dbmanage.ExecuteSql(strsql); Response.Write("<script>alert('保存成功')</script>"); }
public List <DataTable> GetTableData(List <string> tables) { List <DataTable> lst = new List <DataTable>(); help.BeginTransation(); foreach (var table in tables) { //避免取出来的数据过大 var dt = help.ExecuteSql("select * from \"" + table + "\" where ROWNUM < 50 "); dt.TableName = table; lst.Add(dt); } help.Commit(); return(lst); }
protected void SavePassword(object sender, EventArgs e) { string userid = Request.QueryString["ID"]; string strpassword = ""; for (int i = 0; i < 6; i++) { strpassword += txtPWD.Value; } string strsql = "update AccountInf set [PassWord]='" + strpassword + "' where ID=" + userid; DbHelp dbmanage = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); dbmanage.ExecuteSql(strsql); Response.Write("<script>alert('修改成功')</script>"); }
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e) { DbHelp dbmanage1 = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); string strSql = "delete from BaseInfo where ID =" + GridView1.DataKeys[e.RowIndex].Value.ToString(); //OleDbConnection cn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("..//DataBase//CustomerBaseInfo.accdb")); //OleDbCommand myCmd = new OleDbCommand(strSql, cn); string test = GridView1.DataKeys[e.RowIndex].Value.ToString(); //cn.Open(); //myCmd.ExecuteNonQuery(); //cn.Close(); dbmanage1.ExecuteSql(strSql); strbind = ViewState["strsql_ClientManage"].ToString(); binddata(strbind); }
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e) { DbHelp dbmanage = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); string userid = GridView1.DataKeys[e.RowIndex].Value.ToString(); string strSql = "delete from AccountInf where ID = " + userid; //OleDbConnection cn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("//DataBase//AccountInf.accdb")); //OleDbCommand myCmd = new OleDbCommand(strSql, cn); //myCmd.Parameters.Add("@id", OleDbType.VarChar, 11).Value = GridView1.DataKeys[e.RowIndex].Value.ToString(); //string test = GridView1.DataKeys[e.RowIndex].Value.ToString(); //cn.Open(); //myCmd.ExecuteNonQuery(); //cn.Close(); dbmanage.ExecuteSql(strSql); gridviewbinddata(gloablestrSQL); }
protected void GridView1_Command(object sender, System.Web.UI.WebControls.GridViewCommandEventArgs e) { string id = e.CommandArgument.ToString(); if (e.CommandName.Equals("Btmanage")) { DbHelp dbmanage; String[] argument = e.CommandArgument.ToString().Split(','); string tableid = argument[0]; string enabled = argument[1]; if (enabled == "启用") { enabled = "禁用"; } else { enabled = "启用"; } dbmanage = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); string strSQL = "update AccountInf set Enabled='" + enabled + "' where ID=" + tableid.ToString(); dbmanage.ExecuteSql(strSQL); gridviewbinddata(gloablestrSQL); Response.Write("<script>alert('保存成功')</script>"); // Response.Write("<script>alert('保存成功!')</sctript>"); } else if (e.CommandName.Equals("RegEdit")) { string cmd = "<script>winPW('UserList/UserReg.aspx?OperNo=EDIT&ID=" + id + "','修改用户',600,500)</script>"; ClientScript.RegisterStartupScript(ClientScript.GetType(), "winPW", cmd); } else if (e.CommandName.Equals("ArrangeRole")) { string cmd = "<script>winPW('UserList/UserToRole.aspx?ID=" + id + "','角色分配',250,200)</script>"; ClientScript.RegisterStartupScript(ClientScript.GetType(), "winPW", cmd); } else if (e.CommandName.Equals("ReArrange")) { string cmd = "<script>winPW('UserList/ADR.aspx?ID=" + id + "','重新分配',400,340)</script>"; ClientScript.RegisterStartupScript(ClientScript.GetType(), "winPW", cmd); } else if (e.CommandName.Equals("Resetpass")) { string cmd = "<script>winPW('UserList/UserPWSet.aspx?ID=" + id + "','重置密码',150,110)</script>"; ClientScript.RegisterStartupScript(ClientScript.GetType(), "winPW", cmd); } }
protected void OnEnsureSuccess(object sender, EventArgs e) { if (txtxis.Value == "null") { return; } string nowTime = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); string customerid = Request.QueryString["CustomerID"]; string strpay = "刷卡"; if (RadioButton1.Checked == true) { strpay = "刷卡"; } if (RadioButton2.Checked == true) { strpay = "转账"; } if (RadioButton3.Checked == true) { strpay = "现金"; } if (RadioButton4.Checked == true) { strpay = "上门"; } DbHelp dbmanage = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); string strsql = "update BaseInfo set Address='" + txtlxdz.Value + "',Addition='" + txtbz.Value + "',PayManner='" + strpay + "',Gift='" + txtpinpai3.Value + "',[State]='成功" + "',CommitTime='" + nowTime + "',ServiceTime='" + txtfwsj.Value + "',UserExtraID='" + txtxis.Value + "' where ID=" + customerid; int num = dbmanage.ExecuteSql(strsql); if (num > 0) { Response.Write("<script>alert('提交成功')</script>"); Response.Write("<script language=javascript>opener.location.href=opener.location.href;window.close();</script>"); } else { Response.Write("<script>alert('提交失败,num < 0')</script>"); } }
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e) { DbHelp dbmanage = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); string userid = GridView1.DataKeys[e.RowIndex].Value.ToString(); string strSql = "delete from BaseInfo where ID = " + userid;; //OleDbConnection cn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("..//DataBase//CustomerBaseInfo.accdb")); //OleDbCommand myCmd = new OleDbCommand(strSql, cn); //myCmd.Parameters.Add("@id", OleDbType.VarChar, 11).Value = GridView1.DataKeys[e.RowIndex].Value.ToString(); //string test = GridView1.DataKeys[e.RowIndex].Value.ToString(); //cn.Open(); //myCmd.ExecuteNonQuery(); //Response.Write("<script > alert(id) </script>"); //cn.Close(); dbmanage.ExecuteSql(strSql); strSql = ViewState["strsql_mindanchuli"].ToString(); binddata(strSql); }
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e) { GridViewRow gvr = GridView1.Rows[e.RowIndex]; string strAssignTime = gvr.Cells[4].Text; string userid = GridView1.DataKeys[e.RowIndex].Value.ToString(); string strSql = "update BaseInfo set UserID=null,OwnedService=null,AssignTime=null,State=null where UserID = " + userid + " and State='未处理' and AssignTime ='" + strAssignTime + "'"; DbHelp dbmanage = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); //OleDbConnection cn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("..//DataBase//CustomerBaseInfo.accdb")); //OleDbCommand myCmd = new OleDbCommand(strSql, cn); // myCmd.Parameters.Add("@UserID", OleDbType.VarChar, 11).Value = GridView1.DataKeys[e.RowIndex].Value.ToString(); //string test = GridView1.DataKeys[e.RowIndex].Value.ToString(); //cn.Open(); //myCmd.ExecuteNonQuery(); ////Response.Write("<script > alert(id) </script>"); //cn.Close(); dbmanage.ExecuteSql(strSql); GetRecordToGridView(); }
protected void AppointmentButtonClick(object Sender, EventArgs e) { string nowTime = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); string customerid = Request.QueryString["CustomerID"]; string UpdateStr = "update BaseInfo set State ='预约', Appointment='" + txttime.Value + "',CustomerType='" + drkhlx.Value + "',CommitTime='" + nowTime + "'" + " where ID=" + customerid; DbHelp dbmanage = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); int count = dbmanage.ExecuteSql(UpdateStr); if (count > 0) { Response.Write("<script > alert( '预约成功!');</ </script>"); Response.Write("<script language=javascript>opener.location.href=opener.location.href;window.close();</script>"); } else { Response.Write("<script>alert('预约失败,count<0')</script>"); } }
//abstract some behavoir //keep the system reusable public bool HandleDatabase(string cmd, string databasePath) { DbHelp dbmanage = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); // cn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + databasePath); try { //cn.Open(); //OleDbCommand myCmd = new OleDbCommand(cmd, cn); //myCmd.ExecuteNonQuery(); //cn.Close(); dbmanage.ExecuteSql(cmd); return(true); } catch (Exception openerror) { return(false); } }
protected void RefineUserInf(object sender, EventArgs e) { DbHelp dbmanage; dbmanage = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); string strsql = "update AccountInf set UserIdentifier='" + txtUserName.Value + "'," + "Sex='" + (RadioButtonSex0.Checked == true ? "Man" : "Female") + "'," + "Deptment='" + deptment.SelectedItem.Text + "'," + "PhoneNum ='" + txtphone.Value + "'," + "MobileNumber ='" + txtMobile.Value + "'," + "Address ='" + txtAddress.Value + "'," + "IdenfierID='" + txtCardID.Value + "'," + "School='" + listSchool.SelectedValue + "'," + "BirthDay='" + txtBirthDay.Value + "'," + "Email='" + txtMail.Value + "' where ID=" + Session["UserID"]; dbmanage.ExecuteSql(strsql); Response.Write("<script>alert('保存成功')</script>"); }
protected void OnBtnSaveClick(object sender, EventArgs e) { string struserid = Request.QueryString["ID"]; int intnum = int.Parse(txtnum.Value); if (intnum > int.Parse(txtyyl.Value)) { Response.Write("<script>alert('重新分配数目超过已有记录数目!')</script>"); } else { string strsql = "select * from BaseInfo where UserID='" + struserid + "'"; if (DropDownList1.SelectedItem.Value != "--名单状态--" && DropDownList1.SelectedItem.Value != "") { strsql += " and State='" + DropDownList1.SelectedItem.Value + "'"; } if (DropDownList2.SelectedItem.Value != "--客户类型--" && DropDownList2.SelectedItem.Value != "") { strsql += " and CustomerType='" + DropDownList2.SelectedItem.Value + "'"; } //string strsql = "select * from BaseInfo where UserID='" + struserid + "' and [State]='" + DropDownList1.SelectedItem.Value + "'"; DbHelp dbmanage = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); DataSet ds = dbmanage.QueryToDs(strsql); int count = 0; if (ds != null) { count = ds.Tables[0].Rows.Count; for (int i = 0; i < intnum; i++) { string strsql1 = "update BaseInfo set OwnedService='" + DropDownListstaf.SelectedItem.Text + "',UserID='" + DropDownListstaf.SelectedItem.Value + "' where ID=" + ds.Tables[0].Rows[i]["ID"]; dbmanage.ExecuteSql(strsql1); } } // strsql = "select * from BaseInfo where UserID='" + struserid + "'"; ds = dbmanage.QueryToDs(strsql); count = ds.Tables[0].Rows.Count; txtyyl.Value = count.ToString(); Response.Write("<script>alert('重新分配成功')</script>"); } }
protected void ResetSelectedItem(object sender, EventArgs e) { DbHelp dbmanage1 = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); int iRowCount = GridView1.Rows.Count; CheckBox checkAllBox = (CheckBox)this.GridView1.HeaderRow.FindControl("chkAll"); string sql = ViewState["SearchSql"].ToString(); DataSet ds = dbmanage1.QueryToDs(sql); if (checkAllBox.Checked) { iRowCount = ds.Tables[0].Rows.Count; } string userid = ""; string currentDate = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); for (int i = 0; i < iRowCount; i++) { if (checkAllBox.Checked) { userid = ds.Tables[0].Rows[i]["ID"].ToString(); } else { CheckBox selectcheck = (CheckBox)GridView1.Rows[i].FindControl("chkRow"); if (selectcheck.Checked == true) { userid = GridView1.DataKeys[i].Value.ToString(); } } DbHelp dbmanage = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); string strSql = "update BaseInfo set State='',UserID='',CommitTime='"; strSql += currentDate + "' where ID =" + userid; dbmanage1.ExecuteSql(strSql); } string strbind = ViewState["SearchSql"].ToString(); binddata(strbind); }
protected void OnSubbmitRole(object sender, EventArgs e) { DbHelp dbmanage; string ID; dbmanage = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); int selectid = RoleSelect.SelectedIndex; if (selectid != 0) { ID = Request.QueryString["ID"]; string strSQL = "update AccountInf set Authority='" + RoleSelect.SelectedItem.Text + "' where ID=" + ID; dbmanage.ExecuteSql(strSQL); Response.Write("<script>alert('保存成功!');window.close()</script>"); } else { Response.Write("<script>alert('未选择角色!')</script>"); } }
protected void RefinePassword(object sender, EventArgs e) { string originalpass = txtOldPass.Value; string newpass = txtPassWord1.Value; string passagain = txtPassWord2.Value; if (newpass != passagain) { Response.Write("<script>alert('输入新密码不一致')</script>"); return; } string strSQL = "SELECT * FROM AccountInf where ID=" + Session["UserID"] + " and PassWord='******'"; DbHelp dbmanage; dbmanage = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); DataSet ds = dbmanage.QueryToDs(strSQL); int count = ds.Tables[0].Rows.Count; if (count != 1) { Response.Write("<script>alert('输入原密码有误')</script>"); return; } else { strSQL = "update AccountInf set [PassWord]='" + newpass + "'" + " where ID=" + Session["UserID"]; count = dbmanage.ExecuteSql(strSQL); if (count == 1) { Response.Write("<script>alert('密码修改成功')</script>"); } else { Response.Write("<script>alert('密码修改失败')</script>"); } } }
protected void DeleteSelectedItem(object sender, EventArgs e) { DbHelp dbmanage1 = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); for (int i = 0; i <= GridView1.Rows.Count - 1; i++) { CheckBox selectcheck = (CheckBox)GridView1.Rows[i].FindControl("chkRow"); if (selectcheck.Checked == true) { string userid = GridView1.DataKeys[i].Value.ToString(); string strSql = "delete from BaseInfo where ID =" + userid; dbmanage1.ExecuteSql(strSql); //OleDbConnection cn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("..//DataBase//CustomerBaseInfo.accdb")); //OleDbCommand myCmd = new OleDbCommand(strSql, cn); //cn.Open(); //myCmd.ExecuteNonQuery(); //cn.Close(); } } strbind = ViewState["strsql_ClientManage"].ToString(); Response.Write("<script>alert('删除成功')</script>"); binddata(strbind); }
protected void GridView1_Command(object sender, System.Web.UI.WebControls.GridViewCommandEventArgs e) { string strrole = Request.QueryString["Authority"]; if (e.CommandName.Equals("CancleRole")) { DbHelp dbmanage; String ID = e.CommandArgument.ToString(); dbmanage = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); string strSQL = "update AccountInf set Authority='undefined'" + " where ID=" + ID; dbmanage.ExecuteSql(strSQL); gridviewbind(); } if (e.CommandName.Equals("ArrangeRole")) { DbHelp dbmanage; String ID = e.CommandArgument.ToString(); dbmanage = new DbHelp(EnumDbType.DbSqlServer, "server=NOLAN-DEBUG-PC\\SQLEXPRESS1;database=test;Trusted_Connection=SSPI;"); string strSQL = "update AccountInf set Authority='" + strrole + "'" + " where ID=" + ID; dbmanage.ExecuteSql(strSQL); gridviewbind(); } }