//private
private async void queryInternal(string q, Tuple <string, dynamic>[] parameters, Guid g)
{
command = new MySqlCommand(q, conn);
if (parameters != null && parameters.Length > 0)
{
for (int i = 0; i < parameters.Length; i++)
{
command.Parameters.AddWithValue(parameters[i].Item1, parameters[i].Item2);
}
}
DbDataReader reader = null;
try {
reader = await command.ExecuteReaderAsync();
} catch (Exception ex) {
OnError?.Invoke(this, new SQLEventArgs(q, parameters, new SQLError()
{
ex = ex
}, new SQLData(), g));
return;
}
SQLData d = new SQLData();
d.recordsAffected = reader.RecordsAffected;
DataColumnCollection columns = reader.GetSchemaTable().Columns;
List <string> tColumns = new List <string>();
for (int i = 0; i < columns.Count; i++)
{
tColumns.Add(columns[i].ColumnName);
}
d.columns = tColumns.ToArray();
List <object[]> tData = new List <object[]>();
while (await reader.ReadAsync())
{
object[] tVals = new object[tColumns.Count];
reader.GetValues(tVals);
tData.Add(tVals);
}
d.data = new object[tData.Count, tColumns.Count];
for (int i = 0; i < tData.Count; i++)
{
for (int j = 0; j < tColumns.Count; j++)
{
d.data[i, j] = tData[i][j];
}
}
OnData?.Invoke(this, new SQLEventArgs(q, parameters, new SQLError(), d, g));
new Thread(delegate() {
sendNext();
}).Start();
}
//Gets data from the database table. **Table must have a primary key to work**
private void getData(String selectQuery)
{
try
{
SqlConnection sqlCon = new SqlConnection(conString);
sqlCon.Open();
SqlCommand sqlCom = new SqlCommand(selectQuery, sqlCon);
sqlCom.CommandType = CommandType.Text;
dataAdapter = new SqlDataAdapter(sqlCom);
//Creating a command builder
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
//Creating a new data table
dataTable = new DataTable();
//Filling the table from Database
dataAdapter.Fill(dataTable);
//Binding dataTable to bindingSource1
bindingSource1.DataSource = dataTable;
SQLData.DataSource = dataTable;
//Resizes the table.
SQLData.AutoResizeColumns();
SQLData.AutoResizeRows();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
public SQLData Execute(string query)
{
SQLData dt = new SQLData();
if (connection != null)
{
using (SQLiteCommand mycommand = new SQLiteCommand(connection))
{
mycommand.CommandText = query;
using (SQLiteDataReader reader = mycommand.ExecuteReader())
{
if (reader.FieldCount > 0)
dt.Columns.AddRange(reader.GetValues().AllKeys);
while (reader.Read())
{
List<string> fields = new List<string>();
var values = reader.GetValues();
for (int i = 0; i < values.Count; i++)
fields.Add(values[i]);
dt.Rows.Add(new SQLDataRow() { fields = fields });
}
}
}
}
return dt;
}
public DataTable GetList()
{
string cachyname = "UserMedal";
if (HttpRuntime.Cache[cachyname] == null)
{
DataTable tables = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"SELECT *
FROM [USR_Medal] where dr=").Append((int)AppEnum.State.normal);
try
{
tables = data.CmdtoDataTable(builder.ToString());
HttpRuntime.Cache.Insert(cachyname, tables, null, DateTime.Now.AddHours(10), TimeSpan.Zero,
System.Web.Caching.CacheItemPriority.High, null);
}
catch (Exception exception)
{
throw exception;
}
}
}
return((HttpRuntime.Cache[cachyname] as DataTable).Copy());
}
public Dictionary <int, SYS_ArticleContentMod> GetContentByArticle(int ArticleSysno)
{
Dictionary <int, SYS_ArticleContentMod> ret = new Dictionary <int, SYS_ArticleContentMod>();
DataTable m_dt = new DataTable();
string strsql = "select * from SYS_ArticleContent where ArticleSysNo=" + ArticleSysno + " and DR=" + (int)AppEnum.State.normal + " order by Page asc";
using (SQLData m_data = new SQLData())
{
try
{
m_dt = m_data.CmdtoDataTable(strsql);
}
catch (Exception exception)
{
throw exception;
}
}
for (int i = 0; i < m_dt.Rows.Count; i++)
{
SYS_ArticleContentMod m_tmp = new SYS_ArticleContentMod();
m_tmp.ArticleSysNo = int.Parse(m_dt.Rows[i]["ArticleSysNo"].ToString());
m_tmp.Context = m_dt.Rows[i]["Context"].ToString();
m_tmp.DR = int.Parse(m_dt.Rows[i]["DR"].ToString());
m_tmp.Page = int.Parse(m_dt.Rows[i]["Page"].ToString());
m_tmp.SysNo = int.Parse(m_dt.Rows[i]["SysNo"].ToString());
m_tmp.TS = DateTime.Parse(m_dt.Rows[i]["TS"].ToString());
ret.Add(int.Parse(m_dt.Rows[i]["Page"].ToString()), m_tmp);
}
return(ret);
}
public DataTable GetListByQuest(int SysNo)
{
DataTable tables = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"SELECT [QA_Order].[SysNo]
,[AnswerSysNo]
,[QuestionSysNo]
,[CustomerSysNo]
,[Price]
,[QA_Order].[Status]
,[QA_Order].[TS]
,[Words]
,[Description]
,[Score]
,[Trial]
,NickName
,photo
FROM [QA_Order] left join USR_Customer on CustomerSysNo=USR_Customer.SysNo where QuestionSysNo=").Append(SysNo);
try
{
tables = data.CmdtoDataTable(builder.ToString());
}
catch (Exception exception)
{
throw exception;
}
}
return(tables);
}
public void PrepYears()
{
var yearList = SQLData.GetCars()
.Select(c => c.Year)
.Distinct()
.OrderBy(y => y)
.Select(y => new SelectListItem {
Value = y.ToString(), Text = y.ToString()
}).ToList <SelectListItem>();
yearList.Add(new SelectListItem {
Value = "0", Text = "Any Year"
});
SelectYears = (IEnumerable <SelectListItem>)yearList.OrderBy(i => i.Value);
if (String.IsNullOrWhiteSpace(Year))
{
Year = "0";
}
else
{
if (!(yearList.Select(yl => yl.Value).Contains(Year))) //Year must be in the list
{
Year = "0";
}
}
}
private void Btn_UserCZ_Click(object sender, EventArgs e)
{
if (this.Txt_CZID.Text == "")
{
CommFunc.PublicMessageAll("输入会员账号不能为空！", true, MessageBoxIcon.Asterisk, "");
this.Txt_CZID.Focus();
}
else if (this.Txt_CZPW.Text == "")
{
CommFunc.PublicMessageAll("输入充值密码不能为空！", true, MessageBoxIcon.Asterisk, "");
this.Txt_CZPW.Focus();
}
else
{
string text = this.Txt_CZID.Text;
string password = this.Txt_CZPW.Text;
string pUsedTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
string localIP = CommFunc.GetLocalIP();
string xHintStr = SQLData.CZUser(text, password, AppInfo.Account.AppName, pUsedTime, localIP);
if (!xHintStr.Contains("【错误】-"))
{
CommFunc.PublicMessageAll(xHintStr, false, MessageBoxIcon.Asterisk, "");
}
else
{
CommFunc.PublicMessageAll(xHintStr.Replace("【错误】-", ""), true, MessageBoxIcon.Asterisk, "");
}
}
}
public void loadTable()
{
if (Session["user"] != null)
{
if (LocalVars.ADMINS.Contains(Session["user"].ToString()))
{
SQLData data = (SQLData) new SQLConnection().getDataTable("select * from [Users]").end()[0];
msgAdmin = "User table: <br/>" +
data.printDataTable(new SQLData.Filter[] {
new SQLData.Filter(SQLData.Filter.FilterType.ADD_CLASSES, "admin"),
new SQLData.Filter(SQLData.Filter.FilterType.COLOR, LocalVars.ADMINS, "blue"),
new SQLData.Filter(SQLData.Filter.FilterType.CHECKBOX, "to-delete", LocalVars.ADMINS),
new SQLData.Filter(SQLData.Filter.FilterType.ADD_BUTTON, "delete")
})
//+ "<br/>" + "<form method=\"post\"> <input type='text' name='to-delete' id='to - delete' style='margin-bottom:5px;'>" +
//"<input type='submit' value='Delete User' name='delete-user' id='delete-user' style='padding:5px;'> </form>"
;
//ids = data.getIDs();
}
else
{
msgAdmin = "<font color='red'>You need to be an admin to view this page!</font>";
}
}
else
{
msgAdmin = "<font color='red'>You need to be signed in to view this page!</font>";
}
}
public SYS_AdminMod CheckAdmin(string username, string password)
{
SYS_AdminMod model = new SYS_AdminMod();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append("select SysNo from SYS_Admin where Username='******' and Password='******' and DR=").Append(0);
try
{
model.CustomerSysNo = int.Parse(data.CmdtoDataRow(builder.ToString())["SysNo"].ToString());
}
catch (Exception exception)
{
//throw exception;
}
}
if (model.CustomerSysNo != -999999)
{
model = this.GetModel(model.CustomerSysNo);
model.LastLogin = DateTime.Now;
this.Update(model);
}
return(model);
}
public void DeleteServiceShouldThrow()
{
int id = 99999;
var ex = Assert.ThrowsAny <Exception>(() => SQLData.DeleteService(id));
Assert.IsNotType <NullReferenceException>(ex);
}
/// <summary>
/// 前台发问题
/// </summary>
/// <param name="model"></param>
public void AddQuest(ref QA_QuestionMod model, bool isquest)
{
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
using (SQLData m_data = new SQLData())
{
model.SysNo = m_data.GetSequence("QA_Question_Seq");
}
Add(model);
AppMod.User.USR_RecordMod m_record = new AppMod.User.USR_RecordMod();
m_record.CustomerSysNo = model.CustomerSysNo;
m_record.TargetSysNo = model.SysNo;
m_record.TS = DateTime.Now;
m_record.Type = (int)AppEnum.ActionType.AddQuest;
User.USR_RecordBll.GetInstance().Add(m_record);
if (isquest)
{
User.USR_CustomerBll.GetInstance().AddPoint(0 - model.Award, model.CustomerSysNo);
User.USR_CustomerBll.GetInstance().AddCount(model.CustomerSysNo, 1, 0, 0, 0, 0, 0, 0, 0, 0);
}
else
{
User.USR_CustomerBll.GetInstance().AddPoint(AppConst.TalkPoint, model.CustomerSysNo);
User.USR_CustomerBll.GetInstance().AddCount(model.CustomerSysNo, 0, 0, 0, 0, 1, 0, 0, 0, 0);
}
User.USR_CustomerBll.GetInstance().AddExp(AppConst.TalkExp, model.CustomerSysNo);
scope.Complete();
}
}
/// <summary>
/// 获取目录中贴数，返回四个表，分别为总主题数，总回帖评论数，24小时内新主题数，24小时内新回帖评论数
/// </summary>
/// <returns></returns>
public DataSet GetCatesPostNum()
{
string cachyname = "QACatePostNum";
if (HttpRuntime.Cache[cachyname] == null)
{
DataSet tables = new DataSet();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"
SELECT COUNT(*) as questnum
,[CateSysNo],(case EndTime when null then '0' else '1' end) as IsSolved
FROM [QA_Question] where dr=0 group by [CateSysNo],(case EndTime when null then '0' else '1' end);
select * from
(SELECT COUNT(*) as AnswerNum
,[CateSysNo]
FROM QA_Answer left join [QA_Question] on QA_Answer.QuestionSysNo = QA_Question.SysNo where QA_Answer.dr=0 group by [CateSysNo]
) as PP,
(SELECT COUNT(*) as CommentNum
,[CateSysNo]
FROM QA_Comment left join [QA_Question] on QA_Comment.QuestionSysNo = QA_Question.SysNo where QA_Comment.dr=0 group by [CateSysNo]
) as KK where PP.CateSysNo = KK.CateSysNo;
SELECT COUNT(*) as questnum
,[CateSysNo]
FROM [QA_Question] where datediff(day,TS,getdate())<1 and dr=0 group by [CateSysNo];
select * from
(SELECT COUNT(*) as AnswerNum
,[CateSysNo]
FROM QA_Answer left join [QA_Question] on QA_Answer.QuestionSysNo = QA_Question.SysNo where datediff(day,QA_Answer.TS,getdate())<1 and QA_Answer.dr=0 group by [CateSysNo]
) as PP,
(SELECT COUNT(*) as CommentNum
,[CateSysNo]
FROM QA_Comment left join [QA_Question] on QA_Comment.QuestionSysNo = QA_Question.SysNo where datediff(day,QA_Comment.TS,getdate())<1 and QA_Comment.dr=0 group by [CateSysNo]
) as KK where PP.CateSysNo = KK.CateSysNo");
try
{
tables = data.CmdtoDataSet(builder.ToString());
HttpRuntime.Cache.Insert(cachyname, tables, null, DateTime.Now.AddHours(1), TimeSpan.Zero,
System.Web.Caching.CacheItemPriority.High, null);
}
catch (Exception exception)
{
throw exception;
}
}
}
return((HttpRuntime.Cache[cachyname] as DataSet).Copy());
}
public DataTable GetIndexCateArticle(int count, int cate)
{
string cachyname = "IndexArticle";
if (HttpRuntime.Cache[cachyname + cate.ToString() + "-" + count.ToString()] == null)
{
DataTable tables = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"SELECT top " + count + @" [SysNo]
,[ArticleSysNo]
,[CateSysNo]
,[KeyWords]
,[TS]
,[OrderID]
,[Title]
,Description
,ReadCount
FROM CMS_ArticleView where CateSysNo in (select SysNo from CMS_Category where SysNo=").Append(cate).Append(" or TopSysNo=").Append(cate).Append(" and dr=").Append((int)AppEnum.State.normal).Append(") and dr=").Append((int)AppEnum.State.normal);
builder.Append(" order by [OrderID] desc, [TS] desc;");
try
{
tables = data.CmdtoDataTable(builder.ToString());
HttpRuntime.Cache.Insert(cachyname + cate.ToString() + "-" + count.ToString(), tables, null, DateTime.Now.AddHours(2), TimeSpan.Zero,
System.Web.Caching.CacheItemPriority.High, null);
}
catch (Exception exception)
{
throw exception;
}
}
}
return((HttpRuntime.Cache[cachyname + cate.ToString() + "-" + count.ToString()] as DataTable).Copy());
}
public DataTable GetAllCates()
{
DataTable tables = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"SELECT [SysNo]
,[Name]
,[ParentSysNo]
,[DR]
,[TS]
FROM [QA_Category] where dr=").Append((int)AppEnum.State.normal);
try
{
tables = data.CmdtoDataTable(builder.ToString());
}
catch (Exception exception)
{
throw exception;
}
}
return(tables);
}
public DataTable GetList(int pagesize, int pageindex, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "*";
string tables = "USR_Notice";
string where = "1=1";
string order = "SysNo desc";
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return(m_dt);
}
/// <summary>
/// 仅用于用户登录
/// </summary>
/// <param name="username"></param>
/// <param name="password"></param>
/// <returns></returns>
public USR_CustomerMod CheckUser(string username, string password)
{
USR_CustomerMod model = new USR_CustomerMod();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
if (Util.IsEmailAddress(username))
{
builder.Append("select SysNo from USR_Customer where Email='").Append(SQLData.SQLFilter(username)).Append("' and Password='******' and Status=").Append((int)AppEnum.State.normal);
}
else
{
builder.Append("select SysNo from USR_Customer where Phone='").Append(SQLData.SQLFilter(username)).Append("' and Password='******' and Status=").Append((int)AppEnum.State.normal);
}
try
{
model.SysNo = int.Parse(data.CmdtoDataRow(builder.ToString())["SysNo"].ToString());
}
catch (Exception exception)
{
//throw exception;
}
}
if (model.SysNo != AppConst.IntNull)
{
model = this.GetModel(model.SysNo);
model.LastLoginTime = DateTime.Now;
this.Update(model);
}
return(model);
}
public DataTable GetTopicByReciever(int UserSysno, int pagesize, int pageindex, ref int total)
{
DataTable m_dt = new DataTable();
string columns = "";
string tables = "";
string where = "";
string order = "SysNo desc";
#region 设置参数
columns = @"USR_SMS.SysNo, FromSysNo, Title, Context, IsRead, USR_SMS.DR, USR_SMS.TS, NickName, Photo,ReplyCount";
tables = "USR_SMS left join USR_Customer on FromSysNo= customer.sysNo";
where = "ToSysNo=" + UserSysno + " and Parent=0 and dr=" + (int)AppEnum.State.normal + " and IsToDeleted=" + (int)AppEnum.BOOL.False;
#endregion
using (SQLData m_data = new SQLData())
{
m_data.AddParameter("SelectList", columns);
m_data.AddParameter("TableSource", tables);
m_data.AddParameter("SearchCondition", where);
m_data.AddParameter("OrderExpression", order);
m_data.AddParameter("PageIndex", pageindex);
m_data.AddParameter("pagesize", pagesize);
DataSet m_ds = m_data.SPtoDataSet("GetRecordFromPage");
if (m_ds.Tables.Count == 2)
{
m_dt = m_ds.Tables[0];
total = int.Parse(m_ds.Tables[1].Rows[0][0].ToString());
}
}
return(m_dt);
}
public async Task <IActionResult> SaveProfile(UserProfileViewModel model)
{
UserProfileModel m = model.GetProfileModel();
const double maxSize = 368640; // 350x350 24 bit bmp file size
if (model.Files != null && model.Files.Length > 0)
{
byte[] imageBytes = null;
using (MemoryStream stream = new MemoryStream())
{
model.Files.CopyTo(stream);
imageBytes = stream.ToArray();
}
if (ImageHelper.isKnownFormat(imageBytes) &&
(ImageHelper.IsImageFit(imageBytes, 350)))
{
m.Image = imageBytes;
}
}
SQLData.UpdateProfile(m);
return(RedirectToAction("Profile"));
}
public int AddExp(int exp, int sysno)
{
int ret = 0;
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append("update USR_Customer set Exp = Exp+(").Append(exp).Append(") where SysNo=").Append(sysno)
.Append(";select Exp from USR_Customer where SysNo=").Append(sysno)
.Append(";select * from USR_Grade where SysNo in (select GradeSysNo from USR_Customer where SysNo=").Append(sysno).Append(")");
try
{
DataSet m_ds = data.CmdtoDataSet(builder.ToString());
ret = int.Parse(m_ds.Tables[0].Rows[0]["Exp"].ToString());
DataTable m_grade = USR_GradeBll.GetInstance().GetList();
for (int i = 0; i < m_grade.Rows.Count; i++)
{
if (int.Parse(m_grade.Rows[i]["LevelNum"].ToString()) == int.Parse(m_ds.Tables[1].Rows[0]["LevelNum"].ToString()) + 1)
{
if (ret >= int.Parse(m_grade.Rows[i]["Exp"].ToString()))
{
data.CmdtoNone("update USR_Customer set GradeSysNo=" + m_grade.Rows[i]["SysNo"].ToString() + " where sysno=" + sysno);
}
}
}
}
catch (Exception exception)
{
//throw exception;
}
}
return(ret);
}
public DataTable GetCatesForAdmin(int parent)
{
DataTable tables = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"SELECT [SysNo]
,[Name]
,[ParentSysNo]
,IsHide
,[DR]
,[TS]
FROM [CMS_Category] where ")
.Append(" [ParentSysNo] = ").Append(parent).Append(" order by [Name];");
try
{
tables = data.CmdtoDataTable(builder.ToString());
}
catch (Exception exception)
{
throw exception;
}
}
return(tables);
}
public DataSet GetSameFamous(AstroMod m_astro)
{
DataSet m_ds = new DataSet();
using (SQLData data = new SQLData())
{
string sql = "select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where degree=" + m_astro.Stars[0].Degree + " and Constellation=" + (int)m_astro.Stars[0].Constellation + " and star=" + (int)PublicValue.AstroStar.Sun + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where degree=" + m_astro.Stars[1].Degree + " and Constellation=" + (int)m_astro.Stars[1].Constellation + " and star=" + (int)PublicValue.AstroStar.Moo + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where degree=" + m_astro.Stars[2].Degree + " and Constellation=" + (int)m_astro.Stars[2].Constellation + " and star=" + (int)PublicValue.AstroStar.Mer + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where degree=" + m_astro.Stars[3].Degree + " and Constellation=" + (int)m_astro.Stars[3].Constellation + " and star=" + (int)PublicValue.AstroStar.Ven + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where degree=" + m_astro.Stars[4].Degree + " and Constellation=" + (int)m_astro.Stars[4].Constellation + " and star=" + (int)PublicValue.AstroStar.Mar + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where gong=" + m_astro.Stars[0].Gong + " and star=" + (int)PublicValue.AstroStar.Sun + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where gong=" + m_astro.Stars[1].Gong + " and star=" + (int)PublicValue.AstroStar.Moo + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where gong=" + m_astro.Stars[2].Gong + " and star=" + (int)PublicValue.AstroStar.Mer + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where gong=" + m_astro.Stars[3].Gong + " and star=" + (int)PublicValue.AstroStar.Ven + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where gong=" + m_astro.Stars[4].Gong + " and star=" + (int)PublicValue.AstroStar.Mar + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where gong=" + m_astro.Stars[5].Gong + " and star=" + (int)PublicValue.AstroStar.Jup + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where gong=" + m_astro.Stars[6].Gong + " and star=" + (int)PublicValue.AstroStar.Sat + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where gong=" + m_astro.Stars[7].Gong + " and star=" + (int)PublicValue.AstroStar.Ura + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where gong=" + m_astro.Stars[8].Gong + " and star=" + (int)PublicValue.AstroStar.Nep + " and IsTop=1;" +
"select top 3 Sys_Famous.name,Sys_Famous.sysno from Sys_Famous_AstroStar left join Sys_Famous on Sys_Famous.sysno = famoussysno where gong=" + m_astro.Stars[9].Gong + " and star=" + (int)PublicValue.AstroStar.Plu + " and IsTop=1;";
try
{
m_ds = data.CmdtoDataSet(sql);
}
catch (Exception exception)
{
//throw exception;
}
return(m_ds);
}
}
public DataTable GetCates(int parent)
{
string cachyname = "CMSCate";
if (HttpRuntime.Cache[cachyname + parent] == null)
{
DataTable tables = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"SELECT [SysNo]
,[Name]
,[ParentSysNo]
,[DR]
,[TS]
FROM [CMS_Category] where dr=").Append((int)AppEnum.State.normal)
.Append(" and IsHide=").Append((int)AppEnum.BOOL.False)
.Append(" and [ParentSysNo] = ").Append(parent).Append(" order by [Name];");
try
{
tables = data.CmdtoDataTable(builder.ToString());
HttpRuntime.Cache.Insert(cachyname + parent, tables, null, DateTime.Now.AddHours(2), TimeSpan.Zero,
System.Web.Caching.CacheItemPriority.High, null);
}
catch (Exception exception)
{
throw exception;
}
}
}
return((HttpRuntime.Cache[cachyname + parent] as DataTable).Copy());
}
public void RemoveAttention(int UserSysNo, int TagetSysNo)
{
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append("update BLG_Attention set DR=").Append((int)AppEnum.State.deleted).Append(" where CustomerSysNo=").Append(UserSysNo).Append(" and TargetSysNo=").Append(TagetSysNo).Append(" and DR=").Append((int)AppEnum.State.normal);
try
{
data.CmdtoNone(builder.ToString());
}
catch (Exception exception)
{
//throw exception;
}
}
scope.Complete();
}
}
public DataTable GetSubLevel(int upSysNo)
{
DataTable table = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"select [SysNo]
,[UpSysNo]
,[Name]
,[EnglishName]
,[Level]
,[UseType]
,[DR]
,[TS]
,[Latitude]
,[longitude]
from [SYS_District] where upsysno=").Append(upSysNo).Append(" and dr=").Append((int)AppEnum.State.normal)
.Append(" order by [Name]");
try
{
table = data.CmdtoDataTable(builder.ToString());
}
catch (Exception exception)
{
throw exception;
}
}
return(table);
}
public Dictionary <int, SYS_PrivilegeMod> GetAdminPrivilege(int AdminSysNo)
{
DataTable table = new DataTable();
Dictionary <int, SYS_PrivilegeMod> dictionary = new Dictionary <int, SYS_PrivilegeMod>();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append("select SysNo,[Name],url from SYS_Privilege where SysNo in (select Privilege_SysNo from REL_Admin_Privilege where Admin_SysNo=").Append(AdminSysNo).Append(") and DR=").Append((int)AppEnum.State.normal);
try
{
table = data.CmdtoDataTable(builder.ToString());
}
catch (Exception exception)
{
throw exception;
}
}
if (table.Rows.Count > 0)
{
for (int i = 0; i < table.Rows.Count; i++)
{
SYS_PrivilegeMod mod = new SYS_PrivilegeMod();
mod.Name = table.Rows[i]["Name"].ToString();
mod.SysNo = int.Parse(table.Rows[i]["SysNo"].ToString());
mod.URL = table.Rows[i]["url"].ToString();
dictionary.Add(int.Parse(table.Rows[i]["SysNo"].ToString()), mod);
}
}
return(dictionary);
}
public SYS_DistrictMod GetNearestByPoi(string lng, string lat)
{
DataTable table = new DataTable();
SYS_DistrictMod m_dist = new SYS_DistrictMod();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"select top 1 sysno
from [Sys_District] where level=3 and englishname is null and ABS([longitude]-").Append(SQLData.SQLFilter(lng)).Append(")<3 and ABS([Latitude]-").Append(SQLData.SQLFilter(lat)).Append(")<3 order by SQUARE([longitude]-").Append(SQLData.SQLFilter(lng)).Append(")+ SQUARE([Latitude]-").Append(SQLData.SQLFilter(lat)).Append(")");
//try
//{
table = data.CmdtoDataTable(builder.ToString());
//}
//catch (Exception exception)
//{
// throw exception;
//}
if (table.Rows.Count > 0)
{
m_dist = GetModel(int.Parse(table.Rows[0]["sysno"].ToString()));
table = data.CmdtoDataTable("select * from [District3Level] where SysNo3=" + m_dist.SysNo);
m_dist.Name = (table.Rows[0]["Name1"].ToString() + "-" + table.Rows[0]["Name2"].ToString() + "-" + table.Rows[0]["Name3"].ToString()).Replace("市辖区-", "");
}
else
{
return(null);
}
}
return(m_dist);
}
public IActionResult ShowDocs(HeaderListFilters filters = null)
{
if (_user.IsAuthenticated)
{
ViewBag.User = _user.User; ViewBag.Roles = _user.Roles;
}
ViewData["Title"] = "Documents";
IEnumerable <HeaderModel> hdrList = null;
if (_user.HasRole("Admin"))
{
hdrList = SQLData.GetHeadersWithImages();
}
else
{
hdrList = SQLData.GetHeadersWithImagesByIssuer(_user.User);
}
if (filters != null)
{
if (!string.IsNullOrEmpty(filters.Id))
{
hdrList = hdrList.Where(e => String.Compare(e.HeaderId, filters.Id) == 0);
}
if (!string.IsNullOrEmpty(filters.ValidatorId))
{
hdrList = hdrList.Where(e => String.Compare(e.ValidatorUuid, filters.ValidatorId) == 0);
}
}
return(View(new HeaderListViewModel(hdrList, _configuration)));
}
/// <summary>
///
/// </summary>
/// <param name="UseType">全部则为0</param>
/// <returns></returns>
public DataTable GetFirstLevel(int UseType)
{
DataTable table = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"select [SysNo]
,[UpSysNo]
,[Name]
,[EnglishName]
,[Level]
,[UseType]
,[DR]
,[TS]
,[Latitude]
,[longitude]
from [SYS_District] where [Level]=1 and dr=").Append((int)AppEnum.State.normal);
if (UseType != 0 && UseType != AppConst.IntNull)
{
builder.Append(" and usetype=").Append(UseType);
}
builder.Append(" order by [Name] desc");
try
{
table = data.CmdtoDataTable(builder.ToString());
}
catch (Exception exception)
{
throw exception;
}
}
return(table);
}
private void Btn_Ok_Click(object sender, EventArgs e)
{
if (this.Txt_LoginID.Text == "")
{
CommFunc.PublicMessageAll("输入会员账号不能为空！", true, MessageBoxIcon.Asterisk, "");
this.Txt_LoginID.Focus();
}
else if (this.Txt_LoginPW.Text == "")
{
CommFunc.PublicMessageAll("输入会员密码不能为空！", true, MessageBoxIcon.Asterisk, "");
this.Txt_LoginPW.Focus();
}
else
{
string pHint = "";
if (SQLData.UserLogin(this.Txt_LoginID.Text, this.Txt_LoginPW.Text, this.MachineCode, this.AccountData, ref pHint))
{
base.DialogResult = DialogResult.OK;
}
else
{
CommFunc.PublicMessageAll(pHint, true, MessageBoxIcon.Asterisk, "");
this.Txt_LoginID.SelectAll();
this.Txt_LoginID.Focus();
}
}
}
public DataTable GetStarsList(int customersysno, int count)
{
string cachyname = "QAStarsQuest";
if (HttpRuntime.Cache[cachyname + customersysno.ToString() + "-" + count.ToString()] == null)
{
DataTable tables = new DataTable();
using (SQLData data = new SQLData())
{
StringBuilder builder = new StringBuilder();
builder.Append(@"SELECT top " + count + @" Title
, Award
, EndTime
, ReplyCount
, Context
FROM [QA_Question] where sysno in (select QuestionSysNo from QA_Answer where CustomerSysNo and Award>0 amd dr=").Append((int)AppEnum.State.normal).Append(") as T and IsSecret=0 and dr=").Append((int)AppEnum.State.normal);
builder.Append(" order by [OrderID] desc;");
try
{
tables = data.CmdtoDataTable(builder.ToString());
HttpRuntime.Cache.Insert(cachyname + customersysno.ToString() + "-" + count.ToString(), tables, null, DateTime.Now.AddHours(2), TimeSpan.Zero,
System.Web.Caching.CacheItemPriority.High, null);
}
catch (Exception exception)
{
throw exception;
}
}
}
return((HttpRuntime.Cache[cachyname + customersysno.ToString() + "-" + count.ToString()] as DataTable).Copy());
}
public SQLData Execute(string query)
{
SQLiteResultSet result = sqlClient.Execute(query);
SQLData dt = new SQLData(result.ColumnNames);
foreach (SQLiteResultSet.Row row in result.Rows)
dt.Rows.Add(new SQLDataRow() { fields = row.fields });
return dt;
}
public MainWindow (): base (Gtk.WindowType.Toplevel)
{
// System.Data.Bindings.DebugInformation.Debug.Active = true;
// System.Data.Bindings.DebugInformation.Debug.DevelopmentInfo = true;
System.Data.Bindings.DebugInformation.ConsoleDebug.Connect();
System.Data.Bindings.DebugInformation.Debug.DevelInfo("test");
Build ();
sql = new SQLData();
datatreeview1.ItemsDataSource = sql.DemoTable;
}
//Validate user
public Boolean ValidateUser(string userName, string password)
{
DataTable dt=new SQLData().GetData("SELECT ID FROM tbUsers WHERE Username='******' AND Password='******'");
if (dt.Rows.Count != 0)
{
HttpContext.Current.Session["LoginUserId"] = dt.Rows[0][0].ToString();
return true;
}
else
return false;
}
void upgradeEmulators(SQLData emulatorData)
{
emuLookup[-1] = createPC();
int currentEmulator = 2;
int totalEmulators = emulatorData.Rows.Count + 1;
foreach (SQLDataRow sqlRow in emulatorData.Rows)
{
setProgress("{0}/{1} - Parsing Emulators", currentEmulator, totalEmulators);
currentEmulator++;
currentItem++;
Emulator emu = new Emulator();
emu.Id = int.Parse(sqlRow.fields[0]);
emu.Title = decode(sqlRow.fields[1]);
emu.PathToRoms = decode(sqlRow.fields[2]);
emu.Filter = decode(sqlRow.fields[3]);
emu.Position = int.Parse(sqlRow.fields[4]);
emu.View = int.Parse(sqlRow.fields[5]);
emu.Platform = decode(sqlRow.fields[6]);
emu.Developer = decode(sqlRow.fields[7]);
emu.Year = int.Parse(sqlRow.fields[8]);
emu.Description = decode(sqlRow.fields[9]);
emu.Grade = int.Parse(sqlRow.fields[10]);
emu.VideoPreview = decode(sqlRow.fields[11]);
int lAspect = int.Parse(sqlRow.fields[12]);
if (lAspect != 0)
emu.CaseAspect = lAspect / 100.00;
upgradeProfiles(emu);
foreach (EmulatorProfile profile in emu.EmulatorProfiles)
{
if (profile.IsDefault)
{
emu.DefaultProfile = profile;
break;
}
}
emuLookup[(int)emu.Id] = emu;
}
}
List<Game> upgradeGames(SQLData gameData)
{
int currentGame = 1;
int totalGames = gameData.Rows.Count;
List<Game> games = new List<Game>();
foreach (SQLDataRow sqlRow in gameData.Rows)
{
setProgress("{0}/{1} - Parsing Games", currentGame, totalGames);
currentGame++;
currentItem++;
Emulator parent;
if (!emuLookup.TryGetValue(int.Parse(sqlRow.fields[2], culture), out parent))
continue;
Game game = new Game();
game.ParentEmulator = parent;
game.Id = int.Parse(sqlRow.fields[0]);
game.Title = decode(sqlRow.fields[3]);
game.Grade = int.Parse(sqlRow.fields[4]);
game.PlayCount = int.Parse(sqlRow.fields[5]);
game.Year = int.Parse(sqlRow.fields[6]);
game.Latestplay = DateTime.Parse(sqlRow.fields[7], culture);
game.Description = decode(sqlRow.fields[8]);
game.Genre = decode(sqlRow.fields[9]);
game.Developer = decode(sqlRow.fields[10]);
game.Favourite = bool.Parse(sqlRow.fields[12]);
game.InfoChecked = bool.Parse(sqlRow.fields[15]);
game.VideoPreview = decode(sqlRow.fields[17]);
if (parent.IsPc())
{
upgradeProfiles(game);
if (game.GameProfiles.Count < 1)
{
EmulatorProfile profile = new EmulatorProfile(true);
profile.Arguments = decode(sqlRow.fields[19]);
profile.SuspendMP = true;
game.GameProfiles.Add(profile);
}
}
int selectedProfileId = int.Parse(sqlRow.fields[14]);
foreach (EmulatorProfile profile in game.EmulatorProfiles)
{
if (profile.Id == selectedProfileId)
{
game.CurrentProfile = profile;
break;
}
}
upgradeDiscs(game);
if (game.Discs.Count < 1)
{
GameDisc disc = new GameDisc();
disc.Number = 1;
disc.Path = decode(sqlRow.fields[1]);
disc.LaunchFile = decode(sqlRow.fields[13]);
game.Discs.Add(disc);
game.CurrentDisc = disc;
}
else
{
int discNum = int.Parse(sqlRow.fields[18]);
foreach (GameDisc disc in game.Discs)
{
if (discNum == disc.Number)
{
game.CurrentDisc = disc;
break;
}
}
}
games.Add(game);
}
return games;
}
private bool ReadSQLRow(string queryString, SqlConnection connection, out SQLData dsdReturnData, string trace = "")
{
dsdReturnData = new SQLData();
Stopwatch sw = new Stopwatch();
TimeMeasure(sw ,ref sqlreadtime);
try
{
//using (SqlConnection connection = new SqlConnection(connectionString)) //{
SqlCommand command = new SqlCommand(queryString, connection);
//command.Connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
reader.Read();
dsdReturnData = new SQLData(reader.FieldCount);
for (int i = 0; i < reader.FieldCount; i++)
{
dsdReturnData.strData[i] = Convert.ToString(reader[i]);//reader.GetString(i);
dsdReturnData.strDescription[i] = reader.GetName(i);
}
reader.Close();
return true;
} //}
}
catch (SqlException err)
{
dsdReturnData.strError = err.Message;
dsdReturnData.strTrace = trace;
dsdReturnData.ErrorCode = 1;
return false;
}
catch (Exception err)
{
dsdReturnData.strError = err.Message;
dsdReturnData.strTrace = trace;
dsdReturnData.ErrorCode = 2;
return false;
}
finally
{
TimeMeasure(sw,ref sqlreadtime);
sqlreadtime.strRemark1 = queryString;
if (dsdReturnData.ErrorCode != 0)
{
sqlreadtime.strRemark2 = dsdReturnData.strError;
}
}
}
