////////////////////////// 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 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_SurveyQuestionOptions table. Returns true if row deleted. /// </summary> /// <param name="questionOptionGuid"> questionOptionGuid </param> /// <returns>bool</returns> public static bool Delete( Guid questionOptionGuid) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("DELETE FROM mp_surveyquestionoptions "); sqlCommand.Append("WHERE "); sqlCommand.Append("questionoptionguid = :questionoptionguid "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("questionoptionguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = questionOptionGuid.ToString(); int rowsAffected = NpgsqlHelper.ExecuteNonQuery(ConnectionString.GetWriteConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams); return(rowsAffected > -1); }
/// <summary> /// Selects all Files for a Player from the mp_MediaFile table. /// </summary> /// <param name="trackID">The ID of the Player.</param> /// <returns>An IDataReader containing the Media File(s) data.</returns> public static IDataReader SelectByPlayer(int playerId) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_mediafile "); sqlCommand.Append("WHERE "); sqlCommand.Append("trackid IN (SELECT trackid FROM mp_mediatrack WHERE playerid = :playerid) "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("playerid", NpgsqlTypes.NpgsqlDbType.Integer); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = playerId; return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
/// <summary> /// Gets an IDataReader with the row from the doan_MediaPlayers table that exists for a Module. /// </summary> /// <param name="moduleID">The ID of the Module.</param> /// <returns>The data for the Medai Player as an IDataReader object.</returns> public static IDataReader SelectByModule(int moduleId) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_mediaplayer "); 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; return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
/// <summary> /// Gets an IDataReader with all rows in the mp_SurveyPages table. /// </summary> public static IDataReader GetAll(Guid surveyGuid) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT sp.*, "); sqlCommand.Append("(SELECT COUNT(*) FROM mp_surveyquestions sq WHERE sp.pageguid = sq.pageguid) AS questioncount "); sqlCommand.Append("FROM mp_surveypages sp "); sqlCommand.Append("WHERE sp.surveyguid = :surveyguid "); sqlCommand.Append("ORDER BY sp.pageorder; "); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("surveyguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = surveyGuid.ToString(); return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
/// <summary> /// Gets the user. /// </summary> /// <returns>The user.</returns> /// <param name="email">Email.</param> /// <param name="pw">Pw.</param> public static JObject GetUser(string email, string pw) { var obj = new JObject(); try { String sql = @"SELECT B.comp_name,B.comp_locale,B.comp_currency, B.contact_user_email, B.payment_type, A.* FROM b2b.b2d_account A JOIN b2b.b2d_company B ON A.company_xid = B.xid WHERE A.enable = TRUE AND A.email = :email AND A.password = :pw "; NpgsqlParameter[] np = new NpgsqlParameter[] { new NpgsqlParameter("email", email), new NpgsqlParameter("pw", pw) }; DataSet ds = NpgsqlHelper.ExecuteDataset(Website.Instance.B2D_DB, CommandType.Text, sql.ToString(), np); if (ds != null && ds.Tables[0].Rows.Count > 0) { ds.AcceptChanges(); //把dataset轉成結森物件 string json = JsonConvert.SerializeObject(ds, Formatting.Indented); obj = JObject.Parse(json); } } catch (Exception ex) { Website.Instance.logger.FatalFormat("{0},{1}", ex.Message, ex.StackTrace); } return(obj); }
public DataSet SqlConsultaDs(string nameRelation, string whereList, string ordersList, string fieldsList) { if (string.IsNullOrEmpty(nameRelation)) { throw new ArgumentException("nameTable"); } string conditionsOrder = string.Empty; if (!string.IsNullOrEmpty(whereList)) { conditionsOrder = " where " + whereList; } if (!string.IsNullOrEmpty(ordersList)) { conditionsOrder = conditionsOrder + " order by " + ordersList; } string listFields = fieldsList ?? "*"; string sql = string.Format("select {0} from {1} {2}", listFields, nameRelation, conditionsOrder); DataSet ds = null; switch (TypeDataBase.ToLower()) { case "postgresql": ds = NpgsqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, sql); break; case "sqlserver": ds = SqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, sql); break; } return(ds); }
/// <summary> /// Gets an IDataReader with all rows in the mp_SurveyQuestionAnswers table. /// </summary> public static IDataReader GetOneResult(Guid responseGuid) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_surveys s "); sqlCommand.Append("JOIN mp_surveyresponses sr "); sqlCommand.Append("ON s.surveyguid = sr.SurveyGuid "); sqlCommand.Append("JOIN mp_surveypages sp "); sqlCommand.Append("ON sr.surveyguid = sp.SurveyGuid "); sqlCommand.Append("JOIN mp_surveyquestions sq "); sqlCommand.Append("ON sp.pageguid = sq.pageguid "); sqlCommand.Append("LEFT JOIN mp_SurveyQuestionAnswers qa "); sqlCommand.Append("ON sq.questionguid = qa.questionguid "); sqlCommand.Append("AND sr.responseguid = qa.responseguid "); sqlCommand.Append("WHERE sr.responseguid = :responseguid "); sqlCommand.Append("AND sr.complete = true "); sqlCommand.Append("AND sp.pageenabled = true "); sqlCommand.Append("ORDER BY sp.pageorder, sq.questionorder; "); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("responseguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = responseGuid.ToString(); return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
/// <summary> /// Gets the guid of the next page in the survey /// </summary> /// <param name="pageGuid">The guid of the current page</param> /// <returns></returns> public static Guid GetNextPageGuid(Guid pageGuid) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT pageguid "); sqlCommand.Append("FROM mp_surveypages "); sqlCommand.Append("WHERE pageorder > ("); sqlCommand.Append("SELECT pageorder "); sqlCommand.Append("FROM mp_surveypages "); sqlCommand.Append("WHERE "); sqlCommand.Append("pageguid = :pageguid) "); sqlCommand.Append("AND "); sqlCommand.Append("surveyguid = ("); sqlCommand.Append("SELECT surveyguid "); sqlCommand.Append("FROM mp_surveypages "); sqlCommand.Append("WHERE "); sqlCommand.Append("pageguid = :pageguid) "); sqlCommand.Append("Order By pageorder "); sqlCommand.Append("Limit 1; "); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("pageguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = pageGuid.ToString(); object id = NpgsqlHelper.ExecuteScalar(ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams); if (id == null) { return(Guid.Empty); } return(new Guid(id.ToString())); }
/// <summary> /// Gets the currency. /// </summary> /// <returns>The currency.</returns> /// <param name="locale">Locale.</param> public static JObject GetCurrency(string locale) { var obj = new JObject(); try { String sql = @"SELECT CUR.currency,CUR.name FROM b2b.b2d_currency CUR WHERE 1=1 AND CUR.locale = :locale "; NpgsqlParameter[] np = new NpgsqlParameter[] { new NpgsqlParameter("locale", locale) }; DataSet ds = NpgsqlHelper.ExecuteDataset(Website.Instance.B2D_DB, CommandType.Text, sql.ToString(), np); if (ds != null && ds.Tables[0].Rows.Count > 0) { ds.AcceptChanges(); //把dataset轉成結森物件 string json = JsonConvert.SerializeObject(ds, Formatting.Indented); obj = JObject.Parse(json); } } catch (Exception ex) { Website.Instance.logger.FatalFormat("{0},{1}", ex.Message, ex.StackTrace); } return(obj); }
/// <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); }
public static void InsertFixedPriceProduct(FixedPriceProduct fp_prod, string crt_user) { try { string sqlStmt = @"INSERT INTO b2b.b2d_fixedprice_prod(company_xid, prod_no, prod_name, state, crt_user, crt_datetime) VALUES (:company_xid, :prod_no, :prod_name, :state, :crt_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("state", fp_prod.STATE), 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; } }
/// <summary> /// Gets an IDataReader with one row from the mp_SurveyQuestions table. /// </summary> /// <param name="questionGuid"> questionGuid </param> public static IDataReader GetOne( Guid questionGuid) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("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(); return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
/// <summary> /// Gets an IDataReader from the mp_PollOptions table. /// </summary> /// <param name="optionGuid"> pollGuid </param> public static IDataReader GetPollOptions(Guid pollGuid) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_polloptions "); sqlCommand.Append("WHERE "); sqlCommand.Append("pollguid = :pollguid "); sqlCommand.Append("ORDER BY \"order\", answer "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("pollguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = pollGuid.ToString(); return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
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); }
/// <summary> /// Gets an IDataReader with the first response to a survey /// </summary> public static IDataReader GetFirst(Guid surveyGuid) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_surveyresponses "); sqlCommand.Append("WHERE surveyguid = :surveyguid "); sqlCommand.Append("AND complete = true "); sqlCommand.Append("ORDER BY submissiondate, responseguid "); sqlCommand.Append("LIMIT 1; "); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("surveyguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = surveyGuid.ToString(); return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
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> /// Gets an IDataReader with all rows from the mp_Polls table matching the siteGuid. /// </summary> /// <param name="siteGuid"> siteGuid </param> public static IDataReader GetPolls(Guid siteGuid) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT * "); sqlCommand.Append("FROM mp_polls "); sqlCommand.Append("WHERE "); sqlCommand.Append("siteguid = :siteguid "); sqlCommand.Append("ORDER BY activefrom DESC, question "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = siteGuid.ToString(); return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
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 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); }
/// <summary> /// Gets an IDataReader with all rows in the mp_Surveys table. /// </summary> public static IDataReader GetAll(Guid siteGuid) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT "); sqlCommand.Append("s.surveyguid, "); sqlCommand.Append("s.siteguid, "); sqlCommand.Append("s.surveyname, "); sqlCommand.Append("s.creationdate, "); sqlCommand.Append("s.startpagetext, "); sqlCommand.Append("s.endpagetext, "); sqlCommand.Append("(SELECT COUNT(*) FROM mp_surveypages sp WHERE sp.surveyguid = s.surveyguid) AS pagecount, "); sqlCommand.Append("(SELECT COUNT(*) FROM mp_surveyresponses sr WHERE sr.surveyguid = s.surveyguid) AS responsecount "); sqlCommand.Append(" "); sqlCommand.Append(" "); sqlCommand.Append(" "); sqlCommand.Append("FROM mp_surveys s "); sqlCommand.Append("WHERE "); sqlCommand.Append("s.siteguid = :siteguid "); sqlCommand.Append("ORDER BY "); sqlCommand.Append("s.surveyname "); //sqlCommand.Append(" "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("siteguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = siteGuid.ToString(); return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
/// <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); }
/// <summary> /// Gets an IDataReader with one row from the mp_Polls table. /// </summary> /// <param name="pollGuid"> pollGuid </param> public static IDataReader GetPoll(Guid pollGuid) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT p.*, "); sqlCommand.Append("(SELECT SUM(votes) FROM mp_polloptions WHERE mp_polloptions.pollguid = :pollguid) As totalvotes "); sqlCommand.Append("FROM mp_polls p "); sqlCommand.Append("WHERE "); sqlCommand.Append("p.pollguid = :pollguid "); sqlCommand.Append(";"); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("pollguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = pollGuid.ToString(); return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
/// <summary> /// Gets an IDataReader with all items for a single definition. /// </summary> /// <param name="itemID"> itemID </param> public static IDataReader GetAllForDefinition(Guid definitionGuid, Guid siteGuid) { string sqlCommand = @"select siteguid, featureguid, i.moduleguid, i.moduleid, definitionguid, itemguid, itemid, i.sortorder, createdutc, lastmodutc, ms.settingvalue as globalviewsortorder from i7_sflexi_items i left join mp_modulesettings ms on ms.moduleguid = i.moduleguid where definitionguid = :defguid and i.siteguid = :siteguid and ms.settingname = 'GlobalViewSortOrder' order by globalviewsortorder asc, i.moduleid asc, sortorder asc, createdutc asc;"; var sqlParam = new List <NpgsqlParameter> { new NpgsqlParameter(":defguid", NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = definitionGuid }, new NpgsqlParameter(":siteguid", NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = siteGuid } }; return(NpgsqlHelper.ExecuteReader( ConnectionString.GetWriteConnectionString(), CommandType.Text, sqlCommand, sqlParam.ToArray())); }
/// <summary> /// Get the survey's first page GUID /// </summary> /// <param name="surveyGuid">The GUID of the current Survey</param> /// <returns></returns> public static Guid GetFirstPageGuid(Guid surveyGuid) { string sqlCommand = @" SELECT pageguid FROM mp_surveypages WHERE surveyguid = :surveyguid ORDER BY pageorder LIMIT 1;" ; var arParams = new List <NpgsqlParameter> { new NpgsqlParameter("surveyguid", NpgsqlTypes.NpgsqlDbType.Char, 36) { Direction = ParameterDirection.Input, Value = surveyGuid.ToString() } }; object id = NpgsqlHelper.ExecuteScalar(ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand, arParams.ToArray() ); if (id == null) { return(Guid.Empty); } return(new Guid(id.ToString())); }
/// <summary> /// Gets an IDataReader with one row from the mp_Surveys table. /// </summary> /// <param name="surveyGuid"> surveyGuid </param> public static IDataReader GetOne( Guid surveyGuid) { StringBuilder sqlCommand = new StringBuilder(); sqlCommand.Append("SELECT s.*, "); sqlCommand.Append("(SELECT COUNT(*) FROM mp_surveypages sp WHERE sp.surveyguid = s.surveyguid) AS pagecount, "); sqlCommand.Append("(SELECT COUNT(*) FROM mp_surveyresponses sr WHERE sr.surveyguid = s.surveyguid) AS responsecount "); sqlCommand.Append("FROM mp_surveys s "); sqlCommand.Append("WHERE "); sqlCommand.Append("s.surveyguid = :surveyguid; "); NpgsqlParameter[] arParams = new NpgsqlParameter[1]; arParams[0] = new NpgsqlParameter("surveyguid", NpgsqlTypes.NpgsqlDbType.Char, 36); arParams[0].Direction = ParameterDirection.Input; arParams[0].Value = surveyGuid.ToString(); return(NpgsqlHelper.ExecuteReader( ConnectionString.GetReadConnectionString(), CommandType.Text, sqlCommand.ToString(), arParams)); }
// 新增分銷商主帳號 public static string InsAccount(NpgsqlTransaction trans, RegisterModel reg, object comp_xid) { try { string sqlStmt = @"INSERT INTO b2b.b2d_account(company_xid, account_type, enable, password, name_last, name_first, gender_title, email, tel, crt_user, crt_datetime, job_title, user_uuid) VALUES (:company_xid, :account_type, :enable, :password,:name_last, :name_first, :gender_title, :email, :tel, :crt_user, now(), :job_title, :user_uuid); SELECT currval('b2b.b2d_account_xid_seq') AS new_comp_xid ;"; NpgsqlParameter[] sqlParams = new NpgsqlParameter[] { new NpgsqlParameter("company_xid", comp_xid), new NpgsqlParameter("account_type", "01"), //帳號權限(00一般/01管理者) new NpgsqlParameter("enable", false), //是否有效(true/false) new NpgsqlParameter("password", reg.PASSWORD), new NpgsqlParameter("gender_title", reg.GENDER_TITLE), new NpgsqlParameter("name_last", reg.NAME_LAST), new NpgsqlParameter("name_first", reg.NAME_FIRST), new NpgsqlParameter("job_title", reg.JOB_TITLE), new NpgsqlParameter("email", reg.EMAIL), new NpgsqlParameter("tel", reg.TEL), new NpgsqlParameter("crt_user", "system"), new NpgsqlParameter("user_uuid", reg.USER_UUID) }; var new_xid = NpgsqlHelper.ExecuteScalar(trans, CommandType.Text, sqlStmt, sqlParams); return(new_xid.ToString()); } 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; } }
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); }