/// <summary> /// 获得EntityList类型数据 /// </summary> /// <typeparam name="T">数据模型</typeparam> /// <param name="strTableName">表名</param> /// <param name="strWhere">过滤条件</param> /// <returns>成功返回ListT,失败返回null</returns> public static List <T> GetEntityList <T>(string strTableName, string strWhere) where T : class, new() { try { List <T> listT = new List <T>(); SQLiteHelper sqlHelper = new SQLiteHelper(); sqlHelper.SQLiteConnectionString(strSQLiteConn); sqlHelper.Open(); if (string.IsNullOrEmpty(strWhere)) { listT = DataProcessing.ConvertToList <T>(sqlHelper.GetDataTable(string.Format("SELECT * FROM {0}", strTableName))); } else { listT = DataProcessing.ConvertToList <T>(sqlHelper.GetDataTable(string.Format("SELECT * FROM {0} WHERE {1}", strTableName, strWhere))); } sqlHelper.Close(); return(listT); } catch (Exception ex) { TXTHelper.Logs(ex.ToString()); return(null); } }
protected void Page_Load(object sender, EventArgs e) { SQLiteHelper sh = new SQLiteHelper(); string sql = "select b.c_name_chn as c_name_chn, c.c_addr_id,c.x_coord as x_coord ,c.y_coord as y_coord from biog_addr_data A,biog_main B,ADDR_CODES c"; sql += " where A.c_personid = B.c_personid AND c.c_addr_id = a.c_addr_id and "; sql += " (b.c_deathyear>=907 and b.c_birthyear<=1200) and c.x_coord is not null and c.y_coord is not null"; //sql += "c.x_coord is not null and c.y_coord is not null"; DataSet ds = new DataSet(); try { sh.RunSQL(sql, ref ds); dt = ds.Tables[0]; // personnumber = dt.Rows.Count; foreach (DataRow dr in dt.Rows) { // jsstirng += string.Format(@"{name:{0}'', geoCoord:[{1}, {2}]},", dr["c_name_chn"].ToString(), dr["x_coord"].ToString(), dr["y_coord"].ToString()); //jsstirng += string.Format(@"{{name:'{0}',geoCoord:[{1}, {2}]}},", dr["c_name_chn"].ToString(), dr["x_coord"].ToString(), dr["y_coord"].ToString()); if (dr["c_name_chn"].ToString() == "呂祖謙") { jsli += string.Format(@"{{name:'{0}',geoCoord:[{1}, {2}]}},", dr["c_name_chn"].ToString(), dr["x_coord"].ToString(), dr["y_coord"].ToString()); } } } catch (Exception ex) { Response.Write("数据库出错"); } finally { sh.Close(); } }
protected void Page_Load(object sender, EventArgs e) { SQLiteHelper sh = new SQLiteHelper(); string sql = "SELECT c_dy,c_dynasty_chn FROM DYNASTIES ORDER BY c_sort;"; DataTable dt = new DataTable(); DataSet ds = new DataSet(); try { sh.RunSQL(sql, ref ds); if (ds.Tables[0] != null) { dt = ds.Tables[0]; if (dt.Rows.Count > 0) { DdlDynasties.DataTextField = "c_dynasty_chn"; DdlDynasties.DataValueField = "c_dy"; DdlDynasties.DataSource = dt; DdlDynasties.DataBind(); } } } catch (Exception ex) { Response.Write(ex.Message); } finally { sh.Close(); } }
protected void DdlDynasties_SelectedIndexChanged(object sender, EventArgs e) { SQLiteHelper sQ = new SQLiteHelper(); string sqlEra = "SELECT c_nianhao_id, c_nianhao_chn, c_dy FROM NIAN_HAO " + "WHERE c_dy = " + DdlDynasties.SelectedValue + ";"; DataTable dt = new DataTable(); DataSet ds = new DataSet(); try { sQ.RunSQL(sqlEra, ref ds); if (ds.Tables[0] != null) { dt = ds.Tables[0]; if (dt.Rows.Count >= 0) { DdlEraName.DataTextField = "c_nianhao_chn"; DdlEraName.DataValueField = "c_nianhao_id"; DdlEraName.DataSource = dt; DdlEraName.DataBind(); } } } catch (Exception ex) { Response.Write(ex.Message); } finally { sQ.Close(); } }
private static void DisplayMenu() { Console.Clear(); Console.WriteLine("\n***************************************"); Console.WriteLine("\t 1. Create Database"); Console.WriteLine("\t 2. Init spatial metadata"); Console.WriteLine("\t 3. Create spatial table"); Console.WriteLine("\t 4. Show Table Info"); Console.WriteLine("\t 5. Query"); Console.WriteLine("\t 6. Create polylines"); Console.WriteLine("\t 7. test intersect"); Console.WriteLine("\t 0.Exit"); Console.WriteLine("***************************************"); Console.Write(">"); string s = Console.ReadLine(); switch (s) { case "1": CreateDatabase(); break; case "2": InitSpatialMeta(); break; case "3": CreateSpatialTable(); break; case "4": ShowTableInfo(); break; case "5": Query(); break; case "6": CreatePolylines(); break; case "7": TestIntersect(); break; case "0": sh.Close(); System.Environment.Exit(0); break; } }
private void button11_Click(object sender, EventArgs e) { Enabled = false; var sqlhelper = new SQLiteHelper(); var teacherList = sqlhelper.GetTeacherInfoList(); var allMajorList = sqlhelper.GetAllMajor(); sqlhelper.Close(); Form_IncludeClass frm = new Form_IncludeClass(teacherList, allMajorList); frm.ShowDialog(); Enabled = true; }
private void LoadTeacherProcess() { try { OpenFileDialog openFile = new OpenFileDialog(); bool NeedReturn = false; Invoke((EventHandler) delegate { Enabled = false; MessageBox.Show(this, "*导入数据仅支持(.xls)格式的数据文件,如不是请自行更改保存格式\n\n*数据表格式如下且需包含表头:\n姓名|系别|包含班级\n*姓名:参赛教师姓名\n*系别:参赛教师所属系\n*包含班级:参赛教师的所有班级,格式为\n[专业][.][班级][,][专业][.][班级][,]……………………[专业][.][班级]\n如:软件工程.2016级01班,软件工程.2016级02班\n\".\"及\",\"均为英文状态下标点,[专业]及[班级]需与学生数据表中的对应字段相匹配\n\n*不能重复导入相同的数据表\n\n*由于不遵守此规定导致的一切错误请自行承担", "重要提示"); openFile.Title = "请选择目标Excel表"; openFile.Filter = "Excel(*.xls)|*.xls"; openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop); openFile.Multiselect = false; if (openFile.ShowDialog() == DialogResult.Cancel) { NeedReturn = true; } }); if (NeedReturn) { return; } var dt = ExcelHelper.GetDataFromExcelByConn(openFile.FileName, true); SQLiteHelper sqlLiteHelper = new SQLiteHelper(); if (sqlLiteHelper.InsertNewTeacher(dt)) { Invoke((EventHandler) delegate { MessageBox.Show(this, "数据已经成功导入", "导入成功"); }); } sqlLiteHelper.Close(); } catch (Exception e) { Invoke((EventHandler) delegate { MessageBox.Show(this, e.Message, "导入失败"); }); } finally { Invoke((EventHandler) delegate { Text = "设置"; Enabled = true; }); } }
public static void CreateUser() { string path = Path.Combine(Application.StartupPath, "Data", "alo_users.xls"); //DataTable dt = util.Excel_To_DataTable(path,0); DataTable dt = util.GetRequestsDataFromExcel(path); SQLiteHelper sh = new SQLiteHelper(); sh = sh.Init(); sh.BeginTransaction(); foreach (DataRow dr in dt.Rows) { string username = dr["HoTen"].ToString().Replace("'", ""); username = fullname2username(username).ToLower(); string mail = username + "@alobhxh.com"; string birthDay = dr["NgaySinh"].ToString(); // // if (Convert.ToInt32(sh.ExecuteScalar("SELECT COUNT(*) FROM USERS WHERE USERNAME = '******'")) > 0) { continue; } // userInfo user = new userInfo(); user.Username = username.Replace("'", ""); user.Birthday = birthDay; user.Fullname = dr["HoTen"].ToString().Replace("'", ""); user.Address = dr["DiaChi"].ToString().Replace("'", ""); user.Sex = dr["GioiTinh"].ToString().Replace("'", ""); // sh.Insert("USERS", user.ToDictionary()); // } sh.Commit(); sh.Close(); }
public static string GetRandomUID(string siteId = "") { if (siteId == "bhxhdanang.gov.vn") { return(Program.API_MOD_ID_DNG); } else if (siteId == "baohiemxahoi.gov.vn") { return("1012"); } string randomUid = ""; if (lstUser == null) { SQLiteHelper sh = new SQLiteHelper(); sh = sh.Init(); DataTable dtUser = sh.Select("SELECT uid FROM USERS WHERE uid is not null and uid <> ''"); if (dtUser.Rows.Count > 0) { lstUser = new List <string>(); foreach (DataRow dr in dtUser.Rows) { lstUser.Add(dr[0].ToString()); } // } // sh.Close(); } // Random r = new Random(); int index = r.Next(lstUser.Count - 1); randomUid = lstUser[index]; return(randomUid); }
public void Close() { dbHelper.Close(); }
public void Post2Site(List <postInfo> posts) { SQLiteHelper sh = new SQLiteHelper(); sh = sh.Init(); sh.BeginTransaction(); //Luu DB local - SQLLITE, de check trung string strUrrl = "https://bds5s.com/posts"; string apiKey = "a82841644696ebb6381bd74b41c81fb4d5bddc0b91de59a932da5a9fb87ce9be"; string user = "", catid = "", category = ""; foreach (postInfo post in posts) { if (Convert.ToInt32(sh.ExecuteScalar("SELECT COUNT(*) FROM POSTS WHERE title='" + post.Title + "'")) > 0) { continue; } //1. Insert local DB sh.Insert("POSTS", post.ToDictionary()); //2. Post site string purl = util.RemoveVietnamese(post.Province); if (purl.Equals("ha-noi")) { user = "******"; catid = util.getQuanHuyen(util.RemoveVietnamese(post.District)); if (catid.Equals("")) { category = "5"; } else { category = catid; } // } else if (purl.Equals("ho-chi-minh")) { user = "******"; catid = util.getQuanHuyen(util.RemoveVietnamese(post.District)); if (catid.Equals("")) { category = "35"; } else { category = catid; } } else if (purl.Equals("da-nang")) { user = "******"; catid = util.getQuanHuyen(util.RemoveVietnamese(post.District)); if (catid.Equals("")) { category = "61"; } else { category = catid; } } if (!category.Equals("")) { string api_username = user; string strData = "api_key=" + apiKey + "&api_username="******"&title=" + post.Title + "&category=" + category + "&raw=" + post.Content + ""; util.HTTP_POST(strUrrl, strData); } } sh.Commit(); sh.Close(); }
public void Post2Site_Alo(List <postInfo> posts, string categoryId) { //return; SQLiteHelper sh = new SQLiteHelper(); sh = sh.Init(); sh.BeginTransaction(); //Luu DB local - SQLLITE, de check trung //string root = "http://localhost:4567/api/v2"; string root = "https://alobhxh.com/api/v2"; string postUrl = "/topics"; string replyUrl = ""; //string apiKey = "Bearer 94cdf3b2-7b9c-4c67-9b98-e0e20682a5fb"; //Master string apiKey = String.Format("Bearer {0}", Program.API_MASTER_KEY);// 15baf25c-5d82-47c8-a660-71b4abb7b675"; //Master //string apiKey = "Bearer 02ca7aea-28e5-4f04-b6c6-02861c1c6d36"; //string apiKey = "Bearer f440c24f-a7b1-401e-a788-31c1dc1df19c"; string cid = categoryId, title = "", content = ""; string url = root + postUrl; foreach (postInfo post in posts) { string sqlExist = "SELECT COUNT(*) FROM POSTS WHERE siteId = '" + post.SiteId + "' AND postId='" + post.PostId + "'"; if (post.PostId == "0" || post.PostId == "") { sqlExist = "SELECT COUNT(*) FROM POSTS WHERE siteId = '" + post.SiteId + "' AND title='" + post.Title + "'"; } if (Convert.ToInt32(sh.ExecuteScalar(sqlExist)) > 0) { continue; } //1. Insert local DB sh.Insert("POSTS", post.ToDictionary()); if (post.SiteId == "bhxhhn.com.vn" && Program.lstExistPost != null) { string checkExist = String.Format("{0}|{1}", post.CategoryId, post.Title); if (Program.lstExistPost.Contains(checkExist)) { continue; } } //2. Post site //string strData = "api_key=" + apiKey + "&api_username="******"&title=" + post.Title + "&category=" + category + "&raw=" + post.Content + ""; try { var converter = new Converter(); JObject o = new JObject(); o["cid"] = categoryId; o["title"] = post.Title; // string contentMD = post.Content; contentMD = converter.Convert(contentMD).Trim(); contentMD = contentMD.Replace("\\r\\n\\", ""); // string contentRepMD = post.ContentReply; if (!String.IsNullOrEmpty(contentRepMD)) { contentRepMD = converter.Convert(contentRepMD).Trim(); contentRepMD = contentRepMD.Replace("\\r\\n\\", ""); } //Nếu bài dài quá, thì cắt làm 2 if (contentMD.Length > 32000 && String.IsNullOrEmpty(post.ContentReply)) { string initContent = contentMD; contentMD = initContent.Substring(0, 32000); contentRepMD = initContent.Substring(32001, 32000); } o["content"] = contentMD; string userId = userhelper.GetRandomUID(post.SiteId); o["_uid"] = userId; //var jsonContent = String.Format("{'cid': '{0}','title': '{1}', 'content': '{2}'}", cid, post.Title, post.Content); string jsonContent = o.ToString(); JObject obj = util.HTTP_POST_JSON(url, apiKey, jsonContent); if (obj != null && obj["code"].ToString() == "ok" && !String.IsNullOrEmpty(contentRepMD)) { string tId = obj["payload"]["topicData"]["tid"].ToString(); if (!String.IsNullOrEmpty(tId)) { string url2 = url + "/" + tId; o = new JObject(); //contentMD = post.ContentReply; // //contentMD = converter.Convert(post.ContentReply); o["content"] = contentRepMD.Trim(); o["_uid"] = userhelper.GetRandomUIDReply(post.SiteId, userId); jsonContent = o.ToString(); // if (!String.IsNullOrEmpty(contentRepMD)) { string modAPI = apiKey; obj = util.HTTP_POST_JSON(url2, modAPI, jsonContent); if (obj["code"].ToString() == "ok") { Console.WriteLine("OK: " + post.Title); } } } } } catch (Exception ex) { string s = ex.ToString(); } } sh.Commit(); sh.Close(); }
private void LoadExcelProcess() { try { OpenFileDialog openFile = new OpenFileDialog(); bool NeedReturn = false; Invoke((EventHandler) delegate { Enabled = false; MessageBox.Show(this, "\n\n数据表格式如下且需包含表头:\n学号|姓名|性别|班级|专业|政治面貌|民族|担任职务|家庭住址|宿舍|宿舍成员|家庭经济状况|奖惩情况|学习状况|兴趣爱好|职业倾向\n\n不能重复导入相同的数据表\n\n", "重要提示"); openFile.Title = "请选择目标Excel表"; openFile.Filter = "Excel(*.xls;*.xlsx)|*.xls;*.xlsx"; openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop); openFile.Multiselect = true; if (openFile.ShowDialog() == DialogResult.Cancel) { NeedReturn = true; } }); if (NeedReturn) { return; } Invoke((EventHandler) delegate { Text = "正在导入中,请耐心等候"; }); //var dt = ExcelHelper.GetDataFromExcelByConn(openFile.FileName, true); string input_success = string.Empty; string input_error = string.Empty; int input_success_total = 0; SQLiteHelper sqlLiteHelper = new SQLiteHelper(); foreach (var fs in openFile.FileNames) { var dt = ExcelHelper.GetDataFromExcelByConn(fs, true); int temp_input_success_total = 0; if (sqlLiteHelper.InsertNewStudent(dt, input_success_total, ref temp_input_success_total, this)) { input_success = input_success + fs + "\n"; input_success_total += temp_input_success_total; } else { input_error = input_error + fs + "\n"; //Invoke((EventHandler)delegate //{ // MessageBox.Show(this, "未导入任何数据\n\n\"" + fs + "\"\n\n请检查格式是否有误", "导入失败"); //}); } } sqlLiteHelper.Close(); Invoke((EventHandler) delegate { MessageBox.Show(this, string.Format("{0}\n\n{1}\n{2}", (string.Format("成功导入 {0} 名学生数据", input_success_total)), (string.IsNullOrEmpty(input_success) ? "" : string.Format("以下文件的数据均已成功导入\n\n{0}", input_success)), (string.IsNullOrEmpty(input_error) ? "" : string.Format("以下文件的数据导入失败,请检查格式是否有误\n\n{0}", input_error)) ), "导入完成"); if (input_success_total > 0) { MessageBox.Show(this, "\n\n请确保学生照片位于(程序运行目录/Data/IMG/[对应专业]/[对应年级]/[对应姓名.jpg])中\n\n如不存在,学生照片将不能正常显示", "提示"); } }); } catch (Exception e) { Invoke((EventHandler) delegate { MessageBox.Show(this, e.Message, "导入失败"); }); } finally { Invoke((EventHandler) delegate { Invoke((EventHandler) delegate { Text = "设置"; }); Enabled = true; }); } }