// 關閉使用者
public static void CloseAccount_Api(Int64 xid, string upd_user)
{
try
{
string sqlStmt = @"UPDATE b2b.b2d_account_api
SET enable = false,upd_user=:upd_user
WHERE xid=:xid";
NpgsqlParameter[] sqlParams = new NpgsqlParameter[] {
new NpgsqlParameter("xid", xid),
new NpgsqlParameter("upd_user", upd_user)
};
NpgsqlHelper.ExecuteNonQuery(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, sqlParams);
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0}.{1}", ex.Message, ex.StackTrace);
throw ex;
}
}
/// <summary>
/// Deletes a row from the mp_SurveyQuestions table. Returns true if row deleted.
/// </summary>
/// <param name="questionGuid"> questionGuid </param>
/// <returns>bool</returns>
public static bool Delete(
Guid questionGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_surveyquestions ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("questionguid = :questionguid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("questionguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = questionGuid.ToString();
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Set the current survey for a module
/// </summary>
/// <param name="surveyGuid"></param>
/// <param name="moduleId"></param>
public static void AddToModule(Guid surveyGuid, int moduleId)
{
string sqlCommand = @"
DELETE
FROM
mp_surveymodules
WHERE
moduleid = :moduleid;
INSERT INTO
mp_surveymodules (
surveyguid,
moduleid
)
VALUES (
:surveyguid,
:moduleid
);" ;
var arParams = new List <NpgsqlParameter>
{
new NpgsqlParameter("surveyguid", NpgsqlTypes.NpgsqlDbType.Char, 36)
{
Direction = ParameterDirection.Input,
Value = surveyGuid.ToString()
},
new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input,
Value = moduleId
}
};
NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand,
arParams.ToArray()
);
}
public static void UpdateLicenses(Int64 xid, string[] license_url, string upd_user)
{
try
{
string sqlStmt = @"UPDATE b2b.b2d_company SET {UPD_FIELDS},
upd_user=:upd_user, upd_datetime=Now()
WHERE xid=:xid";
List <NpgsqlParameter> sqlParams = new List <NpgsqlParameter>();
sqlParams.Add(new NpgsqlParameter("xid", xid));
sqlParams.Add(new NpgsqlParameter("upd_user", upd_user));
var upd_fileds = "";
switch (license_url.Length)
{
case 1:
upd_fileds = "comp_license_2=:comp_license_2";
sqlParams.Add(new NpgsqlParameter("xid", license_url[0]));
break;
case 2:
upd_fileds = "comp_license=:comp_license, comp_license_2=:comp_license_2";
sqlParams.Add(new NpgsqlParameter("comp_license", license_url[0]));
sqlParams.Add(new NpgsqlParameter("comp_license_2", license_url[1]));
break;
default: break;
}
sqlStmt = sqlStmt.Replace("{UPD_FIELDS}", upd_fileds);
NpgsqlHelper.ExecuteNonQuery(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, sqlParams.ToArray());
}
catch (Exception ex)
{
throw ex;
}
}
public static void UpdateFixedPriceProduct(FixedPriceProduct fp_prod, string upd_user)
{
try
{
string sqlStmt = @"INSERT INTO b2b.b2d_fixedprice_prod(company_xid, prod_no, prod_name, upd_user, upd_datetime)
VALUES (:company_xid, :prod_no, :prod_name, :upd_user, now())";
NpgsqlParameter[] sqlParams = new NpgsqlParameter[] {
new NpgsqlParameter("company_xid", fp_prod.COMPANY_XID),
new NpgsqlParameter("prod_no", fp_prod.PROD_NO),
new NpgsqlParameter("prod_name", fp_prod.PROD_NAME),
new NpgsqlParameter("crt_user", upd_user)
};
NpgsqlHelper.ExecuteNonQuery(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, sqlParams);
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0}.{1}", ex.Message, ex.StackTrace);
throw ex;
}
}
//////////////////////////
public static void InsertDiscountCurrAmnt(B2dDiscountCurrAmt cur_amt, string crt_user)
{
try
{
string sqlStmt = @"INSERT INTO b2b.b2d_discount_curr_amt(mst_xid, currency, amount, crt_user, crt_datetime)
VALUES (:mst_xid, :currency, :amount, :crt_user, now())";
NpgsqlParameter[] sqlParams = new NpgsqlParameter[] {
new NpgsqlParameter("mst_xid", cur_amt.MST_XID),
new NpgsqlParameter("currency", cur_amt.CURRENCY),
new NpgsqlParameter("amount", cur_amt.AMOUNT),
new NpgsqlParameter("crt_user", crt_user)
};
NpgsqlHelper.ExecuteNonQuery(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, sqlParams);
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0},{1}", ex.Message, ex.StackTrace);
throw ex;
}
}
public static bool DeleteByTrack(int trackId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_mediafile ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("trackid = :trackid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("trackid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = trackId;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
public static void UpdateDiscountCurrAmnt(B2dDiscountCurrAmt cur_amt, string upd_user)
{
try
{
string sqlStmt = @"UPDATE b2b.b2d_discount_curr_amt SET currency=:currency,
amount=:amount, upd_user=:upd_user, upd_datetime=now()
WHERE xid=:xid";
NpgsqlParameter[] sqlParams = new NpgsqlParameter[] {
new NpgsqlParameter("xid", cur_amt.XID),
new NpgsqlParameter("currency", cur_amt.CURRENCY),
new NpgsqlParameter("amount", cur_amt.AMOUNT),
new NpgsqlParameter("upd_user", upd_user)
};
NpgsqlHelper.ExecuteNonQuery(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, sqlParams);
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0},{1}", ex.Message, ex.StackTrace);
throw ex;
}
}
/// <summary>
/// to comment
/// </summary>
/// <param name="moduleID"> moduleID </param>
/// <returns>bool</returns>
public static bool RemoveFromModule(int moduleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_pollmodules ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("moduleid = :moduleid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Updates a row in the mp_PollOptions table. Returns true if row updated.
/// </summary>
/// <param name="optionGuid"> optionGuid </param>
/// <param name="pollGuid"> pollGuid </param>
/// <param name="answer"> answer </param>
/// <param name="votes"> votes </param>
/// <param name="order"> order </param>
/// <returns>bool</returns>
public static bool Update(
Guid optionGuid,
string answer,
int order)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_polloptions ");
sqlCommand.Append("SET ");
sqlCommand.Append("answer = :answer, ");
sqlCommand.Append("\"order\" = :sort ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("optionguid = :optionguid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[3];
arParams[0] = new NpgsqlParameter("optionguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = optionGuid.ToString();
arParams[1] = new NpgsqlParameter("answer", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = answer;
arParams[2] = new NpgsqlParameter("sort", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = order;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
public static bool DeleteBySite(int siteId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_pollmodules ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("moduleid IN (SELECT moduleid FROM mp_modules WHERE siteid = :siteid) ");
sqlCommand.Append(";");
sqlCommand.Append("DELETE FROM mp_pollusers ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("pollguid IN (SELECT pollguid FROM mp_polls WHERE siteguid IN (SELECT siteguid FROM mp_sites WHERE siteid = :siteid)) ");
sqlCommand.Append(";");
sqlCommand.Append("DELETE FROM mp_polloptions ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("pollguid IN (SELECT pollguid FROM mp_Polls WHERE siteguid IN (SELECT siteguid FROM mp_sites WHERE siteid = :siteid)) ");
sqlCommand.Append(";");
sqlCommand.Append("DELETE FROM mp_polls ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("pollguid IN (SELECT pollguid FROM mp_Polls WHERE siteguid IN (SELECT siteguid FROM mp_sites WHERE siteid = :siteid)) ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Updates the status of a response. Returns true if row updated.
/// </summary>
/// <param name="responseGuid"> responseGuid </param>
/// <param name="complete"> complete </param>
/// <returns>bool</returns>
public static bool Update(
Guid responseGuid,
DateTime submissionDate,
bool complete)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_surveyresponses ");
sqlCommand.Append("SET ");
sqlCommand.Append("submissiondate = :submissiondate, ");
sqlCommand.Append("complete = :complete ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("responseguid = :responseguid ");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[3];
arParams[0] = new NpgsqlParameter("responseguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = responseGuid.ToString();
arParams[1] = new NpgsqlParameter("submissiondate", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = submissionDate;
arParams[2] = new NpgsqlParameter("complete", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = complete;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
public static void RemoveFromModule(Guid surveyGuid, int moduleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE ");
sqlCommand.Append("FROM mp_surveymodules ");
sqlCommand.Append("WHERE moduleid = :moduleid ");
sqlCommand.Append("AND surveyguid = :surveyguid; ");
NpgsqlParameter[] arParams = new NpgsqlParameter[2];
arParams[0] = new NpgsqlParameter("surveyguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = surveyGuid.ToString();
arParams[1] = new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = moduleId;
NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
}
/// <summary>
/// Deletes a row from the mp_SurveyPages table. Returns true if row deleted.
/// </summary>
/// <param name="pageGuid"> pageGuid </param>
/// <returns>bool</returns>
public static bool Delete(
Guid pageGuid)
{
//first delete questionOptions
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_surveyquestionoptions ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("questionguid IN (");
sqlCommand.Append("SELECT questionguid ");
sqlCommand.Append("FROM mp_surveyquestions ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("pageguid = :pageguid); ");
//now delete survey questions
sqlCommand.Append("DELETE FROM mp_surveyquestions ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("pageguid = :pageguid; ");
//now delete pages
sqlCommand.Append("DELETE FROM mp_surveypages ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("pageguid = :pageguid ;");
NpgsqlParameter[] arParams = new NpgsqlParameter[1];
arParams[0] = new NpgsqlParameter("pageguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = pageGuid.ToString();
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Remove all surveys from Module
/// </summary>
/// <param name="moduleId">The current Module's ID</param>
public static void RemoveFromModule(int moduleId)
{
string sqlCommand = @"
DELETE FROM
mp_surveymodules
WHERE
moduleid = :moduleid;" ;
var arParams = new List <NpgsqlParameter>
{
new NpgsqlParameter("moduleid", NpgsqlTypes.NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input,
Value = moduleId
}
};
NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand,
arParams.ToArray()
);
}
public static void DeleteDiscountMst(Int64 mst_xid, string del_user)
{
NpgsqlConnection conn = new NpgsqlConnection();
NpgsqlTransaction trans = null;
try
{
conn.Open();
trans = conn.BeginTransaction();
DeleteDiscountDtl(trans, mst_xid, del_user);
DeleteDiscountCurrAmnt(trans, mst_xid, del_user);
string sqlStmt = @"DELETE FROM b2b.b2d_discount_mst WHERE xid=:xid";
NpgsqlParameter[] sqlParams = new NpgsqlParameter[] {
new NpgsqlParameter("xid,", mst_xid)
};
NpgsqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlStmt, sqlParams);
trans.Commit();
conn.Close();
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0},{1}", ex.Message, ex.StackTrace);
if (trans != null)
{
trans.Rollback();
}
conn.Close();
throw ex;
}
}
// 新增API使用者
public static void InsertApiAccount_Api(B2dAccount account, string crt_user)
{
try
{
string sqlStmt = @"INSERT INTO b2b.b2d_account_api(
user_uuid, source, company_xid, account_type, enable, password, name_last, name_first, gender_title,
department, job_title, email, tel, crt_user, crt_datetime, api_token)
VALUES (:user_uuid, :source, :company_xid, :account_type, :enable, :password, :name_last, :name_first,
:gender_title, :department, :job_title, :email, :tel, :crt_user, now(), :api_token);";
NpgsqlParameter[] sqlParams = new NpgsqlParameter[] {
new NpgsqlParameter("user_uuid", account.UUID),
new NpgsqlParameter("source", ""),
new NpgsqlParameter("company_xid", account.COMPANY_XID),
new NpgsqlParameter("account_type", account.USER_TYPE),
new NpgsqlParameter("enable", account.ENABLE),
new NpgsqlParameter("password", account.PASSWORD),
new NpgsqlParameter("name_last", account.NAME_LAST),
new NpgsqlParameter("name_first", account.NAME_FIRST),
new NpgsqlParameter("gender_title", account.GENDER_TITLE),
new NpgsqlParameter("department", account.DEPARTMENT),
new NpgsqlParameter("job_title", account.JOB_TITLE),
new NpgsqlParameter("email", account.EMAIL),
new NpgsqlParameter("tel", account.TEL),
new NpgsqlParameter("crt_user", crt_user),
new NpgsqlParameter("api_token", "")
};
NpgsqlHelper.ExecuteNonQuery(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, sqlParams);
}
catch (Exception ex)
{
Website.Instance.logger.FatalFormat("{0}.{1}", ex.Message, ex.StackTrace);
throw ex;
}
}
public static void UpdateVouchAddon(B2dVoucherAddon addon, string upd_user)
{
try
{
string sqlStmt = @"UPDATE b2b.b2d_voucher_addon SET company_email=:company_email, company_tel=:company_tel,
company_name=:company_name, company_address=:company_address, upd_user=:upd_user, upd_datetime=now()
WHERE company_xid=:company_xid";
NpgsqlParameter[] sqlParams = new NpgsqlParameter[] {
new NpgsqlParameter("company_xid", addon.COMPANY_XID),
new NpgsqlParameter("company_email", (object)addon.EMAIL ?? DBNull.Value),
new NpgsqlParameter("company_tel", (object)addon.TEL ?? DBNull.Value),
new NpgsqlParameter("company_name", (object)addon.COMPANY_NAME ?? DBNull.Value),
new NpgsqlParameter("company_address", (object)addon.ADDRESS ?? DBNull.Value),
new NpgsqlParameter("upd_user", upd_user)
};
NpgsqlHelper.ExecuteNonQuery(Website.Instance.SqlConnectionString, CommandType.Text, sqlStmt, sqlParams);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Updates a row in the i7_sflexi_fields table. Returns true if row updated.
/// </summary>
public static bool Update(
Guid siteGuid,
Guid featureGuid,
Guid definitionGuid,
Guid fieldGuid,
string definitionName,
string name,
string label,
string defaultValue,
string controlType,
string controlSrc,
int sortOrder,
string helpKey,
bool required,
string requiredMessageFormat,
string regex,
string regexMessageFormat,
string token,
string preTokenString,
string postTokenString,
string preTokenStringWhenTrue,
string postTokenStringWhenTrue,
string preTokenStringWhenFalse,
string postTokenStringWhenFalse,
bool searchable,
string editPageControlWrapperCssClass,
string editPageLabelCssClass,
string editPageControlCssClass,
bool datePickerIncludeTimeForDate,
bool datePickerShowMonthList,
bool datePickerShowYearList,
string datePickerYearRange,
string imageBrowserEmptyUrl,
//string iSettingControlSettings,
string options,
bool checkBoxReturnBool,
string checkBoxReturnValueWhenTrue,
string checkBoxReturnValueWhenFalse,
string dateFormat,
string textBoxMode,
string attributes,
bool isDeleted,
bool isGlobal,
string viewRoles,
string editRoles)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.AppendFormat("update i7_sflexi_fields set {0} where fieldguid = :fieldguid;"
, @"siteguid = :siteguid
,featureguid = :featureguid
,definitionguid = :definitionguid
,definitionname = :definitionname
,name = :name
,label = :label
,defaultvalue = :defaultvalue
,controltype = :controltype
,controlsrc = :controlsrc
,sortorder = :sortorder
,helpkey = :helpkey
,required = :required
,requiredmessageformat = :requiredmessageformat
,regex = :regex
,regexmessageformat = :regexmessageformat
,token = :token
,pretokenstring = :pretokenstring
,posttokenstring = :posttokenstring
,pretokenstringwhentrue = :pretokenstringwhentrue
,posttokenstringwhentrue = :posttokenstringwhentrue
,pretokenstringwhenfalse = :pretokenstringwhenfalse
,posttokenstringwhenfalse = :posttokenstringwhenfalse
,searchable = :searchable
,editpagecontrolwrappercssclass = :editpagecontrolwrappercssclass
,editpagelabelcssclass = :editpagelabelcssclass
,editpagecontrolcssclass = :editpagecontrolcssclass
,datepickerincludetimefordate = :datepickerincludetimefordate
,datepickershowmonthlist = :datepickershowmonthlist
,datepickershowyearlist = :datepickershowyearlist
,datepickeryearrange = :datepickeryearrange
,imagebrowseremptyurl = :imagebrowseremptyurl
,options = :options
,checkboxreturnbool = :checkboxreturnbool
,checkboxreturnvaluewhentrue = :checkboxreturnvaluewhentrue
,checkboxreturnvaluewhenfalse = :checkboxreturnvaluewhenfalse
,dateformat = :dateformat
,textboxmode = :textboxmode
,attributes = :attributes
,isdeleted = :isdeleted
,isglobal = :isglobal
,viewroles = :viewroles
,editroles = :editroles" );
var sqlParams = new List <NpgsqlParameter>
{
new NpgsqlParameter(":siteguid", NpgsqlDbType.Uuid)
{
Direction = ParameterDirection.Input, Value = siteGuid
},
new NpgsqlParameter(":featureguid", NpgsqlDbType.Uuid)
{
Direction = ParameterDirection.Input, Value = featureGuid
},
new NpgsqlParameter(":definitionguid", NpgsqlDbType.Uuid)
{
Direction = ParameterDirection.Input, Value = definitionGuid
},
new NpgsqlParameter(":fieldguid", NpgsqlDbType.Uuid)
{
Direction = ParameterDirection.Input, Value = fieldGuid
},
new NpgsqlParameter(":definitionname", NpgsqlDbType.Varchar, 50)
{
Direction = ParameterDirection.Input, Value = definitionName
},
new NpgsqlParameter(":name", NpgsqlDbType.Varchar, 50)
{
Direction = ParameterDirection.Input, Value = name
},
new NpgsqlParameter(":label", NpgsqlDbType.Varchar, 255)
{
Direction = ParameterDirection.Input, Value = label
},
new NpgsqlParameter(":defaultvalue", NpgsqlDbType.Text)
{
Direction = ParameterDirection.Input, Value = defaultValue
},
new NpgsqlParameter(":controltype", NpgsqlDbType.Varchar, 16)
{
Direction = ParameterDirection.Input, Value = controlType
},
new NpgsqlParameter(":controlsrc", NpgsqlDbType.Varchar, 255)
{
Direction = ParameterDirection.Input, Value = controlSrc
},
new NpgsqlParameter(":sortorder", NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input, Value = sortOrder
},
new NpgsqlParameter(":helpkey", NpgsqlDbType.Varchar, 255)
{
Direction = ParameterDirection.Input, Value = helpKey
},
new NpgsqlParameter(":required", NpgsqlDbType.Boolean)
{
Direction = ParameterDirection.Input, Value = required
},
new NpgsqlParameter(":requiredmessageformat", NpgsqlDbType.Varchar, 255)
{
Direction = ParameterDirection.Input, Value = requiredMessageFormat
},
new NpgsqlParameter(":regex", NpgsqlDbType.Varchar, 255)
{
Direction = ParameterDirection.Input, Value = regex
},
new NpgsqlParameter(":regexmessageformat", NpgsqlDbType.Varchar, 255)
{
Direction = ParameterDirection.Input, Value = regexMessageFormat
},
new NpgsqlParameter(":token", NpgsqlDbType.Varchar, 50)
{
Direction = ParameterDirection.Input, Value = token
},
new NpgsqlParameter(":pretokenstring", NpgsqlDbType.Text)
{
Direction = ParameterDirection.Input, Value = preTokenString
},
new NpgsqlParameter(":posttokenstring", NpgsqlDbType.Text)
{
Direction = ParameterDirection.Input, Value = postTokenString
},
new NpgsqlParameter(":pretokenstringwhentrue", NpgsqlDbType.Text)
{
Direction = ParameterDirection.Input, Value = preTokenStringWhenTrue
},
new NpgsqlParameter(":posttokenstringwhentrue", NpgsqlDbType.Text)
{
Direction = ParameterDirection.Input, Value = postTokenStringWhenTrue
},
new NpgsqlParameter(":pretokenstringwhenfalse", NpgsqlDbType.Text)
{
Direction = ParameterDirection.Input, Value = preTokenStringWhenFalse
},
new NpgsqlParameter(":posttokenstringwhenfalse", NpgsqlDbType.Text)
{
Direction = ParameterDirection.Input, Value = postTokenStringWhenFalse
},
new NpgsqlParameter(":searchable", NpgsqlDbType.Boolean)
{
Direction = ParameterDirection.Input, Value = searchable
},
new NpgsqlParameter(":editpagecontrolwrappercssclass", NpgsqlDbType.Varchar, 50)
{
Direction = ParameterDirection.Input, Value = editPageControlWrapperCssClass
},
new NpgsqlParameter(":editpagelabelcssclass", NpgsqlDbType.Varchar, 50)
{
Direction = ParameterDirection.Input, Value = editPageLabelCssClass
},
new NpgsqlParameter(":editpagecontrolcssclass", NpgsqlDbType.Varchar, 50)
{
Direction = ParameterDirection.Input, Value = editPageControlCssClass
},
new NpgsqlParameter(":datepickerincludetimefordate", NpgsqlDbType.Boolean)
{
Direction = ParameterDirection.Input, Value = datePickerIncludeTimeForDate
},
new NpgsqlParameter(":datepickershowmonthlist", NpgsqlDbType.Boolean)
{
Direction = ParameterDirection.Input, Value = datePickerShowMonthList
},
new NpgsqlParameter(":datepickershowyearlist", NpgsqlDbType.Boolean)
{
Direction = ParameterDirection.Input, Value = datePickerShowYearList
},
new NpgsqlParameter(":datepickeryearrange", NpgsqlDbType.Varchar, 10)
{
Direction = ParameterDirection.Input, Value = datePickerYearRange
},
new NpgsqlParameter(":imagebrowseremptyurl", NpgsqlDbType.Varchar, 255)
{
Direction = ParameterDirection.Input, Value = imageBrowserEmptyUrl
},
new NpgsqlParameter(":options", NpgsqlDbType.Text)
{
Direction = ParameterDirection.Input, Value = options
},
new NpgsqlParameter(":checkboxreturnbool", NpgsqlDbType.Boolean)
{
Direction = ParameterDirection.Input, Value = checkBoxReturnBool
},
new NpgsqlParameter(":checkboxreturnvaluewhentrue", NpgsqlDbType.Text)
{
Direction = ParameterDirection.Input, Value = checkBoxReturnValueWhenTrue
},
new NpgsqlParameter(":checkboxreturnvaluewhenfalse", NpgsqlDbType.Text)
{
Direction = ParameterDirection.Input, Value = checkBoxReturnValueWhenFalse
},
new NpgsqlParameter(":dateformat", NpgsqlDbType.Varchar, 255)
{
Direction = ParameterDirection.Input, Value = dateFormat
},
new NpgsqlParameter(":textboxmode", NpgsqlDbType.Varchar, 25)
{
Direction = ParameterDirection.Input, Value = textBoxMode
},
new NpgsqlParameter(":attributes", NpgsqlDbType.Varchar, 255)
{
Direction = ParameterDirection.Input, Value = attributes
},
new NpgsqlParameter(":isdeleted", NpgsqlDbType.Boolean)
{
Direction = ParameterDirection.Input, Value = isDeleted
},
new NpgsqlParameter(":isglobal", NpgsqlDbType.Boolean)
{
Direction = ParameterDirection.Input, Value = isGlobal
},
new NpgsqlParameter(":viewroles", NpgsqlDbType.Varchar, 255)
{
Direction = ParameterDirection.Input, Value = viewRoles
},
new NpgsqlParameter(":editroles", NpgsqlDbType.Varchar, 255)
{
Direction = ParameterDirection.Input, Value = editRoles
}
};
int rowsAffected = 0;
var returnValue = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
sqlParams.ToArray());
rowsAffected = Convert.ToInt32(returnValue.ToString());
return(rowsAffected > 0);
}
/// <summary>
/// Inserts a row in the mp_Polls table. Returns rows affected count.
/// </summary>
/// <param name="pollGuid"> pollGuid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="question"> question </param>
/// <param name="active"> active </param>
/// <param name="anonymousVoting"> anonymousVoting </param>
/// <param name="allowViewingResultsBeforeVoting"> allowViewingResultsBeforeVoting </param>
/// <param name="showOrderNumbers"> showOrderNumbers </param>
/// <param name="showResultsWhenDeactivated"> showResultsWhenDeactivated </param>
/// <param name="activeFrom"> activeFrom </param>
/// <param name="activeTo"> activeTo </param>
/// <returns>int</returns>
public static int Add(
Guid pollGuid,
Guid siteGuid,
string question,
bool active,
bool anonymousVoting,
bool allowViewingResultsBeforeVoting,
bool showOrderNumbers,
bool showResultsWhenDeactivated,
DateTime activeFrom,
DateTime activeTo)
{
NpgsqlParameter[] arParams = new NpgsqlParameter[10];
arParams[0] = new NpgsqlParameter("pollguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = pollGuid.ToString();
arParams[1] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = siteGuid.ToString();
arParams[2] = new NpgsqlParameter("question", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = question;
arParams[3] = new NpgsqlParameter("active", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = active;
arParams[4] = new NpgsqlParameter("anonymousvoting", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = anonymousVoting;
arParams[5] = new NpgsqlParameter("allowviewingresultsbeforevoting", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = allowViewingResultsBeforeVoting;
arParams[6] = new NpgsqlParameter("showordernumbers", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = showOrderNumbers;
arParams[7] = new NpgsqlParameter("showresultswhendeactivated", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = showResultsWhenDeactivated;
arParams[8] = new NpgsqlParameter("activefrom", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = activeFrom;
arParams[9] = new NpgsqlParameter("activeto", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = activeTo;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_polls (");
sqlCommand.Append("pollguid, ");
sqlCommand.Append("siteguid, ");
sqlCommand.Append("question, ");
sqlCommand.Append("active, ");
sqlCommand.Append("anonymousvoting, ");
sqlCommand.Append("allowviewingresultsbeforevoting, ");
sqlCommand.Append("showordernumbers, ");
sqlCommand.Append("showresultswhendeactivated, ");
sqlCommand.Append("activefrom, ");
sqlCommand.Append("activeto )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":pollguid, ");
sqlCommand.Append(":siteguid, ");
sqlCommand.Append(":question, ");
sqlCommand.Append(":active, ");
sqlCommand.Append(":anonymousvoting, ");
sqlCommand.Append(":allowviewingresultsbeforevoting, ");
sqlCommand.Append(":showordernumbers, ");
sqlCommand.Append(":showresultswhendeactivated, ");
sqlCommand.Append(":activefrom, ");
sqlCommand.Append(":activeto ");
sqlCommand.Append(")");
sqlCommand.Append(";");
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
/// <summary>
/// Deletes a row from the mp_Surveys table. Returns true if row deleted.
/// </summary>
/// <param name="surveyGuid"> surveyGuid </param>
/// <returns>bool</returns>
public static void Delete(Guid surveyGuid)
{
string sqlCommand = @"
DELETE FROM
mp_surveyquestionoptions
WHERE
questionguid IN (
SELECT
questionguid
FROM
mp_surveyquestions
WHERE
pageguid
IN (
SELECT
pageguid
FROM
mp_surveypages
WHERE
surveyguid = :surveyguid
)
);
DELETE FROM
mp_surveyquestions
WHERE
pageguid IN (
SELECT
pageguid
FROM
mp_surveypages
WHERE
surveyguid = :surveyguid
);
DELETE FROM
mp_surveypages
WHERE
surveyguid = :surveyguid;
DELETE FROM
mp_surveys
WHERE
surveyguid = :surveyguid;" ;
var arParams = new List <NpgsqlParameter>
{
new NpgsqlParameter("surveyguid", NpgsqlTypes.NpgsqlDbType.Char, 36)
{
Direction = ParameterDirection.Input,
Value = surveyGuid.ToString()
}
};
NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand,
arParams.ToArray()
);
}
/// <summary>
/// Updates a row in the i7_sflexi_items table. Returns true if row updated.
/// </summary>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="featureGuid"> featureGuid </param>
/// <param name="moduleGuid"> moduleGuid </param>
/// <param name="moduleID"> moduleID </param>
/// <param name="definitionGuid"> definitionGuid </param>
/// <param name="itemGuid"> itemGuid </param>
/// <param name="itemID"> itemID </param>
/// <param name="sortOrder"> sortOrder </param>
/// <param name="createdUtc"> createdUtc </param>
/// <param name="lastModUtc"> lastModUtc </param>
/// <returns>bool</returns>
public static bool Update(
Guid siteGuid,
Guid featureGuid,
Guid moduleGuid,
int moduleID,
Guid definitionGuid,
Guid itemGuid,
int sortOrder,
DateTime createdUtc,
DateTime lastModUtc,
string viewRoles,
string editRoles)
{
var sqlCommand = @"
update i7_sflexi_items
set siteguid = :siteguid
,featureguid = :featureguid
,moduleguid = :moduleguid
,moduleid = :moduleid
,definitionguid = :definitionguid
,sortorder = :sortorder
,createdutc = :createdutc
,lastmodutc = :lastmodutc
,viewroles = :viewroles
,editroles = :editroles
where itemguid = :itemguid;" ;
var sqlParams = new List <NpgsqlParameter>
{
new NpgsqlParameter(":itemguid", NpgsqlDbType.Uuid)
{
Direction = ParameterDirection.Input, Value = itemGuid
},
new NpgsqlParameter(":siteguid", NpgsqlDbType.Uuid)
{
Direction = ParameterDirection.Input, Value = siteGuid
},
new NpgsqlParameter(":featureguid", NpgsqlDbType.Uuid)
{
Direction = ParameterDirection.Input, Value = featureGuid
},
new NpgsqlParameter(":moduleguid", NpgsqlDbType.Uuid)
{
Direction = ParameterDirection.Input, Value = moduleGuid
},
new NpgsqlParameter(":moduleid", NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input, Value = moduleID
},
new NpgsqlParameter(":definitionguid", NpgsqlDbType.Uuid)
{
Direction = ParameterDirection.Input, Value = definitionGuid
},
new NpgsqlParameter(":sortorder", NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input, Value = sortOrder
},
new NpgsqlParameter(":createdutc", NpgsqlDbType.Timestamp)
{
Direction = ParameterDirection.Input, Value = createdUtc
},
new NpgsqlParameter(":lastmodutc", NpgsqlDbType.Timestamp)
{
Direction = ParameterDirection.Input, Value = lastModUtc
},
new NpgsqlParameter(":viewroles", NpgsqlDbType.Varchar)
{
Direction = ParameterDirection.Input, Value = viewRoles
},
new NpgsqlParameter(":editroles", NpgsqlDbType.Varchar)
{
Direction = ParameterDirection.Input, Value = editRoles
}
};
int rowsAffected = Convert.ToInt32(NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
sqlParams.ToArray()).ToString());
return(rowsAffected > 0);
}
/// <summary>
/// deletes all survey content for the passed in siteid
/// </summary>
/// <param name="siteId"></param>
/// <returns></returns>
public static bool DeleteBySite(int siteId)
{
string sqlCommand = @"
DELETE FROM
mp_surveyquestionoptions
WHERE
questionguid IN (
SELECT
questionguid
FROM
mp_surveyquestions
WHERE
pageguid IN (
SELECT
pageguid
FROM
mp_surveypages
WHERE
surveyguid IN (
SELECT
surveyguid
FROM
mp_surveys
WHERE
siteguid IN (
SELECT
siteguid
FROM
mp_sites
WHERE
siteid = :siteid
)
)
)
);
DELETE FROM
mp_surveyquestions
WHERE
pageguid IN (
SELECT
pageguid
FROM
mp_surveypages
WHERE
surveyguid IN (
SELECT
surveyguid
FROM
mp_surveys
WHERE
siteguid IN (
SELECT
siteguid
FROM
mp_sites
WHERE
siteid = :siteid
)
)
);
DELETE FROM
mp_surveypages
WHERE
surveyguid IN (
SELECT
surveyguid
FROM
mp_surveys
WHERE
siteguid IN (
SELECT
siteguid
FROM
mp_sites
WHERE
siteid = :siteid
)
);
DELETE FROM
mp_surveys
WHERE
siteguid IN (
SELECT
siteguid
FROM
mp_sites
WHERE
siteid = :siteid
);" ;
var arParams = new List <NpgsqlParameter>
{
new NpgsqlParameter("siteid", NpgsqlTypes.NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input,
Value = siteId
}
};
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand,
arParams.ToArray()
);
return(rowsAffected > 0);
}
/// <summary>
/// Inserts a row in the mp_SurveyQuestions table. Returns rows affected count.
/// </summary>
/// <param name="questionGuid"> questionGuid </param>
/// <param name="pageGuid"> pageGuid </param>
/// <param name="questionName"> questionText </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 questionName,
string questionText,
int questionTypeId,
bool answerIsRequired,
string validationMessage
)
{
string sqlCommand = @"
INSERT INTO
mp_surveyquestions (
questionguid,
pageguid,
questionname,
questiontext,
questiontypeid,
answerisrequired,
questionorder,
validationmessage
)
SELECT
:questionguid,
:pageguid,
:questionname,
:questiontext,
:questiontypeid,
:answerisrequired,
Count(*),
:validationmessage
FROM
mp_surveypages;" ;
var sqlParams = new List <NpgsqlParameter>
{
new NpgsqlParameter("questionguid", NpgsqlTypes.NpgsqlDbType.Char, 36)
{
Direction = ParameterDirection.Input,
Value = questionGuid.ToString()
},
new NpgsqlParameter("pageguid", NpgsqlTypes.NpgsqlDbType.Char, 36)
{
Direction = ParameterDirection.Input,
Value = pageGuid.ToString()
},
new NpgsqlParameter("questionname", NpgsqlTypes.NpgsqlDbType.Varchar, 255)
{
Direction = ParameterDirection.Input,
Value = questionName
},
new NpgsqlParameter("questiontext", NpgsqlTypes.NpgsqlDbType.Text)
{
Direction = ParameterDirection.Input,
Value = questionText
},
new NpgsqlParameter("questiontypeid", NpgsqlTypes.NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input,
Value = questionTypeId
},
new NpgsqlParameter("answerisrequired", NpgsqlTypes.NpgsqlDbType.Boolean)
{
Direction = ParameterDirection.Input,
Value = answerIsRequired
},
new NpgsqlParameter("validationmessage", NpgsqlTypes.NpgsqlDbType.Varchar, 255)
{
Direction = ParameterDirection.Input,
Value = validationMessage
},
};
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand,
sqlParams.ToArray()
);
return(rowsAffected);
}
/// <summary>
/// Updates a row in the mp_SurveyQuestions table. Returns true if row updated.
/// </summary>
/// <param name="questionGuid"> questionGuid </param>
/// <param name="pageGuid"> pageGuid </param>
/// <param name="questionName"> questionText </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 questionName,
string questionText,
int questionTypeId,
bool answerIsRequired,
int questionOrder,
string validationMessage
)
{
string sqlCommand = @"
UPDATE
mp_surveyquestions
SET
pageguid = :pageguid,
questionname = :questionname,
questiontext = :questiontext,
questiontypeid = :questiontypeid,
answerisrequired = :answerisrequired,
questionorder = :questionorder,
validationmessage = :validationmessage
WHERE
questionguid = :questionguid;" ;
var arParams = new List <NpgsqlParameter> {
new NpgsqlParameter("questionguid", NpgsqlTypes.NpgsqlDbType.Char, 36)
{
Direction = ParameterDirection.Input,
Value = questionGuid.ToString()
},
new NpgsqlParameter("pageguid", NpgsqlTypes.NpgsqlDbType.Char, 36)
{
Direction = ParameterDirection.Input,
Value = pageGuid.ToString()
},
new NpgsqlParameter("questionname", NpgsqlTypes.NpgsqlDbType.Varchar, 255)
{
Direction = ParameterDirection.Input,
Value = questionName
},
new NpgsqlParameter("questiontext", NpgsqlTypes.NpgsqlDbType.Text)
{
Direction = ParameterDirection.Input,
Value = questionText
},
new NpgsqlParameter("questiontypeid", NpgsqlTypes.NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input,
Value = questionTypeId
},
new NpgsqlParameter("answerisrequired", NpgsqlTypes.NpgsqlDbType.Boolean)
{
Direction = ParameterDirection.Input,
Value = answerIsRequired
},
new NpgsqlParameter("questionorder", NpgsqlTypes.NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input,
Value = questionOrder
},
new NpgsqlParameter("validationmessage", NpgsqlTypes.NpgsqlDbType.Varchar, 256)
{
Direction = ParameterDirection.Input,
Value = validationMessage
}
};
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand,
arParams.ToArray()
);
return(rowsAffected > -1);
}
/// <summary>
/// Inserts a row in the mp_Surveys table. Returns rows affected count.
/// </summary>
/// <param name="surveyGuid"> surveyGuid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="surveyName"> surveyName </param>
/// <param name="creationDate"> creationDate </param>
/// <param name="startPageText"> startPageText </param>
/// <param name="endPageText"> endPageText </param>
/// <param name="submissionLimit"> submissionLimit </param>
/// <returns>int</returns>
public static int Add(
Guid surveyGuid,
Guid siteGuid,
string surveyName,
DateTime creationDate,
string startPageText,
string endPageText,
int submissionLimit
)
{
string sqlCommand = @"
INSERT INTO
mp_surveys (
surveyguid,
siteguid,
surveyname,
creationdate,
startpagetext,
endpagetext,
submissionlimit
)
VALUES (
:surveyguid,
:siteguid,
:surveyname,
:creationdate,
:startpagetext,
:endpagetext,
:submissionlimit
);" ;
var arParams = new List <NpgsqlParameter>
{
new NpgsqlParameter("surveyguid", NpgsqlTypes.NpgsqlDbType.Char, 36)
{
Direction = ParameterDirection.Input,
Value = surveyGuid.ToString()
},
new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36)
{
Direction = ParameterDirection.Input,
Value = siteGuid.ToString()
},
new NpgsqlParameter("surveyname", NpgsqlTypes.NpgsqlDbType.Varchar, 255)
{
Direction = ParameterDirection.Input,
Value = surveyName
},
new NpgsqlParameter("creationdate", NpgsqlTypes.NpgsqlDbType.Timestamp)
{
Direction = ParameterDirection.Input,
Value = creationDate
},
new NpgsqlParameter("startpagetext", NpgsqlTypes.NpgsqlDbType.Text)
{
Direction = ParameterDirection.Input,
Value = startPageText
},
new NpgsqlParameter("endpagetext", NpgsqlTypes.NpgsqlDbType.Text)
{
Direction = ParameterDirection.Input,
Value = endPageText
},
new NpgsqlParameter("submissionlimit", NpgsqlTypes.NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input,
Value = submissionLimit
}
};
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand,
arParams.ToArray()
);
return(rowsAffected);
}
/// <summary>
/// Updates a row in the mp_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)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_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(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[7];
arParams[0] = new NpgsqlParameter("questionguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = questionGuid.ToString();
arParams[1] = new NpgsqlParameter("pageguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = pageGuid.ToString();
arParams[2] = new NpgsqlParameter("questiontext", NpgsqlTypes.NpgsqlDbType.Text);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = questionText;
arParams[3] = new NpgsqlParameter("questiontypeid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = questionTypeId;
arParams[4] = new NpgsqlParameter("answerisrequired", NpgsqlTypes.NpgsqlDbType.Boolean);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = answerIsRequired;
arParams[5] = new NpgsqlParameter("questionorder", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = questionOrder;
arParams[6] = new NpgsqlParameter("validationmessage", NpgsqlTypes.NpgsqlDbType.Varchar, 256);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = validationMessage;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Updates a row in the i7_sflexi_items table. Returns true if row updated.
/// </summary>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="featureGuid"> featureGuid </param>
/// <param name="moduleGuid"> moduleGuid </param>
/// <param name="moduleID"> moduleID </param>
/// <param name="definitionGuid"> definitionGuid </param>
/// <param name="itemGuid"> itemGuid </param>
/// <param name="itemID"> itemID </param>
/// <param name="sortOrder"> sortOrder </param>
/// <param name="createdUtc"> createdUtc </param>
/// <param name="lastModUtc"> lastModUtc </param>
/// <returns>bool</returns>
public static bool Update(
Guid siteGuid,
Guid featureGuid,
Guid moduleGuid,
int moduleID,
Guid definitionGuid,
Guid itemGuid,
int sortOrder,
DateTime createdUtc,
DateTime lastModUtc)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.AppendFormat("update i7_sflexi_items set {0} where itemguid = :itemguid;"
, @"siteguid = :siteguid
,featureguid = :featureguid
,moduleguid = :moduleguid
,moduleid = :moduleid
,definitionguid = :definitionguid
,sortorder = :sortorder
,createdutc = :createdutc
,lastmodutc = :lastmodutc"
);
var sqlParams = new List <NpgsqlParameter>
{
new NpgsqlParameter(":itemguid", NpgsqlDbType.Uuid)
{
Direction = ParameterDirection.Input, Value = itemGuid
},
new NpgsqlParameter(":siteguid", NpgsqlDbType.Uuid)
{
Direction = ParameterDirection.Input, Value = siteGuid
},
new NpgsqlParameter(":featureguid", NpgsqlDbType.Uuid)
{
Direction = ParameterDirection.Input, Value = featureGuid
},
new NpgsqlParameter(":moduleguid", NpgsqlDbType.Uuid)
{
Direction = ParameterDirection.Input, Value = moduleGuid
},
new NpgsqlParameter(":moduleid", NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input, Value = moduleID
},
new NpgsqlParameter(":definitionguid", NpgsqlDbType.Uuid)
{
Direction = ParameterDirection.Input, Value = definitionGuid
},
new NpgsqlParameter(":sortorder", NpgsqlDbType.Integer)
{
Direction = ParameterDirection.Input, Value = sortOrder
},
new NpgsqlParameter(":createdutc", NpgsqlDbType.Timestamp)
{
Direction = ParameterDirection.Input, Value = createdUtc
},
new NpgsqlParameter(":lastmodutc", NpgsqlDbType.Timestamp)
{
Direction = ParameterDirection.Input, Value = lastModUtc
}
};
int rowsAffected = Convert.ToInt32(NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
sqlParams.ToArray()).ToString());
return(rowsAffected > 0);
}
/// <summary>
/// Inserts a row in the mp_Surveys table. Returns rows affected count.
/// </summary>
/// <param name="surveyGuid"> surveyGuid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="surveyName"> surveyName </param>
/// <param name="creationDate"> creationDate </param>
/// <param name="startPageText"> startPageText </param>
/// <param name="endPageText"> endPageText </param>
/// <returns>int</returns>
public static int Add(
Guid surveyGuid,
Guid siteGuid,
string surveyName,
DateTime creationDate,
string startPageText,
string endPageText)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_surveys (");
sqlCommand.Append("surveyguid, ");
sqlCommand.Append("siteguid, ");
sqlCommand.Append("surveyname, ");
sqlCommand.Append("creationdate, ");
sqlCommand.Append("startpagetext, ");
sqlCommand.Append("endpagetext )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append(":surveyguid, ");
sqlCommand.Append(":siteguid, ");
sqlCommand.Append(":surveyname, ");
sqlCommand.Append(":creationdate, ");
sqlCommand.Append(":startpagetext, ");
sqlCommand.Append(":endpagetext ");
sqlCommand.Append(")");
sqlCommand.Append(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[6];
arParams[0] = new NpgsqlParameter("surveyguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = surveyGuid.ToString();
arParams[1] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = siteGuid.ToString();
arParams[2] = new NpgsqlParameter("surveyname", NpgsqlTypes.NpgsqlDbType.Varchar, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = surveyName;
arParams[3] = new NpgsqlParameter("creationdate", NpgsqlTypes.NpgsqlDbType.Timestamp);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = creationDate;
arParams[4] = new NpgsqlParameter("startpagetext", NpgsqlTypes.NpgsqlDbType.Text);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = startPageText;
arParams[5] = new NpgsqlParameter("endpagetext", NpgsqlTypes.NpgsqlDbType.Text);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = endPageText;
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
/// <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(";");
NpgsqlParameter[] arParams = new NpgsqlParameter[7];
arParams[0] = new NpgsqlParameter("trackid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = trackId;
arParams[1] = new NpgsqlParameter("playerid", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = playerId;
arParams[2] = new NpgsqlParameter("tracktype", NpgsqlTypes.NpgsqlDbType.Varchar, 10);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = trackType;
arParams[3] = new NpgsqlParameter("trackorder", NpgsqlTypes.NpgsqlDbType.Integer);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = trackOrder;
arParams[4] = new NpgsqlParameter("name", NpgsqlTypes.NpgsqlDbType.Varchar, 100);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = name;
arParams[5] = new NpgsqlParameter("artist", NpgsqlTypes.NpgsqlDbType.Varchar, 100);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = artist;
arParams[6] = new NpgsqlParameter("userguid", NpgsqlTypes.NpgsqlDbType.Char, 36);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = userGuid.ToString();
int rowsAffected = NpgsqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}