public Aminoacids ParseAminoacids(string fileName) { SQLiteDBHelper sqlite = new SQLiteDBHelper(fileName); var result = new Aminoacids(); var aaReader = sqlite.ExecuteReader("select OneLetterCode, MonoisotopicMass, AverageMass from Aminoacids", null); while (aaReader.Read()) { var aa = aaReader.GetString(0); if (string.IsNullOrEmpty(aa) || aa == " ") { continue; } var monomass = aaReader.GetDouble(1); var avemass = aaReader.GetDouble(2); if (monomass == 0.0 || avemass == 0.0) { continue; } result[aa[0]].ResetMass(monomass, avemass); } var staticModReader = sqlite.ExecuteReader("select ParameterName, ParameterValue from ProcessingNodeParameters", null); while (staticModReader.Read()) { var name = staticModReader.GetString(0); var value = staticModReader.GetString(1); if (name.StartsWith("StatMod_")) { var parts = value.Split('#'); if (parts.Length == 2) { var aaid = int.Parse(parts[0]); var modid = int.Parse(parts[1]); var aareader = sqlite.ExecuteReader(string.Format("select aa.OneLetterCode, aam.DeltaMass from Aminoacids as aa, AminoacidModifications as aam where aa.AminoacidID={0} and aam.AminoacidModificationID={1}", aaid, modid), null); while (aareader.Read()) { var aaChar = aareader.GetString(0)[0]; var aaDeltaMass = aareader.GetDouble(1); var curAminoacid = result[aaChar]; curAminoacid.ResetMass(curAminoacid.MonoMass + aaDeltaMass, curAminoacid.AverageMass + aaDeltaMass); } } } } return(result); }
private void AlarmDetailForm_Shown(object sender, EventArgs e) { string EXEPath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase; string dbPath = EXEPath + "Demo.db3"; string sql = "select * from AlarmLog where id = " + m_DBid; SQLiteDBHelper db = new SQLiteDBHelper(dbPath); using (SQLiteDataReader reader = db.ExecuteReader(sql, null)) { while (reader.Read()) { string stID = reader["id"].ToString(); string stdatetime = reader["datetime"].ToString(); string stAlarmID = reader["AlarmID"].ToString(); string stAlarmName = reader["AlarmName"].ToString(); string stAlarmLevel = reader["AlarmLevel"].ToString(); string stHandleAlarmMode = reader["HandleAlarmMode"].ToString(); string stHandleTime = reader["HandleTime"].ToString(); string stHandlePeopleName = reader["HandlePeopleName"].ToString(); string stHandleResult = reader["HandleResult"].ToString(); TimetextBox.Text = stdatetime; IDtextBox.Text = stAlarmID; NametextBox.Text = stAlarmName; LeveltextBox.Text = stAlarmLevel; HandlerichTextBox.Text = stHandleAlarmMode; PeopletextBox.Text = stHandlePeopleName; HandleTimetextBox.Text = stHandleTime; ResultrichTextBox.Text = stHandleResult; } } }
/// <summary> /// 获得所有群 /// </summary> public static List <exRoom> GetRooms() { List <exRoom> Rooms = new List <exRoom>(); string sql = "select * from Rooms"; System.Data.SQLite.SQLiteDataReader dr = SQLiteDBHelper.ExecuteReader(sql, null); if (dr != null) { while (dr.Read()) { exRoom Room = new exRoom(); { Room.RoomID = Convert.ToString(dr["RoomID"]); Room.RoomName = Convert.ToString(dr["RoomName"]); Room.Notice = Convert.ToString(dr["Notice"]); Room.UserIDs = Convert.ToString(dr["Users"]); Room.OrderID = Convert.ToInt32(dr["OrderID"]); Room.CreateUserID = Convert.ToString(dr["CreateUserID"]); } Rooms.Add(Room); } dr.Close(); } dr.Dispose(); return(Rooms); }
public void UnHandleAlarm() {//未处理的报警将其强制曝出 string EXEPath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase; string dbPath = EXEPath + "Demo.db3"; string sql = "select * from AlarmLog where HandleTime is null"; string stID = ""; string stAlarmID = ""; SQLiteDBHelper db = new SQLiteDBHelper(dbPath); using (SQLiteDataReader reader = db.ExecuteReader(sql, null)) { while (reader.Read()) { stID = reader["id"].ToString(); stAlarmID = reader["AlarmID"].ToString(); AlarmDetailForm dlg = new AlarmDetailForm(stID, 1) { StartPosition = FormStartPosition.CenterParent }; dlg.ShowDialog(); dlg.Dispose(); } } }
private void button1_Click(object sender, EventArgs e) { string stPeople = PeopletextBox.Text; string stResult = ResultrichTextBox.Text; if ((string.IsNullOrEmpty(stPeople)) || (string.IsNullOrEmpty(stResult))) { MessageBox.Show("请输入处理人员和处理结果!"); return; } string EXEPath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase; string dbPath = EXEPath + "Demo.db3"; SQLiteDBHelper db = new SQLiteDBHelper(dbPath); string sql2 = "UPDATE AlarmLog SET HandleTime = @HandleTime,HandlePeopleName = @HandlePeopleName,HandleResult = @HandleResult where id = " + m_DBid; SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@HandleTime", DateTime.Now), new SQLiteParameter("@HandlePeopleName", stPeople), new SQLiteParameter("@HandleResult", stResult) }; int DBResult = db.ExecuteNonQuery(sql2, parameters); if (DBResult > 0) { MessageBox.Show("报警处理完成!"); } string ServerWarningID = ""; string stdatetime = ""; string stHandleTime = ""; string stHandleResult = ""; string sql = "select * from AlarmLog where id = " + m_DBid;; using (SQLiteDataReader reader = db.ExecuteReader(sql, null)) { while (reader.Read()) { string stID = reader["id"].ToString(); stdatetime = reader["datetime"].ToString(); string stAlarmID = reader["AlarmID"].ToString(); string stAlarmName = reader["AlarmName"].ToString(); string stAlarmLevel = reader["AlarmLevel"].ToString(); string stHandleAlarmMode = reader["HandleAlarmMode"].ToString(); ServerWarningID = reader["ServerWarningID"].ToString(); stHandleTime = reader["HandleTime"].ToString(); string stHandlePeopleName = reader["HandlePeopleName"].ToString(); stHandleResult = reader["HandleResult"].ToString(); } } DateTime Handletime = Convert.ToDateTime(stHandleTime); DateTime datetime = Convert.ToDateTime(stdatetime); TimeSpan Span = Handletime - datetime; int nDuration = Span.Seconds; DTSManager.PostSever.PostWarningFixedData(ServerWarningID, stHandleTime, stHandleResult, nDuration); this.Close(); }
public void IsPostAlarmData() {//循环查询未发送到服务端的alram,补发 List <string> IsPostIDList = new List <string>() { }; string EXEPath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase; string dbPath = EXEPath + "Demo.db3"; string sql = "select * from AlarmLog where IsPost = 0"; string stID = ""; string stAlarmID = ""; SQLiteDBHelper db = new SQLiteDBHelper(dbPath); using (SQLiteDataReader reader = db.ExecuteReader(sql, null)) { while (reader.Read()) { stID = reader["id"].ToString(); stAlarmID = reader["AlarmID"].ToString(); int nWarningID = DTSManager.PostSever.PostWarningData(Convert.ToInt32(stAlarmID)); if (nWarningID != 0) { string sql2 = "UPDATE AlarmLog SET IsPost = @IsPost, ServerWaringID = @ServerWaringID where id = " + stID; SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@IsPost", 1), new SQLiteParameter("@ServerWaringID", nWarningID) }; int DBResult = db.ExecuteNonQuery(sql2, parameters); } } } }
public void ShowData() { dt = new DataTable(); dt.Columns.Add("ContactID", System.Type.GetType("System.String")); dt.Columns.Add("FirstName", System.Type.GetType("System.String")); dt.Columns.Add("LastName", System.Type.GetType("System.String")); dt.Columns.Add("EmailAddress", System.Type.GetType("System.String")); //查询从50条起的20条记录 string sql = "select * from teacher "; SQLiteDBHelper db = new SQLiteDBHelper("E:\\科研\\726-横向项目\\sqlite\\db2.db"); using (SQLiteDataReader reader = db.ExecuteReader(sql, null)) { while (reader.Read()) { DataRow dr = dt.NewRow(); dr["ContactID"] = reader.GetString(0); dr["FirstName"] = reader.GetString(1); dr["LastName"] = reader.GetString(2); dr["EmailAddress"] = reader.GetString(3); dt.Rows.Add(dr); //Trace.WriteLine("ID:{0},TypeName{1}", reader.GetString(0), reader.GetString(1)); Trace.WriteLine("******************"); Trace.WriteLine(reader.GetString(0)); Trace.WriteLine(reader.GetString(1)); Trace.WriteLine(reader.GetString(2)); Trace.WriteLine(reader.GetString(3)); Trace.WriteLine("******************"); } } }
public Dictionary <int, IIdentifiedSpectrum> ParseSpectrumMap(string fileName) { SQLiteDBHelper sqlite = new SQLiteDBHelper(fileName); Dictionary <int, IIdentifiedSpectrum> result = new Dictionary <int, IIdentifiedSpectrum>(); var fileMap = ParseFileMap(fileName); //读取肽段列表 var peptideReader = sqlite.ExecuteReader("select sh.SpectrumID, sh.FirstScan, sh.LastScan, sh.RetentionTime, sh.Charge, mp.FileID, sh.Mass from spectrumheaders as sh, MassPeaks as mp where sh.MassPeakID=mp.MassPeakID", null); Progress.SetMessage("Parsing peptides ..."); while (peptideReader.Read()) { var specid = peptideReader.GetInt32(0); IIdentifiedSpectrum spectrum = new IdentifiedSpectrum(); result[specid] = spectrum; spectrum.Query.FileScan.FirstScan = peptideReader.GetInt32(1); spectrum.Query.FileScan.LastScan = peptideReader.GetInt32(2); //retention time spectrum.Query.FileScan.Charge = peptideReader.GetInt32(4); spectrum.Query.FileScan.Experimental = fileMap[peptideReader.GetInt32(5)]; spectrum.ExperimentalMH = peptideReader.GetDouble(6); spectrum.Rank = 1; } return(result); }
public Dictionary <int, IIdentifiedProtein> ParseProteinMap(string fileName) { SQLiteDBHelper sqlite = new SQLiteDBHelper(fileName); var result = new Dictionary <int, IIdentifiedProtein>(); string sqlProtein = "select ps.ProteinID, pa.Description, pro.Sequence, ps.ProteinScore, ps.Coverage from ProteinAnnotations as pa, Proteins as pro, ProteinScores as ps where pro.ProteinID=pa.ProteinID and pro.ProteinID=ps.ProteinID"; var proteinReader = sqlite.ExecuteReader(sqlProtein, null); Progress.SetMessage("Parsing proteins ..."); while (proteinReader.Read()) { var protein = new IdentifiedProtein(); var proid = proteinReader.GetInt32(0); var des = proteinReader.GetString(1); if (des.Length > 0 && des[0] == '>') { des = des.Substring(1); } protein.Reference = des; protein.Sequence = proteinReader.GetString(2); protein.Score = proteinReader.GetDouble(3); protein.Coverage = proteinReader.GetDouble(4); result[proid] = protein; } return(result); }
public static string RecordAlarmToDB(AlarmInfo AlaInfo) { string EXEPath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase; string dbPath = EXEPath + "Demo.db3"; SQLiteDBHelper db = new SQLiteDBHelper(dbPath); string sql2 = "INSERT INTO AlarmLog(datetime,AlarmID,AlarmName,AlarmLevel,HandleAlarmMode,ServerWarningID,IsPost)" + "values(@datetime,@AlarmID,@AlarmName,@AlarmLevel,@HandleAlarmMode,@ServerWarningID,@IsPost)"; SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@datetime", DateTime.Now), new SQLiteParameter("@AlarmID", AlaInfo.AlarmID), new SQLiteParameter("@AlarmName", AlaInfo.AlarmName), new SQLiteParameter("@AlarmLevel", AlaInfo.AlarmLevel), new SQLiteParameter("@HandleAlarmMode", AlaInfo.HandleAlarmMode), new SQLiteParameter("@ServerWarningID", AlaInfo.ServerWarningID), new SQLiteParameter("@IsPost", (AlaInfo.ServerWarningID == 0)?0:1) }; db.ExecuteNonQuery(sql2, parameters); string sql3 = "select max(id) from AlarmLog"; string stID = ""; using (SQLiteDataReader reader = db.ExecuteReader(sql3, null)) { while (reader.Read()) { stID = reader["max(id)"].ToString(); } } return(stID); }
public DataSet GetDataByQuery(string search) { CompareInfo Compare = CultureInfo.InvariantCulture.CompareInfo; SQLiteDBHelper db = new SQLiteDBHelper(path); string outS = "("; bool flag = false; search = search.Replace(" ", ""); using (SQLiteDataReader reader = db.ExecuteReader("select ID,CodeName,Comments from CodeMass order by id", null)) { while (reader.Read()) { if ((Compare.IndexOf(reader.GetString(1).Replace(" ", ""), search, CompareOptions.IgnoreCase) != -1) || (Compare.IndexOf(reader.GetString(2).Replace(" ", ""), search, CompareOptions.IgnoreCase) != -1)) { outS = outS + reader.GetInt64(0).ToString() + ","; flag = true; } } if (flag) outS = outS.Substring(0, outS.Length - 1) + ")"; else outS="(-1,-2)"; } String connectionString = "Data Source=" + path; //String selectCommand = "Select ID, CodeName,Language,AddTime,UpdateTime,Source_Code,Comments from CodeMass"; //String selectCommand = "select * from CodeMass where CodeName like '%"+ChangeStrToLikeStr(search) + "%'"; String selectCommand = "select * from CodeMass where ID in " + outS; SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(selectCommand, connectionString); DataSet ds = new DataSet(); dataAdapter.Fill(ds, "T_CLASS"); return ds; }
/// <summary> /// 获得所有分组集合 /// </summary> public static List <exGroup> GetGroups() { List <exGroup> Groups = new List <exGroup>(); string sql = "select * from Groups order by orderID "; System.Data.SQLite.SQLiteDataReader dr = SQLiteDBHelper.ExecuteReader(sql, null); if (dr != null) { while (dr.Read()) { exGroup group = new exGroup(); { group.GroupID = Convert.ToString(dr["GroupID"]); group.GroupName = Convert.ToString(dr["GroupName"]); group.SuperiorID = Convert.ToString(dr["SuperiorID"]); group.OrderID = Convert.ToInt32(dr["orderID"]); } Groups.Add(group); } dr.Close(); } dr.Dispose(); return(Groups); }
public static void InitConsumableRecord() { try { string EXEPath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase; string dbPath = EXEPath + "Demo.db3"; SQLiteDBHelper db = new SQLiteDBHelper(dbPath); XmlDocument XMLalarmInfo = new XmlDocument(); XMLalarmInfo.Load(EXEPath + "ConsumableInfoConfig.xml"); XmlElement el = XMLalarmInfo.DocumentElement; XmlNodeList ConsumableInfoNodes = el.GetElementsByTagName("ConsumableInfo"); foreach (XmlNode node in ConsumableInfoNodes) { XmlNodeList ConsumableNode = node.ChildNodes; string ConsumableID = ((XmlElement)ConsumableNode[0]).InnerText; string ConsumableName = ((XmlElement)ConsumableNode[1]).InnerText; string stLifetime = ((XmlElement)ConsumableNode[3]).InnerText; int nLifetime = Convert.ToInt32(stLifetime); nLifetime = nLifetime * 60; bool bIsInit = false; string sql1 = "select * from ConsumableLog where ConsumableID = " + ConsumableID; using (SQLiteDataReader reader = db.ExecuteReader(sql1, null)) { while (reader.Read()) { bIsInit = true; } } if (!bIsInit) { DateTime Now = DateTime.Now; string sql2 = "INSERT INTO ConsumableLog(datetime,ConsumableID,ConsumableName,Lifetime,WorkingTime,Changetime,ChangePeopleName)" + "values(@datetime,@ConsumableID,@ConsumableName,@Lifetime,@WorkingTime,@Changetime,@ChangePeopleName)"; SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@datetime", Now), new SQLiteParameter("@ConsumableID", ConsumableID), new SQLiteParameter("@ConsumableName", ConsumableName), new SQLiteParameter("@Lifetime", nLifetime), new SQLiteParameter("@WorkingTime", "0"), new SQLiteParameter("@Changetime", Now), new SQLiteParameter("@ChangePeopleName", "初始") }; db.ExecuteNonQuery(sql2, parameters); DTSManager.PostSever.PostConsumableReplaceData("初始", Convert.ToInt32(ConsumableID), 0, Now.ToString()); } } } catch (XmlException ex) { MessageBox.Show(ex.Message); } }
private void ChangeListview(bool bRefresh) {//查询数据库,将信息显示到Listview上 lock (locker) { string EXEPath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase; string dbPath = EXEPath + "Demo.db3"; SQLiteDBHelper db = new SQLiteDBHelper(dbPath); XmlDocument XMLalarmInfo = new XmlDocument(); XMLalarmInfo.Load(EXEPath + "ConsumableInfoConfig.xml"); XmlElement el = XMLalarmInfo.DocumentElement; XmlNodeList ConsumableInfoNodes = el.GetElementsByTagName("ConsumableInfo"); int Count = 0; foreach (XmlNode node in ConsumableInfoNodes) { XmlNodeList ConsumableNode = node.ChildNodes; string ConsumableID = ((XmlElement)ConsumableNode[0]).InnerText; string ConsumableName = ((XmlElement)ConsumableNode[1]).InnerText; string stLifetime = ((XmlElement)ConsumableNode[3]).InnerText; stLifetime = stLifetime + "(" + (Convert.ToInt32(stLifetime) * 60).ToString() + "分钟)"; string sql3 = "select max(id), WorkingTime, Changetime from ConsumableLog where ConsumableID = " + ConsumableID; string stID = ""; string stWorkingTime = ""; string stChangeDate = ""; using (SQLiteDataReader reader = db.ExecuteReader(sql3, null)) { while (reader.Read()) { stID = reader["max(id)"].ToString(); stWorkingTime = reader["WorkingTime"].ToString(); stChangeDate = reader["Changetime"].ToString(); } } if (m_bFormShow) { if (bRefresh) { ConsumablelistView.Items[Count].SubItems[4].Text = stWorkingTime; } else { ListViewItem item1 = new ListViewItem(ConsumableID); item1.SubItems.Add(ConsumableName); item1.SubItems.Add(stLifetime); item1.SubItems.Add(stChangeDate); item1.SubItems.Add(stWorkingTime); ConsumablelistView.Items.Add(item1); } } Count++; } } }
public List <_XJmodel> GetInspectionRecordsByDate(DateTime startDate, DateTime endDate) { SQLiteDataReader dr = null; List <_XJmodel> tempList = new List <_XJmodel>(); SQLiteParameter[] parameters = { new SQLiteParameter(PARAM_STARTTIME, DbType.DateTime), new SQLiteParameter(PARAM_ENDTIME, DbType.DateTime) }; parameters[0].Value = startDate; parameters[1].Value = endDate; try { SQLiteDBHelper sdb = new SQLiteDBHelper(Program.g_dbPath); dr = sdb.ExecuteReader(SELECT_IR, parameters); while (dr.Read()) { _XJmodel inspectionRecord = new _XJmodel(); inspectionRecord.lcNumber = dr.GetString(0).Trim(); inspectionRecord.getStatus = dr.GetString(1).Trim(); if (dr[3] != DBNull.Value) { inspectionRecord.getRecordTime = dr.GetDateTime(3); } else { inspectionRecord.getRecordTime = null; } if (dr[4] != DBNull.Value) { inspectionRecord.getWorker = dr.GetString(4).Trim(); } else { inspectionRecord.getWorker = ""; } tempList.Add(inspectionRecord); } return(tempList); } finally { if (dr != null) { dr.Close(); } } }
public void CreatRunDatalist() {//建立班次和生产数的list m_RunDataList.Clear(); string EXEPath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase; string dbPath = EXEPath + "Demo.db3"; string sql = "select * from RunningLog"; SQLiteDBHelper db = new SQLiteDBHelper(dbPath); string stStarttime = ""; string stStoptime = ""; int ProduceCount = 0; string TypeStart = "start"; string TypeStop = "stop"; string TypeCount = "Produce"; using (SQLiteDataReader reader = db.ExecuteReader(sql, null)) { while (reader.Read()) { string stID = reader["id"].ToString(); string stdatetime = reader["datetime"].ToString(); string stType = reader["Type"].ToString(); string stcount = reader["count"].ToString(); string stRelateID = reader["RelateID"].ToString(); string IsPost = reader["IsPost"].ToString(); if (stType.Equals(TypeStart) && string.IsNullOrEmpty(stStarttime)) { stStarttime = stdatetime; RunData run = new RunData(stStarttime, stStoptime, ProduceCount); m_RunDataList.Add(run); } if (stType.Equals(TypeCount) && !string.IsNullOrEmpty(stStarttime)) { ProduceCount += Convert.ToInt32(stcount); int nListLenght = m_RunDataList.Count(); m_RunDataList[nListLenght - 1].count = ProduceCount; } if (stType.Equals(TypeStop) && !string.IsNullOrEmpty(stStarttime)) { stStoptime = stdatetime; int nListLenght = m_RunDataList.Count(); m_RunDataList[nListLenght - 1].Stoptime = stStoptime; stStarttime = ""; ProduceCount = 0; stStoptime = ""; } } } }
public TrainRemoteControl.Model.Display GetFirstRecord() { SQLiteDataReader dr = null; TrainRemoteControl.Model.Display display = new TrainRemoteControl.Model.Display(); try { SQLiteDBHelper sdb = new SQLiteDBHelper(Program.g_dbPath); dr = sdb.ExecuteReader(GetFirstRecordSqlStr, null); string temp = null; while (dr.Read()) { display.Number = dr.GetInt32(2); display.oil_mass = float.Parse(dr.GetString(3)); display.fire_alarm = dr.GetBoolean(4); display.up_oil_place = dr.GetBoolean(5); display.up_water_place = dr.GetBoolean(6); display.battery_voltage = dr.GetBoolean(7); display.alarm = dr.GetBoolean(8); display.alarm_voice = dr.GetBoolean(9); temp = dr.GetString(10).Trim(); display.oil_press = !string.IsNullOrEmpty(temp)? float.Parse(temp):0; temp = dr.GetString(11).Trim(); display.water_temp = !string.IsNullOrEmpty(temp) ? float.Parse(temp) : 0; temp = dr.GetString(12).Trim(); display.frequency = !string.IsNullOrEmpty(temp) ? float.Parse(temp) : 0; temp = dr.GetString(13).Trim(); display.motor_speed = !string.IsNullOrEmpty(temp) ? float.Parse(temp) : 0; temp = dr.GetString(14).Trim(); display.voltage = !string.IsNullOrEmpty(temp) ? float.Parse(temp) : 0; temp = dr.GetString(15).Trim(); display.current = !string.IsNullOrEmpty(temp) ? float.Parse(temp) : 0; temp = dr.GetString(16).Trim(); display.motor_power = !string.IsNullOrEmpty(temp) ? float.Parse(temp) : 0; temp = dr.GetString(17).Trim(); display.power_factor = !string.IsNullOrEmpty(temp) ? float.Parse(temp) : 0; display.oil_leak = dr.GetBoolean(18); display.OKAlarm = dr.GetBoolean(19); display.NOAlarm = dr.GetBoolean(20); display.NCAlarm = dr.GetBoolean(21); display.time = dr.GetDateTime(22); } return(display); } finally { if (dr != null) { dr.Close(); } } }
public static void ShowData() { //查询从50条起的20条记录 string sql = "select * from test3 order by id desc limit 50 offset 20"; SQLiteDBHelper db = new SQLiteDBHelper("D:\\Demo.db3"); using (SQLiteDataReader reader = db.ExecuteReader(sql, null)) { while (reader.Read()) { Console.WriteLine("ID:{0},TypeName{1}", reader.GetInt64(0), reader.GetString(1)); } } }
public Dictionary <int, string> ParseFileMap(string fileName) { var result = new Dictionary <int, string>(); SQLiteDBHelper sqlite = new SQLiteDBHelper(fileName); var fiReader = sqlite.ExecuteReader("Select FileID, FileName from FileInfos", null); while (fiReader.Read()) { result[fiReader.GetInt32(0)] = Path.GetFileNameWithoutExtension(new FileInfo(fiReader.GetString(1)).Name); } return(result); }
public static void ShowData() { //查询从50条起的20条记录 string sql = "select * from test3 order by id desc limit 50 offset 20"; SQLiteDBHelper db = new SQLiteDBHelper(dbPath); using (SQLiteDataReader reader = db.ExecuteReader(sql, null)) { while (reader.Read()) { Console.WriteLine("ID:{0},TypeName{1}", reader.GetInt64(0), reader.GetString(1)); } } }
public void ReflashData() { ConsumableDetaillistView.Items.Clear(); string EXEPath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase; string dbPath = EXEPath + "Demo.db3"; SQLiteDBHelper db = new SQLiteDBHelper(dbPath); string sql3 = "select * from ConsumableLog where ConsumableID = " + m_stConsumableID; string stID = ""; string stdatetime = ""; m_stConsumableID = ""; string stConsumableName = ""; string stConsumableInfo = ""; string stLifetime = ""; string stWorkingTime = ""; string stChangetime = ""; string stChangePeopleName = ""; string stRemarks = ""; string stIsPost = ""; using (SQLiteDataReader reader = db.ExecuteReader(sql3, null)) { while (reader.Read()) { stID = reader["id"].ToString(); stdatetime = reader["datetime"].ToString(); m_stConsumableID = reader["ConsumableID"].ToString(); stConsumableName = reader["ConsumableName"].ToString(); stConsumableInfo = reader["ConsumableInfo"].ToString(); stLifetime = reader["Lifetime"].ToString(); stLifetime = (Convert.ToInt32(stLifetime) / 60).ToString() + "小时(" + stLifetime + "分钟)"; stWorkingTime = reader["WorkingTime"].ToString() + "分钟"; stChangetime = reader["Changetime"].ToString(); stChangePeopleName = reader["ChangePeopleName"].ToString(); stRemarks = reader["Remarks"].ToString(); stIsPost = reader["IsPost"].ToString(); ListViewItem item1 = new ListViewItem(m_stConsumableID); item1.SubItems.Add(stConsumableName); item1.SubItems.Add(stLifetime); item1.SubItems.Add(stChangetime); item1.SubItems.Add(stWorkingTime); item1.SubItems.Add(stChangePeopleName); item1.SubItems.Add(stRemarks); ConsumableDetaillistView.Items.Add(item1); } } }
public List <CarInit> readCar() { List <CarInit> init = new List <CarInit>(1); SQLiteDBHelper db = getDataBase(); string str = "select * from carInit"; using (SQLiteDataReader reader = db.ExecuteReader(str, null)) { while (reader.Read()) { init.Add(new CarInit(reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4))); } } return(init); }
public void IsPostRunData() {//循环查询未发送到服务端的start和stop信息,补发 List <string> IsPostIDList = new List <string>() { }; string EXEPath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase; string dbPath = EXEPath + "Demo.db3"; string sql = "select * from RunningLog where IsPost = 0 and (Type=='start' or Type=='stop')"; string stID = ""; SQLiteDBHelper db = new SQLiteDBHelper(dbPath); using (SQLiteDataReader reader = db.ExecuteReader(sql, null)) { while (reader.Read()) { stID = reader["id"].ToString(); string stdatetime = reader["datetime"].ToString(); string stType = reader["Type"].ToString(); string stcount = reader["count"].ToString(); string stRelateID = reader["RelateID"].ToString(); string IsPost = reader["IsPost"].ToString(); bool ret = false; if (stType.CompareTo("start") == 0) { ret = DTSManager.PostSever.PostRunningData(1, DTSManager.m_shift, 0); } else { ret = DTSManager.PostSever.PostRunningData(3, DTSManager.m_shift, 0); } if (ret) { IsPostIDList.Add(stID); } } } foreach (string elm in IsPostIDList) { string sql2 = "UPDATE RunningLog SET IsPost = @IsPost where id = " + stID; SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@IsPost", 1) }; int DBResult = db.ExecuteNonQuery(sql2, parameters); } }
public string ShowData(string sql) { //Search Record //string sql = "select * from test3 order by id"; SQLiteDBHelper db = new SQLiteDBHelper(path); string outS = ""; using (SQLiteDataReader reader = db.ExecuteReader(sql, null)) { while (reader.Read()) { outS = outS + reader.GetInt64(0).ToString() + " " + reader.GetString(1) + " " + reader.GetString(6); //Console.WriteLine("ID:{0},TypeName{1}", reader.GetInt64(0), reader.GetString(1)); } } return(outS); }
/// <summary> /// 获取用户资料 /// </summary> /// <param name="UserID"></param> /// <returns></returns> public static UserVcard GetUserVcard(string UserID) { UserVcard card = null; string sql = "select Vcard from UsersVcard where UserID=@UserID"; System.Data.SQLite.SQLiteParameter[] parameters = new System.Data.SQLite.SQLiteParameter[] { new System.Data.SQLite.SQLiteParameter("@UserID", UserID) }; System.Data.SQLite.SQLiteDataReader dr = SQLiteDBHelper.ExecuteReader(sql, parameters); if (dr != null && dr.Read()) { card = Factory.CreateInstanceObject(Convert.ToString(dr["Vcard"])) as UserVcard; dr.Close(); } dr.Dispose(); return(card); }
public ConsumableChange(string stConsumableID, ConsumableDetail DetailDlg) { InitializeComponent(); m_stConsumableID = stConsumableID; m_DetailDlg = DetailDlg; IDtextBox.Enabled = false; NametextBox.Enabled = false; LifttimetextBox.Enabled = false; LastChangetimetextBox.Enabled = false; WorkingtimetextBox.Enabled = false; string EXEPath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase; string dbPath = EXEPath + "Demo.db3"; SQLiteDBHelper db = new SQLiteDBHelper(dbPath); string sql3 = "select max(id),ConsumableName,Lifetime,WorkingTime,Changetime from ConsumableLog where ConsumableID = " + stConsumableID; using (SQLiteDataReader reader = db.ExecuteReader(sql3, null)) { while (reader.Read()) { m_stConsumableName = reader["ConsumableName"].ToString(); m_stLifetime = reader["Lifetime"].ToString(); m_stLifetime = (Convert.ToInt32(m_stLifetime) / 60).ToString() + "小时(" + m_stLifetime + "分钟)"; m_stWorkingTime = reader["WorkingTime"].ToString() + "分钟"; m_stChangetime = reader["Changetime"].ToString(); IDtextBox.Text = stConsumableID; NametextBox.Text = m_stConsumableName; LifttimetextBox.Text = m_stLifetime; LastChangetimetextBox.Text = m_stChangetime; WorkingtimetextBox.Text = m_stWorkingTime; } } if (DetailDlg == null) { this.Text = m_stConsumableName + "易损件工作时间已经超过使用寿命,请及时更换!"; } else { this.Text = m_stConsumableName + "易损件更换"; } }
/// <summary> /// 获得对话记录消息总行数 /// </summary> /// <param name="userID">发送者ID</param> /// <param name="MyID">自己的ID</param> /// <returns></returns> private int GetPageCount(string userID)//获得页数 { int count = 0; string sql = ""; switch (this.MessageType) { case IMLibrary3.Enmu.MessageType.User: sql = string.Format("select Count(*) from [MsgRecord] where ((froms='{0}' and tos='{1}') or (froms='{1}' and tos='{0}')) And MessageType={2} ORDER BY DateTime DESC", userID, myUserID, (byte)this.MessageType); break; case IMLibrary3.Enmu.MessageType.Group: sql = string.Format("select Count(*) from [MsgRecord] where tos='{0}' and MessageType={1} ORDER BY DateTime DESC", SelectUserID, (byte)this.MessageType); break; case IMLibrary3.Enmu.MessageType.Notice: sql = string.Format("select Count(*) from [MsgRecord] where MessageType={0} ORDER BY DateTime DESC", (byte)this.MessageType); break; } SQLiteDataReader dr = SQLiteDBHelper.ExecuteReader(sql, null); if (dr == null) { return(count); } while (dr.Read()) { count = Convert.ToInt32(dr[0]); } dr.Close(); dr.Dispose(); int pageCount = Convert.ToInt32(count / this.pageSize); if ((count % this.pageSize) != 0) { pageCount += 1; } //MessageBox.Show(count.ToString()); return(pageCount); }
public void IsPostConsumable() {//循环查询未发送到服务端的Consumable信息,补发 List <string> IsPostIDList = new List <string>() { }; string EXEPath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase; string dbPath = EXEPath + "Demo.db3"; string sql = "select * from ConsumableLog where IsPost = 0"; string stID = ""; string ChangePeopleName = ""; string stConsumableID = ""; string stWorkingTime = ""; string stChangetime = ""; SQLiteDBHelper db = new SQLiteDBHelper(dbPath); using (SQLiteDataReader reader = db.ExecuteReader(sql, null)) { while (reader.Read()) { stID = reader["id"].ToString(); stConsumableID = reader["ConsumableID"].ToString(); stWorkingTime = reader["WorkingTime"].ToString(); ChangePeopleName = reader["ChangePeopleName"].ToString(); stChangetime = reader["Changetime"].ToString(); bool ret = false; ret = DTSManager.PostSever.PostConsumableReplaceData(ChangePeopleName, Convert.ToInt32(stConsumableID), Convert.ToInt32(stWorkingTime), stChangetime); if (ret) { IsPostIDList.Add(stID); } } } foreach (string elm in IsPostIDList) { string sql2 = "UPDATE ConsumableLog SET IsPost = @IsPost where id = " + stID; SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@IsPost", 1) }; int DBResult = db.ExecuteNonQuery(sql2, parameters); } }
private void AlarmDataDialog_Shown(object sender, EventArgs e) { string EXEPath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase; string dbPath = EXEPath + "Demo.db3"; string sql = "select * from AlarmLog"; SQLiteDBHelper db = new SQLiteDBHelper(dbPath); AlarmlistView.Items.Clear(); using (SQLiteDataReader reader = db.ExecuteReader(sql, null)) { while (reader.Read()) { string stID = reader["id"].ToString(); string stdatetime = reader["datetime"].ToString(); string stAlarmID = reader["AlarmID"].ToString(); string stAlarmName = reader["AlarmName"].ToString(); string stAlarmLevel = reader["AlarmLevel"].ToString(); string stHandleAlarmMode = reader["HandleAlarmMode"].ToString(); string stHandleTime = reader["HandleTime"].ToString(); string stHandlePeopleName = reader["HandlePeopleName"].ToString(); string stHandleResult = reader["HandleResult"].ToString(); ListViewItem item1 = new ListViewItem(stID); item1.SubItems.Add(stdatetime); item1.SubItems.Add(stAlarmID); item1.SubItems.Add(stAlarmName); item1.SubItems.Add(stAlarmLevel); item1.SubItems.Add(stHandleAlarmMode); item1.SubItems.Add(stHandleTime); item1.SubItems.Add(stHandlePeopleName); if (string.IsNullOrEmpty(stHandleResult)) { item1.SubItems.Add("未处理"); } else { item1.SubItems.Add(stHandleResult); } AlarmlistView.Items.Add(item1); } } }
public DataSet GetDataByQuery(string search) { CompareInfo Compare = CultureInfo.InvariantCulture.CompareInfo; SQLiteDBHelper db = new SQLiteDBHelper(path); string outS = "("; bool flag = false; search = search.Replace(" ", ""); using (SQLiteDataReader reader = db.ExecuteReader("select ID,CodeName,Comments from CodeMass order by id", null)) { while (reader.Read()) { if ((Compare.IndexOf(reader.GetString(1).Replace(" ", ""), search, CompareOptions.IgnoreCase) != -1) || (Compare.IndexOf(reader.GetString(2).Replace(" ", ""), search, CompareOptions.IgnoreCase) != -1)) { outS = outS + reader.GetInt64(0).ToString() + ","; flag = true; } } if (flag) { outS = outS.Substring(0, outS.Length - 1) + ")"; } else { outS = "(-1,-2)"; } } String connectionString = "Data Source=" + path; //String selectCommand = "Select ID, CodeName,Language,AddTime,UpdateTime,Source_Code,Comments from CodeMass"; //String selectCommand = "select * from CodeMass where CodeName like '%"+ChangeStrToLikeStr(search) + "%'"; String selectCommand = "select * from CodeMass where ID in " + outS; SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(selectCommand, connectionString); DataSet ds = new DataSet(); dataAdapter.Fill(ds, "T_CLASS"); return(ds); }
public override IEnumerable <string> Process(string fileName) { var parser = new MsfDatabaseParser(SearchEngineType.SEQUEST); var seqs = parser.ParseProteinSequences(fileName); SQLiteDBHelper sqlite = new SQLiteDBHelper(fileName); var result = new List <Sequence>(); var aaReader = sqlite.ExecuteReader("select count(*) from peptides_decoy", null); if (aaReader.Read()) { if (aaReader.GetInt32(0) > 0) // there are decoy database { foreach (var seq in seqs) { result.Add(seq); var revseq = new Sequence(MsfDatabaseParser.GetReversedReference(seq.Reference), SequenceUtils.GetReversedSequence(seq.SeqString)); result.Add(revseq); } } } if (result.Count == 0) { result = seqs; } var fastafile = fileName + ".fasta"; using (var sw = new StreamWriter(fastafile)) { var ff = new FastaFormat(); foreach (var seq in result) { ff.WriteSequence(sw, seq); } } return(new[] { fastafile }); }
public void LinkPeptideToProtein(string fileName, Dictionary <int, IIdentifiedProtein> proMap, Dictionary <int, IIdentifiedPeptide> pepMap) { SQLiteDBHelper sqlite = new SQLiteDBHelper(fileName); var sqlLink = "select PeptideID, ProteinID from PeptidesProteins"; var linkReader = sqlite.ExecuteReader(sqlLink, null); while (linkReader.Read()) { var pepid = linkReader.GetInt32(0); var proid = linkReader.GetInt32(1); if (proMap.ContainsKey(proid) && pepMap.ContainsKey(pepid)) { var pro = proMap[proid]; var pep = pepMap[pepid]; pro.Peptides.Add(pep); pep.AddProtein(pro.Name); } } }
/// <summary> /// 查询所有数据 /// </summary> /// <returns>返回数据集</returns> public static List<User> Query() { try { StringBuilder strSQL = new StringBuilder(); strSQL.AppendFormat("SELECT ID, name, email FROM user"); SQLiteDBHelper sqliteDB = new SQLiteDBHelper(); SQLiteDataReader reader = sqliteDB.ExecuteReader(strSQL.ToString()); List<User> results = new List<User>(); while (reader.Read()) { User user = new User(); user.ID = Convert.ToInt32(reader["ID"]); user.name = reader["name"].ToString(); user.email = reader["email"].ToString(); results.Add(user); } return results; } catch { return null; } }
public string ShowData(string sql) { //Search Record //string sql = "select * from test3 order by id"; SQLiteDBHelper db = new SQLiteDBHelper(path); string outS=""; using (SQLiteDataReader reader = db.ExecuteReader(sql, null)) { while (reader.Read()) { outS = outS + reader.GetInt64(0).ToString() + " " + reader.GetString(1) + " " + reader.GetString(6); //Console.WriteLine("ID:{0},TypeName{1}", reader.GetInt64(0), reader.GetString(1)); } } return outS; }
public static List<Data> ShowData() { string sql = "select * from Demo order by id desc"; SQLiteDBHelper db = new SQLiteDBHelper("D:\\Demo.db3"); List<Data> datas = new List<Data>(); using (SQLiteDataReader reader = db.ExecuteReader(sql, null)) { while (reader.Read()) { datas.Add(new Data() { Name = reader.GetString(1), Rank = reader.GetInt32(2), Comments = reader.GetString(3) }); } } return datas; }