//[ScriptMethod(ResponseFormat = System.Web.Script.Services.ResponseFormat.Xml, XmlSerializeString = true)]
public Apq.STReturn UserEditLoginPwd(string LoginPwd_C, string LoginPwd)
{
Apq.STReturn stReturn = new Apq.STReturn();
DinnerSession ApqSession = new DinnerSession(Session);
if (!CheckLoginPage.CheckLogin(ref stReturn, ApqSession))
{
return(stReturn);
}
// 检测旧密码
System.Security.Cryptography.SHA512 SHA512 = System.Security.Cryptography.SHA512.Create();
byte[] binLoginPwd_C = SHA512.ComputeHash(System.Text.Encoding.Unicode.GetBytes(LoginPwd_C));
string SqlLoginPwd_C = Apq.Data.SqlClient.Common.ConvertToSqlON(binLoginPwd_C);
string SqlLoginPwd_DB = Apq.Data.SqlClient.Common.ConvertToSqlON(ApqSession.ApqLogin.LoginPwd);
if (SqlLoginPwd_C != SqlLoginPwd_DB)
{
stReturn.NReturn = -1;
stReturn.ExMsg = "原密码输入错误";
return(stReturn);
}
byte[] binLoginPwd = SHA512.ComputeHash(System.Text.Encoding.Unicode.GetBytes(LoginPwd));
DbConnection SqlConn = null;
using (SqlConn = Apq.DBC.Common.CreateDBConnection("Dinner", ref SqlConn))
{
Apq.Data.Common.DbConnectionHelper dbch = new Apq.Data.Common.DbConnectionHelper(SqlConn);
DbCommand sc = SqlConn.CreateCommand();
sc.CommandText = "dbo.Dinner_User_UpdateLoginPwd";
sc.CommandType = CommandType.StoredProcedure;
Apq.Data.Common.DbCommandHelper dch = new Apq.Data.Common.DbCommandHelper(sc);
dch.AddParameter("rtn", 0, DbType.Int32);
dch.AddParameter("ExMsg", stReturn.ExMsg, DbType.String, -1);
dch.AddParameter("LoginID", ApqSession.ApqLogin.LoginID);
dch.AddParameter("LoginPwd", binLoginPwd);
sc.Parameters["rtn"].Direction = ParameterDirection.ReturnValue;
sc.Parameters["ExMsg"].Direction = ParameterDirection.InputOutput;
SqlConn.Open();
sc.ExecuteNonQuery();
stReturn.NReturn = System.Convert.ToInt32(sc.Parameters["rtn"].Value);
stReturn.ExMsg = sc.Parameters["ExMsg"].Value.ToString();
sc.Dispose();
SqlConn.Close();
}
// 更新Session中的密码信息
ApqSession.ApqLogin.LoginPwd = binLoginPwd;
// 返回客户端
stReturn.FNReturn = Apq.Data.SqlClient.Common.ConvertToSqlON(binLoginPwd);
return(stReturn);
}
//[ScriptMethod(ResponseFormat = System.Web.Script.Services.ResponseFormat.Xml, XmlSerializeString = true)]
public Apq.STReturn Dinner_Restaurant_List()
{
Apq.STReturn stReturn = new Apq.STReturn();
DinnerSession ApqSession = new DinnerSession(Session);
if (!CheckLoginPage.CheckAdmin(ref stReturn, ApqSession))
{
return stReturn;
}
DataSet ds = new DataSet();
DbConnection SqlConn = null;
using (SqlConn = Apq.DBC.Common.CreateDBConnection("Dinner", ref SqlConn))
{
Apq.Data.Common.DbConnectionHelper dbch = new Apq.Data.Common.DbConnectionHelper(SqlConn);
DbDataAdapter sda = dbch.CreateAdapter();
sda.SelectCommand.CommandText = "dbo.Dinner_Restaurant_List";
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
Apq.Data.Common.DbCommandHelper dch = new Apq.Data.Common.DbCommandHelper(sda.SelectCommand);
dch.AddParameter("rtn", 0, DbType.Int32);
sda.SelectCommand.Parameters["rtn"].Direction = ParameterDirection.ReturnValue;
SqlConn.Open();
sda.Fill(ds);
stReturn.NReturn = System.Convert.ToInt32(sda.SelectCommand.Parameters["rtn"].Value);
stReturn.FNReturn = ds.Tables[0];
sda.Dispose();
SqlConn.Close();
}
return stReturn;
}
//[ScriptMethod(ResponseFormat = System.Web.Script.Services.ResponseFormat.Xml, XmlSerializeString = true)]
public Apq.STReturn UserEditSelf(long UserID, string Name, short Sex, string PhotoUrl, DateTime Birthday
, string IDCard, string Alipay)
{
Apq.STReturn stReturn = new Apq.STReturn();
DinnerSession ApqSession = new DinnerSession(Session);
if (!CheckLoginPage.CheckPwdExpire(ref stReturn, ApqSession))
{
return(stReturn);
}
DbConnection SqlConn = null;
using (SqlConn = Apq.DBC.Common.CreateDBConnection("Dinner", ref SqlConn))
{
Apq.Data.Common.DbConnectionHelper dbch = new Apq.Data.Common.DbConnectionHelper(SqlConn);
DbCommand sc = SqlConn.CreateCommand();
sc.CommandText = "Dinner.Dinner_User_UpdateSelf";
sc.CommandType = CommandType.StoredProcedure;
Apq.Data.Common.DbCommandHelper dch = new Apq.Data.Common.DbCommandHelper(sc);
dch.AddParameter("rtn", 0, DbType.Int32);
dch.AddParameter("ExMsg", stReturn.ExMsg, DbType.String, -1);
dch.AddParameter("_OperID", ApqSession);
dch.AddParameter("_OpTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
IPAddress ipa;
if (IPAddress.TryParse(HttpContext.Current.Request.UserHostAddress, out ipa))
{
dch.AddParameter("_OperIP", ipa.GetAddressBytes());
}
dch.AddParameter("UserID", UserID);
dch.AddParameter("Name", Name);
dch.AddParameter("Sex", Sex);
dch.AddParameter("PhotoUrl", PhotoUrl);
//dch.AddParameter("Expire", Expire.ToString("yyyy-MM-dd HH:mm:ss.fff"));
//dch.AddParameter("Status", Status);
//dch.AddParameter("IsAdmin", IsAdmin);
dch.AddParameter("Birthday", Birthday);
dch.AddParameter("IDCard", IDCard);
dch.AddParameter("Alipay", Alipay);
sc.Parameters["rtn"].Direction = ParameterDirection.ReturnValue;
sc.Parameters["ExMsg"].Direction = ParameterDirection.InputOutput;
SqlConn.Open();
sc.ExecuteNonQuery();
stReturn.NReturn = System.Convert.ToInt32(sc.Parameters["rtn"].Value);
stReturn.ExMsg = sc.Parameters["ExMsg"].Value.ToString();
sc.Dispose();
SqlConn.Close();
}
return(stReturn);
}
//[ScriptMethod(ResponseFormat = System.Web.Script.Services.ResponseFormat.Xml, XmlSerializeString = true)]
public Apq.STReturn UserEditSelf(long UserID, string Name, short Sex, string PhotoUrl, DateTime Birthday
, string IDCard, string Alipay)
{
Apq.STReturn stReturn = new Apq.STReturn();
DinnerSession ApqSession = new DinnerSession(Session);
if (!CheckLoginPage.CheckPwdExpire(ref stReturn, ApqSession))
{
return stReturn;
}
DbConnection SqlConn = null;
using (SqlConn = Apq.DBC.Common.CreateDBConnection("Dinner", ref SqlConn))
{
Apq.Data.Common.DbConnectionHelper dbch = new Apq.Data.Common.DbConnectionHelper(SqlConn);
DbCommand sc = SqlConn.CreateCommand();
sc.CommandText = "Dinner.Dinner_User_UpdateSelf";
sc.CommandType = CommandType.StoredProcedure;
Apq.Data.Common.DbCommandHelper dch = new Apq.Data.Common.DbCommandHelper(sc);
dch.AddParameter("rtn", 0, DbType.Int32);
dch.AddParameter("ExMsg", stReturn.ExMsg, DbType.String, -1);
dch.AddParameter("_OperID", ApqSession);
dch.AddParameter("_OpTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
IPAddress ipa;
if (IPAddress.TryParse(HttpContext.Current.Request.UserHostAddress, out ipa))
{
dch.AddParameter("_OperIP", ipa.GetAddressBytes());
}
dch.AddParameter("UserID", UserID);
dch.AddParameter("Name", Name);
dch.AddParameter("Sex", Sex);
dch.AddParameter("PhotoUrl", PhotoUrl);
//dch.AddParameter("Expire", Expire.ToString("yyyy-MM-dd HH:mm:ss.fff"));
//dch.AddParameter("Status", Status);
//dch.AddParameter("IsAdmin", IsAdmin);
dch.AddParameter("Birthday", Birthday);
dch.AddParameter("IDCard", IDCard);
dch.AddParameter("Alipay", Alipay);
sc.Parameters["rtn"].Direction = ParameterDirection.ReturnValue;
sc.Parameters["ExMsg"].Direction = ParameterDirection.InputOutput;
SqlConn.Open();
sc.ExecuteNonQuery();
stReturn.NReturn = System.Convert.ToInt32(sc.Parameters["rtn"].Value);
stReturn.ExMsg = sc.Parameters["ExMsg"].Value.ToString();
sc.Dispose();
SqlConn.Close();
}
return stReturn;
}
//[ScriptMethod(ResponseFormat = System.Web.Script.Services.ResponseFormat.Xml, XmlSerializeString = true)]
public Apq.STReturn Dinner_Admin_EmDinner_ListPager(int start, int limit, DateTime BTime, DateTime ETime, long RestID, bool IsDoDinner)
{
int Pager_Page = start / limit;
int Pager_PageSize = limit;
Apq.STReturn stReturn = new Apq.STReturn();
DinnerSession ApqSession = new DinnerSession(Session);
if (!CheckLoginPage.CheckAdmin(ref stReturn, ApqSession))
{
return(stReturn);
}
DataSet ds = new DataSet();
DbConnection SqlConn = null;
using (SqlConn = Apq.DBC.Common.CreateDBConnection("Dinner", ref SqlConn))
{
Apq.Data.Common.DbConnectionHelper dbch = new Apq.Data.Common.DbConnectionHelper(SqlConn);
DbDataAdapter sda = dbch.CreateAdapter();
sda.SelectCommand.CommandText = "dbo.Dinner_Admin_EmDinner_ListPager";
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
Apq.Data.Common.DbCommandHelper dch = new Apq.Data.Common.DbCommandHelper(sda.SelectCommand);
dch.AddParameter("rtn", 0, DbType.Int32);
//dch.AddParameter("ExMsg", stReturn.ExMsg, DbType.String, -1);
dch.AddParameter("Pager_Page", Pager_Page, DbType.Int32);
dch.AddParameter("Pager_PageSize", Pager_PageSize);
dch.AddParameter("Pager_RowCount", 0, DbType.Int32);
dch.AddParameter("BTime", BTime);
dch.AddParameter("ETime", ETime);
dch.AddParameter("RestID", RestID);
dch.AddParameter("State", IsDoDinner);
sda.SelectCommand.Parameters["rtn"].Direction = ParameterDirection.ReturnValue;
//sda.SelectCommand.Parameters["ExMsg"].Direction = ParameterDirection.InputOutput;
sda.SelectCommand.Parameters["Pager_RowCount"].Direction = ParameterDirection.InputOutput;
SqlConn.Open();
sda.Fill(ds);
stReturn.NReturn = System.Convert.ToInt32(sda.SelectCommand.Parameters["rtn"].Value);
//stReturn.ExMsg = sda.SelectCommand.Parameters["ExMsg"].Value.ToString();
stReturn.FNReturn = ds.Tables[0];
stReturn.POuts.Add(sda.SelectCommand.Parameters["Pager_Page"].Value);
stReturn.POuts.Add(sda.SelectCommand.Parameters["Pager_RowCount"].Value);
sda.Dispose();
SqlConn.Close();
}
return(stReturn);
}
/// <summary>
/// 下载文件
/// </summary>
public void FileDow(string DBFullName, string FullName)
{
string FileName = System.IO.Path.GetFileName(FullName);
string CFolder = System.IO.Path.GetDirectoryName(FullName);
string DBFolder = System.IO.Path.GetDirectoryName(DBFullName);
// 准备本地目录
if (!System.IO.Directory.Exists(CFolder))
{
System.IO.Directory.CreateDirectory(CFolder);
}
// 1.写入数据库
Apq.Data.Common.DbConnectionHelper.Open(Connection);
System.Data.Common.DbCommand sqlCmd = Connection.CreateCommand();
sqlCmd.CommandText = "Apq_FileTrans_Insert_ADO";
sqlCmd.CommandType = CommandType.StoredProcedure;
Apq.Data.Common.DbCommandHelper cmdHelper = new Apq.Data.Common.DbCommandHelper(sqlCmd);
cmdHelper.AddParameter("@FullName", DBFullName);
cmdHelper.AddParameter("@CFolder", CFolder);
cmdHelper.AddParameter("@ID", 0);
sqlCmd.Parameters["@ID"].Direction = ParameterDirection.InputOutput;
sqlCmd.ExecuteNonQuery();
int ID = Apq.Convert.ChangeType <int>(sqlCmd.Parameters["@ID"].Value);
// 2.读取到本地
DataSet ds = new DataSet();
Apq.Data.Common.DbConnectionHelper connHelper = new Apq.Data.Common.DbConnectionHelper(Connection);
DbDataAdapter da = connHelper.CreateAdapter();
da.SelectCommand.CommandText = "Apq_FileTrans_List";
da.SelectCommand.CommandType = CommandType.StoredProcedure;
Apq.Data.Common.DbCommandHelper daHelper = new Apq.Data.Common.DbCommandHelper(da.SelectCommand);
daHelper.AddParameter("@ID", ID);
da.Fill(ds);
// 3.保存文件
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
byte[] bFile = Apq.Convert.ChangeType <byte[]>(ds.Tables[0].Rows[0]["FileStream"]);
System.IO.FileStream fs = new System.IO.FileStream(FullName, System.IO.FileMode.Create);
fs.Write(bFile, 0, bFile.Length);
fs.Flush();
fs.Close();
}
// 4.删除数据库行
da.SelectCommand.CommandText = "Apq_FileTrans_Delete";
da.SelectCommand.ExecuteNonQuery();
}
/// <summary>
/// 执行登录存储过程
/// </summary>
/// <param name="LoginName">用户名</param>
/// <param name="binLoginPwd">密码</param>
/// <returns></returns>
private Apq.STReturn DBLogin(string LoginName, byte[] binLoginPwd)
{
Apq.STReturn stReturn = new Apq.STReturn();
DataSet ds = new DataSet();
DbConnection SqlConn = null;
using (SqlConn = Apq.DBC.Common.CreateDBConnection("Dinner", ref SqlConn))
{
Apq.Data.Common.DbConnectionHelper dbch = new Apq.Data.Common.DbConnectionHelper(SqlConn);
DbDataAdapter sda = dbch.CreateAdapter();
sda.SelectCommand.CommandText = "dbo.Dinner_Login";
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
Apq.Data.Common.DbCommandHelper dch = new Apq.Data.Common.DbCommandHelper(sda.SelectCommand);
dch.AddParameter("rtn", 0, DbType.Int32);
dch.AddParameter("ExMsg", stReturn.ExMsg, DbType.String, -1);
dch.AddParameter("LoginName", LoginName);
dch.AddParameter("LoginPwd", binLoginPwd);
sda.SelectCommand.Parameters["rtn"].Direction = ParameterDirection.ReturnValue;
sda.SelectCommand.Parameters["ExMsg"].Direction = ParameterDirection.InputOutput;
SqlConn.Open();
sda.Fill(ds);
stReturn.NReturn = System.Convert.ToInt32(sda.SelectCommand.Parameters["rtn"].Value);
stReturn.ExMsg = sda.SelectCommand.Parameters["ExMsg"].Value.ToString();
if (ds.Tables.Count > 0)
{
stReturn.FNReturn = ds.Tables[0];
}
sda.Dispose();
SqlConn.Close();
}
// 返回调用者
string SqlLoginPwd = Apq.Data.SqlClient.Common.ConvertToSqlON(binLoginPwd);
if (stReturn.NReturn > 0 && ds.Tables[0].Rows.Count > 0)
{
stReturn.NReturn = 1;
stReturn.FNReturn = ds.Tables[0];
stReturn.POuts.Add(SqlLoginPwd);
}
return(stReturn);
}
//[ScriptMethod(ResponseFormat = System.Web.Script.Services.ResponseFormat.Xml, XmlSerializeString = true)]
public Apq.STReturn Dinner_Stat_EmDinner_Food(DateTime BTime, DateTime ETime, bool State)
{
Apq.STReturn stReturn = new Apq.STReturn();
DinnerSession ApqSession = new DinnerSession(Session);
if (!CheckLoginPage.CheckAdmin(ref stReturn, ApqSession))
{
return(stReturn);
}
DataSet ds = new DataSet();
DbConnection SqlConn = null;
using (SqlConn = Apq.DBC.Common.CreateDBConnection("Dinner", ref SqlConn))
{
Apq.Data.Common.DbConnectionHelper dbch = new Apq.Data.Common.DbConnectionHelper(SqlConn);
DbDataAdapter sda = dbch.CreateAdapter();
sda.SelectCommand.CommandText = "dbo.Dinner_Stat_EmDinner_Food";
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
Apq.Data.Common.DbCommandHelper dch = new Apq.Data.Common.DbCommandHelper(sda.SelectCommand);
dch.AddParameter("rtn", 0, DbType.Int32);
//dch.AddParameter("ExMsg", stReturn.ExMsg, DbType.String, -1);
dch.AddParameter("BTime", BTime);
dch.AddParameter("ETime", ETime);
dch.AddParameter("State", State);
sda.SelectCommand.Parameters["rtn"].Direction = ParameterDirection.ReturnValue;
//sda.SelectCommand.Parameters["ExMsg"].Direction = ParameterDirection.InputOutput;
SqlConn.Open();
sda.Fill(ds);
stReturn.NReturn = System.Convert.ToInt32(sda.SelectCommand.Parameters["rtn"].Value);
//stReturn.ExMsg = sda.SelectCommand.Parameters["ExMsg"].Value.ToString();
stReturn.FNReturn = ds.Tables[0];
sda.Dispose();
SqlConn.Close();
}
return(stReturn);
}
//[ScriptMethod(ResponseFormat = System.Web.Script.Services.ResponseFormat.Xml, XmlSerializeString = true)]
public Apq.STReturn Dinner_Food_Save(long FoodID, long RestID, string FoodName, decimal FoodPrice)
{
Apq.STReturn stReturn = new Apq.STReturn();
DinnerSession ApqSession = new DinnerSession(Session);
if (!CheckLoginPage.CheckAdmin(ref stReturn, ApqSession))
{
return(stReturn);
}
DbConnection SqlConn = null;
using (SqlConn = Apq.DBC.Common.CreateDBConnection("Dinner", ref SqlConn))
{
Apq.Data.Common.DbConnectionHelper dbch = new Apq.Data.Common.DbConnectionHelper(SqlConn);
DbCommand sc = SqlConn.CreateCommand();
sc.CommandText = "dbo.Dinner_Food_Save";
sc.CommandType = CommandType.StoredProcedure;
Apq.Data.Common.DbCommandHelper dch = new Apq.Data.Common.DbCommandHelper(sc);
dch.AddParameter("rtn", 0, DbType.Int32);
dch.AddParameter("ExMsg", stReturn.ExMsg, DbType.String, -1);
dch.AddParameter("FoodID", FoodID);
dch.AddParameter("RestID", RestID);
dch.AddParameter("FoodName", FoodName);
dch.AddParameter("FoodPrice", FoodPrice);
sc.Parameters["rtn"].Direction = ParameterDirection.ReturnValue;
sc.Parameters["ExMsg"].Direction = ParameterDirection.InputOutput;
sc.Parameters["FoodID"].Direction = ParameterDirection.InputOutput;
SqlConn.Open();
sc.ExecuteNonQuery();
stReturn.NReturn = System.Convert.ToInt32(sc.Parameters["rtn"].Value);
stReturn.ExMsg = Apq.Convert.ChangeType <string>(sc.Parameters["ExMsg"].Value);
stReturn.POuts.Add(sc.Parameters["FoodID"]);
sc.Dispose();
SqlConn.Close();
}
return(stReturn);
}
//[ScriptMethod(ResponseFormat = System.Web.Script.Services.ResponseFormat.Xml, XmlSerializeString = true)]
public Apq.STReturn Dinner_Employee_Update(long EmID, string EmName, bool EmStatus, bool IsAdmin, string LoginName)
{
Apq.STReturn stReturn = new Apq.STReturn();
DinnerSession ApqSession = new DinnerSession(Session);
if (!CheckLoginPage.CheckAdmin(ref stReturn, ApqSession))
{
return(stReturn);
}
DbConnection SqlConn = null;
using (SqlConn = Apq.DBC.Common.CreateDBConnection("Dinner", ref SqlConn))
{
Apq.Data.Common.DbConnectionHelper dbch = new Apq.Data.Common.DbConnectionHelper(SqlConn);
DbCommand sc = SqlConn.CreateCommand();
sc.CommandText = "dbo.Dinner_Employee_Update";
sc.CommandType = CommandType.StoredProcedure;
Apq.Data.Common.DbCommandHelper dch = new Apq.Data.Common.DbCommandHelper(sc);
dch.AddParameter("rtn", 0, DbType.Int32);
dch.AddParameter("ExMsg", stReturn.ExMsg, DbType.String, -1);
dch.AddParameter("EmID", EmID);
dch.AddParameter("EmName", EmName);
dch.AddParameter("EmStatus", EmStatus, DbType.Int32);
dch.AddParameter("IsAdmin", IsAdmin);
dch.AddParameter("LoginName", LoginName);
sc.Parameters["rtn"].Direction = ParameterDirection.ReturnValue;
sc.Parameters["ExMsg"].Direction = ParameterDirection.InputOutput;
SqlConn.Open();
sc.ExecuteNonQuery();
stReturn.NReturn = System.Convert.ToInt32(sc.Parameters["rtn"].Value);
stReturn.ExMsg = sc.Parameters["ExMsg"].Value.ToString();
sc.Dispose();
SqlConn.Close();
}
return(stReturn);
}
//[ScriptMethod(ResponseFormat = System.Web.Script.Services.ResponseFormat.Xml, XmlSerializeString = true)]
public Apq.STReturn Dinner_Admin_EmDinner_DoDinner(DateTime BTime, DateTime ETime)
{
Apq.STReturn stReturn = new Apq.STReturn();
DinnerSession ApqSession = new DinnerSession(Session);
if (!CheckLoginPage.CheckAdmin(ref stReturn, ApqSession))
{
return(stReturn);
}
DataSet ds = new DataSet();
DbConnection SqlConn = null;
using (SqlConn = Apq.DBC.Common.CreateDBConnection("Dinner", ref SqlConn))
{
Apq.Data.Common.DbConnectionHelper dbch = new Apq.Data.Common.DbConnectionHelper(SqlConn);
DbCommand sc = SqlConn.CreateCommand();
sc.CommandText = "dbo.Dinner_Admin_EmDinner_DoDinner";
sc.CommandType = CommandType.StoredProcedure;
Apq.Data.Common.DbCommandHelper dch = new Apq.Data.Common.DbCommandHelper(sc);
dch.AddParameter("rtn", 0, DbType.Int32);
//dch.AddParameter("ExMsg", stReturn.ExMsg, DbType.String, -1);
dch.AddParameter("BTime", BTime);
dch.AddParameter("ETime", ETime);
sc.Parameters["rtn"].Direction = ParameterDirection.ReturnValue;
//sc.Parameters["ExMsg"].Direction = ParameterDirection.InputOutput;
SqlConn.Open();
sc.ExecuteNonQuery();
stReturn.NReturn = System.Convert.ToInt32(sc.Parameters["rtn"].Value);
//stReturn.ExMsg = sc.Parameters["ExMsg"].Value.ToString();
sc.Dispose();
SqlConn.Close();
}
return(stReturn);
}
//[ScriptMethod(ResponseFormat = System.Web.Script.Services.ResponseFormat.Xml, XmlSerializeString = true)]
public Apq.STReturn Dinner_Restaurant_List()
{
Apq.STReturn stReturn = new Apq.STReturn();
DinnerSession ApqSession = new DinnerSession(Session);
if (!CheckLoginPage.CheckLogin(ref stReturn, ApqSession))
{
return(stReturn);
}
DataSet ds = new DataSet();
DbConnection SqlConn = null;
using (SqlConn = Apq.DBC.Common.CreateDBConnection("Dinner", ref SqlConn))
{
Apq.Data.Common.DbConnectionHelper dbch = new Apq.Data.Common.DbConnectionHelper(SqlConn);
DbDataAdapter sda = dbch.CreateAdapter();
sda.SelectCommand.CommandText = "dbo.Dinner_Restaurant_List";
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
Apq.Data.Common.DbCommandHelper dch = new Apq.Data.Common.DbCommandHelper(sda.SelectCommand);
dch.AddParameter("rtn", 0, DbType.Int32);
sda.SelectCommand.Parameters["rtn"].Direction = ParameterDirection.ReturnValue;
SqlConn.Open();
sda.Fill(ds);
stReturn.NReturn = System.Convert.ToInt32(sda.SelectCommand.Parameters["rtn"].Value);
stReturn.FNReturn = ds.Tables[0];
sda.Dispose();
SqlConn.Close();
}
return(stReturn);
}
//[ScriptMethod(ResponseFormat = System.Web.Script.Services.ResponseFormat.Xml, XmlSerializeString = true)]
public Apq.STReturn Dinner_RegEmployee(string EmName, string LoginName, string LoginPwd)
{
Apq.STReturn stReturn = new Apq.STReturn();
if (EmName.Length < 1)
{
stReturn.NReturn = -1;
stReturn.ExMsg = "姓名不允许为空";
return(stReturn);
}
if (LoginName.Length < 1)
{
stReturn.NReturn = -1;
stReturn.ExMsg = "登录不允许为空";
return(stReturn);
}
if (LoginPwd.Length < 1)
{
stReturn.NReturn = -1;
stReturn.ExMsg = "密码不允许为空";
return(stReturn);
}
System.Security.Cryptography.SHA512 SHA512 = System.Security.Cryptography.SHA512.Create();
byte[] binLoginPwd = SHA512.ComputeHash(System.Text.Encoding.Unicode.GetBytes(LoginPwd));
string SqlLoginPwd = Apq.Data.SqlClient.Common.ConvertToSqlON(binLoginPwd);
DbConnection SqlConn = null;
using (SqlConn = Apq.DBC.Common.CreateDBConnection("Dinner", ref SqlConn))
{
Apq.Data.Common.DbConnectionHelper dbch = new Apq.Data.Common.DbConnectionHelper(SqlConn);
DbCommand sc = SqlConn.CreateCommand();
sc.CommandText = "dbo.Dinner_RegEmployee";
sc.CommandType = CommandType.StoredProcedure;
Apq.Data.Common.DbCommandHelper dch = new Apq.Data.Common.DbCommandHelper(sc);
dch.AddParameter("rtn", 0, DbType.Int32);
dch.AddParameter("ExMsg", stReturn.ExMsg, DbType.String, -1);
//IPAddress ipa;
//if (IPAddress.TryParse(HttpContext.Current.Request.UserHostAddress, out ipa))
//{
// dch.AddParameter("RegIP", ipa.GetAddressBytes());
//}
dch.AddParameter("EmName", EmName);
dch.AddParameter("LoginName", LoginName);
dch.AddParameter("LoginPwd", binLoginPwd);
//dch.AddParameter("Sex", Sex);
//dch.AddParameter("PhotoUrl", PhotoUrl);
//dch.AddParameter("Birthday", Birthday.ToString("yyyy-MM-dd HH:mm:ss.fff"));
//dch.AddParameter("Alipay", Alipay);
//dch.AddParameter("IntroUserID", IntroUserID);
//dch.AddParameter("UserType", UserType);
//dch.AddParameter("Expire", DateTime.Now.AddYears(10).ToString("yyyy-MM-dd HH:mm:ss.fff"));
//dch.AddParameter("IDCard", IDCard);
//dch.AddParameter("IDCard_Name", IDCard_Name);
//dch.AddParameter("IDCard_Sex", IDCard_Sex);
//dch.AddParameter("IDCard_PhotoUrl", IDCard_PhotoUrl);
dch.AddParameter("EmID", 0, DbType.Int64);
dch.AddParameter("LoginID", 0, DbType.Int64);
sc.Parameters["rtn"].Direction = ParameterDirection.ReturnValue;
sc.Parameters["ExMsg"].Direction = ParameterDirection.InputOutput;
sc.Parameters["EmID"].Direction = ParameterDirection.InputOutput;
sc.Parameters["LoginID"].Direction = ParameterDirection.InputOutput;
SqlConn.Open();
sc.ExecuteNonQuery();
stReturn.NReturn = System.Convert.ToInt32(sc.Parameters["rtn"].Value);
stReturn.ExMsg = sc.Parameters["ExMsg"].Value.ToString();
stReturn.POuts.Add(sc.Parameters["EmID"].Value);
stReturn.POuts.Add(sc.Parameters["LoginID"].Value);
stReturn.POuts.Add(LoginName);
stReturn.POuts.Add(SqlLoginPwd);
sc.Dispose();
SqlConn.Close();
}
return(stReturn);
}
// 生成初始数据语句并保存到文件
private void tsmiData_Click(object sender, EventArgs e)
{
try
{
_DBConnection.Open();
}
catch
{
MessageBox.Show(this, Apq.GlobalObject.UILang["数据库未连接或已断开"]);
return;
}
finally
{
Apq.Data.Common.DbConnectionHelper.Close(_DBConnection);
}
Apq.Data.Common.DbConnectionHelper dch = new Apq.Data.Common.DbConnectionHelper(_DBConnection);
StringBuilder sb = new StringBuilder();
sfdMeta.InitialDirectory = GlobalObject.XmlConfigChain[this.GetType(), "sfdMeta_InitialDirectory"];
if (sfdMeta.ShowDialog(this) == System.Windows.Forms.DialogResult.OK)
{
GlobalObject.XmlConfigChain[this.GetType(), "sfdMeta_InitialDirectory"] = Path.GetDirectoryName(sfdMeta.FileName);
if (tscbSqlProduct.SelectedIndex == 0)
{
#region MySql
#region 初始数据文件头
// 为列表中已选中的表生成初始化语句，语句功能：完成将数据库中的数据插入到对应的表中，按主键添加
sb.Append(string.Format(@"
USE `{0}`;
-- 为INSERT准备临时表
DROP TABLE IF EXISTS `_Apq_Ins`;
CREATE TEMPORARY TABLE `_Apq_Ins` (
`_ID` INT(11) DEFAULT NULL
) ENGINE = HEAP;
INSERT INTO `_Apq_Ins`(`_ID`) VALUES (1);
-- 初始数据", _DBConnection.Database)
);
#endregion
foreach (SqlGen_XSD.MetaRow dr in _xsd.Meta.Rows)
{
if (dr._CheckState == 0 || dr.ObjectType != 1)
{
continue;
}
SqlGen_XSD.dbv_tableRow drT = _xsd.dbv_table.FindByTableName(dr.SchemaName, dr.ObjectName);
SqlGen_XSD.dbv_columnRow[] drCs = drT.GetChildRows("FK_dbv_table_dbv_column") as SqlGen_XSD.dbv_columnRow[];
#region 数据Key
/*
1.dbv_table_key已有指定数据Key时，使用此值
2.否则查找其它表中的存在同名主键列(2列以上)，看作关联型数据
3.否则将主键作为对象型数据Key，加入到WHERE条件中
4.否则将自增列作为对象型数据Key，加入到WHERE条件中
*/
List<SqlGen_XSD.dbv_columnRow> colKeys = new List<SqlGen_XSD.dbv_columnRow>();
if (colKeys.Count == 0)
{
SqlGen_XSD.dbv_table_keyRow drTK = _xsd.dbv_table_key.FindBySchemaNameTableName(dr.SchemaName, dr.ObjectName);
if (drTK != null)
{
string[] aryDataKeys = drTK.PrimaryKeys.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
for (int i = 0; i < aryDataKeys.Length; i++)
{
aryDataKeys[i] = Apq.Data.MySqlClient.Common.ConvertToSqlON(aryDataKeys[i]);
}
SqlGen_XSD.dbv_columnRow[] drDataKeys = _xsd.dbv_column.Select(string.Format("SchemaName = {0} AND TableName = {1} AND ColName IN ({2})",
Apq.Data.MySqlClient.Common.ConvertToSqlON(dr.SchemaName),
Apq.Data.MySqlClient.Common.ConvertToSqlON(dr.ObjectName),
string.Join(",", aryDataKeys))) as SqlGen_XSD.dbv_columnRow[];
colKeys.AddRange(drDataKeys);
}
}
if (colKeys.Count == 0)
{
foreach (SqlGen_XSD.dbv_columnRow drC in drCs)
{
DataRow[] aryTemp = _xsd.dbv_column.Select(string.Format("TableName <> {0} AND ColName = {1} AND COLUMN_KEY = 'PRI'",
Apq.Data.MySqlClient.Common.ConvertToSqlON(drC.TableName),
Apq.Data.MySqlClient.Common.ConvertToSqlON(drC.ColName)));
if (aryTemp != null && aryTemp.Length > 0)
{
colKeys.Add(drC);
}
}
if (colKeys.Count < 2)
{
colKeys.Clear();
}
}
if (colKeys.Count == 0)
{
foreach (SqlGen_XSD.dbv_columnRow drC in drCs)
{
if ("PRI".Equals(drC.COLUMN_KEY, StringComparison.OrdinalIgnoreCase))
{
colKeys.Add(drC);
}
}
}
if (colKeys.Count == 0)
{
foreach (SqlGen_XSD.dbv_columnRow drC in drCs)
{
if (drC.is_auto_increment)
{
colKeys.Add(drC);
}
}
}
#endregion
#region 输出列
string strCols = string.Empty;
List<SqlGen_XSD.dbv_columnRow> colInserts = new List<SqlGen_XSD.dbv_columnRow>();
foreach (SqlGen_XSD.dbv_columnRow drC in drCs)
{
if (drC.is_auto_increment && !colKeys.Contains(drC))
{
// 跳过无意义的自增列
continue;
}
colInserts.Add(drC);
strCols += ",`" + drC.ColName + "`";
}
#endregion
#region WHERE语句(替换模式)
string strWhereTmp = string.Empty;// WHERE 条件
if (colKeys.Count > 0)
{
for (int i = 0; i < colKeys.Count; i++)
{
strWhereTmp += string.Format(" AND `{0}`", colKeys[i].ColName) + " = {" + i + "}";
}
if (strWhereTmp.Length > 1)
{
strWhereTmp = " WHERE" + strWhereTmp.Substring(4);
}
}
else
{
string strExMsg = string.Format("无法生成条件的表：{0}，请检查其数据Key设置、关联列名、主键、自增列。", drT.TableName);
ErrList.AddRow(DateTime.Now, 4, 16, Apq.GlobalObject.UILang["生成表失败"], strExMsg);
continue;
}
#endregion
if (strCols.Length > 1)
{
#region 表说明
strCols = strCols.Substring(1);
sb.Append(string.Format(@"
-- 表：`{0}`", drT.TableName));
#endregion
#region 分页查询并输出一个表
long CRow = 0, PageSize = 1000;
while (true)
{
#region 从数据库读取一页
DataSet ds = new DataSet();
DbDataAdapter dda = dch.CreateAdapter(string.Format(@"SELECT * FROM {0} LIMIT {1},{2};", drT.TableName, CRow, PageSize));
int RowCount = dda.Fill(ds);
#endregion
if (RowCount > 0)
{
foreach (DataRow drData in ds.Tables[0].Rows)
{
#region 输出一行
List<object> objWhere = new List<object>();
for (int i = 0; i < colKeys.Count; i++)
{
objWhere.Add(Apq.Data.MySqlClient.Common.ConvertToSqlON(drData[colKeys[i].ColName]));
}
List<string> aryValues = new List<string>();
for (int i = 0; i < colInserts.Count; i++)
{
aryValues.Add(Apq.Data.MySqlClient.Common.ConvertToSqlON(drData[colInserts[i].ColName]));
}
string strValues = string.Join(",", aryValues);
string strWhere = string.Format(strWhereTmp, objWhere.ToArray());
sb.Append(string.Format(@"
INSERT INTO `{0}`({1}) SELECT {2} FROM `_Apq_Ins` WHERE NOT EXISTS(SELECT 1 FROM `{0}`{3});", drT.TableName, strCols, strValues, strWhere));
#endregion
}
}
if (RowCount < PageSize)
{
break;
}
CRow += RowCount;
}
#endregion
}
}
#region 文件尾
sb.Append(@"
-- 删除临时表
DROP TABLE IF EXISTS `_Apq_Ins`;
COMMIT;
");
#endregion
#endregion
}
if (tscbSqlProduct.SelectedIndex == 1)
{
#region MsSql
// 为列表中已选中的表生成初始化语句，语句功能：完成将数据库中的数据插入到对应的表中，按主键添加
sb.Append(string.Format(@"
USE [{0}];
-- 初始数据", _DBConnection.Database)
);
foreach (SqlGen_XSD.MetaRow dr in _xsd.Meta.Rows)
{
if (dr._CheckState == 0 || dr.ObjectType != 1)
{
continue;
}
SqlGen_XSD.dbv_tableRow drT = _xsd.dbv_table.FindByTableName(dr.SchemaName, dr.ObjectName);
SqlGen_XSD.dbv_columnRow[] drCs = drT.GetChildRows("FK_dbv_table_dbv_column") as SqlGen_XSD.dbv_columnRow[];
#region 计算WHERE语句(替换模式)
/*
1.dbv_table_key已有指定数据Key时，使用此值
2.否则查找其它表中的存在同名主键列(2列以上)，看作关联型数据
3.否则将主键作为对象型数据Key，加入到WHERE条件中
4.否则将自增列作为对象型数据Key，加入到WHERE条件中
*/
List<SqlGen_XSD.dbv_columnRow> colKeys = new List<SqlGen_XSD.dbv_columnRow>();
if (colKeys.Count == 0)
{
SqlGen_XSD.dbv_table_keyRow drTK = _xsd.dbv_table_key.FindBySchemaNameTableName(dr.SchemaName, dr.ObjectName);
if (drTK != null)
{
string[] aryDataKeys = drTK.PrimaryKeys.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
for (int i = 0; i < aryDataKeys.Length; i++)
{
aryDataKeys[i] = Apq.Data.SqlClient.Common.ConvertToSqlON(aryDataKeys[i]);
}
SqlGen_XSD.dbv_columnRow[] drDataKeys = _xsd.dbv_column.Select(string.Format("SchemaName = {0} AND TableName = {1} AND ColName IN ({2})",
Apq.Data.SqlClient.Common.ConvertToSqlON(dr.SchemaName),
Apq.Data.SqlClient.Common.ConvertToSqlON(dr.ObjectName),
string.Join(",", aryDataKeys))) as SqlGen_XSD.dbv_columnRow[];
colKeys.AddRange(drDataKeys);
}
}
if (colKeys.Count == 0)
{
foreach (SqlGen_XSD.dbv_columnRow drC in drCs)
{
DataRow[] aryTemp = _xsd.dbv_column.Select(string.Format("TableName <> {0} AND ColName = {1} AND COLUMN_KEY = 'PRI'",
Apq.Data.SqlClient.Common.ConvertToSqlON(drC.TableName),
Apq.Data.SqlClient.Common.ConvertToSqlON(drC.ColName)));
if (aryTemp != null && aryTemp.Length > 0)
{
colKeys.Add(drC);
}
}
if (colKeys.Count < 2)
{
colKeys.Clear();
}
}
if (colKeys.Count == 0)
{
foreach (SqlGen_XSD.dbv_columnRow drC in drCs)
{
if ("PRI".Equals(drC.COLUMN_KEY, StringComparison.OrdinalIgnoreCase))
{
colKeys.Add(drC);
}
}
}
if (colKeys.Count == 0)
{
foreach (SqlGen_XSD.dbv_columnRow drC in drCs)
{
if (drC.is_auto_increment)
{
colKeys.Add(drC);
}
}
}
string strWhereTmp = string.Empty;// WHERE 条件
if (colKeys.Count > 0)
{
for (int i = 0; i < colKeys.Count; i++)
{
strWhereTmp += string.Format(" AND [{0}]", colKeys[i].ColName) + " = {" + i + "}";
}
if (strWhereTmp.Length > 1)
{
strWhereTmp = " WHERE" + strWhereTmp.Substring(4);
}
}
else
{
string strExMsg = string.Format("无法生成条件的表：{0}，请检查其数据Key设置、关联列名、主键、自增列。", drT.TableName);
ErrList.AddRow(DateTime.Now, 4, 16, Apq.GlobalObject.UILang["生成表失败"], strExMsg);
continue;
}
#endregion
//将表读到内存，并为每一行生成脚本
DataSet ds = new DataSet();
DbDataAdapter dda = dch.CreateAdapter(string.Format(@"SELECT * FROM {0}", drT.TableName));
dda.Fill(ds);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
string strCols = string.Empty;
List<SqlGen_XSD.dbv_columnRow> colInserts = new List<SqlGen_XSD.dbv_columnRow>();
foreach (SqlGen_XSD.dbv_columnRow drC in drCs)
{
if (drC.is_auto_increment && !colKeys.Contains(drC))
{
// 跳过无意义的自增列
continue;
}
colInserts.Add(drC);
strCols += ",[" + drC.ColName + "]";
}
if (strCols.Length > 1)
{
strCols = strCols.Substring(1);
sb.Append(string.Format(@"
-- 表：[{0}]", drT.TableName));
}
foreach (DataRow drData in ds.Tables[0].Rows)
{
List<object> objWhere = new List<object>();
for (int i = 0; i < colKeys.Count; i++)
{
objWhere.Add(Apq.Data.SqlClient.Common.ConvertToSqlON(drData[colKeys[i].ColName]));
}
List<string> aryValues = new List<string>();
for (int i = 0; i < colInserts.Count; i++)
{
aryValues.Add(Apq.Data.SqlClient.Common.ConvertToSqlON(drData[colInserts[i].ColName]));
}
string strValues = string.Join(",", aryValues);
string strWhere = string.Format(strWhereTmp, objWhere.ToArray());
sb.Append(string.Format(@"
IF(NOT EXISTS(SELECT TOP(1) 1 FROM [dbo].[{0}]{3})) INSERT INTO [dbo].[{0}]({1}) VALUES({2})", drT.TableName, strCols, strValues, strWhere));
}
}
}
sb.Append(@"
");
#endregion
}
File.WriteAllText(sfdMeta.FileName, sb.ToString());
MessageBox.Show(this, Apq.GlobalObject.UILang["保存成功！"]);
if (ErrList.ErrXSD.ErrList.Rows.Count > 0)
{
ErrList.Show(SqlGenPanel.DockDoc);
}
}
}
//[ScriptMethod(ResponseFormat = System.Web.Script.Services.ResponseFormat.Xml, XmlSerializeString = true)]
public Apq.STReturn Dinner_Stat_EmDinner_Food(DateTime BTime, DateTime ETime, bool State)
{
Apq.STReturn stReturn = new Apq.STReturn();
DinnerSession ApqSession = new DinnerSession(Session);
if (!CheckLoginPage.CheckAdmin(ref stReturn, ApqSession))
{
return stReturn;
}
DataSet ds = new DataSet();
DbConnection SqlConn = null;
using (SqlConn = Apq.DBC.Common.CreateDBConnection("Dinner", ref SqlConn))
{
Apq.Data.Common.DbConnectionHelper dbch = new Apq.Data.Common.DbConnectionHelper(SqlConn);
DbDataAdapter sda = dbch.CreateAdapter();
sda.SelectCommand.CommandText = "dbo.Dinner_Stat_EmDinner_Food";
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
Apq.Data.Common.DbCommandHelper dch = new Apq.Data.Common.DbCommandHelper(sda.SelectCommand);
dch.AddParameter("rtn", 0, DbType.Int32);
//dch.AddParameter("ExMsg", stReturn.ExMsg, DbType.String, -1);
dch.AddParameter("BTime", BTime);
dch.AddParameter("ETime", ETime);
dch.AddParameter("State", State);
sda.SelectCommand.Parameters["rtn"].Direction = ParameterDirection.ReturnValue;
//sda.SelectCommand.Parameters["ExMsg"].Direction = ParameterDirection.InputOutput;
SqlConn.Open();
sda.Fill(ds);
stReturn.NReturn = System.Convert.ToInt32(sda.SelectCommand.Parameters["rtn"].Value);
//stReturn.ExMsg = sda.SelectCommand.Parameters["ExMsg"].Value.ToString();
stReturn.FNReturn = ds.Tables[0];
sda.Dispose();
SqlConn.Close();
}
return stReturn;
}
//[ScriptMethod(ResponseFormat = System.Web.Script.Services.ResponseFormat.Xml, XmlSerializeString = true)]
public Apq.STReturn Dinner_Restaurant_Save(long RestID, string RestName, string RestAddr)
{
Apq.STReturn stReturn = new Apq.STReturn();
DinnerSession ApqSession = new DinnerSession(Session);
if (!CheckLoginPage.CheckAdmin(ref stReturn, ApqSession))
{
return stReturn;
}
DbConnection SqlConn = null;
using (SqlConn = Apq.DBC.Common.CreateDBConnection("Dinner", ref SqlConn))
{
Apq.Data.Common.DbConnectionHelper dbch = new Apq.Data.Common.DbConnectionHelper(SqlConn);
DbCommand sc = SqlConn.CreateCommand();
sc.CommandText = "dbo.Dinner_Restaurant_Save";
sc.CommandType = CommandType.StoredProcedure;
Apq.Data.Common.DbCommandHelper dch = new Apq.Data.Common.DbCommandHelper(sc);
dch.AddParameter("rtn", 0, DbType.Int32);
dch.AddParameter("ExMsg", stReturn.ExMsg, DbType.String, -1);
dch.AddParameter("RestID", RestID);
dch.AddParameter("RestName", RestName);
dch.AddParameter("RestAddr", RestAddr);
sc.Parameters["rtn"].Direction = ParameterDirection.ReturnValue;
sc.Parameters["ExMsg"].Direction = ParameterDirection.InputOutput;
sc.Parameters["RestID"].Direction = ParameterDirection.InputOutput;
SqlConn.Open();
sc.ExecuteNonQuery();
stReturn.NReturn = System.Convert.ToInt32(sc.Parameters["rtn"].Value);
stReturn.ExMsg = Apq.Convert.ChangeType<string>(sc.Parameters["ExMsg"].Value);
stReturn.POuts.Add(sc.Parameters["RestID"]);
sc.Dispose();
SqlConn.Close();
}
return stReturn;
}
/// <summary>
/// 执行登录存储过程
/// </summary>
/// <param name="LoginName">用户名</param>
/// <param name="binLoginPwd">密码</param>
/// <returns></returns>
private Apq.STReturn DBLogin(string LoginName, byte[] binLoginPwd)
{
Apq.STReturn stReturn = new Apq.STReturn();
DataSet ds = new DataSet();
DbConnection SqlConn = null;
using (SqlConn = Apq.DBC.Common.CreateDBConnection("Dinner", ref SqlConn))
{
Apq.Data.Common.DbConnectionHelper dbch = new Apq.Data.Common.DbConnectionHelper(SqlConn);
DbDataAdapter sda = dbch.CreateAdapter();
sda.SelectCommand.CommandText = "dbo.Dinner_Login";
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
Apq.Data.Common.DbCommandHelper dch = new Apq.Data.Common.DbCommandHelper(sda.SelectCommand);
dch.AddParameter("rtn", 0, DbType.Int32);
dch.AddParameter("ExMsg", stReturn.ExMsg, DbType.String, -1);
dch.AddParameter("LoginName", LoginName);
dch.AddParameter("LoginPwd", binLoginPwd);
sda.SelectCommand.Parameters["rtn"].Direction = ParameterDirection.ReturnValue;
sda.SelectCommand.Parameters["ExMsg"].Direction = ParameterDirection.InputOutput;
SqlConn.Open();
sda.Fill(ds);
stReturn.NReturn = System.Convert.ToInt32(sda.SelectCommand.Parameters["rtn"].Value);
stReturn.ExMsg = sda.SelectCommand.Parameters["ExMsg"].Value.ToString();
if (ds.Tables.Count > 0)
{
stReturn.FNReturn = ds.Tables[0];
}
sda.Dispose();
SqlConn.Close();
}
// 返回调用者
string SqlLoginPwd = Apq.Data.SqlClient.Common.ConvertToSqlON(binLoginPwd);
if (stReturn.NReturn > 0 && ds.Tables[0].Rows.Count > 0)
{
stReturn.NReturn = 1;
stReturn.FNReturn = ds.Tables[0];
stReturn.POuts.Add(SqlLoginPwd);
}
return stReturn;
}
//[ScriptMethod(ResponseFormat = System.Web.Script.Services.ResponseFormat.Xml, XmlSerializeString = true)]
public Apq.STReturn Dinner_EmDinner_Delete(long ID)
{
Apq.STReturn stReturn = new Apq.STReturn();
DinnerSession ApqSession = new DinnerSession(Session);
if (!CheckLoginPage.CheckLogin(ref stReturn, ApqSession))
{
return stReturn;
}
DataSet ds = new DataSet();
DbConnection SqlConn = null;
using (SqlConn = Apq.DBC.Common.CreateDBConnection("Dinner", ref SqlConn))
{
Apq.Data.Common.DbConnectionHelper dbch = new Apq.Data.Common.DbConnectionHelper(SqlConn);
DbCommand sc = SqlConn.CreateCommand();
sc.CommandText = "dbo.Dinner_EmDinner_Delete";
sc.CommandType = CommandType.StoredProcedure;
Apq.Data.Common.DbCommandHelper dch = new Apq.Data.Common.DbCommandHelper(sc);
dch.AddParameter("rtn", 0, DbType.Int32);
dch.AddParameter("ExMsg", stReturn.ExMsg, DbType.String, -1);
dch.AddParameter("ID", ID);
sc.Parameters["rtn"].Direction = ParameterDirection.ReturnValue;
sc.Parameters["ExMsg"].Direction = ParameterDirection.InputOutput;
SqlConn.Open();
sc.ExecuteNonQuery();
stReturn.NReturn = System.Convert.ToInt32(sc.Parameters["rtn"].Value);
stReturn.ExMsg = sc.Parameters["ExMsg"].Value.ToString();
sc.Dispose();
SqlConn.Close();
}
return stReturn;
}
//[ScriptMethod(ResponseFormat = System.Web.Script.Services.ResponseFormat.Xml, XmlSerializeString = true)]
public Apq.STReturn Dinner_EmDinner_ListPager(int start, int limit)
{
int Pager_Page = start / limit;
int Pager_PageSize = limit;
Apq.STReturn stReturn = new Apq.STReturn();
DinnerSession ApqSession = new DinnerSession(Session);
if (!CheckLoginPage.CheckLogin(ref stReturn, ApqSession))
{
return stReturn;
}
DataSet ds = new DataSet();
DbConnection SqlConn = null;
using (SqlConn = Apq.DBC.Common.CreateDBConnection("Dinner", ref SqlConn))
{
Apq.Data.Common.DbConnectionHelper dbch = new Apq.Data.Common.DbConnectionHelper(SqlConn);
DbDataAdapter sda = dbch.CreateAdapter();
sda.SelectCommand.CommandText = "dbo.Dinner_EmDinner_ListPager";
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
Apq.Data.Common.DbCommandHelper dch = new Apq.Data.Common.DbCommandHelper(sda.SelectCommand);
dch.AddParameter("rtn", 0, DbType.Int32);
//dch.AddParameter("ExMsg", stReturn.ExMsg, DbType.String, -1);
dch.AddParameter("Pager_Page", Pager_Page, DbType.Int32);
dch.AddParameter("Pager_PageSize", Pager_PageSize);
dch.AddParameter("Pager_RowCount", 0, DbType.Int32);
dch.AddParameter("EmID", ApqSession.Employee.EmID);
sda.SelectCommand.Parameters["rtn"].Direction = ParameterDirection.ReturnValue;
//sda.SelectCommand.Parameters["ExMsg"].Direction = ParameterDirection.InputOutput;
sda.SelectCommand.Parameters["Pager_RowCount"].Direction = ParameterDirection.InputOutput;
SqlConn.Open();
sda.Fill(ds);
stReturn.NReturn = System.Convert.ToInt32(sda.SelectCommand.Parameters["rtn"].Value);
//stReturn.ExMsg = sda.SelectCommand.Parameters["ExMsg"].Value.ToString();
stReturn.FNReturn = ds.Tables[0];
stReturn.POuts.Add(sda.SelectCommand.Parameters["Pager_Page"].Value);
stReturn.POuts.Add(sda.SelectCommand.Parameters["Pager_RowCount"].Value);
sda.Dispose();
SqlConn.Close();
}
return stReturn;
}
//[ScriptMethod(ResponseFormat = System.Web.Script.Services.ResponseFormat.Xml, XmlSerializeString = true)]
public Apq.STReturn UserEditLoginPwd(string LoginPwd_C, string LoginPwd)
{
Apq.STReturn stReturn = new Apq.STReturn();
DinnerSession ApqSession = new DinnerSession(Session);
if (!CheckLoginPage.CheckLogin(ref stReturn, ApqSession))
{
return stReturn;
}
// 检测旧密码
System.Security.Cryptography.SHA512 SHA512 = System.Security.Cryptography.SHA512.Create();
byte[] binLoginPwd_C = SHA512.ComputeHash(System.Text.Encoding.Unicode.GetBytes(LoginPwd_C));
string SqlLoginPwd_C = Apq.Data.SqlClient.Common.ConvertToSqlON(binLoginPwd_C);
string SqlLoginPwd_DB = Apq.Data.SqlClient.Common.ConvertToSqlON(ApqSession.ApqLogin.LoginPwd);
if (SqlLoginPwd_C != SqlLoginPwd_DB)
{
stReturn.NReturn = -1;
stReturn.ExMsg = "原密码输入错误";
return stReturn;
}
byte[] binLoginPwd = SHA512.ComputeHash(System.Text.Encoding.Unicode.GetBytes(LoginPwd));
DbConnection SqlConn = null;
using (SqlConn = Apq.DBC.Common.CreateDBConnection("Dinner", ref SqlConn))
{
Apq.Data.Common.DbConnectionHelper dbch = new Apq.Data.Common.DbConnectionHelper(SqlConn);
DbCommand sc = SqlConn.CreateCommand();
sc.CommandText = "dbo.Dinner_User_UpdateLoginPwd";
sc.CommandType = CommandType.StoredProcedure;
Apq.Data.Common.DbCommandHelper dch = new Apq.Data.Common.DbCommandHelper(sc);
dch.AddParameter("rtn", 0, DbType.Int32);
dch.AddParameter("ExMsg", stReturn.ExMsg, DbType.String, -1);
dch.AddParameter("LoginID", ApqSession.ApqLogin.LoginID);
dch.AddParameter("LoginPwd", binLoginPwd);
sc.Parameters["rtn"].Direction = ParameterDirection.ReturnValue;
sc.Parameters["ExMsg"].Direction = ParameterDirection.InputOutput;
SqlConn.Open();
sc.ExecuteNonQuery();
stReturn.NReturn = System.Convert.ToInt32(sc.Parameters["rtn"].Value);
stReturn.ExMsg = sc.Parameters["ExMsg"].Value.ToString();
sc.Dispose();
SqlConn.Close();
}
// 更新Session中的密码信息
ApqSession.ApqLogin.LoginPwd = binLoginPwd;
// 返回客户端
stReturn.FNReturn = Apq.Data.SqlClient.Common.ConvertToSqlON(binLoginPwd);
return stReturn;
}
// 刷新
private void tsbRefresh_Click(object sender, EventArgs e)
{
try
{
_DBConnection.Open();
}
catch
{
MessageBox.Show(this, Apq.GlobalObject.UILang["数据库未连接或已断开"]);
return;
}
finally
{
Apq.Data.Common.DbConnectionHelper.Close(_DBConnection);
}
ErrList.ErrXSD.ErrList.Clear();
_xsd.Meta.Rows.Clear();
_xsd.dbv_trigger.Rows.Clear();
_xsd.dbv_proc.Rows.Clear();
_xsd.dbv_column.Rows.Clear();
_xsd.dbv_table.Rows.Clear();
try
{
Apq.Data.Common.DbConnectionHelper dch = new Apq.Data.Common.DbConnectionHelper(_DBConnection);
DbDataAdapter dda = dch.CreateAdapter();
if (_DBConnectionType == Apq.Data.Common.DBProduct.MySql)
{
// 表
dda.SelectCommand.CommandText = @"
SELECT `TABLE_SCHEMA` as SchemaName,`Table_Name` as TableName,`ENGINE`,`CREATE_OPTIONS`,`TABLE_COMMENT`,'' as PrimaryKeys
FROM `information_schema`.`TABLES`
WHERE `TABLE_SCHEMA` = DATABASE() AND `Table_Name` NOT IN ('dbv_BuildLog','dbv_table','dbv_column','dbv_trigger','dbv_view','dbv_proc');
";
dda.Fill(_xsd.dbv_table);
// 列
dda.SelectCommand.CommandText = @"
SELECT `COLUMN_NAME` as ColName,
CONVERT(`COLUMN_DEFAULT` USING utf8) AS DefaultValue,
CASE `IS_NULLABLE` WHEN 'NO' THEN 0 ELSE 1 END as NullAble,
`DATA_TYPE`,`CHARACTER_MAXIMUM_LENGTH`,`CHARACTER_OCTET_LENGTH`,`NUMERIC_PRECISION`,`NUMERIC_SCALE`,`CHARACTER_SET_NAME`,`COLLATION_NAME`,
CONVERT(`COLUMN_TYPE` USING utf8) AS COLUMN_TYPE,
`COLUMN_KEY`,
CASE WHEN INSTR(`EXTRA`,'auto_increment') > 0 THEN 1 ELSE 0 END as is_auto_increment,
`COLUMN_COMMENT`,c.`TABLE_SCHEMA` as SchemaName,c.`TABLE_NAME` as TableName
FROM `information_schema`.`COLUMNS` c
INNER JOIN `information_schema`.`TABLES` t ON c.`TABLE_SCHEMA` = t.`TABLE_SCHEMA` AND c.`TABLE_NAME` = t.`TABLE_NAME`
-- INNER JOIN `dbv_table` dt ON t.`TABLE_SCHEMA` = dt.`SchemaName` AND t.`TABLE_NAME` = dt.`TableName`
WHERE c.`TABLE_SCHEMA` = DATABASE() AND t.`Table_Name` NOT IN ('dbv_BuildLog','dbv_table','dbv_column','dbv_trigger','dbv_view','dbv_proc');
";
dda.Fill(_xsd.dbv_column);
// 视图
dda.SelectCommand.CommandText = @"
SELECT `TABLE_SCHEMA` as SchemaName,`Table_Name` as TableName,`VIEW_DEFINITION`
FROM `information_schema`.`VIEWS`
WHERE `TABLE_SCHEMA` = DATABASE();
";
dda.Fill(_xsd.dbv_view);
// 存储过程,函数
dda.SelectCommand.CommandText = @"
SELECT `db` AS SchemaName,`name` AS ProcName,
CASE `type` WHEN 'FUNCTION' THEN 4 ELSE 2 END AS type,
CONVERT(`param_list` USING utf8) AS param_list,
CONVERT(`returns` USING utf8) AS RETURNS,
CONVERT(`body` USING utf8) AS body,
`comment`
FROM `mysql`.`proc`
WHERE `db` = DATABASE() AND `name` NOT IN ('Apq_CreateNewTable');
";
dda.Fill(_xsd.dbv_proc);
// 触发器
dda.SelectCommand.CommandText = @"
SELECT `TRIGGER_SCHEMA` as SchemaName,`TRIGGER_NAME` as TriName,`EVENT_MANIPULATION`,`EVENT_OBJECT_TABLE` as TableName,`ACTION_ORDER`,`ACTION_CONDITION`,`ACTION_STATEMENT`,`ACTION_ORIENTATION`,`ACTION_TIMING`
FROM `information_schema`.`TRIGGERS` t
WHERE t.`TRIGGER_SCHEMA` = DATABASE();
";
dda.Fill(_xsd.dbv_trigger);
// 为列设置TID
foreach (SqlGen_XSD.dbv_columnRow dr in _xsd.dbv_column.Rows)
{
SqlGen_XSD.dbv_tableRow drT = _xsd.dbv_table.FindByTableName(dr.SchemaName, dr.TableName);
dr.TID = drT.TID;
}
// 为表查找主键列
foreach (SqlGen_XSD.dbv_tableRow dr in _xsd.dbv_table.Rows)
{
SqlGen_XSD.dbv_columnRow[] drCs = dr.GetChildRows("FK_dbv_table_dbv_column") as SqlGen_XSD.dbv_columnRow[];
string strCols = string.Empty;
foreach (SqlGen_XSD.dbv_columnRow drc in drCs)
{
if ("PRI".Equals(drc.COLUMN_KEY, StringComparison.OrdinalIgnoreCase))
{
strCols += ",`" + drc.ColName + "`";
}
}
if (strCols.Length > 0)
{
dr.PrimaryKeys = strCols.Substring(1);
}
}
// 将名称加到元数据表，用于界面显示
int nCheckState = Apq.Convert.ChangeType<int>(tsmiSelectTable.CheckState);
foreach (SqlGen_XSD.dbv_tableRow drT in _xsd.dbv_table.Rows)
{
SqlGen_XSD.MetaRow drMeta = _xsd.Meta.NewMetaRow();
drMeta.ObjectType = 1;
drMeta._CheckState = nCheckState;
drMeta.SchemaName = drT.SchemaName;
drMeta.ObjectName = drT.TableName;
_xsd.Meta.Rows.Add(drMeta);
}
foreach (SqlGen_XSD.dbv_viewRow drV in _xsd.dbv_view.Rows)
{
SqlGen_XSD.MetaRow drMeta = _xsd.Meta.NewMetaRow();
drMeta.ObjectType = 6;
drMeta._CheckState = nCheckState;
drMeta.SchemaName = drV.SchemaName;
drMeta.ObjectName = drV.TableName;
_xsd.Meta.Rows.Add(drMeta);
}
nCheckState = Apq.Convert.ChangeType<int>(tsmiSelectProc.CheckState);
foreach (SqlGen_XSD.dbv_procRow drP in _xsd.dbv_proc.Rows)
{
SqlGen_XSD.MetaRow drMeta = _xsd.Meta.NewMetaRow();
drMeta.ObjectType = drP.type;
drMeta._CheckState = nCheckState;
drMeta.SchemaName = drP.SchemaName;
drMeta.ObjectName = drP.ProcName;
_xsd.Meta.Rows.Add(drMeta);
}
nCheckState = Apq.Convert.ChangeType<int>(tsmiSelectTrigger.CheckState);
foreach (SqlGen_XSD.dbv_triggerRow drTri in _xsd.dbv_trigger.Rows)
{
SqlGen_XSD.MetaRow drMeta = _xsd.Meta.NewMetaRow();
drMeta.ObjectType = 3;
drMeta._CheckState = nCheckState;
drMeta.SchemaName = drTri.SchemaName;
drMeta.ObjectName = drTri.TriName;
_xsd.Meta.Rows.Add(drMeta);
}
}
}
catch (Exception ex)
{
MessageBox.Show(this, ex.Message);
}
finally
{
if (ErrList.ErrXSD.ErrList.Rows.Count > 0)
{
ErrList.Show(SqlGenPanel.DockDoc);
}
}
}
/// <summary>
/// 到一个服务器执行并显示结果[多线程入口]
/// </summary>
/// <param name="ServerID"></param>
private void Worker_Start(object DBIID)
{
object rtLock0 = GetLock("0");
object rtLock = GetLock(DBIID.ToString());
DataView dvDBI = new DataView(_dsDBC.DBI);
dvDBI.RowFilter = "DBIID = " + DBIID;
DataRowView drv = dvDBI[0];
DataSet dsTabPage = new DataSet(drv["DBIName"].ToString());
dsTabPage.ExtendedProperties.Add("DBIID", DBIID);
// 0.结果DataSet准备
int nbliResult_ItemIndex = Apq.Convert.ChangeType <int>(Apq.Windows.Controls.ControlExtension.GetControlValues(this, "bliResult.ItemIndex"));
if (nbliResult_ItemIndex == 0) //分列
{
lock (rtLock0)
{
Apq.Windows.Delegates.Action_UI <ToolStripLabel>(this, tslDBName, delegate(ToolStripLabel ctrl)
{
lstds.Add(dsTabPage);
});
}
}
else //合并
{
dsTabPage = lstds[0];
}
DbConnection sc = null;
try
{
sc = Apq.DBC.Common.CreateDBIConnection(drv["DBIName"].ToString(), ref sc);
Apq.Data.Common.DbConnectionHelper dch = new Apq.Data.Common.DbConnectionHelper(sc);
DbDataAdapter sda = dch.CreateAdapter();
sda.SelectCommand.CommandTimeout = 86400; //3600*24
// 1.设置数据库
Apq.Data.Common.DbConnectionHelper.Open(sc);
string DBName = Apq.Convert.ChangeType <string>(Apq.Windows.Controls.ControlExtension.GetControlValues(this, "tscbDBName.Text"));
sc.ChangeDatabase(DBName); //更改数据库后再启用消息事件可防止执行到其它数据库
//sc.StatisticsEnabled = true;// 启用统计
//sc.FireInfoMessageEventOnUserErrors = true;// 启用消息事件
//sc.InfoMessage += new SqlInfoMessageEventHandler(rt.sc_InfoMessage);
// 2.准备语句
string[] arySql = Apq.Windows.Controls.ControlExtension.GetControlValues(this, "arySql") as string[];
// 3.执行语句
if (arySql != null)
{
int TableCount = 0;
for (int i = 0; i < arySql.Length; i++)
{
DataSet ds1 = new DataSet();
sda.SelectCommand.CommandText = arySql[i];
sda.Fill(ds1); // 连接对象启用消息事件后一般不会引发异常
foreach (DataTable ds1Table in ds1.Tables)
{
ds1Table.TableName = "Apq_Table" + TableCount++;
dsTabPage.Tables.Add(ds1Table.Copy());
}
}
}
}
catch (ThreadAbortException)
{
}
//catch (DbException ex)
//{
//}
catch (Exception ex)
{
// 标记本服执行出错
lock (rtLock)
{
Apq.Windows.Delegates.Action_UI <ToolStripLabel>(this, tslDBName, delegate(ToolStripLabel ctrl)
{
ErrList_XSD.ErrListRow drErrList = _dsErr.ErrList.NewErrListRow();
drErrList.DBIID = (int)DBIID;
drErrList["__DBIName"] = drv["DBIName"];
drErrList.s = ex.Message;
_dsErr.ErrList.Rows.Add(drErrList);
drv["Err"] = true;
});
}
Apq.GlobalObject.ApqLog.Warn(drv["DBIName"], ex);
}
finally
{
Apq.Data.Common.DbConnectionHelper.Close(sc);
lock (rtLock0)
{
Apq.Windows.Delegates.Action_UI <ToolStripLabel>(this, tslDBName, delegate(ToolStripLabel ctrl)
{
if (SqlEdit.tspb.Value < SqlEdit.tspb.Maximum)
{
SqlEdit.tspb.Value++;
if (SqlEdit.tspb.Value == SqlEdit.tspb.Maximum)
{
_dsDBC.DBI.AcceptChanges();
_dsErr.ErrList.AcceptChanges();
SqlEdit.tsslStatus.Text = "已全部完成";
tsbCancel.Enabled = false;
tsbExec.Enabled = true;
DataView dvErr = new DataView(_dsDBC.DBI);
dvErr.RowFilter = "Err = 1";
// 标记本服执行出错
if (dvErr.Count > 0)
{
SqlEdit.tsslStatus.Text += ",但有错误发生,请查看";
}
}
}
});
}
}
}
//[ScriptMethod(ResponseFormat = System.Web.Script.Services.ResponseFormat.Xml, XmlSerializeString = true)]
public Apq.STReturn Dinner_RegEmployee(string EmName, string LoginName, string LoginPwd)
{
Apq.STReturn stReturn = new Apq.STReturn();
if (EmName.Length < 1)
{
stReturn.NReturn = -1;
stReturn.ExMsg = "姓名不允许为空";
return stReturn;
}
if (LoginName.Length < 1)
{
stReturn.NReturn = -1;
stReturn.ExMsg = "登录不允许为空";
return stReturn;
}
if (LoginPwd.Length < 1)
{
stReturn.NReturn = -1;
stReturn.ExMsg = "密码不允许为空";
return stReturn;
}
System.Security.Cryptography.SHA512 SHA512 = System.Security.Cryptography.SHA512.Create();
byte[] binLoginPwd = SHA512.ComputeHash(System.Text.Encoding.Unicode.GetBytes(LoginPwd));
string SqlLoginPwd = Apq.Data.SqlClient.Common.ConvertToSqlON(binLoginPwd);
DbConnection SqlConn = null;
using (SqlConn = Apq.DBC.Common.CreateDBConnection("Dinner", ref SqlConn))
{
Apq.Data.Common.DbConnectionHelper dbch = new Apq.Data.Common.DbConnectionHelper(SqlConn);
DbCommand sc = SqlConn.CreateCommand();
sc.CommandText = "dbo.Dinner_RegEmployee";
sc.CommandType = CommandType.StoredProcedure;
Apq.Data.Common.DbCommandHelper dch = new Apq.Data.Common.DbCommandHelper(sc);
dch.AddParameter("rtn", 0, DbType.Int32);
dch.AddParameter("ExMsg", stReturn.ExMsg, DbType.String, -1);
//IPAddress ipa;
//if (IPAddress.TryParse(HttpContext.Current.Request.UserHostAddress, out ipa))
//{
// dch.AddParameter("RegIP", ipa.GetAddressBytes());
//}
dch.AddParameter("EmName", EmName);
dch.AddParameter("LoginName", LoginName);
dch.AddParameter("LoginPwd", binLoginPwd);
//dch.AddParameter("Sex", Sex);
//dch.AddParameter("PhotoUrl", PhotoUrl);
//dch.AddParameter("Birthday", Birthday.ToString("yyyy-MM-dd HH:mm:ss.fff"));
//dch.AddParameter("Alipay", Alipay);
//dch.AddParameter("IntroUserID", IntroUserID);
//dch.AddParameter("UserType", UserType);
//dch.AddParameter("Expire", DateTime.Now.AddYears(10).ToString("yyyy-MM-dd HH:mm:ss.fff"));
//dch.AddParameter("IDCard", IDCard);
//dch.AddParameter("IDCard_Name", IDCard_Name);
//dch.AddParameter("IDCard_Sex", IDCard_Sex);
//dch.AddParameter("IDCard_PhotoUrl", IDCard_PhotoUrl);
dch.AddParameter("EmID", 0, DbType.Int64);
dch.AddParameter("LoginID", 0, DbType.Int64);
sc.Parameters["rtn"].Direction = ParameterDirection.ReturnValue;
sc.Parameters["ExMsg"].Direction = ParameterDirection.InputOutput;
sc.Parameters["EmID"].Direction = ParameterDirection.InputOutput;
sc.Parameters["LoginID"].Direction = ParameterDirection.InputOutput;
SqlConn.Open();
sc.ExecuteNonQuery();
stReturn.NReturn = System.Convert.ToInt32(sc.Parameters["rtn"].Value);
stReturn.ExMsg = sc.Parameters["ExMsg"].Value.ToString();
stReturn.POuts.Add(sc.Parameters["EmID"].Value);
stReturn.POuts.Add(sc.Parameters["LoginID"].Value);
stReturn.POuts.Add(LoginName);
stReturn.POuts.Add(SqlLoginPwd);
sc.Dispose();
SqlConn.Close();
}
return stReturn;
}
/// <summary>
/// 下载文件
/// </summary>
public void FileDow(string DBFullName, string FullName)
{
string FileName = System.IO.Path.GetFileName(FullName);
string CFolder = System.IO.Path.GetDirectoryName(FullName);
string DBFolder = System.IO.Path.GetDirectoryName(DBFullName);
// 准备本地目录
if (!System.IO.Directory.Exists(CFolder))
{
System.IO.Directory.CreateDirectory(CFolder);
}
// 1.写入数据库
Apq.Data.Common.DbConnectionHelper.Open(Connection);
System.Data.Common.DbCommand sqlCmd = Connection.CreateCommand();
sqlCmd.CommandText = "Apq_FileTrans_Insert_ADO";
sqlCmd.CommandType = CommandType.StoredProcedure;
Apq.Data.Common.DbCommandHelper cmdHelper = new Apq.Data.Common.DbCommandHelper(sqlCmd);
cmdHelper.AddParameter("@FullName", DBFullName);
cmdHelper.AddParameter("@CFolder", CFolder);
cmdHelper.AddParameter("@ID", 0);
sqlCmd.Parameters["@ID"].Direction = ParameterDirection.InputOutput;
sqlCmd.ExecuteNonQuery();
int ID = Apq.Convert.ChangeType<int>(sqlCmd.Parameters["@ID"].Value);
// 2.读取到本地
DataSet ds = new DataSet();
Apq.Data.Common.DbConnectionHelper connHelper = new Apq.Data.Common.DbConnectionHelper(Connection);
DbDataAdapter da = connHelper.CreateAdapter();
da.SelectCommand.CommandText = "Apq_FileTrans_List";
da.SelectCommand.CommandType = CommandType.StoredProcedure;
Apq.Data.Common.DbCommandHelper daHelper = new Apq.Data.Common.DbCommandHelper(da.SelectCommand);
daHelper.AddParameter("@ID", ID);
da.Fill(ds);
// 3.保存文件
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
byte[] bFile = Apq.Convert.ChangeType<byte[]>(ds.Tables[0].Rows[0]["FileStream"]);
System.IO.FileStream fs = new System.IO.FileStream(FullName, System.IO.FileMode.Create);
fs.Write(bFile, 0, bFile.Length);
fs.Flush();
fs.Close();
}
// 4.删除数据库行
da.SelectCommand.CommandText = "Apq_FileTrans_Delete";
da.SelectCommand.ExecuteNonQuery();
}
/// <summary>
/// 到一个服务器执行并显示结果[多线程入口]
/// </summary>
/// <param name="ServerID"></param>
private void Worker_Start(object DBIID)
{
object rtLock0 = GetLock("0");
object rtLock = GetLock(DBIID.ToString());
DataView dvDBI = new DataView(_dsDBC.DBI);
dvDBI.RowFilter = "DBIID = " + DBIID;
DataRowView drv = dvDBI[0];
DataSet dsTabPage = new DataSet(drv["DBIName"].ToString());
dsTabPage.ExtendedProperties.Add("DBIID", DBIID);
// 0.结果DataSet准备
int nbliResult_ItemIndex = Apq.Convert.ChangeType<int>(Apq.Windows.Controls.ControlExtension.GetControlValues(this, "bliResult.ItemIndex"));
if (nbliResult_ItemIndex == 0)//分列
{
lock (rtLock0)
{
Apq.Windows.Delegates.Action_UI<ToolStripLabel>(this, tslDBName, delegate(ToolStripLabel ctrl)
{
lstds.Add(dsTabPage);
});
}
}
else//合并
{
dsTabPage = lstds[0];
}
DbConnection sc = null;
try
{
sc = Apq.DBC.Common.CreateDBIConnection(drv["DBIName"].ToString(), ref sc);
Apq.Data.Common.DbConnectionHelper dch = new Apq.Data.Common.DbConnectionHelper(sc);
DbDataAdapter sda = dch.CreateAdapter();
sda.SelectCommand.CommandTimeout = 86400;//3600*24
// 1.设置数据库
Apq.Data.Common.DbConnectionHelper.Open(sc);
string DBName = Apq.Convert.ChangeType<string>(Apq.Windows.Controls.ControlExtension.GetControlValues(this, "tscbDBName.Text"));
sc.ChangeDatabase(DBName);//更改数据库后再启用消息事件可防止执行到其它数据库
//sc.StatisticsEnabled = true;// 启用统计
//sc.FireInfoMessageEventOnUserErrors = true;// 启用消息事件
//sc.InfoMessage += new SqlInfoMessageEventHandler(rt.sc_InfoMessage);
// 2.准备语句
string[] arySql = Apq.Windows.Controls.ControlExtension.GetControlValues(this, "arySql") as string[];
// 3.执行语句
if (arySql != null)
{
int TableCount = 0;
for (int i = 0; i < arySql.Length; i++)
{
DataSet ds1 = new DataSet();
sda.SelectCommand.CommandText = arySql[i];
sda.Fill(ds1); // 连接对象启用消息事件后一般不会引发异常
foreach (DataTable ds1Table in ds1.Tables)
{
ds1Table.TableName = "Apq_Table" + TableCount++;
dsTabPage.Tables.Add(ds1Table.Copy());
}
}
}
}
catch (ThreadAbortException)
{
}
//catch (DbException ex)
//{
//}
catch (Exception ex)
{
// 标记本服执行出错
lock (rtLock)
{
Apq.Windows.Delegates.Action_UI<ToolStripLabel>(this, tslDBName, delegate(ToolStripLabel ctrl)
{
ErrList_XSD.ErrListRow drErrList = _dsErr.ErrList.NewErrListRow();
drErrList.DBIID = (int)DBIID;
drErrList["__DBIName"] = drv["DBIName"];
drErrList.s = ex.Message;
_dsErr.ErrList.Rows.Add(drErrList);
drv["Err"] = true;
});
}
Apq.GlobalObject.ApqLog.Warn(drv["DBIName"], ex);
}
finally
{
Apq.Data.Common.DbConnectionHelper.Close(sc);
lock (rtLock0)
{
Apq.Windows.Delegates.Action_UI<ToolStripLabel>(this, tslDBName, delegate(ToolStripLabel ctrl)
{
if (SqlEdit.tspb.Value < SqlEdit.tspb.Maximum)
{
SqlEdit.tspb.Value++;
if (SqlEdit.tspb.Value == SqlEdit.tspb.Maximum)
{
_dsDBC.DBI.AcceptChanges();
_dsErr.ErrList.AcceptChanges();
SqlEdit.tsslStatus.Text = "已全部完成";
tsbCancel.Enabled = false;
tsbExec.Enabled = true;
DataView dvErr = new DataView(_dsDBC.DBI);
dvErr.RowFilter = "Err = 1";
// 标记本服执行出错
if (dvErr.Count > 0)
{
SqlEdit.tsslStatus.Text += ",但有错误发生,请查看";
}
}
}
});
}
}
}
