/// <summary>
/// 初始化
/// </summary>
public static bool Init()
{
try
{
string dbPath = Environment.CurrentDirectory + "/" + "flow_history.db";
//如果不存在数据库文件，则创建该数据库文件
if (!System.IO.File.Exists(dbPath))
{
SQLiteDBHelper.CreateDB(dbPath);
}
db = new SQLiteDBHelper(dbPath);
string sql;
sql = "CREATE TABLE IF NOT EXISTS flow(fid integer PRIMARY KEY autoincrement DEFAULT (1),pid integer, flow_up long, flow_down long, note_date date);";
db.ExecuteNonQuery(sql, null); // 创建 flow 表
sql = "CREATE TABLE IF NOT EXISTS program(pid integer PRIMARY KEY autoincrement DEFAULT (1), name varchar(30), path text, describe text);";
db.ExecuteNonQuery(sql, null); // 创建 program 表
}
catch (Exception e)
{
//FM_Info.showError(e);
return(false);
}
return(true);
}
protected void Page_Load(object sender, EventArgs e)
{
long ticks = DateTime.Now.Ticks;
SQLiteDBHelper db = new SQLiteDBHelper(Server.MapPath("dtcms.db"));
Button1.Click += new EventHandler(Button1_Click);
Button2.Click += new EventHandler(Button2_Click);
string _pno = Request["pno"];
if (_pno == null) _pno = "1";
int pno = int.Parse(_pno);
int pagecount = 0, recordcount = 0;
DataTable dt = db.SqlLitePaging("*", " from article ", " order by id desc ", pno, 15, out pagecount, out recordcount);
string s = "";
foreach (DataRow dr in dt.Rows)
{
// Response.Write(dr["title"].ToString() + "<br>");
s += dr["title"].ToString() + "<br>";
}
s += "<br>";
s += "共" + pagecount + "页，" + recordcount + "条数据";
s += "<br>";
s += StringUtil.showPage(pno, pagecount, 10, null, "");
s += "";
long ticks2 = DateTime.Now.Ticks;
Response.Write(s + "<br>" + (ticks2 - ticks2));
}
/// <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);
}
/// <summary>
/// 加入或者移出养肥区
/// </summary>
/// <param name="id">书籍ID</param>
/// <param name="state">true为加入养肥区</param>
/// <returns></returns>
public static bool Add_MoveOut(int id, bool state = false)
{
int fatten = state ? 1 : 0;
string sql = "update Books set Fattening=" + fatten + " where Id=" + id;
return((SQLiteDBHelper.ExecuteNonQuery(sql, null) <= 0) ? false : true);
}
public void InitDataBase()
{
// 创建数据库文件
if (!File.Exists(_dataFile))
{
SQLiteDBHelper.CreateDB(_dataFile);
// 设置连接字符串
SQLiteDBHelper.SetConnectionString(Environment.CurrentDirectory + "\\" + _dataFile);
// 创建表结构
_repository.CreateSchema();
UserRepository userRep = new UserRepository();
User systemUser = new User();
systemUser.Id = this.NewGUID();
systemUser.Name = "系统管理员";
systemUser.Account = "admin";
systemUser.Password = "******";
userRep.Insert(systemUser);
}
else
{
// 设置连接字符串
SQLiteDBHelper.SetConnectionString(Environment.CurrentDirectory + "\\" + _dataFile);
}
}
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("******************");
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
string path = Server.MapPath("test.db");
SQLiteDBHelper db = new SQLiteDBHelper(path);
//chuangjiantable
string sql = "CREATE TABLE Test3(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,Name char(3),TypeName varchar(50),addDate datetime,UpdateTime Date,Time time,Comments blob)";
//db.ExecuteNonQuery(sql, null);
// string sql2 = "INSERT INTO Test3(Name,TypeName,addDate,UpdateTime,Time,Comments)values(@Name,@TypeName,@addDate,@UpdateTime,@Time,@Comments)";
// System.Data.Common.DbTransaction trans = db.OpenDbTransaction();
// for (char c = 'A'; c <= 'Z'; c++)
// {
// for (int i = 0; i < 100; i++)
// {
// SQLiteParameter[] parameters = new SQLiteParameter[]{
//new SQLiteParameter("@Name",c+i.ToString()+"s"),
//new SQLiteParameter("@TypeName",c.ToString()),
//new SQLiteParameter("@addDate",DateTime.Now),
//new SQLiteParameter("@UpdateTime",DateTime.Now.Date),
//new SQLiteParameter("@Time",DateTime.Now.ToShortTimeString()),
//new SQLiteParameter("@Comments","Just a Test"+i)
//};
// db.ExecuteNonQueryWithTrans(sql2, parameters, trans);
// }
// }
// db.CommitDbTransaction(trans);
//show
System.Data.DataTable dt = db.ExecuteDataTable("select * from test3", null);
foreach (System.Data.DataRow dr in dt.Rows)
{
Response.Write(dr[0] + "=" + dr[1] + "=" + dr[2] + "=" + dr[3] + "=" + dr[4]+"<br>");
}
}
private async void Load()
{
PageManager.BusyIndicator.IsBusy = true;
Positions = await SQLiteDBHelper.LoadPositions();
PageManager.BusyIndicator.IsBusy = false;
}
public 设备(SQLiteDBHelper db, string name, string groupName = "")
{
this.SizeMode = PictureBoxSizeMode.StretchImage;
base.Name = name;
this.name = name;
DataTable dt = sqlite_gzq.SelectData(db, "interface", "name", name);
if (dt.Rows.Count > 0)
{
this.类型 = Convert.ToInt32(dt.Rows[0]["type"]);
this.colorName = dt.Rows[0]["remark"].ToString();
foreach (DataRow row in dt.Rows)
{
_接口.Add(new 接口(Convert.ToInt32(row["interface_type"]),
Convert.ToInt32(row["position_x"]),
Convert.ToInt32(row["position_y"]),
Convert.ToInt32(row["size_width"]),
Convert.ToInt32(row["size_height"]),
row["picture_url"].ToString()));
}
}
this.groupName = groupName;
if (groupName != "")
{
this.类型 = 设备.接口头;
}
this.groupCenter = new Point();
this.连接设备 = null;
this.BackColor = Color.Transparent;
this.origin = this.Size;
}
public string GetClassNoCount()
{
string sql = string.Format("select distinct classNo from worker where groupname = '{0}'", ConfigurationManager.AppSettings["prorealname"].ToString());
DataTable dt = SQLiteDBHelper.ExecuteDataTable(sql);
if (dt.Rows.Count <= 0)
{
return("暂无班组考勤");
}
string output = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
string countsql = string.Format("select count(id) as count from worker where groupname = '{0}' and classNo = '{1}' and DATE(checkinTime) = '{2}';",
ConfigurationManager.AppSettings["prorealname"].ToString(),
dt.Rows[i]["classNo"].ToString(),
DateTime.Now.ToString("yyyy-MM-dd")
);
DataTable dtt = SQLiteDBHelper.ExecuteDataTable(countsql);
if (dtt.Rows.Count > 0)
{
int cc = Int32.Parse(dtt.Rows[0]["count"].ToString());
if (cc > 0)
{
output += string.Format("{0} 考勤人数：{1} ", dt.Rows[i]["classNo"].ToString(), cc);
}
}
}
return(output);
}
/// <summary>
/// 更新全部书籍
/// </summary>
public static void BooksUpdate(object ob)
{
//获取全部书籍
DataTable dt = SQLiteDBHelper.ExecuteDataTable("select *from Books", null);
List <Book> Books = ModelConvertHelper <Book> .DataTableToList(dt);
foreach (Book book in Books)
{
//获取初始搜索文本
string html = GetHtml.GetHttpWebRequest(book.Url);
BookSource bs = GetBookSource(book.Source);
//检测书源有效性
if (bs.Title is null)
{
Tips tips = new Tips(book.Name + "的书源不存在，请检查书源是否已经被删除掉？");
tips.Show();
return;
}
//获取最新章节
string LatestChapters = Tool.GetRegexStr(html, bs.NewestRegular).Trim();
//获取更新时间
string Update = Tool.GetRegexStr(html, bs.UpdateRegular).Trim();
SQLiteDBHelper.ExecuteNonQuery("Update Books set 'Newest' = '" + LatestChapters + "', 'Update' = '" + Tool.GetUpdataDate(Update) + "' where Id =" + book.Id, null);
}
((MainWindow)ob).Dispatcher.Invoke(new Action(() =>
{
((MainWindow)ob).DataContext = DataFetch.GetBooks();
}));
}
public void SetAppProgramConfig()
{
try
{
SQLiteDBHelper dbApp = new SQLiteDBHelper(GlobalShare.stratPath);
string sql = @"update AppConfig set Value=@Value where Key=@Key";
SQLiteParameter[] parameters = new SQLiteParameter[] {
new SQLiteParameter("@Key", "IP"),
new SQLiteParameter("@Value", GlobalShare.ipAddress)
};
dbApp.ExecuteNonQuery(sql, parameters);
parameters = new SQLiteParameter[] {
new SQLiteParameter("@Key", "Port"),
new SQLiteParameter("@Value", GlobalShare.port)
};
dbApp.ExecuteNonQuery(sql, parameters);
sql = @"select * from LoginInfo";
DataTable dt = db.ExecuteDataTable(sql, null);
if (dt != null && dt.Rows.Count > 0)
{
sql = @"insert into LoginInfo(LoginID,CreateTime) values(@LoginID,@CreateTime)";
for (int i = 0; i < dt.Rows.Count; i++)
{
parameters = new SQLiteParameter[] {
new SQLiteParameter("@LoginID", dt.Rows[i].ItemArray[1].ToString()),
new SQLiteParameter("@CreateTime", dt.Rows[i].ItemArray[2].ToString())
};
dbApp.ExecuteNonQuery(sql, parameters);
}
}
}
catch (Exception ex)
{
}
}
public int GetGameBySearch(DateTime start, DateTime end, string team, ref DataTable dt)
{
SQLiteDBHelper sqliteHelper = new SQLiteDBHelper();
StringBuilder strSql = new StringBuilder();
strSql.Append("select gametime,league,home,visitor,sn from game ");
strSql.Append("where 1 = 1 ");
if (start != null)
{
strSql.Append("and gametime >= @start ");
}
if (end != null)
{
strSql.Append("and gametime <= @end ");
}
if (team != "")
{
strSql.Append("and home like @home or visitor like @visitor ");
}
strSql.Append(" order by gametime desc");
SQLiteParameter[] para = {
sqliteHelper.MakeSQLiteParameter("@start", DbType.DateTime,start),
sqliteHelper.MakeSQLiteParameter("@end", DbType.DateTime,end),
sqliteHelper.MakeSQLiteParameter("@home", DbType.String,32,"%"+team+"%"),
sqliteHelper.MakeSQLiteParameter("@visitor", DbType.String,32,"%"+team+"%")
};
int result = sqliteHelper.ExecuteDataTable(strSql, para, ref dt);
return result;
}
/// <summary>
/// 更换书籍的源
/// </summary>
/// <param name="book">书籍信息</param>
/// <returns>返回更改结果</returns>
public static bool UpdateBookSource(Book book)
{
string sql = "update Books set Url='" + book.Url + "',UpdateState = '" + book.UpdateState + "',Details = '" + book.Details + "',Author = '" + book.Author + "',Source = '" + book.Source + "',Image = '" + book.Image + "',Newest = '" + book.Newest + "','Update' = '" + book.Update + "' where Id = " + book.Id;
return((SQLiteDBHelper.ExecuteNonQuery(sql, null) <= 0) ? false : true);
//需要更新作者、更新日期、最新章节、书籍地址、书源信息、封面图
}
private void DelBtn_Click(object sender, EventArgs e)
{
int id = 0;
if (IdIsNull(ref id))
{
MessageBox.Show("请选择具体的行！", "警告");
return;
}
MessageBoxButtons messButton = MessageBoxButtons.OKCancel;
DialogResult dr = MessageBox.Show("确定要删除该行数据吗?", "确定", messButton);
if (dr == DialogResult.OK)//如果点击“确定”按钮
{
string sql = "delete from Sip2Info where id = @id";
// 获取基目录，它由程序集冲突解决程序用来探测程序集。
string dbPath = AppDomain.CurrentDomain.BaseDirectory + "Sip2Info.db";
SQLiteDBHelper db = new SQLiteDBHelper(dbPath);
SQLiteParameter[] ps =
{
new SQLiteParameter()
{
ParameterName = "id",
Value = id
}
};
db.ExecuteNonQuery(sql, ps);
this.Reload();
}
else//如果点击“取消”按钮
{
}
}
public static void AddRecord(string name)
{
string sql = "update Demo set rank = rank + 1 where name=@name";
SQLiteDBHelper db = new SQLiteDBHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@name", name) };
db.ExecuteNonQuery(sql, parameters);
}
public void DeleteByName(string name)
{
string sql = "delete from CodeMass where CodeName=" + name;
SQLiteDBHelper db = new SQLiteDBHelper(path);
db.ExecuteNonQuery(sql, null);
return;
}
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();
}
/// <summary>
/// 根据项目名及考勤状态查询人数
/// </summary>
/// <param name="groupanme"></param>
/// <param name="state"></param>
/// <returns></returns>
public string UpdateWorkerNumber(string groupanme, int state)
{
string sql = string.Format("select count(job) as num from worker where groupname = '{0}' and checkinState = {1} and DATE(checkinTime) = '{2}';",
groupanme,
state,
DateTime.Now.ToString("yyyy-MM-dd"));
if (state > 1)//统计参与考勤人数
{
sql = string.Format("select count(job) as num from worker where groupname = '{0}' and DATE(checkinTime) = '{1}';",
groupanme,
DateTime.Now.ToString("yyyy-MM-dd"));
}
//Console.WriteLine("+++++" + sql);
DataTable dt = SQLiteDBHelper.ExecuteDataTable(sql);
DataRow row = null;
string num = "未知";
if (dt != null && dt.Rows != null)
{
row = dt.Rows[0];
}
if (null != row)
{
num = row["num"].ToString();
}
return(num);
}
//仅在完整路径时可点击
private void finishConfig_button_Click(object sender, EventArgs e)
{
//currentPath_textBox.Text仅在路径完整时被赋值，所以其为完整路径，但在删除节点时路径不完整
//将本次选择的完整路径写入Path.xml（仅存上次写库的完整路径）,记录上次写库路径以便在下次选择路径时若不需要改变路径则直接使用
if (currentPath_textBox.Text == m_strWellcountPath)//路径为同一路径
{
String[] strWellcountPath = currentPath_textBox.Text.Split("\\".ToArray());
int Length = strWellcountPath.Length;
XmlNodeList pathNodeList = m_xmlUtil.getChildNodes(m_xmlUtil.getRoot(m_xmlUtil.getDocument("Path.xml")));
m_xmlUtil.m_xmlReader.Close();
Trace.WriteLine("finishConfig_button_Click----->getChildNodes()");
pathNodeList.Item(2).Attributes["value"].Value = m_strRootPath + "\\" + strWellcountPath[Length - 3];
pathNodeList.Item(3).Attributes["value"].Value = m_strRootPath + "\\" + strWellcountPath[Length - 3] + "\\" + strWellcountPath[Length - 2];
pathNodeList.Item(4).Attributes["value"].Value = currentPath_textBox.Text;
m_xmlUtil.m_xmlDoc.Save(@"..\..\..\ConfigFile\" + "Path.xml");
m_xmlUtil.m_xmlReader.Close();
setwellcount(strWellcountPath.Last());
SQLiteDBHelper dbhelper = new SQLiteDBHelper(getRealDBPath());
setRealDBHepler(dbhelper);
this.DialogResult = DialogResult.OK;
this.Close();//关闭窗口
}
else
{
MessageBox.Show("选择与写库路径不同，请重新选择！");
}
}
/// <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);
}
/// <summary>
/// 更新书源
/// </summary>
/// <param name="bookSource"></param>
/// <returns></returns>
public static bool SourceUpdate(BookSource bookSource)
{
string sql = "update BookSource set 'Title'='" + bookSource.Title + "','Url'='" + bookSource.Url + "','SearchUrl'='" + bookSource.SearchUrl + "','AddressRangeRegular'='" + bookSource.AddressRangeRegular + "','AddressCuttingRegular'='" + bookSource.AddressCuttingRegular + "','AddressRegular'='" + bookSource.AddressRegular + "','BookNameRegular'='" + bookSource.BookNameRegular + "','AuthorRegular'='" + bookSource.AuthorRegular + "','UpdateRegular'='" + bookSource.UpdateRegular + "','NewestRegular'='" + bookSource.NewestRegular + "','DetailsRegular'='" + bookSource.DetailsRegular + "','StateRegular'='" + bookSource.StateRegular + "','DirectoryScopeRegular'='" + bookSource.DirectoryScopeRegular + "','DirectoryCuttingRegular'='" + bookSource.DirectoryCuttingRegular + "','DirectoryTieleRegular'='" + bookSource.DirectoryTieleRegular + "','DirectoryUrlRegular'='" + bookSource.DirectoryUrlRegular + "','ContentTitleRegular'='" + bookSource.ContentTitleRegular + "','ContentRegular'='" + bookSource.ContentRegular + "','ImageRegular'='" + bookSource.ImageRegular + "' where Id=" + bookSource.Id;
//TempData.UpdateBookSourceS();
return((SQLiteDBHelper.ExecuteNonQuery(sql, null) <= 0) ? false : true);
}
private void button_Send_Click(object sender, EventArgs e)
{
SQLiteDBHelper _helper = new SQLiteDBHelper(Config.CfgInfo.DBPath_Well);
ConcurrentQueue <String> frags = new ConcurrentQueue <string>();
String[] tempFrags;
String data;
String tabid;
WitsTable wt;
String frag;
data = textBox_SendData.Text.Trim();
tempFrags = data.Split(fragSep, StringSplitOptions.RemoveEmptyEntries);
foreach (String str in tempFrags)
{
frags.Enqueue(str);
}
while (frags.Count > 0)
{
frags.TryDequeue(out frag);
toWitsTable(frag, out tabid);
if (tabQueue.Count > 0)
{
tabQueue.TryDequeue(out wt);
idQueue.TryDequeue(out tabid);
_helper.WitsTabAnalysis(tabid, wt);
_helper.InsertWitsData("2", tabid, wt);
}
}
}
/// <summary>
/// 更新本地数据库组织机构版本信息
/// </summary>
public static void UpdateOrgVersion(IMLibrary3.Protocol.OrgVersion orgVersion)
{
try
{
if (orgVersion.RoomsVersion == null)
{
orgVersion.RoomsVersion = "";
}
if (orgVersion.GroupsVersion == null)
{
orgVersion.GroupsVersion = "";
}
if (orgVersion.UsersVersion == null)
{
orgVersion.UsersVersion = "";
}
string sql = "update OrgVersion set Password='',GroupsVersion=@GroupsVersion,UsersVersion=@UsersVersion,RoomsVersion=@RoomsVersion";
System.Data.SQLite.SQLiteParameter[] parameters = new System.Data.SQLite.SQLiteParameter[] {
new System.Data.SQLite.SQLiteParameter("@GroupsVersion", orgVersion.GroupsVersion),
new System.Data.SQLite.SQLiteParameter("@UsersVersion", orgVersion.UsersVersion),
new System.Data.SQLite.SQLiteParameter("@RoomsVersion", orgVersion.RoomsVersion),
};
SQLiteDBHelper.ExecuteNonQuery(sql, parameters);
}
catch (Exception ex)
{
MessageBox.Show(ex.Source + ex.Message);
}
}
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;
}
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;
}
}
}
public void DeleteByID(int id)
{
string sql = "delete from CodeMass where ID="+id.ToString();
SQLiteDBHelper db = new SQLiteDBHelper(path);
db.ExecuteNonQuery(sql, null);
return;
}
/// <summary>
/// 获取LED屏幕设置信息列表
/// </summary>
/// <param name="led_id"></param>
/// <returns></returns>
public List <ModuleInfo> GetLEDArea(int led_id)
{
List <ModuleInfo> lla = new List <ModuleInfo>();
string sql = string.Format("select * from led_area where led_id = 1", led_id);
DataTable dtt = SQLiteDBHelper.ExecuteDataTable("select * from led_area where led_id = 1");
for (int i = 0; i < dtt.Rows.Count; i++)
{
ModuleInfo la = new ModuleInfo();
la.Id = Int32.Parse(dtt.Rows[i]["id"].ToString());
la.Led_id = Int32.Parse(dtt.Rows[i]["led_id"].ToString());
la.Left_begin = Int32.Parse(dtt.Rows[i]["left_begin"].ToString());
la.Top_begin = Int32.Parse(dtt.Rows[i]["top_begin"].ToString());
la.Width = Int32.Parse(dtt.Rows[i]["width"].ToString());
la.Height = Int32.Parse(dtt.Rows[i]["height"].ToString());
la.Area_type = Int32.Parse(dtt.Rows[i]["area_type"].ToString());
la.Module_type = dtt.Rows[i]["module_type"].ToString();
la.Multi_nAlignment = Int32.Parse(dtt.Rows[i]["multi_nAlignment"].ToString());
la.Multi_IsVCenter = Int32.Parse(dtt.Rows[i]["multi_IsVCenter"].ToString());
la.Font_size = Int32.Parse(dtt.Rows[i]["font_size"].ToString());
la.Font_bold = Int32.Parse(dtt.Rows[i]["font_bold"].ToString());
la.In_style = Int32.Parse(dtt.Rows[i]["in_style"].ToString());
la.Delay_time = Int32.Parse(dtt.Rows[i]["delay_time"].ToString());
la.Speed = Int32.Parse(dtt.Rows[i]["speed"].ToString());
lla.Add(la);
}
return(lla);
}
public static int GetRecord(string name)
{
string sql = "select rank from Demo where name=@name";
SQLiteDBHelper db = new SQLiteDBHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@name", SqliteEscape(name)) };
return int.Parse(db.ExecuteScalar(sql, parameters).ToString());
}
public async void Prepare()
{
PageManager.BusyIndicator.IsBusy = true;
await SQLiteDBHelper.Initialize();
PageManager.BusyIndicator.IsBusy = false;
}
public void addArc(Point center, int radius, int startAngle, int sweepAngle, int index)
{
string sql = @"INSERT INTO arcTable(indexNo,Ox,Oy,startAngle,sweepAngle,endAngle,radius,ownerMap)
values(@index,@Ox,@Oy,@startAngle,@sweepAngle,@endAngle,@radius,@ownerMap)";
SQLiteDBHelper db = getDataBase();
SQLiteParameter[] parameters = new SQLiteParameter[]
{
new SQLiteParameter("@index", index),
new SQLiteParameter("@Ox", center.X),
new SQLiteParameter("@Oy", center.Y),
new SQLiteParameter("@startAngle", startAngle),
new SQLiteParameter("@sweepAngle", sweepAngle),
new SQLiteParameter("@endAngle", startAngle + sweepAngle),
new SQLiteParameter("@radius", radius),
new SQLiteParameter("@ownerMap", mapName),
};
try
{
db.ExecuteNonQuery(sql, parameters);
}
catch (Exception x)
{
Console.WriteLine(x.Message);
}
}
private void ModuleManager_Load(object sender, EventArgs e)
{
string sql = "select id,module_type as 模板类型,module_text as 内容 from led_module";
DataTable table = SQLiteDBHelper.ExecuteDataTable(sql);
moduleList.DataSource = table;
}
private void MultiSettingForm_Load(object sender, EventArgs e)
{
string sql = "select id,port as 串口,baud_rate as 波特率,(case tag when 1 then '进场' when 2 then '出场' end) as 人员状态 from setting_info";
DataTable table = SQLiteDBHelper.ExecuteDataTable(sql);
portData.DataSource = table;
}
public void modifyArc(Point center, int radius, int startAngle, int sweepAngle, int index)
{
string sql = @"UPDATE arcTable SET radius = @radius,Ox=@Ox,Oy = @Oy, startAngle = @startAngle,sweepAngle = @sweepAngle, endAngle = @endAngle
WHERE (indexNo = @index) and (ownerMap=@ownerMap)";
SQLiteDBHelper db = getDataBase();
SQLiteParameter[] parameters = new SQLiteParameter[]
{
new SQLiteParameter("@index", index),
new SQLiteParameter("@Ox", center.X),
new SQLiteParameter("@Oy", center.Y),
new SQLiteParameter("@startAngle", startAngle),
new SQLiteParameter("@sweepAngle", sweepAngle),
new SQLiteParameter("@endAngle", startAngle + sweepAngle),
new SQLiteParameter("@radius", radius),
new SQLiteParameter("@ownerMap", mapName),
};
try
{
db.ExecuteNonQuery(sql, parameters);
}
catch (Exception x)
{
Console.WriteLine(x.Message);
}
}
public string DeleteSelectedColumn(List <string> arr)
{
string resultStr = "";
string str = "(";
if (arr.Count > 0)
{
for (var a = 0; a < arr.Count; a++)
{
str += "'" + arr[a] + "',";
}
}
str = str.Substring(0, str.Length - 1) + ")";
string sqlString = "delete from led_info where id in" + str;
try
{
SQLiteDBHelper.ExecuteNonQuery(sqlString, null);
resultStr = "删除成功！";
}
catch (Exception e)
{
Console.Out.WriteLine("" + e.Message);
resultStr = "删除失败，请联系管理员!";
}
return(resultStr);
}
public void InitDbTest()
{
var filepath = @"E:\0src\dictionary\stardict-dicts\spanish\stardict-es-es_Moliner-2.4.2\es-es_Moliner.idx";
var files = StarDictParser.ParseFiles(filepath);
var dbFilePath = SQLiteDBHelper.ParseDbFilePath(files.idx);
using SQLiteDBHelper converter = new SQLiteDBHelper();
converter.InitDb(dbFilePath);
var word1 = new WordEntry {
word = "hello", content = "a welcome!"
};
converter.InsertDictEntry(word1.word, word1.content);
var w1 = converter.ReadDictEntry(word1.word);
Assert.IsTrue(word1.CompareTo(w1) == 0);
var info1 = new WordEntry {
word = "name", content = "a magic thing"
};
converter.InsertIfoEntry(info1.word, info1.content);
var i1 = converter.ReadIfoEntry(info1.word);
Assert.IsTrue(info1.CompareTo(i1) == 0);
}
private async void Load()
{
PageManager.BusyIndicator.IsBusy = true;
Employees = await SQLiteDBHelper.LoadEmployees();
PageManager.BusyIndicator.IsBusy = false;
}
private void btnOK_Click(object sender, EventArgs e)
{
try
{
//获取用户设置的配置内容，并保存到数据库，数据库ledscreen,表名 setting_info
string execSql = "delete from setting_info";
SQLiteDBHelper.ExecuteNonQuery(execSql);
string serialPort = this.cbxSerialPort.Text.Trim();
string baudRate = this.cbxBaudRate.Text.Trim();
string oddEvenValid = this.cbxValidate.Text.Trim();
string sql = "insert into setting_info(port,baud_rate,odd_even_valid) values('" + serialPort + "','" +
baudRate + "','" + oddEvenValid + "')";
int count = SQLiteDBHelper.ExecuteNonQuery(sql);
if (count > 0)
{
MessageBox.Show("参数设置成功！", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
this.Close();
}
else
{
MessageBox.Show("参数设置失败！", "错误", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
}
}
catch
{
MessageBox.Show("参数设置出错", "错误", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
}
}
public static bool DataExist(string name)
{
string sql = "select COUNT(0) from Demo where name=@name";
SQLiteDBHelper db = new SQLiteDBHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@name", SqliteEscape(name)) };
int result = int.Parse(db.ExecuteScalar(sql, parameters).ToString());
return result > 0;
}
/// <summary>
/// 新建一张user表
/// </summary>
public static void CreateTable()
{
StringBuilder strSQL = new StringBuilder();
strSQL.AppendFormat(@"CREATE TABLE user(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
name varchar(20),
email varchar(20)
)");
SQLiteDBHelper sqliteDB = new SQLiteDBHelper();
sqliteDB.CreateTable(strSQL.ToString());
}
private DAOFactory()
{
string DataBasePath = Environment.CurrentDirectory + Path.DirectorySeparatorChar + Constants.DB_FILE;
//File.Delete(DataBasePath);
if (!File.Exists(DataBasePath))
{
SQLiteConnection.CreateFile(DataBasePath);
}
dbHelper = new SQLiteDBHelper(DataBasePath);
}
public static void InsertData(Data data)
{
string sql = "INSERT INTO Demo(Name,Rank,Comments)values(@Name,@Rank,@Comments)";
SQLiteDBHelper db = new SQLiteDBHelper(dbPath);
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@Name",SqliteEscape(data.Name)),
new SQLiteParameter("@Rank",data.Rank),
new SQLiteParameter("@Comments",SqliteEscape(data.Comments))
};
db.ExecuteNonQuery(sql, parameters);
}
private DAOFactory()
{
if (SQLiteDHelper == null)
{
string DataBasePath = Environment.CurrentDirectory + Path.DirectorySeparatorChar + "DB.sqlite";
if (!File.Exists(DataBasePath))
{
SQLiteConnection.CreateFile(DataBasePath);
}
SQLiteDHelper = new SQLiteDBHelper(DataBasePath);
}
}
public static void CreateTable()
{
//如果不存在改数据库文件，则创建该数据库文件
if (!System.IO.File.Exists(dbPath))
{
SQLiteDBHelper.CreateDB(dbPath);
}
SQLiteDBHelper db = new SQLiteDBHelper(dbPath);
string sql = "CREATE TABLE Test3(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE," +
"Name char(3),TypeName varchar(50),AddDate datetime,UpdateTime Date,Time time,Comments blob)";
db.ExecuteNonQuery(sql, null);
}
/// <summary>
/// 执行查询SQL,返回受影响行数
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameters">参数</param>
/// <returns></returns>
public int IntQuery(string sql, SQLiteParameter[] parameters)
{
int result = 0;
try
{
SQLiteDBHelper dbHelper = new SQLiteDBHelper();
result = dbHelper.ExecuteNonQuery(sql, parameters);
}
catch
{
}
return result;
}
/// <summary>
/// 执行查询SQL,返回查询结果表
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameters">参数</param>
/// <returns>查询结果表</returns>
public DataTable DataTableQuery(string sql, SQLiteParameter[] parameters)
{
DataTable data = new DataTable();
try
{
SQLiteDBHelper dbHelper = new SQLiteDBHelper();
data = dbHelper.ExecuteDataTable(sql, parameters);
}
catch
{
}
return data;
}
public int GetGameByKey(int gameSN, ref DataTable dt)
{
SQLiteDBHelper sqliteHelper = new SQLiteDBHelper();
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from game ");
strSql.Append("where sn = @sn order by gametime desc");
SQLiteParameter[] para = {
sqliteHelper.MakeSQLiteParameter("@sn", DbType.Int32,gameSN)
};
int result = sqliteHelper.ExecuteDataTable(strSql, para, ref dt);
return result;
}
public int GetGameDetailByKey(int gameSN, ref DataTable dt)
{
SQLiteDBHelper sqliteHelper = new SQLiteDBHelper();
StringBuilder strSql = new StringBuilder();
strSql.Append("select changetime,win,draw,lose,changeminute from gamedetail ");
strSql.Append("where gamesn = @gamesn ");
SQLiteParameter[] para = {
sqliteHelper.MakeSQLiteParameter("@gamesn", DbType.Int32,gameSN)
};
int result = sqliteHelper.ExecuteDataTable(strSql, para, ref dt);
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));
}
}
}
/// <summary>
/// 执行查询SQL,返回受影响行数
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameters">参数</param>
/// <returns></returns>
public int IntQuery(string sql, SQLiteParameter[] parameters)
{
int result = 0;
try
{
SQLiteDBHelper dbHelper = new SQLiteDBHelper();
result = dbHelper.ExecuteNonQuery(sql, parameters);
}
catch (Exception exMsg)
{
//Log.Error("ExecuteNonQuery出错:" + exMsg.ToString());
}
return result;
}
/// <summary>
/// 执行查询SQL,返回查询结果表
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameters">参数</param>
/// <returns>查询结果表</returns>
public DataTable DataTableQuery(string sql, SQLiteParameter[] parameters)
{
DataTable data = new DataTable();
try
{
SQLiteDBHelper dbHelper = new SQLiteDBHelper();
data = dbHelper.ExecuteDataTable(sql, parameters);
}
catch (Exception ex)
{
//Log.Error("DataTableQuery出错:" + ex.ToString());
}
return data;
}
/// <summary>
/// 插入一条数据，返回插入的序号
/// </summary>
/// <param name="name">用户名</param>
/// <param name="email">Email地址</param>
/// <returns>序号ID</returns>
public static int Insert(string name, string email)
{
try
{
StringBuilder strSQL = new StringBuilder();
strSQL.AppendFormat("INSERT INTO user(name,email) VALUES ('{0}','{1}')", name, email);
SQLiteDBHelper sqliteDB = new SQLiteDBHelper();
return Convert.ToInt32(sqliteDB.ExecuteScalar(strSQL.ToString()));
}
catch(Exception e)
{
return 0;
}
}
/// <summary>
/// 根据序号ID更新指定的数据
/// </summary>
/// <param name="name">用户名</param>
/// <param name="email">email地址</param>
/// <param name="ID">序号</param>
/// <returns>更新的行数</returns>
public static int Update(string name, string email, int ID)
{
try
{
StringBuilder strSQL = new StringBuilder();
strSQL.AppendFormat("UPDATE user SET name = '{0}', email = '{1}' WHERE ID = {2}", name, email, ID);
SQLiteDBHelper sqliteDB = new SQLiteDBHelper();
return Convert.ToInt32(sqliteDB.ExecuteNonQuery(strSQL.ToString()));
}
catch
{
return 0;
}
}
/// <summary>
/// 根据序号ID删除数据
/// </summary>
/// <param name="ID">序号</param>
/// <returns>删除的行数</returns>
public static int Delete(int ID)
{
try
{
StringBuilder strSQL = new StringBuilder();
strSQL.AppendFormat("DELETE FROM user WHERE ID = {0}", ID);
SQLiteDBHelper sqliteDB = new SQLiteDBHelper();
return Convert.ToInt32(sqliteDB.ExecuteNonQuery(strSQL.ToString()));
}
catch
{
return 0;
}
}
void Button1_Click(object sender, EventArgs e)
{
SQLiteDBHelper db = new SQLiteDBHelper(Server.MapPath("dtcms.db"));
System.Data.Common.DbTransaction trans = db.OpenDbTransaction();
string s = NjhLib.Utils.FileUtil.GetHtmlStringByFilePath(Server.MapPath("1.htm"));
s = Server.HtmlEncode(s);
for (int i = 0; i < 100000; i++)
{
string sql = " insert into article(title ,author,form,keyword,zhaiyao,classid,imgurl,daodu,content,click,ismsg,istop,isred,ishot,isslide,islock,addtime) ";
sql += "values('项目背景说明及系统开发要求" + i.ToString() + "','author','form','keyword.keyword','zhaiyaozhaiyaozhaiyaozhaiyaozhaiyaozhaiyao',1,'','daodudaodudaodudddddddddddddddddddddddddddddddddd','" + s + "',1,1,1,1,1,1,0,'" + DateTime.Now.ToString() + "')";
db.ExecuteNonQueryWithTrans(sql, null, trans);
}
db.CommitDbTransaction(trans);
Response.Write("<script>alert('insert suc')</script>");
}
private DAOFactory()
{
if (SQLiteDHelper == null)
{
string DataBasePath = GetUserDataFolder() + Path.DirectorySeparatorChar + Constants.DB_FILE;
if (!File.Exists(DataBasePath))
{
SQLiteConnection.CreateFile(DataBasePath);
}
SQLiteDHelper = new SQLiteDBHelper(DataBasePath);
}
if (!string.IsNullOrEmpty(DataCache.Instance.MySqlConnection) && DataCache.Instance.OpenMySqlDb)
{
MySqlDbHelper = new MysqlDBHelper(DataCache.Instance.MySqlConnection);
}
}
public static AppConfigDAO GetAppConfigDAO()
{
if (SQLiteDHelper == null)
{
string DataBasePath = GetUserDataFolder() + Path.DirectorySeparatorChar + Constants.DB_FILE;
if (!File.Exists(DataBasePath))
{
SQLiteConnection.CreateFile(DataBasePath);
}
SQLiteDHelper = new SQLiteDBHelper(DataBasePath);
}
if (appConfigDAO == null)
{
appConfigDAO = new AppConfigDAO(SQLiteDHelper);
}
return appConfigDAO;
}
public int DeleteAllGame()
{
SQLiteDBHelper sqliteHelper = new SQLiteDBHelper();
StringBuilder strSql = new StringBuilder();
Dictionary<StringBuilder, SQLiteParameter[]> sqlStringList = new Dictionary<StringBuilder, SQLiteParameter[]>();
strSql.Append("delete from game");
sqlStringList.Add(strSql, null);
strSql = new StringBuilder();
strSql.Append("delete from gamedetail");
sqlStringList.Add(strSql, null);
int result = sqliteHelper.ExecuteSqlTran(sqlStringList);
return result;
}
/// <summary>
/// 执行查询SQL,返回查询结果的首行首列
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameters">参数</param>
/// <returns></returns>
public string StringQuery(string sql, SQLiteParameter[] parameters)
{
string result = string.Empty;
try
{
SQLiteDBHelper dbHelper = new SQLiteDBHelper();
DataTable data = dbHelper.ExecuteDataTable(sql, parameters);
if (data != null && data.Rows.Count > 0)
{
result = data.Rows[0][0].ToString();
}
}
catch
{
}
return result;
}
private DAOFactory()
{
string DataBasePath = string.Empty;
#if DEBUG
DataBasePath = FileUtils.GetAppDataFolder() + Path.DirectorySeparatorChar + Constants.DEBUG_DB_FILE;
#else
DataBasePath = FileUtils.GetAppDataFolder() + Path.DirectorySeparatorChar + Constants.DB_FILE;
#endif
//File.Delete(DataBasePath);
if (!File.Exists(DataBasePath))
{
SQLiteConnection.CreateFile(DataBasePath);
SQLiteDBHelper.EncryptDatabase(DataBasePath, Constants.DB_PASSWORD);
}
dbHelper = new SQLiteDBHelper(DataBasePath, Constants.DB_PASSWORD);
keywordDAO = new KeywordDAO(dbHelper);
vpnDAO = new VpnDAO(dbHelper);
inquiryDAO = new InquiryDAO(dbHelper);
profileDAO = new ProfileDAO(dbHelper);
}
