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