/// <summary>
/// Inserts a row in the mp_TagItem table. Returns rows affected count.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="featureGuid"> featureGuid </param>
/// <param name="moduleGuid"> moduleGuid </param>
/// <param name="itemGuid"> itemGuid </param>
/// <param name="tagGuid"> tagGuid </param>
/// <param name="extraGuid"> extraGuid </param>
/// <param name="taggedBy"> taggedBy </param>
/// <returns>int</returns>
public static int Create(
Guid guid,
Guid siteGuid,
Guid featureGuid,
Guid moduleGuid,
Guid itemGuid,
Guid tagGuid,
Guid extraGuid,
Guid taggedBy)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_TagItem (");
sqlCommand.Append("Guid, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("FeatureGuid, ");
sqlCommand.Append("ModuleGuid, ");
sqlCommand.Append("ItemGuid, ");
sqlCommand.Append("TagGuid, ");
sqlCommand.Append("ExtraGuid, ");
sqlCommand.Append("TaggedBy )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":Guid, ");
sqlCommand.Append(":SiteGuid, ");
sqlCommand.Append(":FeatureGuid, ");
sqlCommand.Append(":ModuleGuid, ");
sqlCommand.Append(":ItemGuid, ");
sqlCommand.Append(":TagGuid, ");
sqlCommand.Append(":ExtraGuid, ");
sqlCommand.Append(":TaggedBy )");
sqlCommand.Append(";");
SqliteParameter[] arParams = new SqliteParameter[8];
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(":FeatureGuid", DbType.String, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = featureGuid.ToString();
arParams[3] = new SqliteParameter(":ModuleGuid", DbType.String, 36);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = moduleGuid.ToString();
arParams[4] = new SqliteParameter(":ItemGuid", DbType.String, 36);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = itemGuid.ToString();
arParams[5] = new SqliteParameter(":TagGuid", DbType.String, 36);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = tagGuid.ToString();
arParams[6] = new SqliteParameter(":ExtraGuid", DbType.String, 36);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = extraGuid.ToString();
arParams[7] = new SqliteParameter(":TaggedBy", DbType.String, 36);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = taggedBy.ToString();
int rowsAffected = SqliteHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
/// <summary>
/// Updates a row in the mp_ContentTemplate table. Returns true if row updated.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="title"> title </param>
/// <param name="imageFileName"> imageFileName </param>
/// <param name="description"> description </param>
/// <param name="body"> body </param>
/// <param name="allowedRoles"> allowedRoles </param>
/// <param name="lastModUser"> lastModUser </param>
/// <param name="lastModUtc"> lastModUtc </param>
/// <returns>bool</returns>
public static bool Update(
Guid guid,
Guid siteGuid,
string title,
string imageFileName,
string description,
string body,
string allowedRoles,
Guid lastModUser,
DateTime lastModUtc)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_ContentTemplate ");
sqlCommand.Append("SET ");
sqlCommand.Append("SiteGuid = :SiteGuid, ");
sqlCommand.Append("Title = :Title, ");
sqlCommand.Append("ImageFileName = :ImageFileName, ");
sqlCommand.Append("Description = :Description, ");
sqlCommand.Append("Body = :Body, ");
sqlCommand.Append("AllowedRoles = :AllowedRoles, ");
sqlCommand.Append("LastModUser = :LastModUser, ");
sqlCommand.Append("LastModUtc = :LastModUtc ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("Guid = :Guid ");
sqlCommand.Append(";");
SqliteParameter[] arParams = new SqliteParameter[9];
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(":ImageFileName", DbType.String, 255);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = imageFileName;
arParams[4] = new SqliteParameter(":Description", DbType.Object);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = description;
arParams[5] = new SqliteParameter(":Body", DbType.Object);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = body;
arParams[6] = new SqliteParameter(":AllowedRoles", DbType.Object);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = allowedRoles;
arParams[7] = new SqliteParameter(":LastModUser", DbType.String, 36);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = lastModUser.ToString();
arParams[8] = new SqliteParameter(":LastModUtc", DbType.DateTime);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = lastModUtc;
int rowsAffected = SqliteHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Inserts a row in the cy_TaskQueue table. Returns rows affected count.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="queuedBy"> queuedBy </param>
/// <param name="taskName"> taskName </param>
/// <param name="notifyOnCompletion"> notifyOnCompletion </param>
/// <param name="notificationToEmail"> notificationToEmail </param>
/// <param name="notificationFromEmail"> notificationFromEmail </param>
/// <param name="notificationSubject"> notificationSubject </param>
/// <param name="taskCompleteMessage"> taskCompleteMessage </param>
/// <param name="canStop"> canStop </param>
/// <param name="canResume"> canResume </param>
/// <param name="updateFrequency"> updateFrequency </param>
/// <param name="queuedUTC"> queuedUTC </param>
/// <param name="completeRatio"> completeRatio </param>
/// <param name="status"> status </param>
/// <param name="serializedTaskObject"> serializedTaskObject </param>
/// <param name="serializedTaskType"> serializedTaskType </param>
/// <returns>int</returns>
public static int Create(
Guid guid,
Guid siteGuid,
Guid queuedBy,
string taskName,
bool notifyOnCompletion,
string notificationToEmail,
string notificationFromEmail,
string notificationSubject,
string taskCompleteMessage,
bool canStop,
bool canResume,
int updateFrequency,
DateTime queuedUTC,
double completeRatio,
string status,
string serializedTaskObject,
string serializedTaskType)
{
#region Bit Conversion
int intNotifyOnCompletion;
if (notifyOnCompletion)
{
intNotifyOnCompletion = 1;
}
else
{
intNotifyOnCompletion = 0;
}
int intCanStop;
if (canStop)
{
intCanStop = 1;
}
else
{
intCanStop = 0;
}
int intCanResume;
if (canResume)
{
intCanResume = 1;
}
else
{
intCanResume = 0;
}
#endregion
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO cy_TaskQueue (");
sqlCommand.Append("Guid, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("QueuedBy, ");
sqlCommand.Append("TaskName, ");
sqlCommand.Append("NotifyOnCompletion, ");
sqlCommand.Append("NotificationToEmail, ");
sqlCommand.Append("NotificationFromEmail, ");
sqlCommand.Append("NotificationSubject, ");
sqlCommand.Append("TaskCompleteMessage, ");
sqlCommand.Append("CanStop, ");
sqlCommand.Append("CanResume, ");
sqlCommand.Append("UpdateFrequency, ");
sqlCommand.Append("QueuedUTC, ");
sqlCommand.Append("CompleteRatio, ");
sqlCommand.Append("Status, ");
sqlCommand.Append("SerializedTaskObject, ");
sqlCommand.Append("SerializedTaskType )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":Guid, ");
sqlCommand.Append(":SiteGuid, ");
sqlCommand.Append(":QueuedBy, ");
sqlCommand.Append(":TaskName, ");
sqlCommand.Append(":NotifyOnCompletion, ");
sqlCommand.Append(":NotificationToEmail, ");
sqlCommand.Append(":NotificationFromEmail, ");
sqlCommand.Append(":NotificationSubject, ");
sqlCommand.Append(":TaskCompleteMessage, ");
sqlCommand.Append(":CanStop, ");
sqlCommand.Append(":CanResume, ");
sqlCommand.Append(":UpdateFrequency, ");
sqlCommand.Append(":QueuedUTC, ");
sqlCommand.Append(":CompleteRatio, ");
sqlCommand.Append(":Status, ");
sqlCommand.Append(":SerializedTaskObject, ");
sqlCommand.Append(":SerializedTaskType )");
sqlCommand.Append(";");
SqliteParameter[] arParams = new SqliteParameter[17];
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(":QueuedBy", DbType.String, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = queuedBy.ToString();
arParams[3] = new SqliteParameter(":TaskName", DbType.String, 255);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = taskName;
arParams[4] = new SqliteParameter(":NotifyOnCompletion", DbType.Int32);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = intNotifyOnCompletion;
arParams[5] = new SqliteParameter(":NotificationToEmail", DbType.String, 255);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = notificationToEmail;
arParams[6] = new SqliteParameter(":NotificationFromEmail", DbType.String, 255);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = notificationFromEmail;
arParams[7] = new SqliteParameter(":NotificationSubject", DbType.String, 255);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = notificationSubject;
arParams[8] = new SqliteParameter(":TaskCompleteMessage", DbType.Object);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = taskCompleteMessage;
arParams[9] = new SqliteParameter(":CanStop", DbType.Int32);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = intCanStop;
arParams[10] = new SqliteParameter(":CanResume", DbType.Int32);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = intCanResume;
arParams[11] = new SqliteParameter(":UpdateFrequency", DbType.Int32);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = updateFrequency;
arParams[12] = new SqliteParameter(":QueuedUTC", DbType.DateTime);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = queuedUTC;
arParams[13] = new SqliteParameter(":CompleteRatio", DbType.Double);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = completeRatio;
arParams[14] = new SqliteParameter(":Status", DbType.String, 255);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = status;
arParams[15] = new SqliteParameter(":SerializedTaskObject", DbType.Object);
arParams[15].Direction = ParameterDirection.Input;
arParams[15].Value = serializedTaskObject;
arParams[16] = new SqliteParameter(":SerializedTaskType", DbType.String, 255);
arParams[16].Direction = ParameterDirection.Input;
arParams[16].Value = serializedTaskType;
int rowsAffected = 0;
rowsAffected = SqliteHelper.ExecuteNonQuery(GetConnectionString(), sqlCommand.ToString(), arParams);
return(rowsAffected);
}
public static bool CreateModuleSetting(
Guid settingGuid,
Guid moduleGuid,
int moduleId,
string settingName,
string settingValue,
string controlType,
string regexValidationExpression,
string controlSrc,
string helpKey,
int sortOrder)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO cy_ModuleSettings (");
sqlCommand.Append("ModuleID, ");
sqlCommand.Append("SettingName, ");
sqlCommand.Append("SettingValue, ");
sqlCommand.Append("ControlType, ");
sqlCommand.Append("ControlSrc, ");
sqlCommand.Append("HelpKey, ");
sqlCommand.Append("SortOrder, ");
sqlCommand.Append("RegexValidationExpression, ");
sqlCommand.Append("SettingGuid, ");
sqlCommand.Append("ModuleGuid )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":ModuleID, ");
sqlCommand.Append(":SettingName, ");
sqlCommand.Append(":SettingValue, ");
sqlCommand.Append(":ControlType, ");
sqlCommand.Append(":ControlSrc, ");
sqlCommand.Append(":HelpKey, ");
sqlCommand.Append(":SortOrder, ");
sqlCommand.Append(":RegexValidationExpression, ");
sqlCommand.Append(":SettingGuid, ");
sqlCommand.Append(":ModuleGuid )");
sqlCommand.Append(";");
SqliteParameter[] arParams = new SqliteParameter[10];
arParams[0] = new SqliteParameter(":ModuleID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
arParams[1] = new SqliteParameter(":SettingName", DbType.String, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = settingName;
arParams[2] = new SqliteParameter(":SettingValue", DbType.String, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = settingValue;
arParams[3] = new SqliteParameter(":ControlType", DbType.String, 50);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = controlType;
arParams[4] = new SqliteParameter(":RegexValidationExpression", DbType.Object);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = regexValidationExpression;
arParams[5] = new SqliteParameter(":SettingGuid", DbType.String, 36);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = settingGuid.ToString();
arParams[6] = new SqliteParameter(":ModuleGuid", DbType.String, 36);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = moduleGuid.ToString();
arParams[7] = new SqliteParameter(":ControlSrc", DbType.String, 255);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = controlSrc;
arParams[8] = new SqliteParameter(":HelpKey", DbType.String, 255);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = helpKey;
arParams[9] = new SqliteParameter(":SortOrder", DbType.Int32);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = sortOrder;
int rowsAffected = SqliteHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
/// <summary>
/// Inserts a row in the mp_MediaTrack table.
/// </summary>
/// <param name="playerID">The ID of the player to which the Media Track is being added.</param>
/// <param name="trackType">The type of the track.</param>
/// <param name="trackOrder">The order position of the Media Track.</param>
/// <param name="name">The name of the Media Track.</param>
/// <param name="artist">The artist of the Media Track.</param>
/// <param name="userGuid">The Guid of the user who added the Media Track.</param>
/// <returns>The ID of the Media Track in the doan_MediaTracks table.</returns>
public static int Insert(
int playerId,
string trackType,
int trackOrder,
string name,
string artist,
Guid userGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_MediaTrack (");
sqlCommand.Append("PlayerID, ");
sqlCommand.Append("TrackType, ");
sqlCommand.Append("TrackOrder, ");
sqlCommand.Append("Name, ");
sqlCommand.Append("Artist, ");
sqlCommand.Append("CreatedDate, ");
sqlCommand.Append("UserGuid )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":PlayerID, ");
sqlCommand.Append(":TrackType, ");
sqlCommand.Append(":TrackOrder, ");
sqlCommand.Append(":Name, ");
sqlCommand.Append(":Artist, ");
sqlCommand.Append(":CreatedDate, ");
sqlCommand.Append(":UserGuid )");
sqlCommand.Append(";");
sqlCommand.Append("SELECT LAST_INSERT_ROWID();");
SqliteParameter[] arParams = new SqliteParameter[7];
arParams[0] = new SqliteParameter(":PlayerID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = playerId;
arParams[1] = new SqliteParameter(":TrackType", DbType.String, 10);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = trackType;
arParams[2] = new SqliteParameter(":TrackOrder", DbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = trackOrder;
arParams[3] = new SqliteParameter(":Name", DbType.String, 100);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = name;
arParams[4] = new SqliteParameter(":Artist", DbType.String, 100);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = artist;
arParams[5] = new SqliteParameter(":CreatedDate", DbType.DateTime);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = DateTime.UtcNow;
arParams[6] = new SqliteParameter(":UserGuid", DbType.String, 36);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = userGuid.ToString();
int newID = Convert.ToInt32(SqliteHelper.ExecuteScalar(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams).ToString());
return(newID);
}
/// <summary>
/// Inserts a row in the cy_SurveyQuestions table. Returns rows affected count.
/// </summary>
/// <param name="questionGuid"> questionGuid </param>
/// <param name="pageGuid"> pageGuid </param>
/// <param name="questionText"> questionText </param>
/// <param name="questionTypeId"> questionTypeId </param>
/// <param name="answerIsRequired"> answerIsRequired </param>
/// <param name="questionOrder"> questionOrder </param>
/// <param name="validationMessage"> validationMessage </param>
/// <returns>int</returns>
public static int Add(
Guid questionGuid,
Guid pageGuid,
string questionText,
int questionTypeId,
bool answerIsRequired,
string validationMessage)
{
#region Bit Conversion
int intAnswerIsRequired;
if (answerIsRequired)
{
intAnswerIsRequired = 1;
}
else
{
intAnswerIsRequired = 0;
}
#endregion
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO cy_SurveyQuestions (");
sqlCommand.Append("QuestionGuid, ");
sqlCommand.Append("PageGuid, ");
sqlCommand.Append("QuestionText, ");
sqlCommand.Append("QuestionTypeId, ");
sqlCommand.Append("AnswerIsRequired, ");
sqlCommand.Append("QuestionOrder, ");
sqlCommand.Append("ValidationMessage )");
sqlCommand.Append("SELECT :QuestionGuid, :PageGuid, :QuestionText, ");
sqlCommand.Append(":QuestionTypeId, :AnswerIsRequired, Count(*), :ValidationMessage ");
sqlCommand.Append("FROM cy_SurveyPages; ");
SqliteParameter[] arParams = new SqliteParameter[6];
arParams[0] = new SqliteParameter(":QuestionGuid", DbType.String, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = questionGuid.ToString();
arParams[1] = new SqliteParameter(":PageGuid", DbType.String, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pageGuid.ToString();
arParams[2] = new SqliteParameter(":QuestionText", DbType.Object);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = questionText;
arParams[3] = new SqliteParameter(":QuestionTypeId", DbType.Int32);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = questionTypeId;
arParams[4] = new SqliteParameter(":AnswerIsRequired", DbType.Int32);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = intAnswerIsRequired;
arParams[5] = new SqliteParameter(":ValidationMessage", DbType.String, 256);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = validationMessage;
int rowsAffected = 0;
rowsAffected = SqliteHelper.ExecuteNonQuery(GetConnectionString(), sqlCommand.ToString(), arParams);
return(rowsAffected);
}
public static bool UpdateSharedFile(
int itemId,
int moduleId,
int uploadUserId,
string friendlyName,
string originalFileName,
string serverFileName,
int sizeInKB,
DateTime uploadDate,
int folderId,
Guid folderGuid,
Guid userGuid,
string description)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_SharedFiles ");
sqlCommand.Append("SET ");
sqlCommand.Append("ModuleID = :ModuleID, ");
sqlCommand.Append("UploadUserID = :UploadUserID, ");
sqlCommand.Append("FriendlyName = :FriendlyName, ");
sqlCommand.Append("OriginalFileName = :OriginalFileName, ");
sqlCommand.Append("ServerFileName = :ServerFileName, ");
sqlCommand.Append("SizeInKB = :SizeInKB, ");
sqlCommand.Append("UploadDate = :UploadDate, ");
sqlCommand.Append("FolderID = :FolderID, ");
sqlCommand.Append("UserGuid = :UserGuid, ");
sqlCommand.Append("Description = :Description, ");
sqlCommand.Append("FolderGuid = :FolderGuid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ItemID = :ItemID ;");
SqliteParameter[] arParams = new SqliteParameter[12];
arParams[0] = new SqliteParameter(":ItemID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = itemId;
arParams[1] = new SqliteParameter(":ModuleID", DbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = moduleId;
arParams[2] = new SqliteParameter(":UploadUserID", DbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = uploadUserId;
arParams[3] = new SqliteParameter(":FriendlyName", DbType.String, 255);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = friendlyName;
arParams[4] = new SqliteParameter(":OriginalFileName", DbType.String, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = originalFileName;
arParams[5] = new SqliteParameter(":ServerFileName", DbType.String, 255);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = serverFileName;
arParams[6] = new SqliteParameter(":SizeInKB", DbType.Int32);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = sizeInKB;
arParams[7] = new SqliteParameter(":UploadDate", DbType.DateTime);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = uploadDate;
arParams[8] = new SqliteParameter(":FolderID", DbType.Int32);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = folderId;
arParams[9] = new SqliteParameter(":UserGuid", DbType.String, 36);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = userGuid.ToString();
arParams[10] = new SqliteParameter(":FolderGuid", DbType.String, 36);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = folderGuid.ToString();
arParams[11] = new SqliteParameter(":Description", DbType.Object);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = description;
int rowsAffected = SqliteHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Adds a new best time for the given user and map.
/// We ensure that the new time is faster than whatever is currently in the database.
/// </summary>
/// <param name="connection"></param>
/// <param name="serverId"></param>
/// <param name="args">
/// {
/// "mapname": "mapname" (string, no file extension)
/// "username": "******" (string)
/// "date": 1610596223836 (int, Unix time s)
/// "time_ms": 234934 (int, ms)
/// "pmove_time_ms": 223320 (int, ms, -1 means no time)
/// "replay_data": "dGhpcyBpcyBhIHRlc3Q=" (string, base64 encoded)
/// }
/// </param>
/// <param name="status"></param>
private static void HandleCommandAddTime(IDbConnection connection, long serverId, dynamic args,
out int status)
{
status = (int)HttpStatusCode.BadRequest;
string mapname = args.mapname;
string username = args.username;
long? date = args.date;
long? timeMs = args.time_ms;
long? pmoveTimeMs = args.pmove_time_ms;
string replayDataB64 = args.replay_data;
if (string.IsNullOrEmpty(mapname) || string.IsNullOrEmpty(username) || date == null || timeMs == null ||
pmoveTimeMs == null || string.IsNullOrEmpty(replayDataB64))
{
return;
}
DateTime dateTime = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc) + TimeSpan.FromSeconds(date.Value);
string dateStr = dateTime.ToString(DateTimeFormat);
var command = connection.CreateCommand();
command.CommandText = $@"
INSERT OR IGNORE INTO MapTimes (MapId, UserId, ServerId, TimeMs, PMoveTimeMs, Date)
VALUES (
(SELECT MapId FROM Maps WHERE MapName = @mapname),
(SELECT UserId FROM Users WHERE UserName = @username),
{serverId},
{timeMs.Value},
{pmoveTimeMs.Value},
@date
);
UPDATE MapTimes
SET ServerId = {serverId}, TimeMs = {timeMs.Value}, PMoveTimeMs = {pmoveTimeMs.Value}, Date = @date
WHERE
MapId = (SELECT MapId FROM Maps WHERE MapName = @mapname)
AND
UserId = (SELECT UserId FROM Users WHERE UserName = @username)
AND
TimeMs > {timeMs.Value}
";
SqliteParameter paramMapName = new SqliteParameter("@mapname", SqliteType.Text);
paramMapName.Value = mapname;
SqliteParameter paramUserName = new SqliteParameter("@username", SqliteType.Text);
paramUserName.Value = username;
SqliteParameter paramDate = new SqliteParameter("@date", SqliteType.Text);
paramDate.Value = dateStr;
command.Parameters.Add(paramMapName);
command.Parameters.Add(paramUserName);
command.Parameters.Add(paramDate);
command.Prepare();
int rows = command.ExecuteNonQuery();
if (rows == 1)
{
if (Statistics.SaveReplayToFile(mapname, username, JsonConvert.SerializeObject(args)))
{
status = (int)HttpStatusCode.OK;
}
}
}
/// <summary>
/// Inserts a row in the mp_LetterInfo table. Returns rows affected count.
/// </summary>
/// <param name="letterInfoGuid"> letterInfoGuid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="title"> title </param>
/// <param name="description"> description </param>
/// <param name="availableToRoles"> availableToRoles </param>
/// <param name="enabled"> enabled </param>
/// <param name="allowUserFeedback"> allowUserFeedback </param>
/// <param name="allowAnonFeedback"> allowAnonFeedback </param>
/// <param name="fromAddress"> fromAddress </param>
/// <param name="fromName"> fromName </param>
/// <param name="replyToAddress"> replyToAddress </param>
/// <param name="sendMode"> sendMode </param>
/// <param name="enableViewAsWebPage"> enableViewAsWebPage </param>
/// <param name="enableSendLog"> enableSendLog </param>
/// <param name="rolesThatCanEdit"> rolesThatCanEdit </param>
/// <param name="rolesThatCanApprove"> rolesThatCanApprove </param>
/// <param name="rolesThatCanSend"> rolesThatCanSend </param>
/// <param name="createdUTC"> createdUTC </param>
/// <param name="createdBy"> createdBy </param>
/// <param name="lastModUTC"> lastModUTC </param>
/// <param name="lastModBy"> lastModBy </param>
/// <returns>int</returns>
public static int Create(
Guid letterInfoGuid,
Guid siteGuid,
string title,
string description,
string availableToRoles,
bool enabled,
bool allowUserFeedback,
bool allowAnonFeedback,
string fromAddress,
string fromName,
string replyToAddress,
int sendMode,
bool enableViewAsWebPage,
bool enableSendLog,
string rolesThatCanEdit,
string rolesThatCanApprove,
string rolesThatCanSend,
DateTime createdUtc,
Guid createdBy,
DateTime lastModUtc,
Guid lastModBy,
bool allowArchiveView,
bool profileOptIn,
int sortRank,
string displayNameDefault,
string firstNameDefault,
string lastNameDefault)
{
#region Bit Conversion
int intAllowArchiveView = 0;
if (allowArchiveView)
{
intAllowArchiveView = 1;
}
int intProfileOptIn = 0;
if (profileOptIn)
{
intProfileOptIn = 1;
}
int intEnabled = 0;
if (enabled)
{
intEnabled = 1;
}
int intAllowUserFeedback = 0;
if (allowUserFeedback)
{
intAllowUserFeedback = 1;
}
int intAllowAnonFeedback = 0;
if (allowAnonFeedback)
{
intAllowAnonFeedback = 1;
}
int intEnableViewAsWebPage = 0;
if (enableViewAsWebPage)
{
intEnableViewAsWebPage = 1;
}
int intEnableSendLog = 0;
if (enableSendLog)
{
intEnableSendLog = 1;
}
#endregion
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_LetterInfo (");
sqlCommand.Append("LetterInfoGuid, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("Title, ");
sqlCommand.Append("Description, ");
sqlCommand.Append("AvailableToRoles, ");
sqlCommand.Append("Enabled, ");
sqlCommand.Append("AllowUserFeedback, ");
sqlCommand.Append("AllowAnonFeedback, ");
sqlCommand.Append("FromAddress, ");
sqlCommand.Append("FromName, ");
sqlCommand.Append("ReplyToAddress, ");
sqlCommand.Append("SendMode, ");
sqlCommand.Append("EnableViewAsWebPage, ");
sqlCommand.Append("EnableSendLog, ");
sqlCommand.Append("RolesThatCanEdit, ");
sqlCommand.Append("RolesThatCanApprove, ");
sqlCommand.Append("RolesThatCanSend, ");
sqlCommand.Append("SubscriberCount, ");
sqlCommand.Append("UnVerifiedCount, ");
sqlCommand.Append("AllowArchiveView, ");
sqlCommand.Append("ProfileOptIn, ");
sqlCommand.Append("SortRank, ");
sqlCommand.Append("DisplayNameDefault, ");
sqlCommand.Append("FirstNameDefault, ");
sqlCommand.Append("LastNameDefault, ");
sqlCommand.Append("CreatedUTC, ");
sqlCommand.Append("CreatedBy, ");
sqlCommand.Append("LastModUTC, ");
sqlCommand.Append("LastModBy )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":LetterInfoGuid, ");
sqlCommand.Append(":SiteGuid, ");
sqlCommand.Append(":Title, ");
sqlCommand.Append(":Description, ");
sqlCommand.Append(":AvailableToRoles, ");
sqlCommand.Append(":Enabled, ");
sqlCommand.Append(":AllowUserFeedback, ");
sqlCommand.Append(":AllowAnonFeedback, ");
sqlCommand.Append(":FromAddress, ");
sqlCommand.Append(":FromName, ");
sqlCommand.Append(":ReplyToAddress, ");
sqlCommand.Append(":SendMode, ");
sqlCommand.Append(":EnableViewAsWebPage, ");
sqlCommand.Append(":EnableSendLog, ");
sqlCommand.Append(":RolesThatCanEdit, ");
sqlCommand.Append(":RolesThatCanApprove, ");
sqlCommand.Append(":RolesThatCanSend, ");
sqlCommand.Append("0, ");
sqlCommand.Append("0, ");
sqlCommand.Append(":AllowArchiveView, ");
sqlCommand.Append(":ProfileOptIn, ");
sqlCommand.Append(":SortRank, ");
sqlCommand.Append(":DisplayNameDefault, ");
sqlCommand.Append(":FirstNameDefault, ");
sqlCommand.Append(":LastNameDefault, ");
sqlCommand.Append(":CreatedUTC, ");
sqlCommand.Append(":CreatedBy, ");
sqlCommand.Append(":LastModUTC, ");
sqlCommand.Append(":LastModBy );");
SqliteParameter[] arParams = new SqliteParameter[27];
arParams[0] = new SqliteParameter(":LetterInfoGuid", DbType.String, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = letterInfoGuid.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(":AvailableToRoles", DbType.Object);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = availableToRoles;
arParams[5] = new SqliteParameter(":Enabled", DbType.Int32);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = intEnabled;
arParams[6] = new SqliteParameter(":AllowUserFeedback", DbType.Int32);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = intAllowUserFeedback;
arParams[7] = new SqliteParameter(":AllowAnonFeedback", DbType.Int32);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = intAllowAnonFeedback;
arParams[8] = new SqliteParameter(":FromAddress", DbType.String, 255);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = fromAddress;
arParams[9] = new SqliteParameter(":FromName", DbType.String, 255);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = fromName;
arParams[10] = new SqliteParameter(":ReplyToAddress", DbType.String, 255);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = replyToAddress;
arParams[11] = new SqliteParameter(":SendMode", DbType.Int32);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = sendMode;
arParams[12] = new SqliteParameter(":EnableViewAsWebPage", DbType.Int32);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = intEnableViewAsWebPage;
arParams[13] = new SqliteParameter(":EnableSendLog", DbType.Int32);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = intEnableSendLog;
arParams[14] = new SqliteParameter(":RolesThatCanEdit", DbType.Object);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = rolesThatCanEdit;
arParams[15] = new SqliteParameter(":RolesThatCanApprove", DbType.Object);
arParams[15].Direction = ParameterDirection.Input;
arParams[15].Value = rolesThatCanApprove;
arParams[16] = new SqliteParameter(":RolesThatCanSend", DbType.Object);
arParams[16].Direction = ParameterDirection.Input;
arParams[16].Value = rolesThatCanSend;
arParams[17] = new SqliteParameter(":CreatedUTC", DbType.DateTime);
arParams[17].Direction = ParameterDirection.Input;
arParams[17].Value = createdUtc;
arParams[18] = new SqliteParameter(":CreatedBy", DbType.String, 36);
arParams[18].Direction = ParameterDirection.Input;
arParams[18].Value = createdBy.ToString();
arParams[19] = new SqliteParameter(":LastModUTC", DbType.DateTime);
arParams[19].Direction = ParameterDirection.Input;
arParams[19].Value = lastModUtc;
arParams[20] = new SqliteParameter(":LastModBy", DbType.String, 36);
arParams[20].Direction = ParameterDirection.Input;
arParams[20].Value = lastModBy.ToString();
arParams[21] = new SqliteParameter(":AllowArchiveView", DbType.Int32);
arParams[21].Direction = ParameterDirection.Input;
arParams[21].Value = intAllowArchiveView;
arParams[22] = new SqliteParameter(":ProfileOptIn", DbType.Int32);
arParams[22].Direction = ParameterDirection.Input;
arParams[22].Value = intProfileOptIn;
arParams[23] = new SqliteParameter(":SortRank", DbType.Int32);
arParams[23].Direction = ParameterDirection.Input;
arParams[23].Value = sortRank;
arParams[24] = new SqliteParameter(":DisplayNameDefault", DbType.String, 50);
arParams[24].Direction = ParameterDirection.Input;
arParams[24].Value = displayNameDefault;
arParams[25] = new SqliteParameter(":FirstNameDefault", DbType.String, 50);
arParams[25].Direction = ParameterDirection.Input;
arParams[25].Value = firstNameDefault;
arParams[26] = new SqliteParameter(":LastNameDefault", DbType.String, 50);
arParams[26].Direction = ParameterDirection.Input;
arParams[26].Value = lastNameDefault;
int rowsAffected = SqliteHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
public static int AddSharedFile(
Guid itemGuid,
Guid moduleGuid,
Guid userGuid,
Guid folderGuid,
int moduleId,
int uploadUserId,
string friendlyName,
string originalFileName,
string serverFileName,
int sizeInKB,
DateTime uploadDate,
int folderId,
string description)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_SharedFiles (");
sqlCommand.Append("ModuleID, ");
sqlCommand.Append("UploadUserID, ");
sqlCommand.Append("FriendlyName, ");
sqlCommand.Append("OriginalFileName, ");
sqlCommand.Append("ServerFileName, ");
sqlCommand.Append("SizeInKB, ");
sqlCommand.Append("UploadDate, ");
sqlCommand.Append("FolderID, ");
sqlCommand.Append("ItemGuid, ");
sqlCommand.Append("ModuleGuid, ");
sqlCommand.Append("UserGuid, ");
sqlCommand.Append("Description, ");
sqlCommand.Append("DownloadCount, ");
sqlCommand.Append("FolderGuid )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":ModuleID, ");
sqlCommand.Append(":UploadUserID, ");
sqlCommand.Append(":FriendlyName, ");
sqlCommand.Append(":OriginalFileName, ");
sqlCommand.Append(":ServerFileName, ");
sqlCommand.Append(":SizeInKB, ");
sqlCommand.Append(":UploadDate, ");
sqlCommand.Append(":FolderID, ");
sqlCommand.Append(":ItemGuid, ");
sqlCommand.Append(":ModuleGuid, ");
sqlCommand.Append(":UserGuid, ");
sqlCommand.Append(":Description, ");
sqlCommand.Append("0, ");
sqlCommand.Append(":FolderGuid )");
sqlCommand.Append(";");
sqlCommand.Append("SELECT LAST_INSERT_ROWID();");
SqliteParameter[] arParams = new SqliteParameter[13];
arParams[0] = new SqliteParameter(":ModuleID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
arParams[1] = new SqliteParameter(":UploadUserID", DbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = uploadUserId;
arParams[2] = new SqliteParameter(":FriendlyName", DbType.String, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = friendlyName;
arParams[3] = new SqliteParameter(":OriginalFileName", DbType.String, 255);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = originalFileName;
arParams[4] = new SqliteParameter(":ServerFileName", DbType.String, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = serverFileName;
arParams[5] = new SqliteParameter(":SizeInKB", DbType.Int32);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = sizeInKB;
arParams[6] = new SqliteParameter(":UploadDate", DbType.DateTime);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = uploadDate;
arParams[7] = new SqliteParameter(":FolderID", DbType.Int32);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = folderId;
arParams[8] = new SqliteParameter(":ItemGuid", DbType.String, 36);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = itemGuid.ToString();
arParams[9] = new SqliteParameter(":ModuleGuid", DbType.String, 36);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = moduleGuid.ToString();
arParams[10] = new SqliteParameter(":UserGuid", DbType.String, 36);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = userGuid.ToString();
arParams[11] = new SqliteParameter(":FolderGuid", DbType.String, 36);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = folderGuid.ToString();
arParams[12] = new SqliteParameter(":Description", DbType.Object);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = description;
int newID = Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams).ToString());
return(newID);
}
/// <summary>
/// Inserts a row in the mp_Category table. Returns rows affected count.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="parentGuid"> parentGuid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="featureGuid"> featureGuid </param>
/// <param name="moduleGuid"> moduleGuid </param>
/// <param name="category"> category </param>
/// <param name="description"> description </param>
/// <param name="createdUtc"> createdUtc </param>
/// <param name="createdBy"> createdBy </param>
/// <returns>int</returns>
public static int Create(
Guid guid,
Guid parentGuid,
Guid siteGuid,
Guid featureGuid,
Guid moduleGuid,
string category,
string description,
DateTime createdUtc,
Guid createdBy)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_Category (");
sqlCommand.Append("Guid, ");
sqlCommand.Append("ParentGuid, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("FeatureGuid, ");
sqlCommand.Append("ModuleGuid, ");
sqlCommand.Append("Category, ");
sqlCommand.Append("Description, ");
sqlCommand.Append("ItemCount, ");
sqlCommand.Append("CreatedUtc, ");
sqlCommand.Append("CreatedBy, ");
sqlCommand.Append("ModifiedUtc, ");
sqlCommand.Append("ModifiedBy )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":Guid, ");
sqlCommand.Append(":ParentGuid, ");
sqlCommand.Append(":SiteGuid, ");
sqlCommand.Append(":FeatureGuid, ");
sqlCommand.Append(":ModuleGuid, ");
sqlCommand.Append(":Category, ");
sqlCommand.Append(":Description, ");
sqlCommand.Append(":ItemCount, ");
sqlCommand.Append(":CreatedUtc, ");
sqlCommand.Append(":CreatedBy, ");
sqlCommand.Append(":ModifiedUtc, ");
sqlCommand.Append(":ModifiedBy )");
sqlCommand.Append(";");
SqliteParameter[] arParams = new SqliteParameter[12];
arParams[0] = new SqliteParameter(":Guid", DbType.String, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = guid.ToString();
arParams[1] = new SqliteParameter(":ParentGuid", DbType.String, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = parentGuid.ToString();
arParams[2] = new SqliteParameter(":SiteGuid", DbType.String, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = siteGuid.ToString();
arParams[3] = new SqliteParameter(":FeatureGuid", DbType.String, 36);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = featureGuid.ToString();
arParams[4] = new SqliteParameter(":ModuleGuid", DbType.String, 36);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = moduleGuid.ToString();
arParams[5] = new SqliteParameter(":Category", DbType.String, 255);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = category;
arParams[6] = new SqliteParameter(":Description", DbType.Object);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = description;
arParams[7] = new SqliteParameter(":ItemCount", DbType.Int32);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = 0;
arParams[8] = new SqliteParameter(":CreatedUtc", DbType.DateTime);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = createdUtc;
arParams[9] = new SqliteParameter(":CreatedBy", DbType.String, 36);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = createdBy.ToString();
arParams[10] = new SqliteParameter(":ModifiedUtc", DbType.DateTime);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = createdUtc;
arParams[11] = new SqliteParameter(":ModifiedBy", DbType.String, 36);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = createdBy.ToString();
int rowsAffected = SqliteHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
public static bool UpdateModuleDefinitionSettingById(
int id,
int moduleDefId,
string resourceFile,
string groupName,
string settingName,
string settingValue,
string controlType,
string regexValidationExpression,
string controlSrc,
string helpKey,
int sortOrder,
string attributes,
string options)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_ModuleDefinitionSettings ");
sqlCommand.Append("SET SettingName = :SettingName, ");
sqlCommand.Append("ResourceFile = :ResourceFile, ");
sqlCommand.Append("SettingValue = :SettingValue, ");
sqlCommand.Append("ControlType = :ControlType, ");
sqlCommand.Append("ControlSrc = :ControlSrc ,");
sqlCommand.Append("HelpKey = :HelpKey ,");
sqlCommand.Append("SortOrder = :SortOrder ,");
sqlCommand.Append("GroupName = :GroupName ,");
sqlCommand.Append("RegexValidationExpression = :RegexValidationExpression, ");
sqlCommand.Append("Attributes = :Attributes, ");
sqlCommand.Append("Options = :Options ");
sqlCommand.Append("WHERE ID = :ID ");
sqlCommand.Append("AND ModuleDefID = :ModuleDefID ; ");
SqliteParameter[] arParams = new SqliteParameter[13];
arParams[0] = new SqliteParameter(":ID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = id;
arParams[1] = new SqliteParameter(":ModuleDefID", DbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = moduleDefId;
arParams[2] = new SqliteParameter(":SettingName", DbType.String, 50);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = settingName;
arParams[3] = new SqliteParameter(":SettingValue", DbType.String, 255);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = settingValue;
arParams[4] = new SqliteParameter(":ControlType", DbType.String, 50);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = controlType;
arParams[5] = new SqliteParameter(":RegexValidationExpression", DbType.Object);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = regexValidationExpression;
arParams[6] = new SqliteParameter(":ResourceFile", DbType.String, 255);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = resourceFile;
arParams[7] = new SqliteParameter(":ControlSrc", DbType.String, 255);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = controlSrc;
arParams[8] = new SqliteParameter(":HelpKey", DbType.String, 255);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = helpKey;
arParams[9] = new SqliteParameter(":SortOrder", DbType.Int32);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = sortOrder;
arParams[10] = new SqliteParameter(":GroupName", DbType.String, 255);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = groupName;
arParams[11] = new SqliteParameter(":Attributes", DbType.Object);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = attributes;
arParams[12] = new SqliteParameter(":Options", DbType.Object);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = options;
int rowsAffected = SqliteHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
public static bool UpdateModuleDefinitionSetting(
Guid featureGuid,
int moduleDefId,
string resourceFile,
string groupName,
string settingName,
string settingValue,
string controlType,
string regexValidationExpression,
string controlSrc,
string helpKey,
int sortOrder,
string attributes,
string options)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT count(*)");
sqlCommand.Append("FROM mp_ModuleDefinitionSettings ");
sqlCommand.Append("WHERE (ModuleDefID = :ModuleDefID OR FeatureGuid = :FeatureGuid) ");
sqlCommand.Append("AND SettingName = :SettingName ;");
SqliteParameter[] arParams = new SqliteParameter[3];
arParams[0] = new SqliteParameter(":ModuleDefID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleDefId;
arParams[1] = new SqliteParameter(":SettingName", DbType.String, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = settingName;
arParams[2] = new SqliteParameter(":FeatureGuid", DbType.String, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = featureGuid;
int count = Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams).ToString());
sqlCommand = new StringBuilder();
int rowsAffected = 0;
if (count > 0)
{
sqlCommand.Append("UPDATE mp_ModuleDefinitionSettings ");
sqlCommand.Append("SET SettingValue = :SettingValue ,");
sqlCommand.Append("FeatureGuid = :FeatureGuid, ");
sqlCommand.Append("ResourceFile = :ResourceFile, ");
sqlCommand.Append("ControlType = :ControlType ,");
sqlCommand.Append("ControlSrc = :ControlSrc ,");
sqlCommand.Append("HelpKey = :HelpKey ,");
sqlCommand.Append("SortOrder = :SortOrder ,");
sqlCommand.Append("GroupName = :GroupName ,");
sqlCommand.Append("RegexValidationExpression = :RegexValidationExpression, ");
sqlCommand.Append("Attributes = :Attributes, ");
sqlCommand.Append("Options = :Options ");
sqlCommand.Append("WHERE ModuleDefID = :ModuleDefID ");
sqlCommand.Append("AND SettingName = :SettingName ; ");
arParams = new SqliteParameter[13];
arParams[0] = new SqliteParameter(":ModuleDefID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleDefId;
arParams[1] = new SqliteParameter(":SettingName", DbType.String, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = settingName;
arParams[2] = new SqliteParameter(":SettingValue", DbType.String, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = settingValue;
arParams[3] = new SqliteParameter(":ControlType", DbType.String, 50);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = controlType;
arParams[4] = new SqliteParameter(":RegexValidationExpression", DbType.Object);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = regexValidationExpression;
arParams[5] = new SqliteParameter(":FeatureGuid", DbType.String, 36);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = featureGuid;
arParams[6] = new SqliteParameter(":ResourceFile", DbType.String, 255);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = resourceFile;
arParams[7] = new SqliteParameter(":ControlSrc", DbType.String, 255);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = controlSrc;
arParams[8] = new SqliteParameter(":HelpKey", DbType.String, 255);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = helpKey;
arParams[9] = new SqliteParameter(":SortOrder", DbType.Int32);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = sortOrder;
arParams[10] = new SqliteParameter(":GroupName", DbType.String, 255);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = groupName;
arParams[11] = new SqliteParameter(":Attributes", DbType.Object);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = attributes;
arParams[12] = new SqliteParameter(":Options", DbType.Object);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = options;
rowsAffected = SqliteHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
else
{
sqlCommand.Append("INSERT INTO mp_ModuleDefinitionSettings ");
sqlCommand.Append("( ");
sqlCommand.Append("FeatureGuid, ");
sqlCommand.Append("ModuleDefID, ");
sqlCommand.Append("ResourceFile, ");
sqlCommand.Append("SettingName, ");
sqlCommand.Append("SettingValue, ");
sqlCommand.Append("ControlType, ");
sqlCommand.Append("ControlSrc, ");
sqlCommand.Append("HelpKey, ");
sqlCommand.Append("SortOrder, ");
sqlCommand.Append("GroupName, ");
sqlCommand.Append("RegexValidationExpression, ");
sqlCommand.Append("Attributes, ");
sqlCommand.Append("Options");
sqlCommand.Append(")");
sqlCommand.Append("VALUES ( ");
sqlCommand.Append(" :FeatureGuid , ");
sqlCommand.Append(" :ModuleDefID , ");
sqlCommand.Append(" :ResourceFile , ");
sqlCommand.Append(" :SettingName , ");
sqlCommand.Append(" :SettingValue ,");
sqlCommand.Append(" :ControlType ,");
sqlCommand.Append(" :ControlSrc, ");
sqlCommand.Append(" :HelpKey, ");
sqlCommand.Append(" :SortOrder, ");
sqlCommand.Append(" :GroupName, ");
sqlCommand.Append(" :RegexValidationExpression, ");
sqlCommand.Append(" :Attributes, ");
sqlCommand.Append(" :Options ");
sqlCommand.Append(");");
arParams = new SqliteParameter[13];
arParams[0] = new SqliteParameter(":ModuleDefID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleDefId;
arParams[1] = new SqliteParameter(":SettingName", DbType.String, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = settingName;
arParams[2] = new SqliteParameter(":SettingValue", DbType.String, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = settingValue;
arParams[3] = new SqliteParameter(":ControlType", DbType.String, 50);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = controlType;
arParams[4] = new SqliteParameter(":RegexValidationExpression", DbType.Object);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = regexValidationExpression;
arParams[5] = new SqliteParameter(":FeatureGuid", DbType.String, 36);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = featureGuid;
arParams[6] = new SqliteParameter(":ResourceFile", DbType.String, 255);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = resourceFile;
arParams[7] = new SqliteParameter(":ControlSrc", DbType.String, 255);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = controlSrc;
arParams[8] = new SqliteParameter(":HelpKey", DbType.String, 255);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = helpKey;
arParams[9] = new SqliteParameter(":SortOrder", DbType.Int32);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = sortOrder;
arParams[10] = new SqliteParameter(":GroupName", DbType.String, 255);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = groupName;
arParams[11] = new SqliteParameter(":Attributes", DbType.Object);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = attributes;
arParams[12] = new SqliteParameter(":Options", DbType.Object);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = options;
rowsAffected = SqliteHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
}
/// <summary>
///
/// </summary>
/// <param name="dt">Data Table</param>
/// <returns></returns>
// private static string defineTable(DataTable dt)
// {
// string sql = "create table " + dt.TableName + "(";
// string subsql = String.Empty;
// foreach (DataColumn col in dt.Columns)
// {
// if (subsql.Length > 0)
// {
// // a map function would rock so much here
// subsql += ",\n";
// }
// subsql += col.ColumnName + " " + sqliteType(col.DataType);
// if (dt.PrimaryKey.Length > 0 && col == dt.PrimaryKey[0])
// {
// subsql += " primary key";
// }
// }
// sql += subsql;
// sql += ")";
// return sql;
// }
/***********************************************************************
*
* Database Binding functions
*
* These will be db specific due to typing, and minor differences
* in databases.
*
**********************************************************************/
///<summary>
/// This is a convenience function that collapses 5 repetitive
/// lines for defining SqliteParameters to 2 parameters:
/// column name and database type.
///
/// It assumes certain conventions like :param as the param
/// name to replace in parametrized queries, and that source
/// version is always current version, both of which are fine
/// for us.
///</summary>
///<returns>a built sqlite parameter</returns>
private static SqliteParameter createSqliteParameter(string name, Type type)
{
SqliteParameter param = new SqliteParameter();
param.ParameterName = ":" + name;
param.DbType = dbtypeFromType(type);
param.SourceColumn = name;
param.SourceVersion = DataRowVersion.Current;
return param;
}
public static int AddSharedFileFolder(
Guid folderGuid,
Guid moduleGuid,
Guid parentGuid,
int moduleId,
string folderName,
int parentId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_SharedFileFolders (");
sqlCommand.Append("ModuleID, ");
sqlCommand.Append("FolderName, ");
sqlCommand.Append("ParentID, ");
sqlCommand.Append("ModuleGuid, ");
sqlCommand.Append("FolderGuid, ");
sqlCommand.Append("ParentGuid )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":ModuleID, ");
sqlCommand.Append(":FolderName, ");
sqlCommand.Append(":ParentID, ");
sqlCommand.Append(":ModuleGuid, ");
sqlCommand.Append(":FolderGuid, ");
sqlCommand.Append(":ParentGuid )");
sqlCommand.Append(";");
sqlCommand.Append("SELECT LAST_INSERT_ROWID();");
SqliteParameter[] arParams = new SqliteParameter[6];
arParams[0] = new SqliteParameter(":ModuleID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
arParams[1] = new SqliteParameter(":FolderName", DbType.String, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = folderName;
arParams[2] = new SqliteParameter(":ParentID", DbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = parentId;
arParams[3] = new SqliteParameter(":ModuleGuid", DbType.String, 36);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = moduleGuid.ToString();
arParams[4] = new SqliteParameter(":FolderGuid", DbType.String, 36);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = folderGuid.ToString();
arParams[5] = new SqliteParameter(":ParentGuid", DbType.String, 36);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = parentGuid.ToString();
int newID = Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams).ToString());
return(newID);
}
public static int AddModuleDefinition(
Guid featureGuid,
int siteId,
string featureName,
string controlSrc,
int sortOrder,
int defaultCacheTime,
String icon,
bool isAdmin,
string resourceFile,
bool isCacheable,
bool isSearchable,
string searchListName,
bool supportsPageReuse,
string deleteProvider,
string partialView,
string skinFileName)
{
int intIsAdmin = 0;
if (isAdmin)
{
intIsAdmin = 1;
}
int intIsCacheable = 0;
if (isCacheable)
{
intIsCacheable = 1;
}
int intIsSearchable = 0;
if (isSearchable)
{
intIsSearchable = 1;
}
int intSupportsPageReuse = 0;
if (supportsPageReuse)
{
intSupportsPageReuse = 1;
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_ModuleDefinitions (");
sqlCommand.Append("Guid, ");
sqlCommand.Append("FeatureName, ");
sqlCommand.Append("ControlSrc, ");
sqlCommand.Append("SortOrder, ");
sqlCommand.Append("DefaultCacheTime, ");
sqlCommand.Append("Icon, ");
sqlCommand.Append("IsAdmin, ");
sqlCommand.Append("IsCacheable, ");
sqlCommand.Append("IsSearchable, ");
sqlCommand.Append("SearchListName, ");
sqlCommand.Append("SupportsPageReuse, ");
sqlCommand.Append("DeleteProvider, ");
sqlCommand.Append("PartialView, ");
sqlCommand.Append("ResourceFile, ");
sqlCommand.Append("SkinFileName ");
sqlCommand.Append(" )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":FeatureGuid, ");
sqlCommand.Append(":FeatureName, ");
sqlCommand.Append(":ControlSrc, ");
sqlCommand.Append(":SortOrder, ");
sqlCommand.Append(":DefaultCacheTime, ");
sqlCommand.Append(":Icon, ");
sqlCommand.Append(":IsAdmin, ");
sqlCommand.Append(":IsCacheable, ");
sqlCommand.Append(":IsSearchable, ");
sqlCommand.Append(":SearchListName, ");
sqlCommand.Append(":SupportsPageReuse, ");
sqlCommand.Append(":DeleteProvider, ");
sqlCommand.Append(":PartialView, ");
sqlCommand.Append(":ResourceFile, ");
sqlCommand.Append(":SkinFileName ");
sqlCommand.Append(" );");
sqlCommand.Append("SELECT LAST_INSERT_ROWID();");
SqliteParameter[] arParams = new SqliteParameter[16];
arParams[0] = new SqliteParameter(":SiteID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new SqliteParameter(":FeatureName", DbType.String, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = featureName;
arParams[2] = new SqliteParameter(":ControlSrc", DbType.String, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = controlSrc;
arParams[3] = new SqliteParameter(":SortOrder", DbType.Int32);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = sortOrder;
arParams[4] = new SqliteParameter(":IsAdmin", DbType.Int32);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = intIsAdmin;
arParams[5] = new SqliteParameter(":Icon", DbType.String, 255);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = icon;
arParams[6] = new SqliteParameter(":DefaultCacheTime", DbType.Int32);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = defaultCacheTime;
arParams[7] = new SqliteParameter(":FeatureGuid", DbType.String, 36);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = featureGuid;
arParams[8] = new SqliteParameter(":ResourceFile", DbType.String, 255);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = resourceFile;
arParams[9] = new SqliteParameter(":IsCacheable", DbType.Int32);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = intIsCacheable;
arParams[10] = new SqliteParameter(":IsSearchable", DbType.Int32);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = intIsSearchable;
arParams[11] = new SqliteParameter(":SearchListName", DbType.String, 255);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = searchListName;
arParams[12] = new SqliteParameter(":SupportsPageReuse", DbType.Int32);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = intSupportsPageReuse;
arParams[13] = new SqliteParameter(":DeleteProvider", DbType.String, 255);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = deleteProvider;
arParams[14] = new SqliteParameter(":PartialView", DbType.String, 255);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = partialView;
arParams[15] = new SqliteParameter(":SkinFileName", DbType.String, 255);
arParams[15].Direction = ParameterDirection.Input;
arParams[15].Value = skinFileName;
int newID = Convert.ToInt32(
SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams).ToString());
if (siteId > -1)
{
// now add to mp_SiteModuleDefinitions
sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_SiteModuleDefinitions (");
sqlCommand.Append("SiteID, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("FeatureGuid, ");
sqlCommand.Append("AuthorizedRoles, ");
sqlCommand.Append("ModuleDefID ) ");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":SiteID, ");
sqlCommand.Append("(SELECT SiteGuid FROM mp_Sites WHERE SiteID = :SiteID LIMIT 1), ");
sqlCommand.Append("(SELECT Guid FROM mp_ModuleDefinitions WHERE ModuleDefID = :ModuleDefID LIMIT 1), ");
sqlCommand.Append("'All Users', ");
sqlCommand.Append(":ModuleDefID ) ; ");
arParams = new SqliteParameter[2];
arParams[0] = new SqliteParameter(":SiteID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new SqliteParameter(":ModuleDefID", DbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = newID;
SqliteHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
}
return(newID);
}
public static bool AddHistory(
Guid itemGuid,
Guid moduleGuid,
Guid userGuid,
int itemId,
int moduleId,
string friendlyName,
string originalFileName,
string serverFileName,
int sizeInKB,
DateTime uploadDate,
int uploadUserId,
DateTime archiveDate)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_SharedFilesHistory (");
sqlCommand.Append("ItemID, ");
sqlCommand.Append("ModuleID, ");
sqlCommand.Append("FriendlyName, ");
sqlCommand.Append("OriginalFileName, ");
sqlCommand.Append("ServerFileName, ");
sqlCommand.Append("SizeInKB, ");
sqlCommand.Append("UploadDate, ");
sqlCommand.Append("UploadUserID, ");
sqlCommand.Append("ArchiveDate, ");
sqlCommand.Append("ItemGuid, ");
sqlCommand.Append("ModuleGuid, ");
sqlCommand.Append("UserGuid )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":ItemID, ");
sqlCommand.Append(":ModuleID, ");
sqlCommand.Append(":FriendlyName, ");
sqlCommand.Append(":OriginalFileName, ");
sqlCommand.Append(":ServerFileName, ");
sqlCommand.Append(":SizeInKB, ");
sqlCommand.Append(":UploadDate, ");
sqlCommand.Append(":UploadUserID, ");
sqlCommand.Append(":ArchiveDate, ");
sqlCommand.Append(":ItemGuid, ");
sqlCommand.Append(":ModuleGuid, ");
sqlCommand.Append(":UserGuid )");
sqlCommand.Append(";");
sqlCommand.Append("SELECT LAST_INSERT_ROWID();");
SqliteParameter[] arParams = new SqliteParameter[12];
arParams[0] = new SqliteParameter(":ItemID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = itemId;
arParams[1] = new SqliteParameter(":ModuleID", DbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = moduleId;
arParams[2] = new SqliteParameter(":FriendlyName", DbType.String, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = friendlyName;
arParams[3] = new SqliteParameter(":OriginalFileName", DbType.String, 255);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = originalFileName;
arParams[4] = new SqliteParameter(":ServerFileName", DbType.String, 50);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = serverFileName;
arParams[5] = new SqliteParameter(":SizeInKB", DbType.Int32);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = sizeInKB;
arParams[6] = new SqliteParameter(":UploadDate", DbType.DateTime);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = uploadDate;
arParams[7] = new SqliteParameter(":UploadUserID", DbType.Int32);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = uploadUserId;
arParams[8] = new SqliteParameter(":ArchiveDate", DbType.DateTime);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = archiveDate;
arParams[9] = new SqliteParameter(":ItemGuid", DbType.String, 36);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = itemGuid.ToString();
arParams[10] = new SqliteParameter(":ModuleGuid", DbType.String, 36);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = moduleGuid.ToString();
arParams[11] = new SqliteParameter(":UserGuid", DbType.String, 36);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = userGuid.ToString();
int newID = 0;
newID = Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams).ToString());
return(newID > 0);
}
/// <summary>
/// Updates a row in the mp_MediaTrack table.
/// </summary>
/// <param name="trackID">The ID of the track.</param>
/// <param name="playerID">The ID of the player instance.</param>
/// <param name="trackType">The type of the track.</param>
/// <param name="trackOrder">The order position of the Media Track.</param>
/// <param name="name">The name of the Media Track.</param>
/// <param name="artist">The artist of the Media Track.</param>
/// <param name="userGuid">The Guid of the user who added the Media Track.</param>
/// <returns>True if the row is successfully updated.</returns>
public static bool Update(
int trackId,
int playerId,
string trackType,
int trackOrder,
string name,
string artist,
Guid userGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_MediaTrack ");
sqlCommand.Append("SET ");
sqlCommand.Append("PlayerID = :PlayerID, ");
sqlCommand.Append("TrackType = :TrackType, ");
sqlCommand.Append("TrackOrder = :TrackOrder, ");
sqlCommand.Append("Name = :Name, ");
sqlCommand.Append("Artist = :Artist, ");
sqlCommand.Append("UserGuid = :UserGuid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("TrackID = :TrackID ");
sqlCommand.Append(";");
SqliteParameter[] arParams = new SqliteParameter[7];
arParams[0] = new SqliteParameter(":TrackID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = trackId;
arParams[1] = new SqliteParameter(":PlayerID", DbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = playerId;
arParams[2] = new SqliteParameter(":TrackType", DbType.String, 10);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = trackType;
arParams[3] = new SqliteParameter(":TrackOrder", DbType.Int32);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = trackOrder;
arParams[4] = new SqliteParameter(":Name", DbType.String, 100);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = name;
arParams[5] = new SqliteParameter(":Artist", DbType.String, 100);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = artist;
arParams[6] = new SqliteParameter(":UserGuid", DbType.String, 36);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = userGuid.ToString();
int rowsAffected = SqliteHelper.ExecuteNonQuery(
ConnectionString.GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Inserts a row in the mp_ContentRating table. Returns rows affected count.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="contentGuid"> contentGuid </param>
/// <param name="userGuid"> userGuid </param>
/// <param name="emailAddress"> emailAddress </param>
/// <param name="rating"> rating </param>
/// <param name="comments"> comments </param>
/// <param name="ipAddress"> ipAddress </param>
/// <param name="createdUtc"> createdUtc </param>
/// <returns>int</returns>
public static int Create(
Guid rowGuid,
Guid siteGuid,
Guid contentGuid,
Guid userGuid,
string emailAddress,
int rating,
string comments,
string ipAddress,
DateTime createdUtc)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_ContentRating (");
sqlCommand.Append("RowGuid, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("ContentGuid, ");
sqlCommand.Append("UserGuid, ");
sqlCommand.Append("EmailAddress, ");
sqlCommand.Append("Rating, ");
sqlCommand.Append("Comments, ");
sqlCommand.Append("IpAddress, ");
sqlCommand.Append("CreatedUtc, ");
sqlCommand.Append("LastModUtc )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":RowGuid, ");
sqlCommand.Append(":SiteGuid, ");
sqlCommand.Append(":ContentGuid, ");
sqlCommand.Append(":UserGuid, ");
sqlCommand.Append(":EmailAddress, ");
sqlCommand.Append(":Rating, ");
sqlCommand.Append(":Comments, ");
sqlCommand.Append(":IpAddress, ");
sqlCommand.Append(":CreatedUtc, ");
sqlCommand.Append(":CreatedUtc )");
sqlCommand.Append(";");
SqliteParameter[] arParams = new SqliteParameter[9];
arParams[0] = new SqliteParameter(":RowGuid", DbType.String, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = rowGuid.ToString();
arParams[1] = new SqliteParameter(":SiteGuid", DbType.String, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = siteGuid.ToString();
arParams[2] = new SqliteParameter(":ContentGuid", DbType.String, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = contentGuid.ToString();
arParams[3] = new SqliteParameter(":UserGuid", DbType.String, 36);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = userGuid.ToString();
arParams[4] = new SqliteParameter(":EmailAddress", DbType.String, 100);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = emailAddress;
arParams[5] = new SqliteParameter(":Rating", DbType.Int32);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = rating;
arParams[6] = new SqliteParameter(":Comments", DbType.Object);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = comments;
arParams[7] = new SqliteParameter(":IpAddress", DbType.String, 50);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = ipAddress;
arParams[8] = new SqliteParameter(":CreatedUtc", DbType.DateTime);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = createdUtc;
int rowsAffected = SqliteHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
/// <summary>
/// Updates a row in the cy_SurveyQuestions table. Returns true if row updated.
/// </summary>
/// <param name="questionGuid"> questionGuid </param>
/// <param name="pageGuid"> pageGuid </param>
/// <param name="questionText"> questionText </param>
/// <param name="questionTypeId"> questionTypeId </param>
/// <param name="answerIsRequired"> answerIsRequired </param>
/// <param name="questionOrder"> questionOrder </param>
/// <param name="validationMessage"> validationMessage </param>
/// <returns>bool</returns>
public static bool Update(
Guid questionGuid,
Guid pageGuid,
string questionText,
int questionTypeId,
bool answerIsRequired,
int questionOrder,
string validationMessage)
{
#region Bit Conversion
int intAnswerIsRequired;
if (answerIsRequired)
{
intAnswerIsRequired = 1;
}
else
{
intAnswerIsRequired = 0;
}
#endregion
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE cy_SurveyQuestions ");
sqlCommand.Append("SET ");
sqlCommand.Append("PageGuid = :PageGuid, ");
sqlCommand.Append("QuestionText = :QuestionText, ");
sqlCommand.Append("QuestionTypeId = :QuestionTypeId, ");
sqlCommand.Append("AnswerIsRequired = :AnswerIsRequired, ");
sqlCommand.Append("QuestionOrder = :QuestionOrder, ");
sqlCommand.Append("ValidationMessage = :ValidationMessage ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("QuestionGuid = :QuestionGuid ");
sqlCommand.Append(";");
SqliteParameter[] arParams = new SqliteParameter[7];
arParams[0] = new SqliteParameter(":QuestionGuid", DbType.String, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = questionGuid.ToString();
arParams[1] = new SqliteParameter(":PageGuid", DbType.String, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pageGuid.ToString();
arParams[2] = new SqliteParameter(":QuestionText", DbType.Object);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = questionText;
arParams[3] = new SqliteParameter(":QuestionTypeId", DbType.Int32);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = questionTypeId;
arParams[4] = new SqliteParameter(":AnswerIsRequired", DbType.Int32);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = intAnswerIsRequired;
arParams[5] = new SqliteParameter(":QuestionOrder", DbType.Int32);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = questionOrder;
arParams[6] = new SqliteParameter(":ValidationMessage", DbType.String, 256);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = validationMessage;
int rowsAffected = SqliteHelper.ExecuteNonQuery(GetConnectionString(), sqlCommand.ToString(), arParams);
return(rowsAffected > -1);
}
internal static void LoadSqliteValues <T>(TableInfo tableInfo, T entity, SqliteCommand command, DbContext dbContext)
{
var propertyColumnsDict = tableInfo.PropertyColumnNamesDict;
foreach (var propertyColumn in propertyColumnsDict)
{
var isShadowProperty = tableInfo.ShadowProperties.Contains(propertyColumn.Key);
string parameterName = propertyColumn.Key.Replace(".", "_");
object value;
if (!isShadowProperty)
{
if (propertyColumn.Key.Contains(".")) // ToDo: change IF clause to check for NavigationProperties, optimise, integrate with same code segment from LoadData method
{
var ownedPropertyNameList = propertyColumn.Key.Split('.');
var ownedPropertyName = ownedPropertyNameList[0];
var subPropertyName = ownedPropertyNameList[1];
var ownedFastProperty = tableInfo.FastPropertyDict[ownedPropertyName];
var ownedProperty = ownedFastProperty.Property;
var propertyType = Nullable.GetUnderlyingType(ownedProperty.GetType()) ?? ownedProperty.GetType();
if (!command.Parameters.Contains("@" + parameterName))
{
var parameter = new SqliteParameter($"@{parameterName}", propertyType);
command.Parameters.Add(parameter);
}
if (ownedProperty == null)
{
value = null;
}
else
{
var ownedPropertyValue = entity == null ? null : tableInfo.FastPropertyDict[ownedPropertyName].Get(entity);
var subPropertyFullName = $"{ownedPropertyName}_{subPropertyName}";
value = ownedPropertyValue == null ? null : tableInfo.FastPropertyDict[subPropertyFullName]?.Get(ownedPropertyValue);
}
}
else
{
value = tableInfo.FastPropertyDict[propertyColumn.Key].Get(entity);
}
}
else
{
if (tableInfo.BulkConfig.EnableShadowProperties)
{
// Get the shadow property value
value = dbContext.Entry(entity).Property(propertyColumn.Key).CurrentValue;
}
else
{
// Set the value for the discriminator column
value = entity.GetType().Name;
}
}
if (tableInfo.ConvertibleProperties.ContainsKey(propertyColumn.Key) && value != DBNull.Value)
{
value = tableInfo.ConvertibleProperties[propertyColumn.Key].ConvertToProvider.Invoke(value);
}
command.Parameters[$"@{parameterName}"].Value = value ?? DBNull.Value;
}
}
/// <summary>
/// Updates a row in the cy_TaskQueue table. Returns true if row updated.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="startUTC"> startUTC </param>
/// <param name="completeUTC"> completeUTC </param>
/// <param name="lastStatusUpdateUTC"> lastStatusUpdateUTC </param>
/// <param name="completeRatio"> completeRatio </param>
/// <param name="status"> status </param>
/// <returns>bool</returns>
public static bool Update(
Guid guid,
DateTime startUTC,
DateTime completeUTC,
DateTime lastStatusUpdateUTC,
double completeRatio,
string status)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE cy_TaskQueue ");
sqlCommand.Append("SET ");
if (startUTC > DateTime.MinValue)
{
sqlCommand.Append("StartUTC = :StartUTC, ");
}
if (completeUTC > DateTime.MinValue)
{
sqlCommand.Append("CompleteUTC = :CompleteUTC, ");
}
sqlCommand.Append("LastStatusUpdateUTC = :LastStatusUpdateUTC, ");
sqlCommand.Append("CompleteRatio = :CompleteRatio, ");
sqlCommand.Append("Status = :Status ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("Guid = :Guid ");
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(":StartUTC", DbType.DateTime);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = startUTC;
arParams[2] = new SqliteParameter(":CompleteUTC", DbType.DateTime);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = completeUTC;
arParams[3] = new SqliteParameter(":LastStatusUpdateUTC", DbType.DateTime);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = lastStatusUpdateUTC;
arParams[4] = new SqliteParameter(":CompleteRatio", DbType.Double);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = completeRatio;
arParams[5] = new SqliteParameter(":Status", DbType.String, 255);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = status;
int rowsAffected = SqliteHelper.ExecuteNonQuery(GetConnectionString(), sqlCommand.ToString(), arParams);
return(rowsAffected > -1);
}
// todo :: [TestCategory("NotWorking")]
// fails randomly :)
public void InsertRandomValuesWithParameter()
{
SqliteParameter textP = new SqliteParameter();
textP.ParameterName = "textP";
textP.SourceColumn = "t";
SqliteParameter floatP = new SqliteParameter();
floatP.ParameterName = "floatP";
floatP.SourceColumn = "nu";
SqliteParameter integerP = new SqliteParameter();
integerP.ParameterName = "integerP";
integerP.SourceColumn = "i";
SqliteParameter blobP = new SqliteParameter();
blobP.ParameterName = "blobP";
blobP.SourceColumn = "b";
Random random = new Random();
StringBuilder builder = new StringBuilder();
for (int k = 0; k < random.Next(7, 100); k++)
{
builder.Append((char)random.Next(127));
}
SqliteCommand createCommand = new SqliteCommand("CREATE TABLE t1(t TEXT, f FLOAT, i INTEGER, b BLOB);", _conn);
SqliteCommand insertCmd = new SqliteCommand("DELETE FROM t1; INSERT INTO t1 (t, f, i, b ) VALUES(:textP,:floatP,:integerP,:blobP)", _conn);
insertCmd.Parameters.Add(textP);
insertCmd.Parameters.Add(floatP);
insertCmd.Parameters.Add(blobP);
insertCmd.Parameters.Add(integerP);
string stringValue = builder.ToString();
double doubleValue = Convert.ToDouble(random.Next(999));
long intValue = random.Next(999);
byte[] blobValue = System.Text.Encoding.UTF8.GetBytes("\u05D0\u05D1\u05D2" + builder.ToString());
textP.Value = stringValue;
floatP.Value = doubleValue;
integerP.Value = intValue;
blobP.Value = blobValue;
SqliteCommand selectCmd = new SqliteCommand("SELECT * from t1", _conn);
using (_conn)
{
_conn.Open();
createCommand.ExecuteNonQuery();
int res = insertCmd.ExecuteNonQuery();
Assert.AreEqual(res, 1);
using (IDataReader reader = selectCmd.ExecuteReader())
{
Assert.AreEqual(reader.Read(), true);
Assert.AreEqual(reader["t"], stringValue);
Assert.AreEqual(reader["f"], doubleValue);
Assert.AreEqual(reader["i"], intValue);
var compareValue = System.Text.Encoding.UTF8.GetString(blobValue, 0, blobValue.Length);
var loadedBytes = ((byte[])reader["b"]);
var fromReader = System.Text.Encoding.UTF8.GetString(loadedBytes, 0, loadedBytes.Length);
Assert.AreEqual(fromReader, compareValue);
Assert.AreEqual(reader.Read(), false);
}
}
}
public override void InsertIntoDB(SecurityDayQuotationDomain dayQuotation)
{
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO SecurityDayQuotation");
sb.Append("(");
sb.Append("SecurityCode,TxDate,ClosePrice,HighPrice,LowPrice,OpenPrice,LastClosePrice,PriceChange,Change,TurnOver,VolumeTurnOver,PriceTurnOver,MarketValue,NegoValue");
sb.Append(")");
sb.Append("VALUES");
sb.Append("(");
sb.Append("@SecurityCode,@TxDate,@ClosePrice,@HighPrice,@LowPrice,@OpenPrice,@LastClosePrice,@PriceChange,@Change,@TurnOver,@VolumeTurnOver,@PriceTurnOver,@MarketValue,@NegoValue");
sb.Append(")");
SqliteParameter prmSecurityCode = new SqliteParameter("@SecurityCode", DbType.String)
{
Value = dayQuotation.SecurityCode
};
SqliteParameter prmTxDate = new SqliteParameter("@TxDate", DbType.DateTime)
{
Value = dayQuotation.TxDate
};
SqliteParameter prmClosePrice = new SqliteParameter("@ClosePrice", dayQuotation.ClosePrice);
SqliteParameter prmHighPrice = new SqliteParameter("@HighPrice", DbType.Single)
{
Value = dayQuotation.HighPrice
};
SqliteParameter prmLowPrice = new SqliteParameter("@LowPrice", DbType.Single)
{
Value = dayQuotation.LowPrice
};
SqliteParameter prmOpenPrice = new SqliteParameter("@OpenPrice", DbType.Single)
{
Value = dayQuotation.OpenPrice
};
SqliteParameter prmLastClosePrice = new SqliteParameter("@LastClosePrice", DbType.Single)
{
Value = dayQuotation.LastClosePrice
};
SqliteParameter prmPriceChange = new SqliteParameter("@PriceChange", DbType.Single)
{
Value = dayQuotation.PriceChange
};
SqliteParameter prmChange = new SqliteParameter("@Change", DbType.Single)
{
Value = dayQuotation.Change
};
SqliteParameter prmTurnOver = new SqliteParameter("@TurnOver", DbType.Single)
{
Value = dayQuotation.TurnOver
};
SqliteParameter prmVolumeTurnOver = new SqliteParameter("@VolumeTurnOver", DbType.Single)
{
Value = dayQuotation.VolumeTurnOver
};
SqliteParameter prmPriceTurnOver = new SqliteParameter("@PriceTurnOver", DbType.Single)
{
Value = dayQuotation.PriceTurnOver
};
SqliteParameter prmMarketValue = new SqliteParameter("@MarketValue", DbType.Single)
{
Value = dayQuotation.MarketValue
};
SqliteParameter prmNegoValue = new SqliteParameter("@NegoValue", DbType.Single)
{
Value = dayQuotation.NegoValue
};
dbContext.ExecuteNoQuery(sb.ToString(), prmSecurityCode, prmTxDate, prmClosePrice, prmHighPrice, prmLowPrice, prmOpenPrice, prmLastClosePrice,
prmPriceChange, prmChange, prmTurnOver, prmVolumeTurnOver, prmPriceTurnOver, prmMarketValue, prmNegoValue);
}
/// <summary>
/// Updates a row in the mp_PayPalLog table. Returns true if row updated.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
/// <param name="createdUtc"> createdUtc </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="userGuid"> userGuid </param>
/// <param name="storeGuid"> storeGuid </param>
/// <param name="cartGuid"> cartGuid </param>
/// <param name="requestType"> requestType </param>
/// <param name="apiVersion"> apiVersion </param>
/// <param name="rawResponse"> rawResponse </param>
/// <param name="token"> token </param>
/// <param name="payerId"> payerId </param>
/// <param name="transactionId"> transactionId </param>
/// <param name="paymentType"> paymentType </param>
/// <param name="paymentStatus"> paymentStatus </param>
/// <param name="pendingReason"> pendingReason </param>
/// <param name="reasonCode"> reasonCode </param>
/// <param name="currencyCode"> currencyCode </param>
/// <param name="exchangeRate"> exchangeRate </param>
/// <param name="cartTotal"> cartTotal </param>
/// <param name="payPalAmt"> payPalAmt </param>
/// <param name="taxAmt"> taxAmt </param>
/// <param name="feeAmt"> feeAmt </param>
/// <param name="settleAmt"> settleAmt </param>
/// <returns>bool</returns>
public static bool Update(
Guid rowGuid,
DateTime createdUtc,
Guid siteGuid,
Guid userGuid,
Guid storeGuid,
Guid cartGuid,
string requestType,
string apiVersion,
string rawResponse,
string token,
string payerId,
string transactionId,
string paymentType,
string paymentStatus,
string pendingReason,
string reasonCode,
string currencyCode,
decimal exchangeRate,
decimal cartTotal,
decimal payPalAmt,
decimal taxAmt,
decimal feeAmt,
decimal settleAmt)
{
#region Bit Conversion
#endregion
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_PayPalLog ");
sqlCommand.Append("SET ");
sqlCommand.Append("CreatedUtc = :CreatedUtc, ");
sqlCommand.Append("SiteGuid = :SiteGuid, ");
sqlCommand.Append("UserGuid = :UserGuid, ");
sqlCommand.Append("StoreGuid = :StoreGuid, ");
sqlCommand.Append("CartGuid = :CartGuid, ");
sqlCommand.Append("RequestType = :RequestType, ");
sqlCommand.Append("ApiVersion = :ApiVersion, ");
sqlCommand.Append("RawResponse = :RawResponse, ");
sqlCommand.Append("Token = :Token, ");
sqlCommand.Append("PayerId = :PayerId, ");
sqlCommand.Append("TransactionId = :TransactionId, ");
sqlCommand.Append("PaymentType = :PaymentType, ");
sqlCommand.Append("PaymentStatus = :PaymentStatus, ");
sqlCommand.Append("PendingReason = :PendingReason, ");
sqlCommand.Append("ReasonCode = :ReasonCode, ");
sqlCommand.Append("CurrencyCode = :CurrencyCode, ");
sqlCommand.Append("ExchangeRate = :ExchangeRate, ");
sqlCommand.Append("CartTotal = :CartTotal, ");
sqlCommand.Append("PayPalAmt = :PayPalAmt, ");
sqlCommand.Append("TaxAmt = :TaxAmt, ");
sqlCommand.Append("FeeAmt = :FeeAmt, ");
sqlCommand.Append("SettleAmt = :SettleAmt ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("RowGuid = :RowGuid ");
sqlCommand.Append(";");
SqliteParameter[] arParams = new SqliteParameter[23];
arParams[0] = new SqliteParameter(":RowGuid", DbType.String, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = rowGuid.ToString();
arParams[1] = new SqliteParameter(":CreatedUtc", DbType.DateTime);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = createdUtc;
arParams[2] = new SqliteParameter(":SiteGuid", DbType.String, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = siteGuid.ToString();
arParams[3] = new SqliteParameter(":UserGuid", DbType.String, 36);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = userGuid.ToString();
arParams[4] = new SqliteParameter(":StoreGuid", DbType.String, 36);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = storeGuid.ToString();
arParams[5] = new SqliteParameter(":CartGuid", DbType.String, 36);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = cartGuid.ToString();
arParams[6] = new SqliteParameter(":RequestType", DbType.String, 255);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = requestType;
arParams[7] = new SqliteParameter(":ApiVersion", DbType.String, 50);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = apiVersion;
arParams[8] = new SqliteParameter(":RawResponse", DbType.Object);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = rawResponse;
arParams[9] = new SqliteParameter(":Token", DbType.String, 50);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = token;
arParams[10] = new SqliteParameter(":PayerId", DbType.String, 50);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = payerId;
arParams[11] = new SqliteParameter(":TransactionId", DbType.String, 50);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = transactionId;
arParams[12] = new SqliteParameter(":PaymentType", DbType.String, 10);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = paymentType;
arParams[13] = new SqliteParameter(":PaymentStatus", DbType.String, 50);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = paymentStatus;
arParams[14] = new SqliteParameter(":PendingReason", DbType.String, 255);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = pendingReason;
arParams[15] = new SqliteParameter(":ReasonCode", DbType.String, 50);
arParams[15].Direction = ParameterDirection.Input;
arParams[15].Value = reasonCode;
arParams[16] = new SqliteParameter(":CurrencyCode", DbType.String, 50);
arParams[16].Direction = ParameterDirection.Input;
arParams[16].Value = currencyCode;
arParams[17] = new SqliteParameter(":ExchangeRate", DbType.Decimal);
arParams[17].Direction = ParameterDirection.Input;
arParams[17].Value = exchangeRate;
arParams[18] = new SqliteParameter(":CartTotal", DbType.Decimal);
arParams[18].Direction = ParameterDirection.Input;
arParams[18].Value = cartTotal;
arParams[19] = new SqliteParameter(":PayPalAmt", DbType.Decimal);
arParams[19].Direction = ParameterDirection.Input;
arParams[19].Value = payPalAmt;
arParams[20] = new SqliteParameter(":TaxAmt", DbType.Decimal);
arParams[20].Direction = ParameterDirection.Input;
arParams[20].Value = taxAmt;
arParams[21] = new SqliteParameter(":FeeAmt", DbType.Decimal);
arParams[21].Direction = ParameterDirection.Input;
arParams[21].Value = feeAmt;
arParams[22] = new SqliteParameter(":SettleAmt", DbType.Decimal);
arParams[22].Direction = ParameterDirection.Input;
arParams[22].Value = settleAmt;
int rowsAffected = SqliteHelper.ExecuteNonQuery(GetConnectionString(), sqlCommand.ToString(), arParams);
return(rowsAffected > -1);
}
public static bool UpdateModuleSetting(
Guid moduleGuid,
int moduleId,
string settingName,
string settingValue)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT count(*) ");
sqlCommand.Append("FROM cy_ModuleSettings ");
sqlCommand.Append("WHERE ModuleID = :ModuleID ");
sqlCommand.Append("AND SettingName = :SettingName ;");
SqliteParameter[] arParams = new SqliteParameter[2];
arParams[0] = new SqliteParameter(":ModuleID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
arParams[1] = new SqliteParameter(":SettingName", DbType.String, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = settingName;
int count = Convert.ToInt32(SqliteHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams).ToString());
sqlCommand = new StringBuilder();
int rowsAffected = 0;
if (count > 0)
{
sqlCommand.Append("UPDATE cy_ModuleSettings ");
sqlCommand.Append("SET SettingValue = :SettingValue ");
sqlCommand.Append("WHERE ModuleID = :ModuleID ");
sqlCommand.Append("AND SettingName = :SettingName ");
arParams = new SqliteParameter[3];
arParams[0] = new SqliteParameter(":ModuleID", DbType.Int32);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
arParams[1] = new SqliteParameter(":SettingName", DbType.String, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = settingName;
arParams[2] = new SqliteParameter(":SettingValue", DbType.String, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = settingValue;
rowsAffected = SqliteHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
else
{
return(false);
//return CreateModuleSetting(
// Guid.NewGuid(),
// moduleGuid,
// moduleId,
// settingName,
// settingValue,
// "TextBox",
// string.Empty);
}
}
/// <summary>
/// Inserts a row in the mp_PayPalLog table. Returns rows affected count.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
/// <param name="createdUtc"> createdUtc </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="userGuid"> userGuid </param>
/// <param name="storeGuid"> storeGuid </param>
/// <param name="cartGuid"> cartGuid </param>
/// <param name="requestType"> requestType </param>
/// <param name="apiVersion"> apiVersion </param>
/// <param name="rawResponse"> rawResponse </param>
/// <param name="token"> token </param>
/// <param name="payerId"> payerId </param>
/// <param name="transactionId"> transactionId </param>
/// <param name="paymentType"> paymentType </param>
/// <param name="paymentStatus"> paymentStatus </param>
/// <param name="pendingReason"> pendingReason </param>
/// <param name="reasonCode"> reasonCode </param>
/// <param name="currencyCode"> currencyCode </param>
/// <param name="exchangeRate"> exchangeRate </param>
/// <param name="cartTotal"> cartTotal </param>
/// <param name="payPalAmt"> payPalAmt </param>
/// <param name="taxAmt"> taxAmt </param>
/// <param name="feeAmt"> feeAmt </param>
/// <param name="settleAmt"> settleAmt </param>
/// <returns>int</returns>
public static int Create(
Guid rowGuid,
DateTime createdUtc,
Guid siteGuid,
Guid userGuid,
Guid storeGuid,
Guid cartGuid,
string requestType,
string apiVersion,
string rawResponse,
string token,
string payerId,
string transactionId,
string paymentType,
string paymentStatus,
string pendingReason,
string reasonCode,
string currencyCode,
decimal exchangeRate,
decimal cartTotal,
decimal payPalAmt,
decimal taxAmt,
decimal feeAmt,
decimal settleAmt,
string providerName,
string returnUrl,
string serializedObject,
string pdtProviderName,
string ipnProviderName,
string response)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_PayPalLog (");
sqlCommand.Append("RowGuid, ");
sqlCommand.Append("CreatedUtc, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("UserGuid, ");
sqlCommand.Append("StoreGuid, ");
sqlCommand.Append("CartGuid, ");
sqlCommand.Append("RequestType, ");
sqlCommand.Append("ApiVersion, ");
sqlCommand.Append("RawResponse, ");
sqlCommand.Append("Token, ");
sqlCommand.Append("PayerId, ");
sqlCommand.Append("TransactionId, ");
sqlCommand.Append("PaymentType, ");
sqlCommand.Append("PaymentStatus, ");
sqlCommand.Append("PendingReason, ");
sqlCommand.Append("ReasonCode, ");
sqlCommand.Append("CurrencyCode, ");
sqlCommand.Append("ExchangeRate, ");
sqlCommand.Append("CartTotal, ");
sqlCommand.Append("PayPalAmt, ");
sqlCommand.Append("TaxAmt, ");
sqlCommand.Append("FeeAmt, ");
sqlCommand.Append("ProviderName, ");
sqlCommand.Append("ReturnUrl, ");
sqlCommand.Append("SerializedObject, ");
sqlCommand.Append("PDTProviderName, ");
sqlCommand.Append("IPNProviderName, ");
sqlCommand.Append("Response, ");
sqlCommand.Append("SettleAmt )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":RowGuid, ");
sqlCommand.Append(":CreatedUtc, ");
sqlCommand.Append(":SiteGuid, ");
sqlCommand.Append(":UserGuid, ");
sqlCommand.Append(":StoreGuid, ");
sqlCommand.Append(":CartGuid, ");
sqlCommand.Append(":RequestType, ");
sqlCommand.Append(":ApiVersion, ");
sqlCommand.Append(":RawResponse, ");
sqlCommand.Append(":Token, ");
sqlCommand.Append(":PayerId, ");
sqlCommand.Append(":TransactionId, ");
sqlCommand.Append(":PaymentType, ");
sqlCommand.Append(":PaymentStatus, ");
sqlCommand.Append(":PendingReason, ");
sqlCommand.Append(":ReasonCode, ");
sqlCommand.Append(":CurrencyCode, ");
sqlCommand.Append(":ExchangeRate, ");
sqlCommand.Append(":CartTotal, ");
sqlCommand.Append(":PayPalAmt, ");
sqlCommand.Append(":TaxAmt, ");
sqlCommand.Append(":FeeAmt, ");
sqlCommand.Append(":ProviderName, ");
sqlCommand.Append(":ReturnUrl, ");
sqlCommand.Append(":SerializedObject, ");
sqlCommand.Append(":PDTProviderName, ");
sqlCommand.Append(":IPNProviderName, ");
sqlCommand.Append(":Response, ");
sqlCommand.Append(":SettleAmt )");
sqlCommand.Append(";");
SqliteParameter[] arParams = new SqliteParameter[29];
arParams[0] = new SqliteParameter(":RowGuid", DbType.String, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = rowGuid.ToString();
arParams[1] = new SqliteParameter(":CreatedUtc", DbType.DateTime);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = createdUtc;
arParams[2] = new SqliteParameter(":SiteGuid", DbType.String, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = siteGuid.ToString();
arParams[3] = new SqliteParameter(":UserGuid", DbType.String, 36);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = userGuid.ToString();
arParams[4] = new SqliteParameter(":StoreGuid", DbType.String, 36);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = storeGuid.ToString();
arParams[5] = new SqliteParameter(":CartGuid", DbType.String, 36);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = cartGuid.ToString();
arParams[6] = new SqliteParameter(":RequestType", DbType.String, 255);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = requestType;
arParams[7] = new SqliteParameter(":ApiVersion", DbType.String, 50);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = apiVersion;
arParams[8] = new SqliteParameter(":RawResponse", DbType.Object);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = rawResponse;
arParams[9] = new SqliteParameter(":Token", DbType.String, 50);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = token;
arParams[10] = new SqliteParameter(":PayerId", DbType.String, 50);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = payerId;
arParams[11] = new SqliteParameter(":TransactionId", DbType.String, 50);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = transactionId;
arParams[12] = new SqliteParameter(":PaymentType", DbType.String, 10);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = paymentType;
arParams[13] = new SqliteParameter(":PaymentStatus", DbType.String, 50);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = paymentStatus;
arParams[14] = new SqliteParameter(":PendingReason", DbType.String, 255);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = pendingReason;
arParams[15] = new SqliteParameter(":ReasonCode", DbType.String, 50);
arParams[15].Direction = ParameterDirection.Input;
arParams[15].Value = reasonCode;
arParams[16] = new SqliteParameter(":CurrencyCode", DbType.String, 50);
arParams[16].Direction = ParameterDirection.Input;
arParams[16].Value = currencyCode;
arParams[17] = new SqliteParameter(":ExchangeRate", DbType.Decimal);
arParams[17].Direction = ParameterDirection.Input;
arParams[17].Value = exchangeRate;
arParams[18] = new SqliteParameter(":CartTotal", DbType.Decimal);
arParams[18].Direction = ParameterDirection.Input;
arParams[18].Value = cartTotal;
arParams[19] = new SqliteParameter(":PayPalAmt", DbType.Decimal);
arParams[19].Direction = ParameterDirection.Input;
arParams[19].Value = payPalAmt;
arParams[20] = new SqliteParameter(":TaxAmt", DbType.Decimal);
arParams[20].Direction = ParameterDirection.Input;
arParams[20].Value = taxAmt;
arParams[21] = new SqliteParameter(":FeeAmt", DbType.Decimal);
arParams[21].Direction = ParameterDirection.Input;
arParams[21].Value = feeAmt;
arParams[22] = new SqliteParameter(":SettleAmt", DbType.Decimal);
arParams[22].Direction = ParameterDirection.Input;
arParams[22].Value = settleAmt;
arParams[23] = new SqliteParameter(":ProviderName", DbType.String, 255);
arParams[23].Direction = ParameterDirection.Input;
arParams[23].Value = providerName;
arParams[24] = new SqliteParameter(":ReturnUrl", DbType.String, 255);
arParams[24].Direction = ParameterDirection.Input;
arParams[24].Value = returnUrl;
arParams[25] = new SqliteParameter(":SerializedObject", DbType.Object);
arParams[25].Direction = ParameterDirection.Input;
arParams[25].Value = serializedObject;
arParams[26] = new SqliteParameter(":PDTProviderName", DbType.String, 255);
arParams[26].Direction = ParameterDirection.Input;
arParams[26].Value = pdtProviderName;
arParams[27] = new SqliteParameter(":IPNProviderName", DbType.String, 255);
arParams[27].Direction = ParameterDirection.Input;
arParams[27].Value = ipnProviderName;
arParams[28] = new SqliteParameter(":Response", DbType.String, 255);
arParams[28].Direction = ParameterDirection.Input;
arParams[28].Value = response;
int rowsAffected = 0;
rowsAffected = SqliteHelper.ExecuteNonQuery(GetConnectionString(), sqlCommand.ToString(), arParams);
return(rowsAffected);
}
// fails randomly :)
public void InsertRandomValuesWithParameter()
{
SqliteParameter textP = new SqliteParameter();
textP.ParameterName = "textP";
textP.SourceColumn = "t";
SqliteParameter floatP = new SqliteParameter();
floatP.ParameterName = "floatP";
floatP.SourceColumn = "nu";
SqliteParameter integerP = new SqliteParameter();
integerP.ParameterName = "integerP";
integerP.SourceColumn = "i";
SqliteParameter blobP = new SqliteParameter();
blobP.ParameterName = "blobP";
blobP.SourceColumn = "b";
Random random = new Random();
StringBuilder builder = new StringBuilder();
for (int k = 0; k < random.Next(0, 100); k++)
{
builder.Append((char)random.Next(65536));
}
SqliteCommand insertCmd = new SqliteCommand("DELETE FROM t1; INSERT INTO t1 (t, f, i, b ) VALUES(:textP,:floatP,:integerP,:blobP)", _conn);
insertCmd.Parameters.Add(textP);
insertCmd.Parameters.Add(floatP);
insertCmd.Parameters.Add(blobP);
insertCmd.Parameters.Add(integerP);
textP.Value = builder.ToString();
floatP.Value = Convert.ToInt64(random.Next(999));
integerP.Value = random.Next(999);
blobP.Value = System.Text.Encoding.UTF8.GetBytes("\u05D0\u05D1\u05D2" + builder.ToString());
SqliteCommand selectCmd = new SqliteCommand("SELECT * from t1", _conn);
using (_conn)
{
_conn.Open();
int res = insertCmd.ExecuteNonQuery();
Assert.AreEqual(res, 1);
using (IDataReader reader = selectCmd.ExecuteReader()) {
Assert.AreEqual(reader.Read(), true);
Assert.AreEqual(reader["t"], textP.Value);
Assert.AreEqual(reader["f"], floatP.Value);
Assert.AreEqual(reader["i"], integerP.Value);
object compareValue;
if (blobP.Value is byte[])
{
compareValue = System.Text.Encoding.UTF8.GetString((byte[])blobP.Value);
}
else
{
compareValue = blobP.Value;
}
Assert.AreEqual(reader["b"], compareValue);
Assert.AreEqual(reader.Read(), false);
}
}
}
/// <summary>
/// Inserts a row in the cy_EmailSendQueue table. Returns rows affected count.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="moduleGuid"> moduleGuid </param>
/// <param name="userGuid"> userGuid </param>
/// <param name="specialGuid1"> specialGuid1 </param>
/// <param name="specialGuid2"> specialGuid2 </param>
/// <param name="fromAddress"> fromAddress </param>
/// <param name="replyTo"> replyTo </param>
/// <param name="toAddress"> toAddress </param>
/// <param name="ccAddress"> ccAddress </param>
/// <param name="bccAddress"> bccAddress </param>
/// <param name="subject"> subject </param>
/// <param name="textBody"> textBody </param>
/// <param name="htmlBody"> htmlBody </param>
/// <param name="type"> type </param>
/// <param name="dateToSend"> dateToSend </param>
/// <param name="createdUtc"> createdUtc </param>
/// <returns>int</returns>
public static int Create(
Guid guid,
Guid siteGuid,
Guid moduleGuid,
Guid userGuid,
Guid specialGuid1,
Guid specialGuid2,
string fromAddress,
string replyTo,
string toAddress,
string ccAddress,
string bccAddress,
string subject,
string textBody,
string htmlBody,
string type,
DateTime dateToSend,
DateTime createdUtc)
{
#region Bit Conversion
#endregion
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO cy_EmailSendQueue (");
sqlCommand.Append("Guid, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("ModuleGuid, ");
sqlCommand.Append("UserGuid, ");
sqlCommand.Append("SpecialGuid1, ");
sqlCommand.Append("SpecialGuid2, ");
sqlCommand.Append("FromAddress, ");
sqlCommand.Append("ReplyTo, ");
sqlCommand.Append("ToAddress, ");
sqlCommand.Append("CcAddress, ");
sqlCommand.Append("BccAddress, ");
sqlCommand.Append("Subject, ");
sqlCommand.Append("TextBody, ");
sqlCommand.Append("HtmlBody, ");
sqlCommand.Append("Type, ");
sqlCommand.Append("DateToSend, ");
sqlCommand.Append("CreatedUtc )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":Guid, ");
sqlCommand.Append(":SiteGuid, ");
sqlCommand.Append(":ModuleGuid, ");
sqlCommand.Append(":UserGuid, ");
sqlCommand.Append(":SpecialGuid1, ");
sqlCommand.Append(":SpecialGuid2, ");
sqlCommand.Append(":FromAddress, ");
sqlCommand.Append(":ReplyTo, ");
sqlCommand.Append(":ToAddress, ");
sqlCommand.Append(":CcAddress, ");
sqlCommand.Append(":BccAddress, ");
sqlCommand.Append(":Subject, ");
sqlCommand.Append(":TextBody, ");
sqlCommand.Append(":HtmlBody, ");
sqlCommand.Append(":Type, ");
sqlCommand.Append(":DateToSend, ");
sqlCommand.Append(":CreatedUtc )");
sqlCommand.Append(";");
SqliteParameter[] arParams = new SqliteParameter[17];
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(":ModuleGuid", DbType.String, 36);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = moduleGuid.ToString();
arParams[3] = new SqliteParameter(":UserGuid", DbType.String, 36);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = userGuid.ToString();
arParams[4] = new SqliteParameter(":SpecialGuid1", DbType.String, 36);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = specialGuid1.ToString();
arParams[5] = new SqliteParameter(":SpecialGuid2", DbType.String, 36);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = specialGuid2.ToString();
arParams[6] = new SqliteParameter(":FromAddress", DbType.String, 100);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = fromAddress;
arParams[7] = new SqliteParameter(":ReplyTo", DbType.String, 100);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = replyTo;
arParams[8] = new SqliteParameter(":ToAddress", DbType.String, 255);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = toAddress;
arParams[9] = new SqliteParameter(":CcAddress", DbType.String, 255);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = ccAddress;
arParams[10] = new SqliteParameter(":BccAddress", DbType.String, 255);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = bccAddress;
arParams[11] = new SqliteParameter(":Subject", DbType.String, 255);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = subject;
arParams[12] = new SqliteParameter(":TextBody", DbType.Object);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = textBody;
arParams[13] = new SqliteParameter(":HtmlBody", DbType.Object);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = htmlBody;
arParams[14] = new SqliteParameter(":Type", DbType.String, 50);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = type;
arParams[15] = new SqliteParameter(":DateToSend", DbType.DateTime);
arParams[15].Direction = ParameterDirection.Input;
arParams[15].Value = dateToSend;
arParams[16] = new SqliteParameter(":CreatedUtc", DbType.DateTime);
arParams[16].Direction = ParameterDirection.Input;
arParams[16].Value = createdUtc;
int rowsAffected = SqliteHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
/// <summary>
/// Gets a page of data from the mp_ContentTemplate table.
/// </summary>
/// <param name="pageNumber">The page number.</param>
/// <param name="pageSize">Size of the page.</param>
/// <param name="totalPages">total pages</param>
public static IDataReader GetPage(
Guid siteGuid,
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCount(siteGuid);
if (pageSize > 0)
{
totalPages = totalRows / pageSize;
}
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_ContentTemplate ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = :SiteGuid ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("Title ");
sqlCommand.Append("LIMIT :PageSize ");
if (pageNumber > 1)
{
sqlCommand.Append("OFFSET :OffsetRows ");
}
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(":PageSize", DbType.Int32);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pageSize;
arParams[2] = new SqliteParameter(":OffsetRows", DbType.Int32);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = pageLowerBound;
return(SqliteHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
internal static SqliteCommand GetSqliteCommand <T>(DbContext context, IList <T> entities, TableInfo tableInfo, SqliteConnection connection, SqliteTransaction transaction)
{
SqliteCommand command = connection.CreateCommand();
command.Transaction = transaction;
OperationType operationType = tableInfo.BulkConfig.OperationType;
if (operationType == OperationType.Insert)
{
command.CommandText = SqlQueryBuilderSqlite.InsertIntoTable(tableInfo, OperationType.Insert);
}
else if (operationType == OperationType.InsertOrUpdate)
{
command.CommandText = SqlQueryBuilderSqlite.InsertIntoTable(tableInfo, OperationType.InsertOrUpdate);
}
else if (operationType == OperationType.InsertOrUpdateDelete)
{
throw new NotSupportedException("Sqlite supports only UPSERT(analog for MERGE WHEN MATCHED) but does not have functionality to do: 'WHEN NOT MATCHED BY SOURCE THEN DELETE'" +
"What can be done is to read all Data, find rows that are not is input List, then with those do the BulkDelete.");
}
else if (operationType == OperationType.Update)
{
command.CommandText = SqlQueryBuilderSqlite.UpdateSetTable(tableInfo);
}
else if (operationType == OperationType.Delete)
{
command.CommandText = SqlQueryBuilderSqlite.DeleteFromTable(tableInfo);
}
var type = tableInfo.HasAbstractList ? entities[0].GetType() : typeof(T);
var entityType = context.Model.FindEntityType(type);
var entityPropertiesDict = entityType.GetProperties().Where(a => tableInfo.PropertyColumnNamesDict.ContainsKey(a.Name)).ToDictionary(a => a.Name, a => a);
var properties = type.GetProperties();
foreach (var property in properties)
{
if (entityPropertiesDict.ContainsKey(property.Name))
{
var columnName = entityPropertiesDict[property.Name].GetColumnName();
var propertyType = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
/*var sqliteType = SqliteType.Text; // "String" || "Decimal" || "DateTime"
* if (propertyType.Name == "Int16" || propertyType.Name == "Int32" || propertyType.Name == "Int64")
* sqliteType = SqliteType.Integer;
* if (propertyType.Name == "Float" || propertyType.Name == "Double")
* sqliteType = SqliteType.Real;
* if (propertyType.Name == "Guid" )
* sqliteType = SqliteType.Blob; */
var parameter = new SqliteParameter($"@{columnName}", propertyType); // ,sqliteType // ,null
command.Parameters.Add(parameter);
}
}
var shadowProperties = tableInfo.ShadowProperties;
foreach (var shadowProperty in shadowProperties)
{
var parameter = new SqliteParameter($"@{shadowProperty}", typeof(string));
command.Parameters.Add(parameter);
}
command.Prepare(); // Not Required (check if same efficiency when removed)
return(command);
}
/// <summary>
/// Avoids the additional database trip that using SqliteCommandBuilder requires.
/// </summary>
/// <returns>The insert command.</returns>
/// <param name="table">Table.</param>
/// <param name="values">Values.</param>
/// <param name="conflictResolutionStrategy">Conflict resolution strategy.</param>
SqliteCommand GetInsertCommand (String table, ContentValues values, ConflictResolutionStrategy conflictResolutionStrategy)
{
var builder = new StringBuilder("INSERT");
if (conflictResolutionStrategy != ConflictResolutionStrategy.None) {
builder.Append(" OR ");
builder.Append(conflictResolutionStrategy);
}
builder.Append(" INTO ");
builder.Append(table);
builder.Append(" (");
// Append our content column names and create our SQL parameters.
var valueSet = values.ValueSet();
var sqlParams = new SqliteParameter[valueSet.LongCount()];
var valueBuilder = new StringBuilder();
var index = 0L;
foreach(var column in valueSet)
{
if (index > 0) {
builder.Append(",");
valueBuilder.Append(",");
}
builder.AppendFormat( "{0}", column.Key);
valueBuilder.AppendFormat("@{0}", column.Key);
sqlParams[index++] = new SqliteParameter(column.Key, column.Value);
}
builder.Append(") VALUES (");
builder.Append(valueBuilder);
builder.Append(")");
var sql = builder.ToString();
var command = new SqliteCommand(sql, Connection, currentTransaction);
command.Parameters.Clear();
command.Parameters.AddRange(sqlParams);
return command;
}
void Parameterize(SqliteParameterCollection command, object idValue)
{
if (null != idValue)
{
var pkf = PrimaryKeyField;
if (!command.Contains(pkf))
{
var p = new SqliteParameter("@" + pkf, idValue);
command.Add(p);
}
else
{
command[PrimaryKeyField].ResetDbType();
command[PrimaryKeyField].Value = idValue;
}
}
}
