// SQliteDataReader
public static List<List<Object>> SqliteDataReader(string q, SQLiteParameter[] sqLiteParameters, int rows)
{
// If there's no parameter, insert NULL
SQLiteDataReader dataReader;
var objects = new List<List<Object>>();
var cnn = CreateConnection();
cnn.Open();
using (DbTransaction dbTrans = cnn.BeginTransaction())
{
using (DbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = q;
if (sqLiteParameters != null)
cmd.Parameters.AddRange(sqLiteParameters);
dataReader = (SQLiteDataReader) cmd.ExecuteReader();
while (dataReader.Read())
{
objects.Add(new List<object>());
for (int i = 0; i < rows; i++)
{
objects[objects.Count - 1].Add(dataReader[i]);
}
}
dataReader.Close();
}
dbTrans.Commit();
}
cnn.Close();
return objects;
}
/// <summary>
/// 根据编号查询系统配置
/// </summary>
/// <param name="Id">编号</param>
/// <returns>数据集合</returns>
public SysConfig Select(string Id)
{
String sql = "select * from SysConfig where Id=@Id";
System.Data.SQLite.SQLiteParameter[] parm = new System.Data.SQLite.SQLiteParameter[] {
new SQLiteParameter("@Id", Id),
};
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text, parm);
if (dt.Rows.Count > 0)
{
SysConfig index = new SysConfig();
index.Id = dt.Rows[0]["Id"].GetString();
index.SysKey = dt.Rows[0]["Sys_Key"].GetString();
index.Name = dt.Rows[0]["Name"].GetString();
index.SysValue = dt.Rows[0]["Sys_Value"].GetString();
index.Remark = dt.Rows[0]["Remark"].GetString();
index.GroupNo = dt.Rows[0]["Group_No"].GetString();
index.IsVisible = dt.Rows[0]["Is_Visible"].GetBoolean();
index.OrderId = dt.Rows[0]["Order_Id"].GetInt32();
index.CreateBy = dt.Rows[0]["Create_By"].GetString();
index.CreateTime = dt.Rows[0]["Create_Time"].GetDateTime();
index.UpdateBy = dt.Rows[0]["UpDate_By"].GetString();
index.UpdateTime = dt.Rows[0]["UpDate_Time"].GetDateTime();
return(index);
}
else
{
return(null);
}
}
/// <summary>
/// 根据编号查询文章类型表
/// </summary>
/// <param name="CatId">编号</param>
/// <returns>数据集合</returns>
public ArtCategory Select(string CatId)
{
String sql = "select * from ArtCategory where Cat_Id=@CatId";
System.Data.SQLite.SQLiteParameter[] parm = new System.Data.SQLite.SQLiteParameter[] {
new SQLiteParameter("@CatId", CatId),
};
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text, parm);
if (dt.Rows.Count > 0)
{
ArtCategory index = new ArtCategory();
index.CatId = dt.Rows[0]["Cat_Id"].GetString();
index.CatName = dt.Rows[0]["Cat_Name"].GetString();
index.CatRemark = dt.Rows[0]["Cat_Remark"].GetString();
index.CatOrder = dt.Rows[0]["Cat_Order"].GetInt32();
index.CreateBy = dt.Rows[0]["Create_By"].GetString();
index.CreateTime = dt.Rows[0]["Create_Time"].GetDateTime();
index.UpdateBy = dt.Rows[0]["UpDate_By"].GetString();
index.UpdateTime = dt.Rows[0]["UpDate_Time"].GetDateTime();
return(index);
}
else
{
return(null);
}
}
/// <summary>
/// 向数据库里插入/修改图像格式的字段
/// </summary>
/// <param name="sSql">SQL语句</param>
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
/// <returns>影响的记录数</returns>
public int ExecuteSqlImg(string sSql, byte[] fs)
{
if (!OpenConn())
{
return(-1);
}
try
{
using (SQLiteCommand cmd = new SQLiteCommand(sSql, Conn))
{
if (Transaction != null)
{
cmd.Transaction = Transaction;
}
System.Data.SQLite.SQLiteParameter myParameter = new System.Data.SQLite.SQLiteParameter("@fs", DbType.Binary);
myParameter.Value = fs;
cmd.Parameters.Add(myParameter);
int rows = cmd.ExecuteNonQuery();
return(rows);
}
}
catch
{
return(-1);
}
}
public static void NewCategory(int account, string title, string type)
{
using (SQLiteConnection con = new SQLiteConnection(Settings.Default.AccountingConnectionString))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = con;
cmd.CommandText = "INSERT INTO main.category VALUES (NULL, @title, @type, @account)";
SQLiteParameter pTitle = new SQLiteParameter("title", title);
pTitle.Direction = ParameterDirection.Input;
SQLiteParameter pAccount = new SQLiteParameter("account", account);
pAccount.Direction = ParameterDirection.Input;
SQLiteParameter pType = new SQLiteParameter("type", type);
pType.Direction = ParameterDirection.Input;
cmd.Parameters.AddRange(new SQLiteParameter[] { pTitle, pAccount, pType });
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString, string content)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
SQLiteCommand cmd = new SQLiteCommand(SQLString, connection);
SQLiteParameter myParameter = new SQLiteParameter("@content", DbType.String);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SQLite.SQLiteException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
public bool SetUserStatus(string uId, int status)
{
string sql = "update t_users set Status=@status where Uid=@uId";
SQLiteParameter[] para = new SQLiteParameter[] { new SQLiteParameter("@status", status), new SQLiteParameter("@uId", uId) };
int result = new SqlLiteHelper().RunSQL(sql, para);
return result > 0;
}
/// <summary>
/// 根据编号查询字典表
/// </summary>
/// <param name="Id">编号</param>
/// <returns>数据集合</returns>
public Diction Select(Decimal Id)
{
String sql = "select * from Diction where Id=@Id";
System.Data.SQLite.SQLiteParameter[] parm = new System.Data.SQLite.SQLiteParameter[] {
new SQLiteParameter("@Id", Id),
};
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text, parm);
if (dt.Rows.Count > 0)
{
Diction index = new Diction();
index.Id = dt.Rows[0]["Id"].GetDecimal();
index.Name = dt.Rows[0]["Name"].GetString();
index.Note = dt.Rows[0]["Note"].GetString();
index.ParentId = dt.Rows[0]["Parent_Id"].GetDecimal();
index.OrderId = dt.Rows[0]["Order_Id"].GetDecimal();
index.CreateBy = dt.Rows[0]["Create_By"].GetString();
index.CreateTime = dt.Rows[0]["Create_Time"].GetDateTime();
index.UpdateBy = dt.Rows[0]["Update_By"].GetString();
index.UpdateTime = dt.Rows[0]["Update_Time"].GetDateTime();
return(index);
}
else
{
return(null);
}
}
public void RemoveConnections(Int64 parentQueryId)
{
const string parentQueryIdParamName = "@parentQueryId";
string strSQLClause = null;
if (!this.ReadOnly)
{
using (this.Connection.OpenWrapper())
{
var deleteCommand = new RowDeleteCommand(this.Connection, this.TableDefinition);
strSQLClause = string.Format(
"[{0}] = {1}",
ParentQueryIdFn,
parentQueryIdParamName
);
var parentQueryIdParam = new SQLiteParameter(parentQueryIdParamName, DbType.Int64)
{
Value = parentQueryId
};
var parameters = new List<SQLiteParameter> { parentQueryIdParam };
deleteCommand.SetCommandConstraints(strSQLClause, parameters);
deleteCommand.Execute(100);
}
}
}
/// <summary>
/// Connects to a SQLite3-Database
/// </summary>
/// <param name="path">Path of the Database</param>
public Database(string path)
{
connection = new SQLiteConnection();
connection.ConnectionString = "Data Source=" + path + ";Version=3;New=True;Compress=True";
connection.Open();
// Create required databases
createTable("pictures", "path VARCHAR(255) PRIMARY KEY NOT NULL, up INT NOT NULL DEFAULT 0, down INT NOT NULL DEFAULT 0");
// Prepare prepared statements, duh.
qRandomPicture = new SQLiteCommand(connection);
qRandomPicture.CommandText = "SELECT path FROM pictures WHERE (up - down) > (SELECT AVG(up-down)-2 FROM pictures) ORDER BY RANDOM() LIMIT 1";
qInsertPicture = new SQLiteCommand(connection);
qInsertPicture.CommandText = "INSERT OR IGNORE INTO pictures (path) VALUES (@path)";
qInsertPath = qInsertPicture.CreateParameter();
qInsertPath.ParameterName = "@path";
qInsertPicture.Parameters.Add(qInsertPath);
qVoteUp = new SQLiteCommand(connection);
qVoteUp.CommandText = "UPDATE pictures SET up = up + 1 WHERE path = @path";
qVoteUpPath = qVoteUp.CreateParameter();
qVoteUpPath.ParameterName = "@path";
qVoteUp.Parameters.Add(qVoteUpPath);
qVoteDown = new SQLiteCommand(connection);
qVoteDown.CommandText = "UPDATE pictures SET down = down + 1 WHERE path = @path";
qVoteDownPath = qVoteDown.CreateParameter();
qVoteDownPath.ParameterName = "@path";
qVoteDown.Parameters.Add(qVoteDownPath);
}
public Module Select(string ModuleID)
{
String sql = "select * from Module where Module_ID=@ModuleID";
System.Data.SQLite.SQLiteParameter[] parm = new System.Data.SQLite.SQLiteParameter[] {
new SQLiteParameter("@ModuleID", ModuleID),
};
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text, parm);
if (dt.Rows.Count > 0)
{
Module index = new Module();
index.ModuleId = dt.Rows[0]["Module_ID"].ToString();
index.ModuleName = dt.Rows[0]["Module_Name"].ToString();
index.ModuleUrl = dt.Rows[0]["Module_URL"].ToString();
index.IconUrl = dt.Rows[0]["Icon_Url"].ToString();
index.ParentId = dt.Rows[0]["Parent_Id"].ToString();
index.OrderId = dt.Rows[0]["Order_Id"].GetDecimal();
index.Notes = dt.Rows[0]["Notes"].ToString();
index.Status = dt.Rows[0]["STATUS"].GetDecimal();
return(index);
}
else
{
return(null);
}
}
public static List<Object[]> SelectArray( IDbTransaction transaction, string statement, IEnumerable<object> parameters )
{
List<Object[]> rows = null;
using ( IDbCommand Command = new SQLiteCommand() ) {
Command.Connection = transaction.Connection;
Command.CommandText = statement;
foreach ( object p in parameters ) {
SQLiteParameter sqp = new SQLiteParameter();
sqp.Value = p;
Command.Parameters.Add( sqp );
}
IDataReader rd = Command.ExecuteReader();
rows = new List<object[]>();
if ( rd.Read() ) {
Object[] fields = new Object[rd.FieldCount];
do {
for ( int i = 0; i < rd.FieldCount; ++i ) {
try {
fields[i] = rd.GetValue( i );
} catch ( OverflowException ) {
// workaround, GetValue() and GetValues() try to incorrectly cast a signed tinyint to an unsigned byte
fields[i] = rd.GetInt32( i );
}
}
rows.Add( fields );
fields = new Object[rd.FieldCount];
} while ( rd.Read() );
}
}
return rows;
}
public int AddRili(Rili rili)
{
int result = 0;
try
{
string sql = "INSERT INTO t_rili(Yangli,Nongli,Zhigan,Xingzuo,Xingqi,Yi,Ji)values(@Yangli,@Nongli,@Zhigan,@Xingzuo,@Xingqi,@Yi,@Ji)";
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@Yangli",rili.Yangli),
new SQLiteParameter("@Nongli",rili.Nongli),
new SQLiteParameter("@Zhigan",rili.Zhigan),
new SQLiteParameter("@Xingzuo",rili.Xingzuo),
new SQLiteParameter("@Xingqi",rili.Xingqi),
new SQLiteParameter("@Yi",rili.Yi),
new SQLiteParameter("@Ji",rili.Ji)
};
SqlAction action = new SqlAction();
result = action.IntQuery(sql, parameters);
}
catch (Exception ex)
{
Log.Error("插入日历数据出错：" + ex.ToString());
}
return result;
}
public static bool Login(int accountId, string password)
{
using (SQLiteConnection con = new SQLiteConnection(Settings.Default.AccountingConnectionString))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = con;
cmd.CommandText = "SELECT COUNT(accountId) FROM vAccountPassword WHERE accountId = @account AND password = @password";
SQLiteParameter pAccount = new SQLiteParameter("account",accountId);
pAccount.Direction = ParameterDirection.Input;
SQLiteParameter pPassword = new SQLiteParameter("password", password);
pPassword.Direction = ParameterDirection.Input;
cmd.Parameters.AddRange(new SQLiteParameter[] { pAccount, pPassword });
con.Open();
if (Convert.ToInt32(cmd.ExecuteScalar()) == 1)
{
con.Close();
return true;
}
con.Close();
return false;
}
}
}
public void AddDirectoriesRecursively(string folder, int? p_id)
{
DirectoryInfo di = new DirectoryInfo(folder);
using (SQLiteCommand command = new SQLiteCommand(ConnectionManager.connection))
{
command.CommandText = @"Insert into [folders] (p_id,name) values (@p_id,@name); SELECT last_insert_rowid() AS [ID]";
command.Parameters.Add(new SQLiteParameter("@p_id", p_id == null ? "null" : p_id.ToString()));
command.Parameters.Add(new SQLiteParameter("@name", di.Name));
p_id = int.Parse(command.ExecuteScalar().ToString());
}
using (SQLiteCommand command = new SQLiteCommand(ConnectionManager.connection))
{
command.CommandText = @"Insert into [files] (p_id,name) values (@p_id,@name)";
SQLiteParameter _name = new SQLiteParameter("@name");
command.Parameters.Add(_name);
command.Parameters.Add(new SQLiteParameter("@p_id", p_id));
foreach (FileInfo cfi in di.GetFiles())
{
_name.Value = cfi.Name;
command.ExecuteNonQuery();
}
}
foreach (DirectoryInfo cdi in di.GetDirectories())
AddDirectoriesRecursively(cdi.FullName, p_id);
}
/// <summary>
/// 添加登录记录
/// </summary>
/// <param name="userid"></param>
/// <returns></returns>
public static bool AddLoginHistory(Int64 userid)
{
string sqlStr = "INSERT INTO LoginHistory(UserID,LoginTime) Values(@USERID,datetime('now','localtime'));";
SQLiteParameter parameter = new SQLiteParameter("@USERID", System.Data.DbType.Int64);
parameter.Value = userid;
return ExecuteNonQuery(sqlStr, parameter) > 0;
}
// add users to the database
public bool addUser(string username, string password)
{
SQLiteCommand dbcmd = dbcon.CreateCommand();
string Sql = "INSERT INTO users VALUES (?,?); INSERT INTO scores VALUES (?,?);";
dbcmd.CommandText = Sql;
SQLiteParameter param1 = new SQLiteParameter();
SQLiteParameter param2 = new SQLiteParameter();
SQLiteParameter param3 = new SQLiteParameter();
SQLiteParameter param4 = new SQLiteParameter();
param1.Value = username;
param2.Value = password;
param3.Value = username;
param4.Value = 0;
dbcmd.Parameters.Add(param1);
dbcmd.Parameters.Add(param2);
dbcmd.Parameters.Add(param3);
dbcmd.Parameters.Add(param4);
SQLiteDataReader reader = dbcmd.ExecuteReader();
reader.Close();
reader = null;
// dispose of database commands
dbcmd.Dispose();
dbcmd = null;
// should only be called if conditions are right
// for adding users; always return true
return true;
}
public void insertCity()
{
try
{
//DataDao.sqlite_cmd = sqlite_conn.CreateCommand();
DataDao.sqlite_cmd.CommandText = "insert into city(city_id,city_name,state) values (@city_id,@city_name,@state)";
p1 = new SQLiteParameter("@city_id", DbType.Int32);
p2 = new SQLiteParameter("@city_name", DbType.String);
p3 = new SQLiteParameter("@state", DbType.String);
DataDao.sqlite_cmd.Parameters.Add(p1);
DataDao.sqlite_cmd.Parameters.Add(p2);
DataDao.sqlite_cmd.Parameters.Add(p3);
for (int m = 1; m <= 3; m++)
{
// for (int n = m; n <= m-1; n++)
// {
p1.Value = m;
p2.Value = city_name[m-1];
p3.Value = state[0];
DataDao.sqlite_cmd.ExecuteNonQuery();
// }
}
}
catch (Exception ex)
{
Console.WriteLine("SQLite Exception : {0}", ex.Message);
}
}
private static void SearchForBook(string input)
{
db.Open();
using (db)
{
string sqlStr = "SELECT AuthorName, titleBook, publishDate, ISBN FROM books " +
"JOIN authors " +
"ON authors.Books_idBooks = books.idBooks " +
"WHERE titleBook LIKE @input";
SQLiteParameter cmdParam = new SQLiteParameter("@input", "%" + input + "%");
SQLiteCommand cmd = new SQLiteCommand(sqlStr, db.ConnectionHandle);
cmd.Parameters.Add(cmdParam);
SQLiteDataReader reader = cmd.ExecuteReader();
using (reader)
{
while (reader.Read())
{
string author = (string)reader["AuthorName"];
string title = (string)reader["titleBook"];
DateTime date = (DateTime)reader["publishDate"];
long isbn = (long)reader["ISBN"];
Console.WriteLine("{0}: {1} {2} {3}", author, title, date, isbn);
}
}
}
}
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());
}
/// <summary>
/// 执行一个查询语句，返回一个包含查询结果的DataTable
/// </summary>
/// <param name="sql">要执行的查询语句</param>
/// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param>
/// <returns></returns>
public int ExecuteDataTable(StringBuilder sql, SQLiteParameter[] parameters, ref DataTable dt)
{
try
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(sql.ToString(), connection))
{
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
adapter.Fill(dt);
return 0;
}
}
}
catch (Exception ex)
{
return -1;
throw new Exception(ex.Message);
}
}
/// <summary>
/// 根据编号查询文章表
/// </summary>
/// <param name="Id">编号</param>
/// <returns>数据集合</returns>
public Article Select(string Id)
{
String sql = "select * from Article where Id=@Id";
System.Data.SQLite.SQLiteParameter[] parm = new System.Data.SQLite.SQLiteParameter[] {
new SQLiteParameter("@Id", Id),
};
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text, parm);
if (dt.Rows.Count > 0)
{
Article index = new Article();
index.Id = dt.Rows[0]["Id"].GetString();
index.Title = dt.Rows[0]["Title"].GetString();
index.Content = dt.Rows[0]["Content"].GetString();
index.CateId = dt.Rows[0]["Cate_Id"].GetString();
index.CreateBy = dt.Rows[0]["Create_By"].GetString();
index.CreateTime = dt.Rows[0]["Create_Time"].GetDateTime();
index.UpdateBy = dt.Rows[0]["UpDate_By"].GetString();
index.UpdateTime = dt.Rows[0]["UpDate_Time"].GetDateTime();
return(index);
}
else
{
return(null);
}
}
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 static List<object[]> GenericSqliteSelect( SQLiteConnection Connection, string statement, IEnumerable<object> parameters )
{
List<object[]> retval = null;
using ( SQLiteTransaction Transaction = Connection.BeginTransaction() )
using ( SQLiteCommand Command = new SQLiteCommand( Connection ) ) {
Command.CommandText = statement;
foreach ( object p in parameters ) {
SQLiteParameter sqp = new SQLiteParameter();
sqp.Value = p;
Command.Parameters.Add( sqp );
}
SQLiteDataReader rd = Command.ExecuteReader();
List<object[]> objs = new List<object[]>();
while ( rd.Read() ) {
object[] obja = new object[rd.FieldCount];
for ( int i = 0; i < rd.FieldCount; ++i ) {
obja[i] = rd.GetValue( i );
}
objs.Add( obja );
}
retval = objs;
Transaction.Commit();
}
return retval;
}
public void SzybkieInserty(string tableName, string parameters, string[,] fields)
{
using (SQLiteTransaction dbTrans = conn.BeginTransaction())
{
using (SQLiteCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "insert into " + tableName + " values(" + parameters + ");";
SQLiteParameter[] sqlparams = new SQLiteParameter[fields.GetLength(1)];
for (int i = 0; i < fields.GetLength(1); i++)
{
sqlparams[i] = cmd.CreateParameter();
cmd.Parameters.Add(sqlparams[i]);
}
for (int i = 0; i < fields.GetLength(0); i++)
{
for (int j = 0; j < fields.GetLength(1); j++)
{
sqlparams[j].Value = fields[i, j];
}
cmd.ExecuteNonQuery();
}
dbTrans.Commit();
}
}
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
public static object ExecuteSqlGet(string SQLString, string content)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
SQLiteCommand cmd = new SQLiteCommand(SQLString, connection);
System.Data.SQLite.SQLiteParameter myParameter = new System.Data.SQLite.SQLiteParameter("@content", SqlDbType.NText);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return(null);
}
else
{
return(obj);
}
}
catch (System.Data.SQLite.SQLiteException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
SQLiteCommand cmd = new SQLiteCommand(strSQL, connection);
System.Data.SQLite.SQLiteParameter myParameter = new System.Data.SQLite.SQLiteParameter("@fs", SqlDbType.Image);
myParameter.Value = fs;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return(rows);
}
catch (System.Data.SQLite.SQLiteException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString, string content)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
SQLiteCommand cmd = new SQLiteCommand(SQLString, connection);
System.Data.SQLite.SQLiteParameter myParameter = new System.Data.SQLite.SQLiteParameter("@content", SqlDbType.NText);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return(rows);
}
catch (System.Data.SQLite.SQLiteException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
public bool AddFile(string fileName, string description, string mapName, string featureSource, long uid)
{
bool result = false;
SQLiteTransaction trans = this._conn.BeginTransaction();
try
{
using (SQLiteCommand cmd = this._conn.CreateCommand())
{
cmd.CommandText = "INSERT INTO tblFiles(FeatureSourceId, FeatureId, MapName, FileName, Description) VALUES(@FeatureSourceId, @FeatureId, @MapName, @FileName, @Description)";
SQLiteParameter featSourceIdParam = new SQLiteParameter("@FeatureSourceId", this.GetFeatureSourceId(featureSource, true));
SQLiteParameter featIdParam = new SQLiteParameter("@FeatureId", uid);
SQLiteParameter mapNameParam = new SQLiteParameter("@MapName", mapName);
SQLiteParameter fileNameParam = new SQLiteParameter("@FileName", fileName);
SQLiteParameter descriptionParam = new SQLiteParameter("@Description", description);
cmd.Parameters.AddRange(new SQLiteParameter[] { featSourceIdParam, featIdParam, mapNameParam, fileNameParam, descriptionParam });
cmd.ExecuteNonQuery();
}
trans.Commit();
result = true;
}
catch (SQLiteException)
{
trans.Rollback();
}
finally
{
if (trans != null)
{
trans.Dispose();
}
}
return result;
}
public void SetValue(string key, string value)
{
string ExistRecordSql = "SELECT count(1) FROM profiles WHERE Key = '{0}'";
int record = Convert.ToInt32(dbHelper.ExecuteScalar(string.Format(ExistRecordSql, key), null));
if (record > 0)
{
string UpdSql = @"UPDATE profiles SET Value =@Value where Key = @Key ";
SQLiteParameter[] parameter = new SQLiteParameter[]
{
new SQLiteParameter("@Key", key),
new SQLiteParameter("@Value",value)
};
dbHelper.ExecuteNonQuery(UpdSql, parameter);
}
else
{
string InsSql = @"INSERT INTO profiles(Key, Value) values(@Key,@Value)";
SQLiteParameter[] parameter = new SQLiteParameter[]
{
new SQLiteParameter("@Key", key),
new SQLiteParameter("@Value",value)
};
dbHelper.ExecuteNonQuery(InsSql, parameter);
}
}
/// <summary>
/// 对SQLite数据库执行Insert操作，并返回rowID。
/// </summary>
/// <param name="sql">要执行的Insert SQL语句</param>
/// <param name="parameters">执行Insert语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param>
/// <returns>RowID</returns>
public static int ExcuteInsertReturnRowID(string sql, SQLiteParameter[] parameters = null)
{
int rowID = -1;
int affectedRows;
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
{
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = sql;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
affectedRows = command.ExecuteNonQuery();
}
transaction.Commit();
}
if (affectedRows == 0)
{
return rowID;
}
string getRowIDSql = "select last_insert_rowid()";
using (SQLiteCommand getRowIDCmd = new SQLiteCommand(getRowIDSql, connection))
{
SQLiteDataAdapter adapter = new SQLiteDataAdapter(getRowIDCmd);
DataTable data = new DataTable();
adapter.Fill(data);
rowID = Convert.ToInt32(data.Rows[0][0]);
}
}
return rowID;
}
public static int ExecuteNonQuery(string sql, SQLiteParameter[] parameters = null, string connectionString = "")
{
if (string.IsNullOrEmpty(connectionString))
connectionString = ConnectionString;
int affectedRows;
using (var connection = new SQLiteConnection(connectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
using (var command = new SQLiteCommand(connection))
{
command.CommandText = sql;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
affectedRows = command.ExecuteNonQuery();
}
transaction.Commit();
}
}
return affectedRows;
}
public Users Login(string loginId, string loginPwd)
{
String sql = "select * from Users where Login_Id=@loginId and login_Pwd=@loginPwd";
System.Data.SQLite.SQLiteParameter[] parm = new System.Data.SQLite.SQLiteParameter[] {
new SQLiteParameter("@loginId", loginId),
new SQLiteParameter("@loginPwd", loginPwd),
};
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text, parm);
if (dt.Rows.Count > 0)
{
Users index = new Users();
index.Id = dt.Rows[0]["Id"].ToString();
index.LoginId = dt.Rows[0]["Login_Id"].ToString();
index.LoginPwd = dt.Rows[0]["Login_Pwd"].ToString();
index.Name = dt.Rows[0]["Name"].ToString();
index.Phone = dt.Rows[0]["Phone"].ToString();
index.Mail = dt.Rows[0]["Mail"].ToString();
index.Address = dt.Rows[0]["Address"].ToString();
index.Age = dt.Rows[0]["Age"].GetInt32();
index.Notes = dt.Rows[0]["Notes"].ToString();
return(index);
}
else
{
return(null);
}
}
/// <summary>
/// 对SQLite数据库执行增删改操作，返回受影响的行数。
/// </summary>
/// <param name="sql">要执行的增删改的SQL语句</param>
/// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param>
/// <returns></returns>
public int ExecuteNonQuery(string sql, SQLiteParameter[] parameters)
{
int affectedRows = 0;
try
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
{
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = sql;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
affectedRows = command.ExecuteNonQuery();
}
transaction.Commit();
}
}
}
catch
{ }
return affectedRows;
}
public List<UserEntity> GetUsers(int skip, int pagesize, string userName, out int count)
{
StringBuilder sql = new StringBuilder();
sql.Append(" select Uid,Name,Photo,RolesIds,Status from t_users ");
if (!string.IsNullOrEmpty(userName))
{
sql.Append(" where Uid like @Uid ");
}
if (pagesize != 0)
{
sql.Append(" Limit @count Offset @skip ");
}
SQLiteParameter[] para = new SQLiteParameter[] {
new SQLiteParameter("@count", pagesize),
new SQLiteParameter("@skip", skip),
new SQLiteParameter("@Uid", "%"+userName+"%")
};
SqlLiteHelper sqliteHelper = new SqlLiteHelper();
DataTable dataTable = sqliteHelper.GetDataTable(sql.ToString(), para);
List<UserEntity> userEntities = new List<UserEntity>();
foreach (DataRow dataRow in dataTable.Rows)
{
userEntities.Add(Conver2Entity(dataRow));
}
count = sqliteHelper.GetCount("t_users");
return userEntities;
}
internal static void PrepareCommand(SQLiteCommand command, SQLiteConnection connection, SQLiteTransaction transaction,
CommandType commandType, string commandText, SQLiteParameter[] commandParameters,
out bool mustCloseConnection)
{
if (command == null) throw new ArgumentNullException("command");
if (string.IsNullOrEmpty(commandText)) throw new ArgumentNullException("commandText");
if (connection.State == ConnectionState.Open)
mustCloseConnection = false;
else
{
mustCloseConnection = true;
connection.Open();
}
command.Connection = connection;
command.CommandText = commandText;
if (transaction != null)
{
if (transaction.Connection == null)
throw new ArgumentException(
"The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
command.Transaction = transaction;
}
command.CommandType = commandType;
if (commandParameters != null)
AttachParameters(command, commandParameters);
return;
}
public override DbParameter CreateParameter(string name, Type type)
{
DbParameter p = new SQLiteParameter();
p.DbType = GetDbType(type);
p.ParameterName = name;
return p;
}
public void data()
{
p1 = new SQLiteParameter("@person_id", DbType.Int32);
p2 = new SQLiteParameter("@day", DbType.Int32);
p3 = new SQLiteParameter("@month_id", DbType.Int32);
p4 = new SQLiteParameter("@usage", DbType.Int32);
// SQLiteParameter p4 = new SQLiteParameter("@w_usage", DbType.Int32);
DataDao.sqlite_cmd.Parameters.Add(p1);
DataDao.sqlite_cmd.Parameters.Add(p2);
DataDao.sqlite_cmd.Parameters.Add(p3);
DataDao.sqlite_cmd.Parameters.Add(p4);
//for (person_id = 1; person_id <= 10000; person_id++)
for (person_id = 1; person_id <= 10; person_id++)
{
// for (month_id = 1; month_id <= 24; month_id++)
for (month_id = 1; month_id <= 12; month_id++)
{
for (day = 1; day <= 30; day++)
// for (day = 1; day <= 10; day++)
{
int usage = rnd.Next(1, 12);
p1.Value = person_id;
p2.Value = day;
p3.Value = month_id;
p4.Value = usage;
DataDao.sqlite_cmd.ExecuteNonQuery();
}
}
}
}
/// <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);
}
}
static void Main(string[] args)
{
// On crée nos variables:
SQLiteConnection connection = new SQLiteConnection();
// Disponible sur le site http://www.connectionstrings.com
// "Data Source=filename;Version=3;"
connection.ConnectionString = "Data Source=test.db;Version=3";
// Il serait bien sûr possible d'en faire plus, par exemple, mot de passe, mais on va s'en tenir à ça.
// Ouvre la connexion à la base de donnée
connection.Open();
// À présent, il faut se rappeler le SQL que nous avons appris pendant les cours de base de donneés:
// Créons donc nos tables:
SQLiteCommand command = new SQLiteCommand("DROP TABLE IF EXISTS Étudiants; CREATE TABLE Étudiants(" +
" ID integer Primary Key, " +
" Nom string, " +
" Prenom string, " +
" DateDeNaissance date, " +
" Ville string, " +
" Technique string, " +
" NuméroTéléphone string); ");
command.Connection = connection;
command.ExecuteNonQuery();
// Il est possible de faire nos opérations en plus qu'une étape:
SQLiteCommand command2 = new SQLiteCommand("INSERT INTO Étudiants VALUES ( @ID, @Nom, @Prenom, @DateNaissance, @Ville, @Technique,@No);", connection);
command2.Parameters.AddWithValue("@ID", null);
command2.Parameters.AddWithValue("@Nom", "Di Croci");
SQLiteParameter param3 = new SQLiteParameter("@Prenom");
param3.Value = "Michel";
command2.Parameters.Add(param3);
command2.Parameters.AddWithValue("@DateNaissance", "13/10/1979");
command2.Parameters.AddWithValue("@Ville", "L'Assomption");
command2.Parameters.AddWithValue("@Technique", "Informatique");
command2.Parameters.AddWithValue("@No", "haha!");
command2.ExecuteNonQuery();
// Comme vous le constatez, on ne sait pas quel numéro d'enregistrement vient d'être entré...
// Dans le cas de l'utilisation de clé étrangère (comme dnas notre TP), il peut devenir pratique d'avoir ce numéro
// dans ce cas, nous devons utiliser select last_insert_rowid
command.CommandText = "SELECT last_insert_rowid() FROM Étudiants";
// Comme vous le savez, SELECT nous retourne un élément contrairement à un INSERT ou un UPDATE OU UN DELETE
// Cela siginifie entre autre que la requête précédente va nous retourner qu'un seul scalaire, sinon nous aurions
// utilisé le SQLiteDataReader que nous verrons ultérieurement
object id = command.ExecuteScalar();
Console.WriteLine(id);
connection.Close();
}
/// <summary>
/// 根据编号查询收入记录表
/// </summary>
/// <param name="Id">编号</param>
/// <returns>数据集合</returns>
public Income Select(string Id)
{
String sql = "select * from Income where Id=@Id";
System.Data.SQLite.SQLiteParameter[] parm = new System.Data.SQLite.SQLiteParameter[] {
new SQLiteParameter("@Id", Id),
};
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text, parm);
if (dt.Rows.Count > 0)
{
Income index = new Income();
index.Id = dt.Rows[0]["Id"].GetString();
index.Time = dt.Rows[0]["TIME"].GetDateTime();
index.Price = dt.Rows[0]["Price"].GetDouble();
index.Note = dt.Rows[0]["Note"].GetString();
index.CreateBy = dt.Rows[0]["Create_By"].GetString();
index.CreateTime = dt.Rows[0]["Create_Time"].GetDateTime();
index.UpdateBy = dt.Rows[0]["UpDate_By"].GetString();
index.UpdateTime = dt.Rows[0]["UpDate_Time"].GetDateTime();
index.FamilyIncome = dt.Rows[0]["FamilyIncome"].GetBoolean();
index.IsMark = dt.Rows[0]["IsMark"].GetBoolean();
return(index);
}
else
{
return(null);
}
}
public long AddPatientBaseInfo(PatientInfo model)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("insert into PatientBaseInfo(");
stringBuilder.Append("PatientAge,PatientName,PatientSex,BeHospitalizedDate,Bed,CourtyardState,LeaveHospitalDate,LeaveHospitalAgent,Diacrisis)");
stringBuilder.Append(" values (");
stringBuilder.Append("@PatientAge,@PatientName,@PatientSex,@BeHospitalizedDate,@Bed,@CourtyardState,@LeaveHospitalDate,@LeaveHospitalAgent,@Diacrisis)");
stringBuilder.Append(";select LAST_INSERT_ROWID()");
SQLiteParameter[] array = new SQLiteParameter[]
{
new SQLiteParameter("@PatientAge", DbType.Int16),
new SQLiteParameter("@PatientName", DbType.String),
new SQLiteParameter("@PatientSex", DbType.String),
new SQLiteParameter("@BeHospitalizedDate", DbType.Date),
new SQLiteParameter("@Bed", DbType.String),
new SQLiteParameter("@CourtyardState", DbType.String),
new SQLiteParameter("@LeaveHospitalDate", DbType.Date),
new SQLiteParameter("@LeaveHospitalAgent", DbType.String),
new SQLiteParameter("@Diacrisis", DbType.String)
};
array[0].Value = model.Age;
array[1].Value = model.Name;
array[2].Value = model.Sex;
array[3].Value = model.BeHospitalizedDate;
array[4].Value = model.Bed;
array[5].Value = model.CourtyardState;
array[6].Value = model.LeaveHospitalDate;
array[7].Value = model.LeaveHospitalAgent;
array[8].Value = model.Diacrisis;
object single = DbHelperSQLite.GetSingle(stringBuilder.ToString(), array);
return Convert.ToInt64(single);
}
/// <summary>
/// 删除消息
/// </summary>
/// <param name="userID">用户ID</param>
public static void DeleteMsg(string userID)
{
try
{
System.Data.SQLite.SQLiteParameter uID = new System.Data.SQLite.SQLiteParameter("userID", userID);
string sql = "delete from MsgRecord where froms=@userID or tos=@userID";
SQLiteDBHelper.ExecuteNonQuery(sql, uID);
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message, ex);
}
}
/// <summary>
/// 删除分组
/// </summary>
public static void DeleteGroup(string groupID)
{
try
{
string sql = "delete from Groups where groupID=@groupID;delete from GroupsVcard where groupID=@groupID";
System.Data.SQLite.SQLiteParameter[] parameters = new System.Data.SQLite.SQLiteParameter[] {
new System.Data.SQLite.SQLiteParameter("@groupID", groupID),
};
SQLiteDBHelper.ExecuteNonQuery(sql, parameters);
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message, ex);
}
}
/// <summary>
/// 更新群Card，无数据则添加
/// </summary>
/// <param name="RoomID"></param>
/// <param name="e"></param>
public static void UpdateRoomVcard(string RoomID, IMLibrary3.Protocol.Element e)
{
//string sql = "delete from RoomsVcard ";
string sql = "update RoomsVcard set Vcard=@Vcard where RoomID=@RoomID";
System.Data.SQLite.SQLiteParameter[] parameters = new System.Data.SQLite.SQLiteParameter[] {
new System.Data.SQLite.SQLiteParameter("@RoomID", RoomID),
new System.Data.SQLite.SQLiteParameter("@Vcard", IMLibrary3.Protocol.Factory.CreateXMLMsg(e))
};
if (SQLiteDBHelper.ExecuteNonQuery(sql, parameters) == 0)
{
sql = "insert into RoomsVcard(RoomID,Vcard) values(@RoomID,@Vcard)";
SQLiteDBHelper.ExecuteNonQuery(sql, parameters);
}
}
/// <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);
}
/// <summary>
/// 更新分组Card，无数据则添加
/// </summary>
/// <param name="GroupID"></param>
/// <param name="e"></param>
public static void UpdateGroupVcard(GroupVcard card)
{
string sql = "update GroupsVcard set Vcard=@Vcard where GroupID=@GroupID";
System.Data.SQLite.SQLiteParameter[] parameters = new System.Data.SQLite.SQLiteParameter[] {
new System.Data.SQLite.SQLiteParameter("@GroupID", card.GroupID),
new System.Data.SQLite.SQLiteParameter("@Vcard", Factory.CreateXMLMsg(card)),
new System.Data.SQLite.SQLiteParameter("@GroupName", card.GroupName),
new System.Data.SQLite.SQLiteParameter("@SuperiorID", card.SuperiorID),
new System.Data.SQLite.SQLiteParameter("@OrderID", card.OrderID),
};
if (SQLiteDBHelper.ExecuteNonQuery(sql, parameters) == 0)
{
sql = "insert into Groups(GroupID,GroupName,SuperiorID,OrderID) values(@GroupID,@GroupName,@SuperiorID,@OrderID);"
+ "insert into GroupsVcard(GroupID,Vcard) values(@GroupID,@Vcard)";
SQLiteDBHelper.ExecuteNonQuery(sql, parameters);
}
}
/// <summary>
/// 根据编号查询银行卡操作记录表
/// </summary>
/// <param name="Id">编号</param>
/// <returns>数据集合</returns>
public BankCard Select(string Id)
{
String sql = "select * from Bank_Card where Id=@Id";
System.Data.SQLite.SQLiteParameter[] parm = new System.Data.SQLite.SQLiteParameter[] {
new SQLiteParameter("@Id", Id),
};
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text, parm);
List <BankCard> list = Life.Model.Common <BankCard> .ConvertToList(dt);
if (list.Count > 0)
{
return(list[0]);
}
else
{
return(null);
}
}
/// <summary>
/// 根据编号查询收入记录表
/// </summary>
/// <param name="Id">编号</param>
/// <returns>数据集合</returns>
public Student Select(string Id)
{
String sql = "select * from Student where Id=@Id";
System.Data.SQLite.SQLiteParameter[] parm = new System.Data.SQLite.SQLiteParameter[] {
new SQLiteParameter("@Id", Id),
};
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text, parm);
if (dt.Rows.Count > 0)
{
List <Student> list = Life.Model.Common <Student> .ConvertToList(dt);
if (list.Count > 0)
{
return(list[0]);
}
}
return(null);
}
/// <summary>
/// 更新用户Card，无数据则添加
/// </summary>
/// <param name="card"></param>
public static void UpdateUserVcard(UserVcard card)
{
string sql = "update Users set UserName=@UserName,GroupID=@GroupID,OrderID=@OrderID where UserID=@UserID;"
+ "update UsersVcard set Vcard=@Vcard,LastUpdateTime=@LastUpdateTime where UserID=@UserID";
System.Data.SQLite.SQLiteParameter[] parameters = new System.Data.SQLite.SQLiteParameter[] {
new System.Data.SQLite.SQLiteParameter("@UserID", card.UserID),
new System.Data.SQLite.SQLiteParameter("@UserName", card.UserName),
new System.Data.SQLite.SQLiteParameter("@GroupID", card.GroupID),
new System.Data.SQLite.SQLiteParameter("@OrderID", card.OrderID),
new System.Data.SQLite.SQLiteParameter("@Vcard", IMLibrary3.Protocol.Factory.CreateXMLMsg(card)),
new System.Data.SQLite.SQLiteParameter("@LastUpdateTime", DateTime.Now),
};
if (SQLiteDBHelper.ExecuteNonQuery(sql, parameters) == 0)
{
sql = "insert into Users(UserID,UserName,GroupID,OrderID) values(@UserID,@UserName,@GroupID,@OrderID);" +
"insert into UsersVcard(UserID,Vcard,LastUpdateTime) values(@UserID,@Vcard,@LastUpdateTime)";
SQLiteDBHelper.ExecuteNonQuery(sql, parameters);
}
}
private void ReLoadDtb()
{
SQLiteHelper.SetConnectionString(System.Environment.CurrentDirectory + "/DBS/MyDicDb.db");
var Param = new System.Data.SQLite.SQLiteParameter[0];
var dt = SQLiteHelper.ExecuteQuery("select * from ListTable", Param);
for (int i = 0; i < dt.Columns.Count; i++)
{
if (dt.Columns[i].ColumnName == "DicKey")
{
dt.Columns[i].ColumnName = "Key";
}
else
{
dt.Columns[i].ColumnName = "Value";
}
}
dgv_All.DataSource = dt;
}
/// <summary>
/// 添加消息
/// </summary>
public static void AddMsg(IMLibrary3.Protocol.Message Msg)
{
string sql = "insert into MsgRecord(froms,tos,MessageType,DateTime,Vcard)" +
"values(@froms,@tos,@MessageType,@DateTime,@Vcard)";
System.Data.SQLite.SQLiteParameter[] parameters = new System.Data.SQLite.SQLiteParameter[] {
new System.Data.SQLite.SQLiteParameter("@froms", Msg.from),
new System.Data.SQLite.SQLiteParameter("@tos", Msg.to),
new System.Data.SQLite.SQLiteParameter("@MessageType", Convert.ToByte(Msg.MessageType)),
new System.Data.SQLite.SQLiteParameter("@DateTime", Msg.DateTime == null?DateTime.Now.ToString():Msg.DateTime),
new System.Data.SQLite.SQLiteParameter("@Vcard", IMLibrary3.Protocol.Factory.CreateXMLMsg(Msg))
};
try
{
SQLiteDBHelper.ExecuteNonQuery(sql, parameters);
}
catch (Exception ex)
{
MessageBox.Show(ex.Source + ex.Message);
}
}
/// <summary>
/// 根据编号查询角色表
/// </summary>
/// <param name="RoleId">编号</param>
/// <returns>数据集合</returns>
public Roles Select(string RoleId)
{
String sql = "select * from Roles where Role_Id=@RoleId";
System.Data.SQLite.SQLiteParameter[] parm = new System.Data.SQLite.SQLiteParameter[] {
new SQLiteParameter("@RoleId", RoleId),
};
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text, parm);
if (dt.Rows.Count > 0)
{
Roles index = new Roles();
index.RoleId = dt.Rows[0]["Role_Id"].ToString();
index.RoleName = dt.Rows[0]["Role_Name"].ToString();
index.Notes = dt.Rows[0]["Notes"].ToString();
return(index);
}
else
{
return(null);
}
}
/// <summary>
/// 根据编号查询角色模块对应表
/// </summary>
/// <param name="Id">编号</param>
/// <returns>数据集合</returns>
public RoleToModule Select(string Id)
{
String sql = "select * from RoleToModule where Id=@Id";
System.Data.SQLite.SQLiteParameter[] parm = new System.Data.SQLite.SQLiteParameter[] {
new SQLiteParameter("@Id", Id),
};
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text, parm);
if (dt.Rows.Count > 0)
{
RoleToModule index = new RoleToModule();
index.Id = dt.Rows[0]["Id"].GetString();
index.RoleId = dt.Rows[0]["Role_Id"].GetString();
index.ModuleId = dt.Rows[0]["Module_Id"].GetString();
return(index);
}
else
{
return(null);
}
}
/// <summary>
/// 创造DbParameter的实例
/// </summary>
public DbParameter CreateDbParameter(string paraName, DbType dbType, int size, object value, ParameterDirection direction)
{
DbParameter para;
para = new System.Data.SQLite.SQLiteParameter();
para.ParameterName = paraName;
if (size != 0)
{
para.Size = size;
}
para.DbType = dbType;
if (value != null)
{
para.Value = value;
}
para.Direction = direction;
return(para);
}
/// <summary>
/// 获得消息集合
/// </summary>
/// <param name="MsgInfoClass">消息类型</param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public static List <IMLibrary3.Protocol.Message> GetMsg(IMLibrary3.Enmu.MessageType MessageType, int pageIndex, int pageSize)
{
List <IMLibrary3.Protocol.Message> Msgs = null;
System.Data.SQLite.SQLiteParameter messageType = new System.Data.SQLite.SQLiteParameter("MessageType", MessageType);
string sql = "select * from MsgRecord where MessageType=@MessageType "
+ " order by ID limit " + pageSize.ToString() + " offset " + ((pageIndex - 1) * pageSize).ToString();
System.Data.SQLite.SQLiteDataReader dr = SQLiteDBHelper.ExecuteReader(sql, messageType);
if (dr != null)
{
Msgs = new List <IMLibrary3.Protocol.Message>();
while (dr.Read())
{
Msgs.Add(GetDrMsg(dr));
}
}
dr.Close(); dr.Dispose();
return(Msgs);
}
/// <summary>
/// 根据编号查询临时信息存储表
/// </summary>
/// <param name="Id">编号</param>
/// <returns>数据集合</returns>
public TempData Select(string Id)
{
String sql = "select * from TempData where Id=@Id";
System.Data.SQLite.SQLiteParameter[] parm = new System.Data.SQLite.SQLiteParameter[] {
new SQLiteParameter("@Id", Id),
};
DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text, parm);
if (dt.Rows.Count > 0)
{
TempData index = new TempData();
index.Id = dt.Rows[0]["Id"].GetString();
index.Email = dt.Rows[0]["Email"].GetString();
index.Expires = dt.Rows[0]["Expires"].GetDateTime();
index.CreateTime = dt.Rows[0]["Create_Time"].GetDateTime();
return(index);
}
else
{
return(null);
}
}
private SQLiteParameter(SQLiteParameter source)
: this(source.ParameterName, (DbType)source._dbType, 0, source.Direction, source.IsNullable, 0, 0, source.SourceColumn, source.SourceVersion, source.Value)
{
_nullMapping = source._nullMapping;
}
/// <summary>
/// 添加或更新一个群信息
/// </summary>
/// <param name="Groups"></param>
public static void UpdateRooms(exRoom room)
{
#region 单次插入数据
string sql = "insert into Rooms(RoomID,RoomName,Users, Notice,CreateUserID) values(@RoomID,@RoomName,@Users,@Notice,@CreateUserID)";
System.Data.SQLite.SQLiteParameter[] parameters = new System.Data.SQLite.SQLiteParameter[] {
new System.Data.SQLite.SQLiteParameter("@RoomID", room.RoomID),
new System.Data.SQLite.SQLiteParameter("@RoomName", room.RoomName),
new System.Data.SQLite.SQLiteParameter("@Users", room.UserIDs),
new System.Data.SQLite.SQLiteParameter("@Notice", room.Notice),
new System.Data.SQLite.SQLiteParameter("@CreateUserID", room.CreateUserID),
};
try
{
SQLiteDBHelper.ExecuteNonQuery(sql, parameters);
}
catch
{
try
{
sql = "update Rooms set RoomName=@RoomName,Users=@Users,Notice=@Notice,CreateUserID=@CreateUserID where RoomID=@RoomID";
SQLiteDBHelper.ExecuteNonQuery(sql, parameters);
}
catch (Exception ex)
{
MessageBox.Show(ex.Source + ex.Message);
}
}
#endregion
#region 一次性事务添加
//SQLiteConnection con = new SQLiteConnection(); //创建连接
//SQLiteCommand cmd = null;
//con.ConnectionString = conStr;
//try
//{
// con.Open();
// using (SQLiteTransaction dbTrans = con.BeginTransaction()) //使用事务
// {
// using (cmd = con.CreateCommand())
// {
// cmd.CommandText = "insert into Groups(GroupID,GroupName,SuperiorID,orderID) values(?,?,?,?)";
// SQLiteParameter Field1 = cmd.CreateParameter(); //添加字段
// SQLiteParameter Field2 = cmd.CreateParameter();
// SQLiteParameter Field3 = cmd.CreateParameter();
// SQLiteParameter Field4 = cmd.CreateParameter();
// cmd.Parameters.Add(Field1);
// cmd.Parameters.Add(Field2);
// cmd.Parameters.Add(Field3);
// cmd.Parameters.Add(Field4);
// foreach (OurMsg.Organization.exGroup group in Groups)
// {
// Field1.Value = group.GroupID; //字符赋值
// Field2.Value = group.GroupName;
// Field3.Value = group.SuperiorID;
// Field4.Value = group.OrderID;
// cmd.ExecuteNonQuery();
// }
// }
// dbTrans.Commit(); //提交事务执行
// }
//}
//catch (Exception ex)
//{
// MessageBox.Show(ex.Source + ex.Message);
//}
//finally
//{
// con.Close();
//}
#endregion
}
/// <summary>
/// Clones a parameter
/// </summary>
/// <returns>A new, unassociated SQLiteParameter</returns>
public object Clone()
{
SQLiteParameter newparam = new SQLiteParameter(this);
return(newparam);
}
