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