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