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