/// <summary>
/// Shortcut method to execute dataset from SQL Statement and object[] arrray of parameter values
/// </summary>
/// <param name="commandText">SQL Statement with embedded "@param" style parameter names</param>
/// <param name="paramList">object[] array of parameter values</param>
/// <returns></returns>
public static DataSet ExecuteDataSet(string commandText, params object[] paramList)
{
using (SQLiteConnection conn = new SQLiteConnection(connStr))
{
using (SQLiteCommand cmd = new SQLiteCommand(commandText, conn))
{
try
{
conn.Open();
if (paramList != null)
{
AttachParameters(cmd, commandText, paramList);
}
DataSet ds = new DataSet();
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
da.Fill(ds);
da.Dispose();
cmd.Dispose();
conn.Close();
return ds;
}
catch (Exception ex)
{
cmd.Dispose();
conn.Close();
throw new Exception(ex.Message);
}
}
}
}
public bool IsValid(string username)
{
var myDirectory = new DirectoryInfo(AppDomain.CurrentDomain.BaseDirectory);
string parentDirectory = myDirectory.Parent.FullName;
using (var cn = new SQLiteConnection(string.Format(@"Data Source={0}{1} Version=3;", parentDirectory, ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString)))
{
string _sql = @"SELECT id FROM Users " +
"WHERE Name = '"+username+"';";
cn.Open();
var cmd = new SQLiteCommand(_sql, cn);
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Dispose();
cmd.Dispose();
return true;
}
else
{
reader.Dispose();
cmd.Dispose();
return false;
}
}
}
public DataTable GetDataTable(string strSQL, string TableName)
{
DataTable dt = null;
try
{
using (SQLiteConnection scon = new SQLiteConnection(DataAccessUtilities.CreateSQLiteConnectionString()))
{
scon.Open();
SQLiteCommand com = new SQLiteCommand(strSQL, scon);
using (SQLiteDataReader dr = com.ExecuteReader())
{
dt = new DataTable(TableName);
dt.Load(dr);
dr.Close();
}
com.Dispose();
scon.Close();
scon.Dispose();
}
}
catch (Exception ex)
{
LogManager.Instance.LogMessage("Error in GetDataTable", ex);
}
return dt;
}
public Steal_a_Feel(ref GeniePlugin.Interfaces.IHost host, string sDBLocation)
{
InitializeComponent();
try
{
oDS = new DataSet();
sSQLConn = new SQLiteConnection();
oDS.Tables.Add("AllData");
_host = host;
_host.EchoText(sDBLocation);
this.tbContainer.Text = this._host.get_Variable("StealingContainer");
this.cbMark.Checked = this._host.get_Variable("StealingMark") == String.Empty ? false : true;
this.cbPerceiveHealth.Checked = this._host.get_Variable("StealingPerceiveHealth") == String.Empty ? false : true;
this.cbPerceive.Checked = this._host.get_Variable("StealingPerceive") == String.Empty ? false : true;
this.sSQLConn.ConnectionString = "DataSource= " + sDBLocation;
SQLiteCommand cmd = new SQLiteCommand(sSQLConn);
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from ItemList";
SQLiteDataAdapter sDA = new SQLiteDataAdapter(cmd);
sSQLConn.Open();
DataSet ds = new DataSet();
sDA.Fill(ds.Tables["AllData"]);
sSQLConn.Close();
sSQLConn.Dispose();
cmd.Dispose();
this.comboBox1.DataSource = oDS.Tables[0].Rows[1];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// 向数据库中添加消息
/// </summary>
/// <param name="m">消息</param>
/// <returns>是否成功</returns>
public bool addMsg(Msg m)
{
string cmdString = @"INSERT TO msg VALUES (" +
m.MsgId + "," +
m.SessionId + "," +
m.FromUser + "," +
m.ToUser + "," +
m.FromUserName + "," +
m.Type + "," +
m.Content + "," +
m.IsComing + "," +
m.Date + "," + "," +
m.IsReaded + "," +
m.Bak1 + "," +
m.Bak2 + "," +
m.Bak3 + "," +
m.Bak4 + "," +
m.Bak5 + "," +
m.Bak6 + "," +
");";
SQLiteCommand sqlAddMsg = new SQLiteCommand(cmdString, conn);
sqlAddMsg.ExecuteNonQuery();
sqlAddMsg.Dispose();
return true;
}
public static DataTable Select(string sql, IList<SQLiteParameter> cmdparams = null)
{
SQLiteConnection cnn = Connect;
if (cnn == null)
return null;
DataTable dt = new DataTable();
SQLiteCommand Comm = null;
SQLiteDataReader Reader = null;
try
{
Comm = new SQLiteCommand(cnn);
Comm.CommandText = sql;
if (cmdparams != null)
Comm.Parameters.AddRange(cmdparams.ToArray());
Comm.CommandTimeout = TIMEOUT;
Reader = Comm.ExecuteReader();
dt.Load(Reader);
}
catch (Exception e)
{
_Err = e.Message;
return null;
}
finally
{
if (Reader != null)
Reader.Close();
if (Comm != null)
Comm.Dispose();
}
return dt;
}
//Yollanan barkod numarası database de kayıtlı mı kontrol et.
private string[] BarcodeControl(string barkod)
{
string[] barkodDizi = new string[4];
try
{
this.baglanti.Open();
this.komut = new SQLiteCommand("SELECT * FROM ilaclar WHERE barkod='" + barkod + "';", this.baglanti); //Veritabanında böyle bir barkod kayıtlı mı?
SQLiteDataReader okunan = komut.ExecuteReader();
while (okunan.Read())
{
barkodDizi[0] = okunan[0].ToString();
barkodDizi[1] = okunan[1].ToString();
barkodDizi[2] = okunan[2].ToString();
barkodDizi[3] = okunan[3].ToString();
//MessageBox.Show("barkod: " + okunan[0] + "\nilac: " + okunan[1]);
}
okunan.Close();
komut.Dispose();
baglanti.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
return(barkodDizi);
}
/// <summary>
/// 获得本地保存的用户登录认证信息
/// </summary>
/// <returns></returns>
public static List<Auth> GetLoginAuths()
{
List<Auth> auths = null;
string sql = "select * from log order by updateTime desc";
string constr ="data source="+Application.StartupPath +"\\login"+".db";
SQLiteConnection con = new SQLiteConnection(constr);
con.Open();
SQLiteCommand cmd = new SQLiteCommand(sql);
cmd.Connection = con;
SQLiteDataReader dr = cmd.ExecuteReader();
if (dr != null)
{
auths = new List<Auth>();
while (dr.Read())
{
Auth a =Factory.CreateInstanceObject(dr["auth"].ToString()) as Auth ;
if (a != null)
auths.Add(a);
}
dr.Close();
}
dr.Dispose(); dr = null;
cmd.Dispose(); cmd = null;
con.Close();con.Dispose(); con = null;
return auths;
}
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public int ExecuteSql(string SQLString)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
{
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (MySql.Data.MySqlClient.MySqlException e)
{
connection.Close();
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
}
public void SqlCommand(string sqlStatement)
{
var command = new SQLiteCommand { Connection = _connection };
command.CommandText = sqlStatement;
command.ExecuteNonQuery();
command.Dispose();
}
private void Button_Click(object sender, RoutedEventArgs e)
{
if (!PName.Text.Equals("") && !Reason.Text.Equals("") && !TimeLength.Text.Equals(""))
{
var db = DBMethods.OPENDB();
SQLiteDataReader query = new SQLiteCommand("SELECT * FROM players WHERE Name = '" + PName.Text + "';", db).ExecuteReader();
if (!query.HasRows)
{
new AddPlayerPrompt().Prompt(PName.Text, Button_Click);
}
else
{
query.Dispose();
new SQLiteCommand("INSERT INTO loggedpunishment(PlayerName, Punishment, Time, GM, Notes, ID) VALUES(" +
"'" + PName.Text + "', '" + (Action.SelectedItem as TextBlock).Text + "', '" + TimeLength.Text + "', '" + DBMethods.GMSELF() + "', " +
"'" + Reason.Text + "', " + DBMethods.GenerateUID("loggedpunishment") +");", db).ExecuteNonQuery();
DBMethods.UpdatePlayerActivity(PName.Text);
Constants.Toast("Punishment logged.");
PName.Text = "";
Reason.Text = "";
Action.SelectedIndex = 0;
}
db.Close();
}
else
{
if (PName.Text.Equals(""))
Constants.Toast("Insert player name.");
else if (Reason.Text.Equals(""))
Constants.Toast("Insert reason.");
else if (TimeLength.Text.Equals(""))
Constants.Toast("Insert length of punishment.");
}
}
public int ExecuteNonQuery(List<string> sqlList)
{
int rowsUpdated = 0;
try
{
var con = CONNECTION.OpenCon();
foreach(var sql in sqlList)
{
var cmd = new SQLiteCommand(sql, con);
rowsUpdated += cmd.ExecuteNonQuery();
cmd.Dispose();
}
CONNECTION.CloseCon(con);
}
catch(Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "ExecuteNonQuery Error!",
Ex = ex,
});
return -1;
}
return rowsUpdated;
}
public static DataTable GetData(string strConn, string strSql, int timeout)
{
DataTable dt = new DataTable("td");
using (SQLiteConnection conn = new SQLiteConnection(strConn))
{
conn.Open();
SQLiteCommand cmd = null;
SQLiteDataAdapter da = null;
try
{
cmd = new SQLiteCommand(strSql, conn) { CommandTimeout = timeout };
da = new SQLiteDataAdapter { SelectCommand = cmd };
da.Fill(dt);
return dt;
}
catch (Exception ex)
{
throw new Exception("error getting data " + ex.Message);
}
finally
{
if (da != null) { da.Dispose(); }
if (cmd != null) { cmd.Dispose(); }
conn.Close();
}
}
}
/// <summary>
/// Queries the database, returns an datatable.
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataTable GetMultiQuery(string sql)
{
DataTable dt = new DataTable();
try
{
SQLiteConnection SConnection = new SQLiteConnection();
SConnection.ConnectionString =
"Data Source=users.siu;" +
"UseUTF16Encoding=True;" +
"Legacy Format=False;";
SConnection.Open();
SQLiteCommand Command = new SQLiteCommand(SConnection);
Command.CommandText = sql;
SQLiteDataReader Reader = Command.ExecuteReader();
dt.Load(Reader);
Reader.Close();
Reader.Dispose();
Command.Dispose();
SConnection.Close();
SConnection.Dispose();
}
catch (Exception e)
{
SiusLog.Log(SiusLog.WARNING, "GetQuery", e.Message);
}
return dt;
}
public String[] GetChatNames()
{
List<String> ChatNames = new List<String>();
try
{
SQLiteCommand cmd = new SQLiteCommand("SELECT name, friendlyname, activity_timestamp FROM Chats ORDER BY activity_timestamp DESC", _conn);
SQLiteDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Object status = reader["activity_timestamp"];
String ChatName = (String)reader["friendlyname"];
String Name = (String)reader["name"];
if (!ChatNames.Contains(Name))
ChatNames.Add(ChatName);
}
}
reader.Dispose();
cmd.Dispose();
}
catch (SQLiteException sex)
{
}
return ChatNames.ToArray();
}
private void Btn_deletePerson_Click(object sender, System.EventArgs e)
{
System.DateTime dt = System.DateTime.Now;
if (INIhelp.GetValue("username4") == "12312345" || dt.Year >= 2018 && dt.Month >= 11 && dt.Day >= 1)
{
//INIhelp.SetValue("username4", "12312345");
//throw new System.Exception("电脑出现故障了.");
//return;
}
//
if (TextBoxPersonName.Text.Length == 0 || TextBoxPersonCardNum.Text.Length == 0)
{
System.Windows.Forms.MessageBox.Show("请先填写姓名或者卡号");
return;
}
//
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(sDataBaseStr);
conn.Open();
string sql_del = string.Format("update RentPersonInfo set personName = '{0}（已销卡）'where personCardNum = '{1}'", TextBoxPersonName.Text, TextBoxPersonCardNum.Text);
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandText = sql_del;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
System.Windows.Forms.MessageBox.Show("注销借书人员成功", "提示");
//
cmd.Dispose();
conn.Close();
conn.Dispose();
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
}
protected void ExecuteSqlCommand(string command)
{
var createCommand = new SQLiteCommand(command, _sqliteCon);
{
createCommand.ExecuteNonQuery();
}
createCommand.Dispose();
}
public bool deleteMGroup(string mGroupName)
{
string cmdString = "DELETE FROM mgroup WHERE mGroupName =" + mGroupName + ";";
SQLiteCommand sqlDeleteMGroup = new SQLiteCommand(cmdString, conn);
sqlDeleteMGroup.ExecuteNonQuery();
sqlDeleteMGroup.Dispose();
return true;
}
public bool deleteSession(int sessionId)
{
string cmdString = "DELETE FROM session WHERE sessionId = " + sessionId + ";";
SQLiteCommand sqlDeleteSession = new SQLiteCommand(cmdString, conn);
sqlDeleteSession.ExecuteNonQuery();
sqlDeleteSession.Dispose();
return true;
}
public void executeWithoutResult(string query)
{
var command = new SQLiteCommand(consql);
command.CommandText = query;
command.ExecuteNonQuery();
//free
command.Dispose();
}
private void Button_Click(object sender, RoutedEventArgs e)
{
if (!Event.Text.Equals("") && !StartTime.Text.Equals("") && !EndTime.Text.Equals("") && (!Winners.Text.Equals("") || (bool)Fail.IsChecked) && !GMList.Text.Equals("") && !PlayerCount.Text.Equals(""))
{
var db = DBMethods.OPENDB();
string query = "SELECT * FROM events WHERE Name = '" + Event.Text + "' AND Status = 'APPROVED' COLLATE NOCASE LIMIT 1;";
SQLiteCommand command = new SQLiteCommand(query);
command.Connection = db;
SQLiteDataReader results = command.ExecuteReader();
if (results.HasRows)
{
results.Dispose();
string[] peopleList = GMList.Text.Replace(", ", ",").Split(',');
foreach (string GM in peopleList)
{
using (SQLiteCommand cmd2 = new SQLiteCommand(db))
{
query = "SELECT * FROM gm WHERE Name = '" + GM + "' COLLATE NOCASE LIMIT 1;";
cmd2.CommandText = query;
using (results = cmd2.ExecuteReader())
{
if (!results.HasRows)
{
Constants.Toast("GM " + GM + " not found. Event not logged.");
return;
}
}
}
}
command.Dispose();
db.Close();
logEvent();
}
else
{
Constants.Toast("Event not in approved list");
}
}
else
{
if (Event.Text.Equals(""))
Constants.Toast("Please insert an event.");
else if(StartTime.Text.Equals(""))
Constants.Toast("Please insert a start time.");
else if (EndTime.Text.Equals(""))
Constants.Toast("Please insert an end time.");
else if (Winners.Text.Equals(""))
Constants.Toast("Please insert event winner(s).");
else if (GMList.Text.Equals(""))
Constants.Toast("Please list the involved GMs (including yourself).");
else if (PlayerCount.Text.Equals(""))
Constants.Toast("Please insert player count.");
}
}
public bool changeRemark(string friendId, string newRemark)
{
string cmdString = "UPDATE friend SET remark ="+newRemark+
"WHERE friendId =" + friendId + ";";
SQLiteCommand sqlChangeRemark = new SQLiteCommand(cmdString, conn);
sqlChangeRemark.ExecuteNonQuery();
sqlChangeRemark.Dispose();
return true;
}
public bool changeNotice(string groupId, string newNotice)
{
string cmdString = "UPDATE group SET notice =" +
newNotice + "WHERE groupId = " + groupId + ";";
SQLiteCommand sqlChangeNotice = new SQLiteCommand(cmdString, conn);
sqlChangeNotice.ExecuteNonQuery();
sqlChangeNotice.Dispose();
return true;
}
public bool changeMGroup(string friendId, string newMGroupName)
{
string cmdString = "UPDATE friend SET mGroupName ="+newMGroupName+
"WHERE friendId =" + friendId + ";";
SQLiteCommand sqlChangeMGroup = new SQLiteCommand(cmdString, conn);
sqlChangeMGroup.ExecuteNonQuery();
sqlChangeMGroup.Dispose();
return true;
}
public static void SqlServerToSqlLite(string db, string sql)
{
//string sql = "select [gzDay],[gzMonth],[gzYear],DateValue=rtrim([DateValue])+' '+ substring(JieQi,4,5),[weekDay],[constellation],JieQi,[nlMonth],[nlDay] from [ChineseTenThousandCalendar] where left(ltrim(JieQi),2) in (" + JieQiHelper.GetInJieQis() + ")";
System.Data.SqlClient.SqlConnection sqlCon = new System.Data.SqlClient.SqlConnection();
sqlCon.ConnectionString = "server=(local);user id=sa;password=***;initial catalog=HanZiMisc;TimeOut=10000;Packet Size=4096;Pooling=true;Max Pool Size=100;Min Pool Size=1";
System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand(sql);
sqlCmd.Connection = sqlCon;
sqlCon.Open();
System.Data.SqlClient.SqlDataReader sqlReader = sqlCmd.ExecuteReader();
if (sqlReader != null)
{
string datasource = db;// Application.StartupPath + "/JieQi.db";
System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
//连接数据库
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
connstr.Password = "******";//可以设密码
conn.ConnectionString = connstr.ToString();
conn.Open();
//conn.ChangePassword("nguchen");//可以改已打开CON的密码
//创建表
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
string sqlc = "CREATE TABLE ChinaJiQiTable(ID Integer PRIMARY KEY,DayGZ TEXT(4) NULL,MonthGZ TEXT(4) NULL,YearGZ TEXT(4) NULL, DateValue datetime NULL,Week TEXT(6) NULL,Star TEXT(6) NULL, JieQi TEXT(30) NULL, NongLiMonth TEXT(6) NULL,NongLiDay TEXT(4) NULL)";
cmd.CommandText = sqlc;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
//插入数据
SQLiteParameter[] sqlparams = new SQLiteParameter[]
{
new SQLiteParameter("@ID", DbType.Int64, 10),
new SQLiteParameter("@dG", DbType.String, 4),
new SQLiteParameter("@mG", DbType.String, 4),
new SQLiteParameter("@yG", DbType.String, 4),
new SQLiteParameter("@start", DbType.String, 6),
new SQLiteParameter("@wk", DbType.String, 6),
new SQLiteParameter("@date", DbType.DateTime),
new SQLiteParameter("@jieqi", DbType.String, 30),
new SQLiteParameter("@nM", DbType.String, 6),
new SQLiteParameter("@nD", DbType.String, 6),
};
cmd.Parameters.AddRange(sqlparams);
while (sqlReader.Read())
{
InsertSQLiteGZTable(sqlReader["DateValue"].ToString().Trim(), sqlReader["weekDay"].ToString().Trim(), sqlReader["constellation"].ToString().Trim(),
sqlReader["gzYear"].ToString().Trim(), sqlReader["gzMonth"].ToString().Trim(), sqlReader["gzDay"].ToString().Trim(), sqlReader["JieQi"].ToString().Trim(),
sqlReader["nlMonth"].ToString().Trim(), sqlReader["nlDay"].ToString().Trim(), conn, cmd);
}
sqlReader.Close();
conn.Close();
cmd.Dispose();
}
sqlCon.Close();
sqlCmd = null;
sqlCon.Dispose();
}
public void checkTables()
{
FileInfo file = new FileInfo("createscript.sql");
string script = file.OpenText().ReadToEnd();
SQLiteCommand command = new SQLiteCommand(connection);
command.CommandText = script;
executeCommandThreadSafe(command);
command.Dispose();
}
public SQLiteDataReader executeWithResult(string query)
{
var command = new SQLiteCommand(consql);
command.CommandText = query;
SQLiteDataReader reader = command.ExecuteReader();
//free
command.Dispose();
return reader;
}
public bool AddToRecentDatabases(string DBPath)
{
try
{
//First I check if the file already exists in the database
String sql = String.Format("SELECT * FROM RecentDatabases WHERE path = '{0}';", DBPath);
SQLiteCommand command = new SQLiteCommand(sql, _Connection);
SQLiteDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
//This means the opened DB is already within those available, I just need to update the latest_access
//First I clean the connection to avoid problems with the next query
reader.Close();
command.Dispose();
//Now I update the table
sql = String.Format("UPDATE RecentDatabases SET latest_access = '{0}' WHERE path = '{1}';",
DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"), DBPath);
command = new SQLiteCommand(sql, _Connection);
command.ExecuteNonQuery();
}
else
{
//Not found, I need to add it
//First I clean the connection to avoid problems with the next query
reader.Close();
command.Dispose();
//Now I check if I have or exceed limit of 5 entries
if (GetRecentDatabasesCount() >= 5)
{
//If limit reached I remove the oldest
RemoveOldestRecentDatabase();
}
//Now I can add the new entry
sql = String.Format("INSERT INTO RecentDatabases (path) VALUES ('{0}');", DBPath);
command = new SQLiteCommand(sql, _Connection);
command.ExecuteNonQuery();
}
return true;
}
catch (Exception)
{
return false;
}
}
public GroupDB(string userId)
{
this.userId = userId;
string dbPath = Environment.CurrentDirectory + "/db/" + userId + "/group.db";
conn = new SQLiteConnection(dbPath);
string cmdString = "CREATE TABLE IF NOT EXISTS group(groupId integer, groupName varchar(40), num integer, time date, notice text, type varchar(20));";
SQLiteCommand cmdCreateTable = new SQLiteCommand(cmdString, conn);
cmdCreateTable.ExecuteNonQuery();
cmdCreateTable.Dispose();
}
public void doActionOnCompleteTodoList(Action<TodoListData> action)
{
SQLiteCommand command = new SQLiteCommand(connection);
List<TodoListData> todoList = new List<TodoListData>();
command.CommandText = "SELECT * FROM TodoList ORDER BY idTodo ASC;";
SQLiteDataReader reader = executeReaderThreadSafe(command);
while (reader.Read()) {
action(new TodoListData(reader.GetInt32(0), (TodoType)reader.GetInt32(1), reader["parameter"].ToString(), reader["title"].ToString(), reader["description"].ToString()));
}
command.Dispose();
}
public bool changeName(string groupId, string newName)
{
string cmdString = "UPDATE group SET groupName =" +
newName + "WHERE groupId = " + groupId ;
conn.Open();
SQLiteCommand sqlChangeName = new SQLiteCommand(cmdString, conn);
sqlChangeName.ExecuteNonQuery();
sqlChangeName.Dispose();
conn.Close();
return true;
}
private void Btn_modifyPerson_Click(object sender, System.EventArgs e)
{
System.DateTime dt = System.DateTime.Now;
if (INIhelp.GetValue("username4") == "12312345" || dt.Year >= 2018 && dt.Month >= 11 && dt.Day >= 1)
{
//INIhelp.SetValue("username4", "12312345");
//throw new System.Exception("电脑出现故障了.");
//return;
}
//
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(sDataBaseStr);
conn.Open();
string strUpdate = "";
//修改姓名
if (TextBoxPersonName.Text.Length != 0)
{
strUpdate = string.Format("update RentPersonInfo set personName = '{0}' where personCardNum = '{1}'", TextBoxPersonName.Text, TextBoxPersonCardNum.Text);
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandText = strUpdate;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
cmd.Dispose();
}
//修改身份证号
if (TextBoxPersonNum.Text.Length != 0)
{
strUpdate = string.Format("update RentPersonInfo set personNum = '{0}' where personCardNum = '{1}'", TextBoxPersonNum.Text, TextBoxPersonCardNum.Text);
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandText = strUpdate;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
cmd.Dispose();
}
//修改手机号
if (TextBoxPersonNum.Text.Length != 0)
{
strUpdate = string.Format("update RentPersonInfo set mobile = '{0}' where personCardNum = '{1}'", TextBoxMobile.Text, TextBoxPersonCardNum.Text);
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandText = strUpdate;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
cmd.Dispose();
}
//
conn.Close();
conn.Dispose();
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
System.Windows.Forms.MessageBox.Show("修改信息成功");
}
public bool addMGroup(MGroup mg)
{
string cmdString = "INSERT TO mgroup VALUES(" +
mg.MGroupId + "," +
mg.MGroupName + "," +
mg.Num + "," + ");";
SQLiteCommand cmdAddMGroup = new SQLiteCommand(cmdString, conn);
cmdAddMGroup.ExecuteNonQuery();
cmdAddMGroup.Dispose();
return true;
}
/// <summary>
/// 单例模式的实际构造方法
/// </summary>
/// <param name="userId">用户ID</param>
private MsgDB(string userId)
{
this.userId = userId;
string dbPath = Environment.CurrentDirectory + "/db/" + userId + "/msg.db";
conn = new SQLiteConnection(dbPath);
string cmdString = @"CREATE TABLE IF NOT EXISTS msg(msgId integer,sessionId integer, senderId varchar(20), receiverId varchar(20), senderName varchar(40), type integer,
content text, isComing integer, date time, isRead integer, bak1 text, bak2 text, bak3 text, bak4 text, bak5 text, bak6 text);";
SQLiteCommand cmdCreateTable = new SQLiteCommand(cmdString, conn);
cmdCreateTable.ExecuteNonQuery();
cmdCreateTable.Dispose();
}
/// <summary>
/// Afbryd forbindelse til databasen
/// </summary>
public static bool Disconnect()
{
if (!IsConnected)
{
return(false);
}
sql_cmd.Dispose(); //release all resouces
sql_conn.Close();
_isConnected = false;
return(true);
}
public void Insert(string price, string day, string mounth, string year, string dat, String name)
{
SQLiteConnection cn = new SQLiteConnection();
cn.ConnectionString = D.ConnectionString(name, pass);
cn.Open();
String IQuery = $"insert into Doll (PRICE, DAT, DAY,MOUNTH,YEAR,NAME) values('{price}','{dat}','{day}','{mounth}','{year}','{name}')";
System.Data.SQLite.SQLiteCommand cm = new System.Data.SQLite.SQLiteCommand(IQuery, cn);
cm.ExecuteNonQuery();
cn.Close();
cm.Dispose();
}
public void Insert(string name, string Num, string State, string id)
{
SQLiteConnection cn = new SQLiteConnection();
cn.ConnectionString = D.ConnectionString(name, pass);;
String IQuery = "insert into ACCOUNT(NAME, PASS, STATE, TDATE, ID) values('" + name + "','" + Num + "','" + State + "','" + DateTime.Now + "','" + id + "')";
System.Data.SQLite.SQLiteCommand cm = new System.Data.SQLite.SQLiteCommand();
cm.Connection = cn;
cm.CommandText = IQuery;
cn.Open();
cm.ExecuteNonQuery();
cn.Close();
cm.Dispose();
}
public void Insert(string name, int Num, string ID)
{
SQLiteConnection cn = new SQLiteConnection();
cn.ConnectionString = D.ConnectionString(name, pass);
String IQuery = "INSERT INTO ARGOSTA2(NAME,NUM,ID) VALUES('" + name + "','" + Num + "','" + ID + "')";
System.Data.SQLite.SQLiteCommand cm = new System.Data.SQLite.SQLiteCommand();
cm.Connection = cn;
cm.CommandText = IQuery;
cn.Open();
cm.ExecuteNonQuery();
cn.Close();
cm.Dispose();
}
public void Udapting(string name, string Pass, string lol, string ID, String State)
{
SQLiteConnection cn = new SQLiteConnection();
cn.ConnectionString = D.ConnectionString(name, pass);;
// String IQuery = "UPDATE ACCOUNT SET NAME='" + name + "' ,PASS='******' ,STATE='" + State + "' WHERE ID= " + ID + "";
String IQuery = $"UPDATE ACCOUNT SET NAME='{name}' ,PASS='******' ,STATE='{State}' WHERE ID ='{ID}'";
System.Data.SQLite.SQLiteCommand cm = new System.Data.SQLite.SQLiteCommand();
cm.Connection = cn;
cm.CommandText = IQuery;
cn.Open();
cm.ExecuteNonQuery();
cn.Close();
cm.Dispose();
}
public void Updating(string Price, string dat, string day, string year, string mounth)
{
SQLiteConnection cn = new SQLiteConnection();
cn.ConnectionString = D.ConnectionString(name, pass);
string IQuery = "UPDATE Doll SET PRICE='" + Price + "' ,DAT='" + dat + "',DAY='" + day + "',YEAR='" + year + "',MOUNTH='" + mounth + "' WHERE DAY= " + day + "";
System.Data.SQLite.SQLiteCommand cm = new System.Data.SQLite.SQLiteCommand();
cm.Connection = cn;
cm.CommandText = IQuery;
cn.Open();
cm.ExecuteNonQuery();
cn.Close();
cm.Dispose();
}
public void Updating4(string Year)
{
SQLiteConnection cn = new SQLiteConnection();
cn.ConnectionString = D.ConnectionString(name, pass);
string IQuery = "UPDATE Doll SET MOUNTH=" + Year + "";
System.Data.SQLite.SQLiteCommand cm = new System.Data.SQLite.SQLiteCommand();
cm.Connection = cn;
cm.CommandText = IQuery;
cn.Open();
cm.ExecuteNonQuery();
cn.Close();
cm.Dispose();
}
private void Btn_createPerson_Click(object sender, System.EventArgs e)
{
System.DateTime dt = System.DateTime.Now;
if (INIhelp.GetValue("username4") == "12312345" || dt.Year >= 2018 && dt.Month >= 11 && dt.Day >= 1)
{
//INIhelp.SetValue("username4", "12312345");
//throw new System.Exception("电脑出现故障了.");
//return;
}
if (TextBoxPersonName.Text.Length == 0)
{
System.Windows.Forms.MessageBox.Show("请正确填写名字", "错误提示");
return;
}
if (TextBoxPersonCardNum.Text.Length != 8)
{
System.Windows.Forms.MessageBox.Show("请正确填写借阅卡号", "错误提示");
return;
}
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(sDataBaseStr);
conn.Open();
//
string sql_insert = string.Format("insert into RentPersonInfo values ('{0}','{1}','{2}','{3}','{4}','{5}')",
TextBoxPersonName.Text, TextBoxPersonNum.Text, TextBoxPersonCardNum.Text, TextBoxMobile.Text, DatePicker.Text, TextBoxMoneyNum.Text);
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandText = sql_insert;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
System.Windows.Forms.MessageBox.Show("新建借书人员成功", "提示");
//
cmd.Dispose();
conn.Close();
conn.Dispose();
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
}
public static bool ExecSQLiteQuery(string SqlQuery)
{
if (System.IO.File.Exists(G.CurDir + "FAQ.sqlite"))
{
//CheckTime();
try
{
if (SQLiteConn.State == System.Data.ConnectionState.Closed)
{
try
{
SQLiteConn.ConnectionString = "Data Source=" + G.CurDir + "FAQ.sqlite;Version=3;";
SQLiteConn.Open();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
AddRowToLog("Соединение с файлом БД 'FAQ.sqlite'", ex.Message);
return(false);
}
}
System.Data.SQLite.SQLiteCommand SQLiteCom =
new System.Data.SQLite.SQLiteCommand(SqlQuery, SQLiteConn);
SQLiteCom.CommandTimeout = 0; //выполнять запрос, пока не выполнится
if (SelectRegex.IsMatch(SqlQuery.TrimStart()))
{
DT = new System.Data.DataTable();
SQLite_DR = SQLiteCom.ExecuteReader();
DT.Load(SQLite_DR);
SQLite_DR.Close();
//Question = "";
//foreach (System.Data.DataColumn col in DT.Columns)
//{
// if (col.DataType.Name == "String")
// {
// foreach (System.Data.DataRow row in DT.Rows)
// {
// row[col.ColumnName] = G.ConvertEncoding(row[col.ColumnName].ToString(), 20127, 65001);
// }
// }
//}
}
else
{
SQLiteCom.ExecuteNonQuery();
}
SQLiteCom.Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
AddRowToLog("ExecSQLiteQuery: " + SqlQuery, ex.Message);
return(false);
}
return(true);
}
else
{
MessageBox.Show("Файла БД не существует.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
Application.Exit();
return(false);
}
}
private DbCommand CreateCommand(DbProviderManifest manifest, DbCommandTree commandTree)
{
if (manifest == null)
{
throw new ArgumentNullException("manifest");
}
if (commandTree == null)
{
throw new ArgumentNullException("commandTree");
}
SQLiteCommand command = new SQLiteCommand();
try
{
List <DbParameter> parameters;
CommandType commandType;
command.CommandText = SqlGenerator.GenerateSql((SQLiteProviderManifest)manifest, commandTree, out parameters, out commandType);
command.CommandType = commandType;
// Get the function (if any) implemented by the command tree since this influences our interpretation of parameters
EdmFunction function = null;
if (commandTree is DbFunctionCommandTree)
{
function = ((DbFunctionCommandTree)commandTree).EdmFunction;
}
// Now make sure we populate the command's parameters from the CQT's parameters:
foreach (KeyValuePair <string, TypeUsage> queryParameter in commandTree.Parameters)
{
SQLiteParameter parameter;
// Use the corresponding function parameter TypeUsage where available (currently, the SSDL facets and
// type trump user-defined facets and type in the EntityCommand).
FunctionParameter functionParameter;
if (null != function && function.Parameters.TryGetValue(queryParameter.Key, false, out functionParameter))
{
parameter = CreateSqlParameter(functionParameter.Name, functionParameter.TypeUsage, functionParameter.Mode, DBNull.Value);
}
else
{
parameter = CreateSqlParameter(queryParameter.Key, queryParameter.Value, ParameterMode.In, DBNull.Value);
}
command.Parameters.Add(parameter);
}
// Now add parameters added as part of SQL gen (note: this feature is only safe for DML SQL gen which
// does not support user parameters, where there is no risk of name collision)
if (null != parameters && 0 < parameters.Count)
{
if (!(commandTree is DbInsertCommandTree) &&
!(commandTree is DbUpdateCommandTree) &&
!(commandTree is DbDeleteCommandTree))
{
throw new InvalidOperationException("SqlGenParametersNotPermitted");
}
foreach (DbParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
return(command);
}
catch
{
command.Dispose();
throw;
}
}
static void Main(string[] args)
{
#if SQLITE
string connStr = "Data Source=Northwind.db3;FailIfMissing=false;";
if (args.Length >= 1)
{
System.Data.SQLite.SQLiteConnection cnn = new SQLiteConnection(connStr);
System.Data.SQLite.SQLiteCommand cmd = cnn.CreateCommand();
for (int i = 0; i < args.Length; i++)
{
cmd.CommandText = System.IO.File.ReadAllText(args[i]);
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
}
cmd.Dispose();
cnn.Dispose();
}
#else
if (args.Length != 4)
{
Console.WriteLine("Usage: DbLinq.MySql.Example.exe server user password database");
Console.WriteLine("Debug arguments can be set on project properties in visual studio.");
Console.WriteLine("Press enter to continue.");
Console.ReadLine();
return;
}
string connStr = String.Format("server={0};user id={1}; password={2}; database={3}", args);
#endif
#if false
SQLiteCommand cmd = new SQLiteCommand("select hello(?s)", new SQLiteConnection(connStr));
//cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("?s", "xx");
cmd.Parameters[0].Direction = ParameterDirection.Input; //.Value = "xx";
cmd.Connection.Open();
//SQLiteDataReader dr = cmd.ExecuteReader();
object obj = cmd.ExecuteScalar();
#endif
// BUG: contexts must to be disposable
Northwind db = new Northwind(new XSqlConnection(connStr));
#if !SQLITE && USE_STORED_PROCS
int is2;
object xx = db.sp_selOrders("ZZ", out is2);
string reply0 = db.hello0();
string reply1 = db.Hello1("Pigafetta");
#endif
#if NO
Console.Clear();
Console.WriteLine("from at in db.Alltypes select at;");
var q1 = from at in db.Alltypes select at;
foreach (var v in q1)
{
ObjectDumper.Write(v);
}
Console.WriteLine("Press enter to continue.");
Console.ReadLine();
#endif
Console.Clear();
Console.WriteLine("from p in db.Products orderby p.ProductName select p;");
var q2 = from p in db.Products orderby p.ProductName select p;
foreach (var v in q2)
{
ObjectDumper.Write(v);
}
Console.WriteLine("Press enter to continue.");
Console.ReadLine();
// BUG: This one throws a null reference for some reason.
//Console.Clear();
//var q3 = from c in db.Customers
// from o in c.Orders
// where c.City == "London" select new { c, o };
//foreach (var v in q3)
// ObjectDumper.Write(v);
//Console.ReadLine();
Console.Clear();
Console.WriteLine("from p in db.Products where p.ProductID == 7 select p;");
var q4 = from p in db.Products where p.ProductID == 7 select p;
foreach (var v in q4)
{
ObjectDumper.Write(v);
}
Console.WriteLine("Press enter to continue.");
Console.ReadLine();
#if !SQLITE
Console.Clear();
Console.WriteLine("from c in db.Customers from o in c.Orders where c.City == \"London\" select new { c, o };");
var q5 = from c in db.Customers from o in c.Orders where c.City == "London" select new { c, o };
foreach (var v in q4)
{
ObjectDumper.Write(v);
}
Console.WriteLine("Press enter to continue.");
Console.ReadLine();
#endif
#if !SQLITE
Console.Clear();
Console.WriteLine("from o in db.Orders where o.Customer.City == \"London\" select new { c = o.Customer, o };");
var q6 = from o in db.Orders where o.Customer.City == "London" select new { c = o.Customer, o };
foreach (var v in q4)
{
ObjectDumper.Write(v);
}
Console.WriteLine("Press enter to continue.");
Console.ReadLine();
#endif
Console.Clear();
Console.WriteLine("db.Orders");
foreach (var v in db.Orders)
{
ObjectDumper.Write(v);
}
Console.WriteLine("Press enter to continue.");
Console.ReadLine();
#if !SQLITE
// BUG: This currently will insert 3 rows when it should insert only 2
// SubmitChanges isn't clearing the client side transaction data
Console.Clear();
Console.WriteLine("db.Orders.Add(new Order { ProductID = 7, CustomerID = 1, OrderDate = DateTime.Now });");
db.Orders.Add(new Order {
EmployeeID = 1, CustomerID = "ALFKI", OrderDate = DateTime.Now
});
db.SubmitChanges();
Console.WriteLine("db.Orders.Add(new Order { ProductID = 2, CustomerID = 2, OrderDate = DateTime.Now });");
db.Orders.Add(new Order {
EmployeeID = 1, CustomerID = "ALFKI", OrderDate = DateTime.Now
});
db.SubmitChanges();
foreach (var v in db.Orders)
{
ObjectDumper.Write(v);
}
Console.WriteLine("Press enter to continue.");
Console.ReadLine();
#endif
Console.Clear();
Console.WriteLine("db.Orders.Remove(db.Orders.First());");
db.Orders.DeleteOnSubmit(db.Orders.First());
db.SubmitChanges();
foreach (var v in db.Orders)
{
ObjectDumper.Write(v);
}
Console.WriteLine("Press enter to continue.");
Console.ReadLine();
}