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];
}
