public bool SetAdmin(int userId) { lock (singelton) { try { string sql = "update users set admin=:ADMIN where id=:USERID"; this.command = (IDbCommand)this.connection.CreateCommand (); this.command.CommandText = sql; this.command.CommandType = CommandType.Text; SqliteParameter param1 = new SqliteParameter () { ParameterName = ":ADMIN", Value = true, DbType = DbType.Boolean }; SqliteParameter param2 = new SqliteParameter () { ParameterName = ":USERID", Value = userId, DbType = DbType.Int16 }; this.command.Parameters.Add (param1); this.command.Parameters.Add (param2); return (bool)(this.command.ExecuteNonQuery () == 0); } catch(SqliteException e) { return false; } } }
public Bill LoadFavoriteBill(int id) { Bill favBill; using (var connection = new SqliteConnection (connectionString)) { using (var cmd = connection.CreateCommand ()) { connection.Open (); cmd.CommandText = "SELECT * FROM FavoriteBills WHERE id = @id"; var idParam = new SqliteParameter ("@id", id); cmd.Parameters.Add (idParam); using (var reader = cmd.ExecuteReader ()) { reader.Read (); favBill = new Bill { Id = Convert.ToInt32 (reader ["id"]), Title = (string)reader ["title"], ThomasLink = (string)reader ["thomas_link"], Notes = reader["notes"] == DBNull.Value ? "" : (string)reader["notes"] }; } } } return favBill; }
/// <summary> /// Clears all items from the database where their PublishDate is before the date provided. /// </summary> /// <param name="date"></param> public void ClearItemsBeforeDate(DateTime date) { try { using (SqliteConnection connection = new SqliteConnection(ItemsConnectionString)) { connection.Open(); using (SqliteCommand command = new SqliteCommand(connection)) { string sql = @"DELETE FROM items WHERE DATETIME(publishdate) <= DATETIME(@date)"; command.CommandText = sql; SqliteParameter parameter = new SqliteParameter("@date", DbType.String); parameter.Value = date.ToString("yyyy-MM-dd HH:mm:ss"); command.Parameters.Add(parameter); int rows = command.ExecuteNonQuery(); Logger.Info("ClearItemsBeforeDate before {0} cleared {1} rows.", date.ToString("yyyy-MM-dd HH:mm:ss"), rows); } } } catch (SqliteException e) { Logger.Warn("SqliteException occured while clearing items before {0}: \n{1}", date, e); } }
public static int Add( Guid guid, Guid siteGuid, string folderName) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("INSERT INTO mp_SiteFolders ("); sqlCommand.Append("Guid, "); sqlCommand.Append("SiteGuid, "); sqlCommand.Append("FolderName )"); sqlCommand.Append(" VALUES ("); sqlCommand.Append(":Guid, "); sqlCommand.Append(":SiteGuid, "); sqlCommand.Append(":FolderName );"); SqliteParameter[] arParams = new SqliteParameter[3]; arParams[0] = new SqliteParameter(":Guid", DbType.String, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = guid.ToString(); arParams[1] = new SqliteParameter(":SiteGuid", DbType.String, 36); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = siteGuid.ToString(); arParams[2] = new SqliteParameter(":FolderName", DbType.String, 255); arParams[2].Direction = ParameterDirection.Input; arParams[2].Value = folderName; int rowsAffected = 0; rowsAffected = SqliteHelper.ExecuteNonQuery(GetConnectionString(), sqlCommand.ToString(), arParams); return rowsAffected; }
public static bool Delete( string loginProvider, string providerKey, string userId) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("DELETE FROM mp_UserLogins "); sqlCommand.Append("WHERE "); sqlCommand.Append("LoginProvider = :LoginProvider AND "); sqlCommand.Append("ProviderKey = :ProviderKey AND "); sqlCommand.Append("UserId = :UserId "); sqlCommand.Append(";"); SqliteParameter[] arParams = new SqliteParameter[3]; arParams[0] = new SqliteParameter(":LoginProvider", DbType.String, 128); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = loginProvider; arParams[1] = new SqliteParameter(":ProviderKey", DbType.String, 128); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = providerKey; arParams[2] = new SqliteParameter(":UserId", DbType.String, 128); arParams[2].Direction = ParameterDirection.Input; arParams[2].Value = userId; int rowsAffected = SqliteHelper.ExecuteNonQuery( ConnectionString.GetConnectionString(), sqlCommand.ToString(), arParams); return (rowsAffected > 0); }
public DataStructure FindByName(string username) { lock (singelton) { try { string sql = "select * from users where username=:USERNAME limit 1"; this.command = (IDbCommand)this.connection.CreateCommand (); this.command.CommandText = sql; SqliteParameter param = new SqliteParameter (); param.ParameterName = ":USERNAME"; param.Value = username; param.DbType = DbType.String; this.command.Parameters.Add (param); IDataReader dr = (IDataReader)this.command.ExecuteReader (); if (dr.Read ()) { return new DataStructure () { id = int.Parse((string)dr["id"].ToString()), username = dr["username"].ToString(), password = (string)dr["password"].ToString(), admin = bool.Parse(dr["admin"].ToString()) }; } return null; } catch(SqliteException e) { return null; } } }
public static bool AccountClearLockout(Guid userGuid) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("UPDATE mp_Users "); sqlCommand.Append("SET IsLockedOut = 0, "); sqlCommand.Append("FailedPasswordAttemptCount = 0, "); sqlCommand.Append("FailedPwdAnswerAttemptCount = 0 "); sqlCommand.Append("WHERE UserGuid = :UserGuid ;"); SqliteParameter[] arParams = new SqliteParameter[1]; arParams[0] = new SqliteParameter(":UserGuid", DbType.String, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = userGuid.ToString(); int rowsAffected = 0; rowsAffected = SqliteHelper.ExecuteNonQuery( GetConnectionString(), sqlCommand.ToString(), arParams); return (rowsAffected > 0); }
/// <summary> /// Updates the TrackOrder values for the tracks that remain for the PlayerID by incrementing any Tracks that have a TrackOrder value /// greater than the provided trackOrder. /// </summary> /// <param name="playerID">The ID of the Player.</param> /// <param name="trackOrder">The TrackOrder value.</param> /// <returns>The number of rows affected by the update.</returns> public static int AdjustTrackOrdersForDelete(int playerId, int trackOrder) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("UPDATE mp_MediaTrack "); sqlCommand.Append("SET TrackOrder = TrackOrder - 1 "); sqlCommand.Append("WHERE "); sqlCommand.Append("PlayerID = :PlayerID "); sqlCommand.Append("AND TrackOrder > :TrackOrder "); sqlCommand.Append(";"); SqliteParameter[] arParams = new SqliteParameter[2]; arParams[0] = new SqliteParameter(":PlayerID", DbType.Int32); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = playerId; arParams[1] = new SqliteParameter(":TrackOrder", DbType.Int32); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = trackOrder; int rowsAffected = SqliteHelper.ExecuteNonQuery( ConnectionString.GetConnectionString(), sqlCommand.ToString(), arParams); return rowsAffected; }
public static bool AccountLockout(Guid userGuid, DateTime lockoutTime) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("UPDATE mp_Users "); sqlCommand.Append("SET IsLockedOut = 1, "); sqlCommand.Append("LastLockoutDate = :LockoutTime "); sqlCommand.Append("WHERE UserGuid = :UserGuid ;"); SqliteParameter[] arParams = new SqliteParameter[2]; arParams[0] = new SqliteParameter(":UserGuid", DbType.String, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = userGuid.ToString(); arParams[1] = new SqliteParameter(":LockoutTime", DbType.DateTime); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = lockoutTime; int rowsAffected = 0; rowsAffected = SqliteHelper.ExecuteNonQuery( GetConnectionString(), sqlCommand.ToString(), arParams); return (rowsAffected > 0); }
/// <summary> /// Inserts a row in the mp_TaxClass table. Returns rows affected count. /// </summary> /// <param name="guid"> guid </param> /// <param name="siteGuid"> siteGuid </param> /// <param name="title"> title </param> /// <param name="description"> description </param> /// <param name="lastModified"> lastModified </param> /// <param name="created"> created </param> /// <returns>int</returns> public static int Create( Guid guid, Guid siteGuid, string title, string description, DateTime lastModified, DateTime created) { #region Bit Conversion #endregion StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("INSERT INTO mp_TaxClass ("); sqlCommand.Append("Guid, "); sqlCommand.Append("SiteGuid, "); sqlCommand.Append("Title, "); sqlCommand.Append("Description, "); sqlCommand.Append("LastModified, "); sqlCommand.Append("Created )"); sqlCommand.Append(" VALUES ("); sqlCommand.Append(":Guid, "); sqlCommand.Append(":SiteGuid, "); sqlCommand.Append(":Title, "); sqlCommand.Append(":Description, "); sqlCommand.Append(":LastModified, "); sqlCommand.Append(":Created )"); sqlCommand.Append(";"); SqliteParameter[] arParams = new SqliteParameter[6]; arParams[0] = new SqliteParameter(":Guid", DbType.String, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = guid.ToString(); arParams[1] = new SqliteParameter(":SiteGuid", DbType.String, 36); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = siteGuid.ToString(); arParams[2] = new SqliteParameter(":Title", DbType.String, 255); arParams[2].Direction = ParameterDirection.Input; arParams[2].Value = title; arParams[3] = new SqliteParameter(":Description", DbType.Object); arParams[3].Direction = ParameterDirection.Input; arParams[3].Value = description; arParams[4] = new SqliteParameter(":LastModified", DbType.DateTime); arParams[4].Direction = ParameterDirection.Input; arParams[4].Value = lastModified; arParams[5] = new SqliteParameter(":Created", DbType.DateTime); arParams[5].Direction = ParameterDirection.Input; arParams[5].Value = created; int rowsAffected = 0; rowsAffected = SqliteHelper.ExecuteNonQuery(GetConnectionString(), sqlCommand.ToString(), arParams); return rowsAffected; }
public static bool DeleteByModule(int moduleId) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("DELETE FROM mp_MediaFile "); sqlCommand.Append("WHERE "); sqlCommand.Append("FileID "); sqlCommand.Append("IN ("); sqlCommand.Append("SELECT FileID FROM mp_MediaFile WHERE TrackID IN ("); sqlCommand.Append("SELECT TrackID FROM mp_MediaTrack WHERE PlayerID IN ("); sqlCommand.Append("SELECT PlayerID FROM mp_MediaPlayer WHERE ModuleID = :ModuleID"); sqlCommand.Append(")"); sqlCommand.Append(")"); sqlCommand.Append(")"); sqlCommand.Append(";"); SqliteParameter[] arParams = new SqliteParameter[1]; arParams[0] = new SqliteParameter(":ModuleID", DbType.Int32); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = moduleId; int rowsAffected = SqliteHelper.ExecuteNonQuery( ConnectionString.GetConnectionString(), sqlCommand.ToString(), arParams); return (rowsAffected > 0); }
public override IDataParameter CreateParameter(string name, DbType dbType, object value) { SqliteParameter p = new SqliteParameter(name); p.DbType = dbType; p.Value = value; return p; }
private static SqliteParameter CreateParameter(string paramName, DbType paramType, object paramValue) { var param = new SqliteParameter(); param.DbType = paramType; param.ParameterName = paramName; param.Value = paramValue; return param; }
/// <summary> /// Inserts a row in the mp_Surveys table. Returns rows affected count. /// </summary> /// <param name="surveyGuid"> surveyGuid </param> /// <param name="siteGuid"> siteGuid </param> /// <param name="surveyName"> surveyName </param> /// <param name="creationDate"> creationDate </param> /// <param name="startPageText"> startPageText </param> /// <param name="endPageText"> endPageText </param> /// <returns>int</returns> public static int Add( Guid surveyGuid, Guid siteGuid, string surveyName, DateTime creationDate, string startPageText, string endPageText) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("INSERT INTO mp_Surveys ("); sqlCommand.Append("SurveyGuid, "); sqlCommand.Append("SiteGuid, "); sqlCommand.Append("SurveyName, "); sqlCommand.Append("CreationDate, "); sqlCommand.Append("StartPageText, "); sqlCommand.Append("EndPageText )"); sqlCommand.Append(" VALUES ("); sqlCommand.Append(":SurveyGuid, "); sqlCommand.Append(":SiteGuid, "); sqlCommand.Append(":SurveyName, "); sqlCommand.Append(":CreationDate, "); sqlCommand.Append(":StartPageText, "); sqlCommand.Append(":EndPageText )"); sqlCommand.Append(";"); SqliteParameter[] arParams = new SqliteParameter[6]; arParams[0] = new SqliteParameter(":SurveyGuid", DbType.String, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = surveyGuid.ToString(); arParams[1] = new SqliteParameter(":SiteGuid", DbType.String, 36); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = siteGuid.ToString(); arParams[2] = new SqliteParameter(":SurveyName", DbType.String, 255); arParams[2].Direction = ParameterDirection.Input; arParams[2].Value = surveyName; arParams[3] = new SqliteParameter(":CreationDate", DbType.DateTime); arParams[3].Direction = ParameterDirection.Input; arParams[3].Value = creationDate; arParams[4] = new SqliteParameter(":StartPageText", DbType.Object); arParams[4].Direction = ParameterDirection.Input; arParams[4].Value = startPageText; arParams[5] = new SqliteParameter(":EndPageText", DbType.Object); arParams[5].Direction = ParameterDirection.Input; arParams[5].Value = endPageText; int rowsAffected = 0; rowsAffected = SqliteHelper.ExecuteNonQuery(GetConnectionString(), sqlCommand.ToString(), arParams); return rowsAffected; }
public static int CountUsersNotSubscribedByLetter(Guid siteGuid, Guid letterInfoGuid, bool excludeIfAnyUnsubscribeHx) { int intExcludeIfAnyUnsubscribeHx = 0; if (excludeIfAnyUnsubscribeHx) { intExcludeIfAnyUnsubscribeHx = 1; } StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT COUNT(*) "); sqlCommand.Append("FROM mp_Users u "); sqlCommand.Append("WHERE "); sqlCommand.Append("u.SiteGuid = :SiteGuid "); sqlCommand.Append("AND u.IsDeleted = 0 "); sqlCommand.Append("AND u.ProfileApproved = 1 "); sqlCommand.Append("AND u.IsLockedOut = 0 "); sqlCommand.Append("AND (u.RegisterConfirmGuid IS NULL OR u.RegisterConfirmGuid = '00000000-0000-0000-0000-000000000000') "); sqlCommand.Append("AND u.UserGuid NOT IN "); sqlCommand.Append("(SELECT ls.UserGuid "); sqlCommand.Append("FROM mp_LetterSubscribe ls "); sqlCommand.Append("WHERE ls.LetterInfoGuid = :LetterInfoGuid "); sqlCommand.Append(") "); sqlCommand.Append("AND u.UserGuid NOT IN "); sqlCommand.Append("(SELECT lsx.UserGuid "); sqlCommand.Append("FROM mp_LetterSubscribeHx lsx "); sqlCommand.Append("WHERE ((:ExcludeIfAnyUnsubscribeHx = 1) OR (lsx.LetterInfoGuid = :LetterInfoGuid)) "); sqlCommand.Append(") "); sqlCommand.Append(";"); SqliteParameter[] arParams = new SqliteParameter[3]; arParams[0] = new SqliteParameter(":SiteGuid", DbType.String, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = siteGuid.ToString(); arParams[1] = new SqliteParameter(":LetterInfoGuid", DbType.String, 36); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = letterInfoGuid.ToString(); arParams[2] = new SqliteParameter(":ExcludeIfAnyUnsubscribeHx", DbType.Int32); arParams[2].Direction = ParameterDirection.Input; arParams[2].Value = intExcludeIfAnyUnsubscribeHx; int count = Convert.ToInt32(SqliteHelper.ExecuteScalar( GetConnectionString(), sqlCommand.ToString(), arParams)); return count; }
public static void AddParameters(SqliteCommand cmd, Object obj, Dictionary<string, DbType> lookup) { foreach (PropertyInfo prop in obj.GetType ().GetProperties ()) { SqliteParameter param = new SqliteParameter (); param.ParameterName = "@" + prop.Name; param.Value = prop.GetValue (obj, null); param.DbType = lookup [prop.Name]; cmd.Parameters.Add (param); } }
/// <summary> /// Inserts a row in the mp_SurveyPages table. Returns rows affected count. /// </summary> /// <param name="pageGuid"> pageGuid </param> /// <param name="surveyGuid"> surveyGuid </param> /// <param name="pageTitle"> pageTitle </param> /// <param name="pageOrder"> pageOrder </param> /// <param name="pageEnabled"> pageEnabled </param> /// <returns>int</returns> public static int Add( Guid pageGuid, Guid surveyGuid, string pageTitle, bool pageEnabled) { #region Bit Conversion int intPageEnabled; if (pageEnabled) { intPageEnabled = 1; } else { intPageEnabled = 0; } #endregion StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("INSERT INTO mp_SurveyPages ("); sqlCommand.Append("PageGuid, "); sqlCommand.Append("SurveyGuid, "); sqlCommand.Append("PageTitle, "); sqlCommand.Append("PageOrder, "); sqlCommand.Append("PageEnabled )"); sqlCommand.Append("SELECT :PageGuid, :SurveyGuid, :PageTitle, "); sqlCommand.Append("Count(*), :PageEnabled FROM mp_SurveyPages; "); SqliteParameter[] arParams = new SqliteParameter[4]; arParams[0] = new SqliteParameter(":PageGuid", DbType.String, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = pageGuid.ToString(); arParams[1] = new SqliteParameter(":SurveyGuid", DbType.String, 36); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = surveyGuid.ToString(); arParams[2] = new SqliteParameter(":PageTitle", DbType.String, 255); arParams[2].Direction = ParameterDirection.Input; arParams[2].Value = pageTitle; arParams[3] = new SqliteParameter(":PageEnabled", DbType.Int32); arParams[3].Direction = ParameterDirection.Input; arParams[3].Value = intPageEnabled; int rowsAffected = 0; rowsAffected = SqliteHelper.ExecuteNonQuery(GetConnectionString(), sqlCommand.ToString(), arParams); return rowsAffected; }
/// <summary> /// Inserts a row in the mp_LetterHtmlTemplate table. Returns rows affected count. /// </summary> /// <param name="guid"> guid </param> /// <param name="siteGuid"> siteGuid </param> /// <param name="title"> title </param> /// <param name="html"> html </param> /// <param name="lastModUTC"> lastModUTC </param> /// <returns>int</returns> public static int Create( Guid guid, Guid siteGuid, string title, string html, DateTime lastModUTC) { #region Bit Conversion #endregion StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("INSERT INTO mp_LetterHtmlTemplate ("); sqlCommand.Append("Guid, "); sqlCommand.Append("SiteGuid, "); sqlCommand.Append("Title, "); sqlCommand.Append("Html, "); sqlCommand.Append("LastModUTC )"); sqlCommand.Append(" VALUES ("); sqlCommand.Append(":Guid, "); sqlCommand.Append(":SiteGuid, "); sqlCommand.Append(":Title, "); sqlCommand.Append(":Html, "); sqlCommand.Append(":LastModUTC );"); SqliteParameter[] arParams = new SqliteParameter[5]; arParams[0] = new SqliteParameter(":Guid", DbType.String, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = guid.ToString(); arParams[1] = new SqliteParameter(":SiteGuid", DbType.String, 36); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = siteGuid.ToString(); arParams[2] = new SqliteParameter(":Title", DbType.String, 255); arParams[2].Direction = ParameterDirection.Input; arParams[2].Value = title; arParams[3] = new SqliteParameter(":Html", DbType.Object); arParams[3].Direction = ParameterDirection.Input; arParams[3].Value = html; arParams[4] = new SqliteParameter(":LastModUTC", DbType.DateTime); arParams[4].Direction = ParameterDirection.Input; arParams[4].Value = lastModUTC; int rowsAffected = 0; rowsAffected = SqliteHelper.ExecuteNonQuery(GetConnectionString(), sqlCommand.ToString(), arParams); return rowsAffected; }
public void DeleteFavoriteBill(int id) { using (var connection = new SqliteConnection (connectionString)) { using (var cmd = connection.CreateCommand ()) { connection.Open (); string sql = "DELETE FROM FavoriteBills Where id=@id"; var idParam = new SqliteParameter ("@id", id); cmd.Parameters.Add (idParam); cmd.CommandText = sql; cmd.ExecuteNonQuery (); } } }
public void Delete () { var connection = NoteDBUtil.CreateConnnection (); using (var cmd = connection.CreateCommand ()) { connection.Open (); string sql = "Delete From Note Where id=@id"; SqliteParameter idParam = new SqliteParameter ("@id", _id); cmd.Parameters.Add (idParam); cmd.CommandText = sql; cmd.ExecuteNonQuery (); connection.Close (); } }
/// <summary> /// Inserts a row in the mp_PollOptions table. Returns rows affected count. /// </summary> /// <param name="optionGuid"> optionGuid </param> /// <param name="pollGuid"> pollGuid </param> /// <param name="answer"> answer </param> /// <param name="votes"> votes </param> /// <param name="order"> order </param> /// <returns>int</returns> public static int Add( Guid optionGuid, Guid pollGuid, string answer, int order) { int votes = 0; StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("INSERT INTO mp_PollOptions ("); sqlCommand.Append("OptionGuid, "); sqlCommand.Append("PollGuid, "); sqlCommand.Append("Answer, "); sqlCommand.Append("Votes, "); sqlCommand.Append("[Order] )"); sqlCommand.Append(" VALUES ("); sqlCommand.Append(":OptionGuid, "); sqlCommand.Append(":PollGuid, "); sqlCommand.Append(":Answer, "); sqlCommand.Append(":Votes, "); sqlCommand.Append(":Sort )"); sqlCommand.Append(";"); SqliteParameter[] arParams = new SqliteParameter[5]; arParams[0] = new SqliteParameter(":OptionGuid", DbType.String, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = optionGuid.ToString(); arParams[1] = new SqliteParameter(":PollGuid", DbType.String, 36); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = pollGuid.ToString(); arParams[2] = new SqliteParameter(":Answer", DbType.String, 255); arParams[2].Direction = ParameterDirection.Input; arParams[2].Value = answer; arParams[3] = new SqliteParameter(":Votes", DbType.Int32); arParams[3].Direction = ParameterDirection.Input; arParams[3].Value = votes; arParams[4] = new SqliteParameter(":Sort", DbType.Int32); arParams[4].Direction = ParameterDirection.Input; arParams[4].Value = order; int rowsAffected = 0; rowsAffected = SqliteHelper.ExecuteNonQuery(GetConnectionString(), sqlCommand.ToString(), arParams); return rowsAffected; }
void UpdateNote (SqliteCommand cmd) { string sql = "Update Note Set title=@title, body=@body Where id = @id"; SqliteParameter titleParam = new SqliteParameter ("@title", Title); SqliteParameter bodyParam = new SqliteParameter ("@body", Body); SqliteParameter idParam = new SqliteParameter ("@id", _id); cmd.Parameters.Add (titleParam); cmd.Parameters.Add (bodyParam); cmd.Parameters.Add (idParam); cmd.CommandText = sql; cmd.ExecuteNonQuery (); }
/// <summary> /// 返回 DataSet /// </summary> /// <param name="cmdType">命令类型</param> /// <param name="cmdText">SQL语句或存储过程名</param> /// <param name="prams">参数组</param> /// <returns>DataSet</returns> public static DataSet ExecuteDataSet(CommandType cmdType, string cmdText, SqliteParameter[] prams) { _querycount++; System.Web.HttpContext.Current.Application["total"] = Convert.ToInt32(System.Web.HttpContext.Current.Application["total"]) + 1; using (SqliteConnection conn = new SqliteConnection(ConnectionString)) { SqliteCommand cmd = MakeCommand(conn, cmdType, cmdText, prams); SqliteDataAdapter apt = new SqliteDataAdapter(cmd); DataSet ds = new DataSet(); apt.Fill(ds); cmd.Parameters.Clear(); return ds; } }
/// <summary> /// Inserts a row in the mp_SurveyQuestionAnswers table. Returns rows affected count. /// </summary> /// <param name="answerGuid"> answerGuid </param> /// <param name="questionGuid"> questionGuid </param> /// <param name="responseGuid"> responseGuid </param> /// <param name="answer"> answer </param> /// <returns>int</returns> public static int Add( Guid answerGuid, Guid questionGuid, Guid responseGuid, string answer) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("INSERT INTO mp_SurveyQuestionAnswers ("); sqlCommand.Append("AnswerGuid, "); sqlCommand.Append("QuestionGuid, "); sqlCommand.Append("ResponseGuid, "); sqlCommand.Append("Answer, "); sqlCommand.Append("AnsweredDate )"); sqlCommand.Append(" VALUES ("); sqlCommand.Append(":AnswerGuid, "); sqlCommand.Append(":QuestionGuid, "); sqlCommand.Append(":ResponseGuid, "); sqlCommand.Append(":Answer, "); sqlCommand.Append(":AnsweredDate )"); sqlCommand.Append(";"); SqliteParameter[] arParams = new SqliteParameter[5]; arParams[0] = new SqliteParameter(":AnswerGuid", DbType.String, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = answerGuid.ToString(); arParams[1] = new SqliteParameter(":QuestionGuid", DbType.String, 36); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = questionGuid.ToString(); arParams[2] = new SqliteParameter(":ResponseGuid", DbType.String, 36); arParams[2].Direction = ParameterDirection.Input; arParams[2].Value = responseGuid.ToString(); arParams[3] = new SqliteParameter(":Answer", DbType.Object); arParams[3].Direction = ParameterDirection.Input; arParams[3].Value = answer; arParams[4] = new SqliteParameter(":AnsweredDate", DbType.DateTime); arParams[4].Direction = ParameterDirection.Input; arParams[4].Value = DateTime.UtcNow; int rowsAffected = 0; rowsAffected = SqliteHelper.ExecuteNonQuery(GetConnectionString(), sqlCommand.ToString(), arParams); return rowsAffected; }
public static bool Delete(Guid guid) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("DELETE FROM mp_SiteFolders "); sqlCommand.Append("WHERE "); sqlCommand.Append("Guid = :Guid ;"); SqliteParameter[] arParams = new SqliteParameter[1]; arParams[0] = new SqliteParameter(":Guid", DbType.String, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = guid.ToString(); int rowsAffected = SqliteHelper.ExecuteNonQuery(GetConnectionString(), sqlCommand.ToString(), arParams); return (rowsAffected > 0); }
public static Dictionary<string, SqliteParameter> GetParameters(Guid dataID, tgProviderSpecificMetadata providerMetadata, tgColumnMetadataCollection columns) { lock (parameterCache) { if (!parameterCache.ContainsKey(dataID)) { // The Parameters for this Table haven't been cached yet, this is a one time operation Dictionary<string, SqliteParameter> types = new Dictionary<string, SqliteParameter>(); SqliteParameter param1; foreach (tgColumnMetadata col in columns) { tgTypeMap typeMap = providerMetadata.GetTypeMap(col.PropertyName); if (typeMap != null) { string nativeType = typeMap.NativeType; System.Data.DbType dbType = Cache.NativeTypeToDbType(nativeType); param1 = new SqliteParameter(Delimiters.Param + col.PropertyName, dbType, 0, col.Name); param1.SourceColumn = col.Name; switch (dbType) { case System.Data.DbType.Int64: //case VistaDBType.Int: //case VistaDBType.SmallInt: //case VistaDBType.Decimal: //case VistaDBType.Float: //case VistaDBType.Money: //case VistaDBType.SmallMoney: param1.Size = (int)col.CharacterMaxLength; break; } types[col.Name] = param1; } } parameterCache[dataID] = types; } } return parameterCache[dataID]; }
static SqliteDataAdapter PrepareDataAdapter() { SqliteCommand select = new SqliteCommand("SELECT t, f, i, b FROM t1",_conn); SqliteCommand update = new SqliteCommand("UPDATE t1 SET t = :textP, f = :floatP, i = :integerP, n=:blobP WHERE t = :textP "); update.Connection=_conn; SqliteCommand delete = new SqliteCommand("DELETE FROM t1 WHERE t = :textP"); delete.Connection=_conn; SqliteCommand insert = new SqliteCommand("INSERT INTO t1 (t, f, i, b ) VALUES(:textP,:floatP,:integerP,:blobP)"); insert.Connection=_conn; SqliteDataAdapter custDA = new SqliteDataAdapter(select); SqliteParameter textP = new SqliteParameter(); textP.ParameterName = "textP"; textP.SourceColumn = "t"; SqliteParameter floatP = new SqliteParameter(); floatP.ParameterName = "floatP"; floatP.SourceColumn = "f"; SqliteParameter integerP = new SqliteParameter(); integerP.ParameterName ="integerP"; integerP.SourceColumn = "i"; SqliteParameter blobP = new SqliteParameter(); blobP.ParameterName = "blobP"; blobP.SourceColumn = "b"; update.Parameters.Add(textP); update.Parameters.Add(floatP); update.Parameters.Add(integerP); update.Parameters.Add(blobP); delete.Parameters.Add(textP); insert.Parameters.Add(textP); insert.Parameters.Add(floatP); insert.Parameters.Add(integerP); insert.Parameters.Add(blobP); custDA.UpdateCommand = update; custDA.DeleteCommand = delete; custDA.InsertCommand = insert; return custDA; }
/// <summary> /// Inserts a row in the mp_GeoCountry table. Returns rows affected count. /// </summary> /// <param name="guid"> guid </param> /// <param name="name"> name </param> /// <param name="iSOCode2"> iSOCode2 </param> /// <param name="iSOCode3"> iSOCode3 </param> /// <returns>int</returns> public static int Create( Guid guid, string name, string iSOCode2, string iSOCode3) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("INSERT INTO mp_GeoCountry ("); sqlCommand.Append("Guid, "); sqlCommand.Append("Name, "); sqlCommand.Append("ISOCode2, "); sqlCommand.Append("ISOCode3 )"); sqlCommand.Append(" VALUES ("); sqlCommand.Append(":Guid, "); sqlCommand.Append(":Name, "); sqlCommand.Append(":ISOCode2, "); sqlCommand.Append(":ISOCode3 )"); sqlCommand.Append(";"); SqliteParameter[] arParams = new SqliteParameter[4]; arParams[0] = new SqliteParameter(":Guid", DbType.String, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = guid.ToString(); arParams[1] = new SqliteParameter(":Name", DbType.String, 255); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = name; arParams[2] = new SqliteParameter(":ISOCode2", DbType.String, 2); arParams[2].Direction = ParameterDirection.Input; arParams[2].Value = iSOCode2; arParams[3] = new SqliteParameter(":ISOCode3", DbType.String, 3); arParams[3].Direction = ParameterDirection.Input; arParams[3].Value = iSOCode3; int rowsAffected = 0; rowsAffected = SqliteHelper.ExecuteNonQuery(GetConnectionString(), sqlCommand.ToString(), arParams); return rowsAffected; }
/// <summary> /// Inserts a row in the mp_Language table. Returns rows affected count. /// </summary> /// <param name="guid"> guid </param> /// <param name="name"> name </param> /// <param name="code"> code </param> /// <param name="sort"> sort </param> /// <returns>int</returns> public static int Create( Guid guid, string name, string code, int sort) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("INSERT INTO mp_Language ("); sqlCommand.Append("Guid, "); sqlCommand.Append("Name, "); sqlCommand.Append("Code, "); sqlCommand.Append("Sort )"); sqlCommand.Append(" VALUES ("); sqlCommand.Append(":Guid, "); sqlCommand.Append(":Name, "); sqlCommand.Append(":Code, "); sqlCommand.Append(":Sort )"); sqlCommand.Append(";"); SqliteParameter[] arParams = new SqliteParameter[4]; arParams[0] = new SqliteParameter(":Guid", DbType.String, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = guid.ToString(); arParams[1] = new SqliteParameter(":Name", DbType.String, 255); arParams[1].Direction = ParameterDirection.Input; arParams[1].Value = name; arParams[2] = new SqliteParameter(":Code", DbType.String, 2); arParams[2].Direction = ParameterDirection.Input; arParams[2].Value = code; arParams[3] = new SqliteParameter(":Sort", DbType.Int32); arParams[3].Direction = ParameterDirection.Input; arParams[3].Value = sort; int rowsAffected = 0; rowsAffected = SqliteHelper.ExecuteNonQuery(GetConnectionString(), sqlCommand.ToString(), arParams); return rowsAffected; }
void InsertNote (SqliteCommand cmd) { string sql = "Insert Into Note (title, body) Values (@title, @body)"; SqliteParameter titleParam = new SqliteParameter ("@title", Title); SqliteParameter bodyParam = new SqliteParameter ("@body", Body); cmd.Parameters.Add (titleParam); cmd.Parameters.Add (bodyParam); cmd.CommandText = sql; cmd.ExecuteNonQuery (); sql = "select last_insert_rowid()"; cmd.CommandText = sql; using (var reader = cmd.ExecuteReader ()) { reader.Read (); _id = (long)reader[0]; } }
public SqliteParameter Add(SqliteParameter param) { Add((object)param); return(param); }
private void BindParameters3(IntPtr pStmt) { if (sql_params == null) { return; } if (sql_params.Count == 0) { return; } int pcount = Sqlite.sqlite3_bind_parameter_count(pStmt); for (int i = 1; i <= pcount; i++) { String name = Sqlite.HeapToString(Sqlite.sqlite3_bind_parameter_name(pStmt, i), Encoding.UTF8); SqliteParameter param = null; if (name != null) { param = sql_params[name] as SqliteParameter; } else { param = sql_params[i - 1] as SqliteParameter; } if (param.Value == null) { Sqlite.sqlite3_bind_null(pStmt, i); continue; } Type ptype = param.Value.GetType(); if (ptype.IsEnum) { ptype = Enum.GetUnderlyingType(ptype); } SqliteError err; if (ptype.Equals(typeof(String))) { String s = (String)param.Value; err = Sqlite.sqlite3_bind_text16(pStmt, i, s, -1, (IntPtr)(-1)); } else if (ptype.Equals(typeof(DBNull))) { err = Sqlite.sqlite3_bind_null(pStmt, i); } else if (ptype.Equals(typeof(Boolean))) { bool b = (bool)param.Value; err = Sqlite.sqlite3_bind_int(pStmt, i, b ? 1 : 0); } else if (ptype.Equals(typeof(Byte))) { err = Sqlite.sqlite3_bind_int(pStmt, i, (Byte)param.Value); } else if (ptype.Equals(typeof(Char))) { err = Sqlite.sqlite3_bind_int(pStmt, i, (Char)param.Value); } else if (ptype.IsEnum) { err = Sqlite.sqlite3_bind_int(pStmt, i, (Int32)param.Value); } else if (ptype.Equals(typeof(Int16))) { err = Sqlite.sqlite3_bind_int(pStmt, i, (Int16)param.Value); } else if (ptype.Equals(typeof(Int32))) { err = Sqlite.sqlite3_bind_int(pStmt, i, (Int32)param.Value); } else if (ptype.Equals(typeof(SByte))) { err = Sqlite.sqlite3_bind_int(pStmt, i, (SByte)param.Value); } else if (ptype.Equals(typeof(UInt16))) { err = Sqlite.sqlite3_bind_int(pStmt, i, (UInt16)param.Value); } else if (ptype.Equals(typeof(DateTime))) { DateTime dt = (DateTime)param.Value; err = Sqlite.sqlite3_bind_int64(pStmt, i, dt.ToFileTime()); } else if (ptype.Equals(typeof(Double))) { err = Sqlite.sqlite3_bind_double(pStmt, i, (Double)param.Value); } else if (ptype.Equals(typeof(Single))) { err = Sqlite.sqlite3_bind_double(pStmt, i, (Single)param.Value); } else if (ptype.Equals(typeof(UInt32))) { err = Sqlite.sqlite3_bind_int64(pStmt, i, (UInt32)param.Value); } else if (ptype.Equals(typeof(Int64))) { err = Sqlite.sqlite3_bind_int64(pStmt, i, (Int64)param.Value); } else if (ptype.Equals(typeof(Byte[]))) { err = Sqlite.sqlite3_bind_blob(pStmt, i, (Byte[])param.Value, ((Byte[])param.Value).Length, (IntPtr)(-1)); } else { throw new ApplicationException("Unkown Parameter Type"); } if (err != SqliteError.OK) { throw new ApplicationException("Sqlite error in bind " + err); } } }
public int IndexOf(SqliteParameter param) { return(IndexOf(param.ParameterName)); }
public void RemoveAt(SqliteParameter param) { RemoveAt(param.ParameterName); }
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> /// Clones a parameter /// </summary> /// <returns>A new, unassociated SqliteParameter</returns> public object Clone() { SqliteParameter newparam = new SqliteParameter(this); return(newparam); }
public bool Contains(SqliteParameter param) { return(Contains(param.ParameterName)); }
/// <summary> /// Minimal amount of parameter processing. Primarily sets the DbType for the parameter equal to the provider type in the schema /// </summary> /// <param name="parameter">The parameter to use in applying custom behaviors to a row</param> /// <param name="row">The row to apply the parameter to</param> /// <param name="statementType">The type of statement</param> /// <param name="whereClause">Whether the application of the parameter is part of a WHERE clause</param> protected override void ApplyParameterInfo(DbParameter parameter, DataRow row, StatementType statementType, bool whereClause) { SqliteParameter param = (SqliteParameter)parameter; param.DbType = (DbType)row[SchemaTableColumn.ProviderType]; }