/// <summary>
/// Updates the TrackOrder values for the tracks that remain for the PlayerID by incrementing any Tracks that have a TrackOrder value
/// greater than the provided trackOrder.
/// </summary>
/// <param name="playerID">The ID of the Player.</param>
/// <param name="trackOrder">The TrackOrder value.</param>
/// <returns>The number of rows affected by the update.</returns>
public static int AdjustTrackOrdersForDelete(int playerId, int trackOrder)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_MediaTrack ");
sqlCommand.Append("SET TrackOrder = TrackOrder - 1 ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("PlayerID = @PlayerID ");
sqlCommand.Append("AND TrackOrder > @TrackOrder ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@PlayerID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = playerId;
arParams[1] = new FbParameter("@TrackOrder", FbDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = trackOrder;
int rowsAffected = FBSqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return rowsAffected;
}
public async Task<int> Create(
int siteId,
string userId,
string claimType,
string claimValue)
{
FbParameter[] arParams = new FbParameter[4];
arParams[0] = new FbParameter(":UserId", FbDbType.VarChar, 128);
arParams[0].Value = userId;
arParams[1] = new FbParameter(":ClaimType", FbDbType.VarChar, -1);
arParams[1].Value = claimType;
arParams[2] = new FbParameter(":ClaimValue", FbDbType.VarChar, -1);
arParams[2].Value = claimValue;
arParams[3] = new FbParameter(":SiteId", FbDbType.Integer);
arParams[3].Value = siteId;
string statement = "EXECUTE PROCEDURE mp_USERCLAIMS_INSERT ("
+ AdoHelper.GetParamString(arParams.Length) + ")";
object result = await AdoHelper.ExecuteScalarAsync(
writeConnectionString,
CommandType.StoredProcedure,
statement,
arParams);
int newID = Convert.ToInt32(result);
return newID;
}
public static int Create(
string userId,
string claimType,
string claimValue)
{
FbParameter[] arParams = new FbParameter[3];
arParams[0] = new FbParameter(":UserId", FbDbType.VarChar, 128);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = userId;
arParams[1] = new FbParameter(":ClaimType", FbDbType.VarChar, -1);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = claimType;
arParams[2] = new FbParameter(":ClaimValue", FbDbType.VarChar, -1);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = claimValue;
string statement = "EXECUTE PROCEDURE mp_USERCLAIMS_INSERT ("
+ FBSqlHelper.GetParamString(arParams.Length) + ")";
int newID = Convert.ToInt32(FBSqlHelper.ExecuteScalar(
ConnectionString.GetWriteConnectionString(),
CommandType.StoredProcedure,
statement,
arParams));
return newID;
}
/// <summary>
/// Inserts a row in the mp_RedirectList table. Returns rows affected count.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="siteID"> siteID </param>
/// <param name="oldUrl"> oldUrl </param>
/// <param name="newUrl"> newUrl </param>
/// <param name="createdUtc"> createdUtc </param>
/// <param name="expireUtc"> expireUtc </param>
/// <returns>int</returns>
public int Create(
Guid rowGuid,
Guid siteGuid,
int siteID,
string oldUrl,
string newUrl,
DateTime createdUtc,
DateTime expireUtc)
{
FbParameter[] arParams = new FbParameter[7];
arParams[0] = new FbParameter("@RowGuid", FbDbType.Char, 36);
arParams[0].Value = rowGuid.ToString();
arParams[1] = new FbParameter("@SiteGuid", FbDbType.Char, 36);
arParams[1].Value = siteGuid.ToString();
arParams[2] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[2].Value = siteID;
arParams[3] = new FbParameter("@OldUrl", FbDbType.VarChar, 255);
arParams[3].Value = oldUrl;
arParams[4] = new FbParameter("@NewUrl", FbDbType.VarChar, 255);
arParams[4].Value = newUrl;
arParams[5] = new FbParameter("@CreatedUtc", FbDbType.TimeStamp);
arParams[5].Value = createdUtc;
arParams[6] = new FbParameter("@ExpireUtc", FbDbType.TimeStamp);
arParams[6].Value = expireUtc;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_RedirectList (");
sqlCommand.Append("RowGuid, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("SiteID, ");
sqlCommand.Append("OldUrl, ");
sqlCommand.Append("NewUrl, ");
sqlCommand.Append("CreatedUtc, ");
sqlCommand.Append("ExpireUtc )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("@RowGuid, ");
sqlCommand.Append("@SiteGuid, ");
sqlCommand.Append("@SiteID, ");
sqlCommand.Append("@OldUrl, ");
sqlCommand.Append("@NewUrl, ");
sqlCommand.Append("@CreatedUtc, ");
sqlCommand.Append("@ExpireUtc )");
sqlCommand.Append(";");
int rowsAffected = AdoHelper.ExecuteNonQuery(
writeConnectionString,
sqlCommand.ToString(),
arParams);
return rowsAffected;
}
public DbDataReader GetUserCountByYearMonth(int siteId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("EXTRACT(YEAR FROM DateCreated) As Y, ");
sqlCommand.Append("EXTRACT(MONTH FROM DateCreated) As M, ");
sqlCommand.Append("EXTRACT(YEAR FROM DateCreated) || '-' || EXTRACT(MONTH FROM DateCreated) As Label, ");
sqlCommand.Append("COUNT(*) As Users ");
sqlCommand.Append("FROM ");
sqlCommand.Append("mp_Users ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteID = @SiteID ");
sqlCommand.Append("GROUP BY EXTRACT(YEAR FROM DateCreated), EXTRACT(MONTH FROM DateCreated) ");
sqlCommand.Append("ORDER BY EXTRACT(YEAR FROM DateCreated), EXTRACT(MONTH FROM DateCreated) ");
sqlCommand.Append("; ");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Value = siteId;
return AdoHelper.ExecuteReader(
readConnectionString,
sqlCommand.ToString(),
arParams);
}
public static void AddFeature(int siteId, int moduleDefId)
{
if (HasFeature(siteId, moduleDefId)) return;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_SiteModuleDefinitions ");
sqlCommand.Append("( ");
sqlCommand.Append("SiteID, ");
sqlCommand.Append("ModuleDefID ");
sqlCommand.Append(") ");
sqlCommand.Append("VALUES ");
sqlCommand.Append("( ");
sqlCommand.Append("@SiteID, ");
sqlCommand.Append("@ModuleDefID ");
sqlCommand.Append(") ;");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new FbParameter("@ModuleDefID", FbDbType.Integer);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = moduleDefId;
FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
}
public DateTime CambiarFechaSacrificio(Producto AProducto)
{
string pSentencia = "UPDATE DRASPROD SET FECHA_SACRIFICIO = @FECHASAC WHERE CLAVE = @CLAVE RETURNING FECHA_SACRIFICIO";
FbConnection con = _Conexiones.ObtenerConexion();
FbCommand com = new FbCommand(pSentencia, con);
com.Parameters.Add("@CLAVE", FbDbType.VarChar).Value = AProducto.Clave;
com.Parameters.Add("@FECHASAC", FbDbType.TimeStamp).Value = AProducto.Fecha_Sacrificio;
FbParameter pOutParameter = new FbParameter("@FECHA_SACRIFICIO", FbDbType.TimeStamp);
pOutParameter.Direction = ParameterDirection.Output;
com.Parameters.Add(pOutParameter);
try
{
con.Open();
com.ExecuteNonQuery();
}
finally
{
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
}
return (DateTime)pOutParameter.Value;
}
public async Task<bool> Add(
Guid guid,
Guid siteGuid,
string folderName)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_SiteFolders (");
sqlCommand.Append("Guid, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("FolderName )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("@Guid, ");
sqlCommand.Append("@SiteGuid, ");
sqlCommand.Append("@FolderName );");
FbParameter[] arParams = new FbParameter[3];
arParams[0] = new FbParameter("@Guid", FbDbType.VarChar, 36);
arParams[0].Value = guid.ToString();
arParams[1] = new FbParameter("@SiteGuid", FbDbType.VarChar, 36);
arParams[1].Value = siteGuid.ToString();
arParams[2] = new FbParameter("@FolderName", FbDbType.VarChar, 255);
arParams[2].Value = folderName;
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
sqlCommand.ToString(),
arParams);
return rowsAffected > 0;
}
public Stream GetFilters(string CustomerId)
{
FbParameter cust = new FbParameter("customer_id", CustomerId);
WebOperationContext.Current.OutgoingResponse.ContentType = contentType;
return new MemoryStream(Encoding.UTF8.GetBytes(GetJson("customer_criteria_list", cust)));
}
public Stream GetTopics(string CustomerId, string ProviderId)
{
FbParameter cust = new FbParameter("customer_id", CustomerId);
FbParameter supplierId = new FbParameter("supplier_id", ProviderId);
WebOperationContext.Current.OutgoingResponse.ContentType = contentType;
return new MemoryStream(Encoding.UTF8.GetBytes(GetJson("supplier_topic_list", "list", supplierId, cust)));
}
public CanalProgramado InsertarLoteSacrificio(CCall ACanal)
{
string pSentencia = "INSERT INTO DRASCCALL (GRANJA, LOTE, FECHA, CANALES) VALUES (@GRANJA, @LOTE, @FECHA, @CANALES) RETURNING CLAVE";
FbConnection con = _Conexion.ObtenerConexion();
FbCommand com = new FbCommand(pSentencia, con);
com.Parameters.Add("@GRANJA", FbDbType.Integer).Value = ACanal.Granja;
com.Parameters.Add("@LOTE", FbDbType.VarChar).Value = ACanal.Lote;
com.Parameters.Add("@FECHA", FbDbType.Integer).Value = ACanal.Fecha;
com.Parameters.Add("@CANALES", FbDbType.Integer).Value = ACanal.Canales;
FbParameter pOutParameter = new FbParameter("@CLAVE", FbDbType.Integer);
pOutParameter.Direction = ParameterDirection.Output;
com.Parameters.Add(pOutParameter);
try
{
con.Open();
com.ExecuteNonQuery();
}
finally
{
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
}
return ObtenerCanalProgramado((int)pOutParameter.Value);
}
/// <summary>
/// Inserts a row in the mp_BannedIPAddresses table. Returns new integer id.
/// </summary>
/// <param name="bannedIP"> bannedIP </param>
/// <param name="bannedUTC"> bannedUTC </param>
/// <param name="bannedReason"> bannedReason </param>
/// <returns>int</returns>
public int Add(
string bannedIP,
DateTime bannedUtc,
string bannedReason)
{
#region Bit Conversion
#endregion
FbParameter[] arParams = new FbParameter[3];
arParams[0] = new FbParameter(":BannedIP", FbDbType.VarChar, 50);
arParams[0].Value = bannedIP;
arParams[1] = new FbParameter(":BannedUTC", FbDbType.TimeStamp);
arParams[1].Value = bannedUtc;
arParams[2] = new FbParameter(":BannedReason", FbDbType.VarChar, 255);
arParams[2].Value = bannedReason;
int newID = Convert.ToInt32(AdoHelper.ExecuteScalar(
writeConnectionString,
CommandType.StoredProcedure,
"EXECUTE PROCEDURE MP_BANNEDIPADDRESSES_INSERT ("
+ AdoHelper.GetParamString(arParams.Length) + ")",
arParams));
return newID;
}
public static bool ContratoTemItens(string contrato)
{
bool retorno = false;
conn = AcessoDados.AcessoDados.getConn();
FbCommand comando = new FbCommand("select COUNT(*) as contador from sci_licitacao_contrato_itens lci where lci.ctrcod = @CONTRATO",conn);
FbParameter IDCONTRATO = new FbParameter("@CONTRATO", FbDbType.Integer);
IDCONTRATO.Value = contrato;
comando.Parameters.Add(IDCONTRATO);
conn.Open();
FbDataReader dr = comando.ExecuteReader();
while (dr.Read()) {
if (Convert.ToInt16(dr["contador"]) > 0)
{
retorno = true;
}
else {
retorno = false;
}
}
conn.Close();
comando.Dispose();
return retorno;
}
public static bool AccountLockout(Guid userGuid, DateTime lockoutTime)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE mp_Users ");
sqlCommand.Append("SET IsLockedOut = 1, ");
sqlCommand.Append("LastLockoutDate = @LockoutTime ");
sqlCommand.Append("WHERE UserGuid = @UserGuid ;");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@UserGuid", FbDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = userGuid.ToString();
arParams[1] = new FbParameter("@LockoutTime", FbDbType.TimeStamp);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = lockoutTime;
int rowsAffected = FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return (rowsAffected > 0);
}
public LoteNoInventariable InsertarLoteNoInventariable(LoteNoInventariable ALote)
{
string pSentencia = "INSERT INTO LOTES_NO_INVENTARIABLES (LOTE) VALUES (@LOTE) RETURNING ID";
FbConnection con = _Conexion.ObtenerConexion();
FbCommand com = new FbCommand(pSentencia, con);
com.Parameters.Add("@LOTE", FbDbType.Integer).Value = ALote.Lote;
FbParameter pOutParameter = new FbParameter("@ID", FbDbType.Integer);
pOutParameter.Direction = ParameterDirection.Output;
com.Parameters.Add(pOutParameter);
try
{
con.Open();
com.ExecuteNonQuery();
}
finally
{
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
}
return ObtenerLote((int)pOutParameter.Value);
}
public void ConstructorsTest()
{
FbParameter ctor01 = new FbParameter();
FbParameter ctor02 = new FbParameter("ctor2", 10);
FbParameter ctor03 = new FbParameter("ctor3", FbDbType.Char);
FbParameter ctor04 = new FbParameter("ctor4", FbDbType.Integer, 4);
FbParameter ctor05 = new FbParameter("ctor5", FbDbType.Integer, 4, "int_field");
FbParameter ctor06 = new FbParameter(
"ctor6",
FbDbType.Integer,
4,
ParameterDirection.Input,
false,
0,
0,
"int_field",
DataRowVersion.Original,
100);
ctor01 = null;
ctor02 = null;
ctor03 = null;
ctor04 = null;
ctor05 = null;
ctor06 = null;
}
public static void ExecuteProcedureNonQuery(string spName, FbParameter[] parameters, FbTransactionBehavior tranBeh)
{
OpenConnection();
if ((!storedProcs.ContainsKey(spName))
|| (storedProcs[spName].Parameters.Count != parameters.Length))
{
string sql = "";
for (int i = 0; i < parameters.Length; i++)
sql += ",?";
sql = "EXECUTE PROCEDURE " + spName + "(" + sql.Substring(1) + ")";
FbCommand commandProcedure = new FbCommand(sql, mConnection);
commandProcedure.CommandType = CommandType.StoredProcedure;
commandProcedure.Parameters.AddRange(parameters);
storedProcs[spName] = commandProcedure;
}
else
{
for (int i = 0; i < parameters.Length; i++)
storedProcs[spName].Parameters[i].Value = parameters[i].Value;
}
FbTransactionOptions transactionOptions = new FbTransactionOptions();
transactionOptions.TransactionBehavior = tranBeh;
storedProcs[spName].Transaction = mConnection.BeginTransaction(transactionOptions);
storedProcs[spName].ExecuteNonQuery();
storedProcs[spName].Transaction.Commit();
}
public static bool Delete(
string loginProvider,
string providerKey,
string userId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_UserLogins ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("LoginProvider = @LoginProvider AND ");
sqlCommand.Append("ProviderKey = @ProviderKey AND ");
sqlCommand.Append("UserId = @UserId ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[3];
arParams[0] = new FbParameter("@LoginProvider", FbDbType.VarChar, 128);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = loginProvider;
arParams[1] = new FbParameter("@ProviderKey", FbDbType.VarChar, 128);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = providerKey;
arParams[2] = new FbParameter("@UserId", FbDbType.VarChar, 128);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = userId;
int rowsAffected = FBSqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return (rowsAffected > -1);
}
public Stream GetBalance(string CustomerId)
{
FbParameter cust = new FbParameter("customer_id", CustomerId);
WebOperationContext.Current.OutgoingResponse.ContentType = contentType;
return new MemoryStream(Encoding.UTF8.GetBytes(GetJson("customer_account_balance", cust)));
}
public async Task<int> RoleCreate(
Guid roleGuid,
Guid siteGuid,
int siteId,
string roleName)
{
FbParameter[] arParams = new FbParameter[5];
arParams[0] = new FbParameter(":SiteID", FbDbType.Integer);
arParams[0].Value = siteId;
arParams[1] = new FbParameter(":RoleName", FbDbType.VarChar, 50);
arParams[1].Value = roleName;
arParams[2] = new FbParameter(":DisplayName", FbDbType.VarChar, 50);
arParams[2].Value = roleName;
arParams[3] = new FbParameter(":SiteGuid", FbDbType.Char, 36);
arParams[3].Value = siteGuid.ToString();
arParams[4] = new FbParameter(":RoleGuid", FbDbType.Char, 36);
arParams[4].Value = roleGuid.ToString();
object result = await AdoHelper.ExecuteScalarAsync(
writeConnectionString,
CommandType.StoredProcedure,
"EXECUTE PROCEDURE MP_ROLES_INSERT ("
+ AdoHelper.GetParamString(arParams.Length) + ")",
arParams);
int newID = Convert.ToInt32(result);
return newID;
}
protected string _executeNonQuery(FbConnection conn, FbTransaction trans, string query, Dictionary<string, object> prms, bool isReturning)
{
string ret = "";
using (FbCommand cmd = new FbCommand(query, conn, trans))
{
foreach (var prm in prms)
cmd.Parameters.AddWithValue(prm.Key, prm.Value);
if (isReturning)
{
FbParameter outparam = new FbParameter("@out", FbDbType.VarChar)
{
Direction = ParameterDirection.Output
};
cmd.Parameters.Add(outparam);
cmd.ExecuteNonQuery();
ret = outparam.Value as string;
}
else
cmd.ExecuteNonQuery();
}
return ret;
}
public static bool DeleteByModule(int moduleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_MediaFile ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("FileID ");
sqlCommand.Append("IN (");
sqlCommand.Append("SELECT FileID FROM mp_MediaFile WHERE TrackID IN (");
sqlCommand.Append("SELECT TrackID FROM mp_MediaTrack WHERE PlayerID IN (");
sqlCommand.Append("SELECT PlayerID FROM mp_MediaPlayer WHERE ModuleID = @ModuleID");
sqlCommand.Append(")");
sqlCommand.Append(")");
sqlCommand.Append(")");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@ModuleID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
int rowsAffected = FBSqlHelper.ExecuteNonQuery(
ConnectionString.GetWriteConnectionString(),
sqlCommand.ToString(),
arParams);
return (rowsAffected > -1);
}
public DbDataReader GetSiteSettingsExList(int siteId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT e.* ");
sqlCommand.Append("FROM mp_SiteSettingsEx e ");
sqlCommand.Append("JOIN ");
sqlCommand.Append("mp_SiteSettingsExDef d ");
sqlCommand.Append("ON ");
sqlCommand.Append("e.KeyName = d.KeyName ");
sqlCommand.Append("AND e.GroupName = d.GroupName ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("e.SiteID = @SiteID ");
sqlCommand.Append("ORDER BY d.GroupName, d.SortOrder ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[2];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Value = siteId;
return AdoHelper.ExecuteReader(
readConnectionString,
sqlCommand.ToString(),
arParams);
}
public override void execute(Options options, INIManager iniManager)
{
string AppKey = iniManager.IniReadValue("APP", "AppKey");
string AppSecret = iniManager.IniReadValue("APP", "AppSecret");
string TaobaoAsistantPath = iniManager.IniReadValue("淘宝助理", "安装目录");
string nick = options.Nick;
string SessionKey = iniManager.IniReadValue(nick, "SessionKey");
string userId = iniManager.IniReadValue(nick, "UserId");
if (userId == null || userId.Trim().Equals(""))
{
userId = "1696293148";
}
StreamWriter MovePicLogWriter;
FileStream MovePicLog = new FileStream(Environment.CurrentDirectory + "\\" + "MovePic.log", FileMode.Append);
MovePicLogWriter = new StreamWriter(MovePicLog, Encoding.Default);
FbConnectionStringBuilder cs = new FbConnectionStringBuilder();
cs.Database = Path.Combine(TaobaoAsistantPath, "users\\" + nick + "\\APPITEM.DAT");
cs.Charset = "UTF8";
cs.UserID = "SYSDBA";
cs.Password = "******";
cs.ServerType = FbServerType.Embedded;
FbConnection fbCon = new FbConnection(cs.ToString());
fbCon.Open();
FbCommand ItemsCommand = null;
ItemsCommand = new FbCommand("SELECT * FROM ITEM WHERE OUTER_ID = @SearchString OR TITLE = @SearchString", fbCon);
FbParameter SearchString = new FbParameter("@SearchString", FbDbType.VarChar);
SearchString.Value = options.Item;
ItemsCommand.Parameters.Add(SearchString);
FbDataReader ItemsReader = ItemsCommand.ExecuteReader();
while (ItemsReader.Read())
{
MovePicLogWriter.WriteLine("--------------------------------------------------------------------------------");
Console.WriteLine("--------------------------------------------------------------------------------");
string ClientID = ItemsReader["CLIENT_ID"].ToString();
string Title = ItemsReader["TITLE"].ToString();
Console.WriteLine("ClientID=" + ClientID);
MovePicLogWriter.WriteLine("ClientID=" + ClientID);
Console.WriteLine("TITLE=" + Title);
MovePicLogWriter.WriteLine("TITLE=" + Title);
FbTransaction myTransaction = fbCon.BeginTransaction();
FbCommand DeleteCommand = new FbCommand("UPDATE ITEM SET CLIENT_IS_DELETE = 1 WHERE CLIENT_ID = @ClientID", fbCon);
FbParameter ParamID = new FbParameter("@ClientID", FbDbType.VarChar);
ParamID.Value = ClientID;
DeleteCommand.Parameters.Add(ParamID);
DeleteCommand.Transaction = myTransaction;
DeleteCommand.ExecuteNonQuery();
myTransaction.Commit();
}
fbCon.Close();
MovePicLogWriter.Close();
MovePicLog.Close();
}
/// <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)
{
FbParameter[] arParams = new FbParameter[6];
arParams[0] = new FbParameter("@SurveyGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = surveyGuid.ToString();
arParams[1] = new FbParameter("@SiteGuid", FbDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = siteGuid.ToString();
arParams[2] = new FbParameter("@SurveyName", FbDbType.VarChar, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = surveyName;
arParams[3] = new FbParameter("@CreationDate", FbDbType.TimeStamp);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = creationDate;
arParams[4] = new FbParameter("@StartPageText", FbDbType.VarChar);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = startPageText;
arParams[5] = new FbParameter("@EndPageText", FbDbType.VarChar);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = 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(";");
int rowsAffected = FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return rowsAffected;
}
/// <summary>
/// Inserts a row in the mp_TaxClass table. Returns rows affected count.
/// </summary>
/// <param name="guid"> guid </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="title"> title </param>
/// <param name="description"> description </param>
/// <param name="lastModified"> lastModified </param>
/// <param name="created"> created </param>
/// <returns>int</returns>
public static int Create(
Guid guid,
Guid siteGuid,
string title,
string description,
DateTime lastModified,
DateTime created)
{
FbParameter[] arParams = new FbParameter[6];
arParams[0] = new FbParameter("@Guid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = guid.ToString();
arParams[1] = new FbParameter("@SiteGuid", FbDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = siteGuid.ToString();
arParams[2] = new FbParameter("@Title", FbDbType.VarChar, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = title;
arParams[3] = new FbParameter("@Description", FbDbType.VarChar);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = description;
arParams[4] = new FbParameter("@LastModified", FbDbType.TimeStamp);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = lastModified;
arParams[5] = new FbParameter("@Created", FbDbType.TimeStamp);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = created;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_TaxClass (");
sqlCommand.Append("Guid, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("Title, ");
sqlCommand.Append("Description, ");
sqlCommand.Append("LastModified, ");
sqlCommand.Append("Created )");
sqlCommand.Append(" VALUES (");
sqlCommand.Append("@Guid, ");
sqlCommand.Append("@SiteGuid, ");
sqlCommand.Append("@Title, ");
sqlCommand.Append("@Description, ");
sqlCommand.Append("@LastModified, ");
sqlCommand.Append("@Created )");
sqlCommand.Append(";");
int rowsAffected = FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return rowsAffected;
}
public Stream GetArticles(string CustomerId)
{
FbParameter cust = new FbParameter("customer_id", CustomerId);
FbParameter filter = new FbParameter("filter", null);
WebOperationContext.Current.OutgoingResponse.ContentType = contentType;
return new MemoryStream(Encoding.UTF8.GetBytes(GetJson("article_list", "articles", cust, filter)));
}
public Stream GetArticle(string CustomerId, string ArticleId)
{
FbParameter cust = new FbParameter("customer_id", int.Parse(CustomerId));
FbParameter article = new FbParameter("article_id", int.Parse(ArticleId));
WebOperationContext.Current.OutgoingResponse.ContentType = contentType;
return new MemoryStream(Encoding.UTF8.GetBytes(GetJson("article_detail", cust, article)));
}
/// <summary>
/// Inserts a row in the mp_SystemLog table. Returns new integer id.
/// </summary>
/// <param name="logDate"> logDate </param>
/// <param name="ipAddress"> ipAddress </param>
/// <param name="culture"> culture </param>
/// <param name="url"> url </param>
/// <param name="shortUrl"> shortUrl </param>
/// <param name="thread"> thread </param>
/// <param name="logLevel"> logLevel </param>
/// <param name="logger"> logger </param>
/// <param name="message"> message </param>
/// <returns>int</returns>
public static int Create(
DateTime logDate,
string ipAddress,
string culture,
string url,
string shortUrl,
string thread,
string logLevel,
string logger,
string message)
{
FbParameter[] arParams = new FbParameter[9];
arParams[0] = new FbParameter(":LogDate", FbDbType.TimeStamp);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = logDate;
arParams[1] = new FbParameter(":IpAddress", FbDbType.VarChar, 50);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = ipAddress;
arParams[2] = new FbParameter(":Culture", FbDbType.VarChar, 10);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = culture;
arParams[3] = new FbParameter(":Url", FbDbType.VarChar);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = url;
arParams[4] = new FbParameter(":ShortUrl", FbDbType.VarChar, 255);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = shortUrl;
arParams[5] = new FbParameter(":Thread", FbDbType.VarChar, 255);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = thread;
arParams[6] = new FbParameter(":LogLevel", FbDbType.VarChar, 20);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = logLevel;
arParams[7] = new FbParameter(":Logger", FbDbType.VarChar, 255);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = logger;
arParams[8] = new FbParameter(":Message", FbDbType.VarChar);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = message;
int newID = Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetWriteConnectionString(),
CommandType.StoredProcedure,
"EXECUTE PROCEDURE MP_SYSTEMLOG_INSERT ("
+ FBSqlHelper.GetParamString(arParams.Length) + ")",
arParams));
return newID;
}
/// <summary>
/// Inserts a row in the mp_SurveyPages table. Returns rows affected count.
/// </summary>
/// <param name="pageGuid"> pageGuid </param>
/// <param name="surveyGuid"> surveyGuid </param>
/// <param name="pageTitle"> pageTitle </param>
/// <param name="pageOrder"> pageOrder </param>
/// <param name="pageEnabled"> pageEnabled </param>
/// <returns>int</returns>
public static int Add(
Guid pageGuid,
Guid surveyGuid,
string pageTitle,
bool pageEnabled)
{
#region Bit Conversion
int intPageEnabled;
if (pageEnabled)
{
intPageEnabled = 1;
}
else
{
intPageEnabled = 0;
}
#endregion
FbParameter[] arParams = new FbParameter[4];
arParams[0] = new FbParameter("@PageGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = pageGuid.ToString();
arParams[1] = new FbParameter("@SurveyGuid", FbDbType.Char, 36);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = surveyGuid.ToString();
arParams[2] = new FbParameter("@PageTitle", FbDbType.VarChar, 255);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = pageTitle;
arParams[3] = new FbParameter("@PageEnabled", FbDbType.SmallInt);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = intPageEnabled;
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_SurveyPages ");
sqlCommand.Append("(PageGuid, ");
sqlCommand.Append("SurveyGuid, ");
sqlCommand.Append("PageTitle, ");
sqlCommand.Append("PageOrder, ");
sqlCommand.Append("PageEnabled) ");
sqlCommand.Append("SELECT @PageGuid, @SurveyGuid, @PageTitle, ");
sqlCommand.Append("Count(*), @PageEnabled FROM mp_SurveyPages; ");
int rowsAffected = FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return rowsAffected;
}
private Descriptor BuildPlaceHoldersDescriptor(short count)
{
Descriptor descriptor = new Descriptor(count);
int index = 0;
for (int i = 0; i < Parameters.Count; i++)
{
FbParameter parameter = Parameters[i];
if (parameter.Direction == ParameterDirection.Input ||
parameter.Direction == ParameterDirection.InputOutput)
{
if (!BuildParameterDescriptor(descriptor, parameter, index++))
{
return(null);
}
}
}
return(descriptor);
}
private void EnsureFbParameterAddOrInsert(FbParameter value)
{
if (value == null)
{
throw new ArgumentException("The value parameter is null.");
}
if (value.Parent != null)
{
throw new ArgumentException("The FbParameter specified in the value parameter is already added to this or another FbParameterCollection.");
}
if (value.ParameterName == null || value.ParameterName.Length == 0)
{
value.ParameterName = GenerateParameterName();
}
else
{
if (IndexOf(value) != -1)
{
throw new ArgumentException("FbParameterCollection already contains FbParameter with ParameterName '" + value.ParameterName + "'.");
}
}
}
private void EnsureFbParameterAddOrInsert(FbParameter value)
{
if (value == null)
{
throw new ArgumentNullException();
}
if (value.Parent != null)
{
throw new ArgumentException($"The {nameof(FbParameter)} specified in the value parameter is already added to this or another {nameof(FbParameterCollection)}.");
}
if (value.ParameterName == null || value.ParameterName.Length == 0)
{
value.ParameterName = GenerateParameterName();
}
else
{
if (Contains(value.ParameterName))
{
throw new ArgumentException($"{nameof(FbParameterCollection)} already contains {nameof(FbParameter)} with {nameof(FbParameter.ParameterName)} '{value.ParameterName}'.");
}
}
}
public static void DeriveParameters(FbCommand command)
{
if (command.CommandType != CommandType.StoredProcedure)
{
throw new InvalidOperationException("DeriveParameters only supports CommandType.StoredProcedure.");
}
string spName = command.CommandText.Trim();
string quotePrefix = "\"";
string quoteSuffix = "\"";
if (spName.StartsWith(quotePrefix) && spName.EndsWith(quoteSuffix))
{
spName = spName.Substring(1, spName.Length - 2);
}
else
{
spName = spName.ToUpper(CultureInfo.CurrentUICulture);
}
string paramsText = string.Empty;
command.Parameters.Clear();
DataView dataTypes = command.Connection.GetSchema("DataTypes").DefaultView;
DataTable spSchema = command.Connection.GetSchema(
"ProcedureParameters", new string[] { null, null, spName });
// SP has zero params. or not exist
// so check whether exists, else thow exception
if (spSchema.Rows.Count == 0)
{
if (command.Connection.GetSchema("Procedures", new string[] { null, null, spName }).Rows.Count == 0)
{
throw new InvalidOperationException("Stored procedure doesn't exist.");
}
}
foreach (DataRow row in spSchema.Rows)
{
dataTypes.RowFilter = String.Format(
CultureInfo.CurrentUICulture,
"TypeName = '{0}'",
row["PARAMETER_DATA_TYPE"]);
FbParameter parameter = command.Parameters.Add(
"@" + row["PARAMETER_NAME"].ToString().Trim(),
FbDbType.VarChar);
parameter.FbDbType = (FbDbType)dataTypes[0]["ProviderDbType"];
parameter.Direction = (ParameterDirection)row["PARAMETER_DIRECTION"];
parameter.Size = Convert.ToInt32(row["PARAMETER_SIZE"], CultureInfo.InvariantCulture);
if (parameter.FbDbType == FbDbType.Decimal ||
parameter.FbDbType == FbDbType.Numeric)
{
if (row["NUMERIC_PRECISION"] != DBNull.Value)
{
parameter.Precision = Convert.ToByte(row["NUMERIC_PRECISION"], CultureInfo.InvariantCulture);
}
if (row["NUMERIC_SCALE"] != DBNull.Value)
{
parameter.Scale = Convert.ToByte(row["NUMERIC_SCALE"], CultureInfo.InvariantCulture);
}
}
}
}
private void AttachParameter(FbParameter parameter)
{
parameter.Parent = this;
}
private void ReleaseParameter(FbParameter parameter)
{
parameter.Parent = null;
}
public void Insert(int index, FbParameter value)
{
this.parameters.Insert(index, value);
}
private bool BuildParameterDescriptor(Descriptor descriptor, FbParameter parameter, int index)
{
if (!parameter.IsTypeSet)
{
return(false);
}
var type = parameter.FbDbType;
var charset = _connection.InnerConnection.Database.Charset;
// Check the parameter character set
if (parameter.Charset == FbCharset.Octets && !(parameter.InternalValue is byte[]))
{
throw new InvalidOperationException("Value for char octets fields should be a byte array");
}
else if (type == FbDbType.Guid)
{
charset = Charset.GetCharset(Charset.Octets);
}
else if (parameter.Charset != FbCharset.Default)
{
charset = Charset.GetCharset((int)parameter.Charset);
}
// Set parameter Data Type
descriptor[index].DataType = (short)TypeHelper.GetSqlTypeFromDbDataType(TypeHelper.GetDbDataTypeFromFbDbType(type), parameter.IsNullable);
// Set parameter Sub Type
switch (type)
{
case FbDbType.Binary:
descriptor[index].SubType = 0;
break;
case FbDbType.Text:
descriptor[index].SubType = 1;
break;
case FbDbType.Guid:
descriptor[index].SubType = (short)charset.Identifier;
break;
case FbDbType.Char:
case FbDbType.VarChar:
descriptor[index].SubType = (short)charset.Identifier;
if (charset.IsOctetsCharset)
{
descriptor[index].Length = (short)parameter.Size;
}
else if (parameter.HasSize)
{
var len = (short)(parameter.Size * charset.BytesPerCharacter);
descriptor[index].Length = len;
}
break;
}
// Set parameter length
if (descriptor[index].Length == 0)
{
descriptor[index].Length = TypeHelper.GetSize((DbDataType)type) ?? 0;
}
// Verify parameter
if (descriptor[index].SqlType == 0 || descriptor[index].Length == 0)
{
return(false);
}
return(true);
}
public bool Contains(FbParameter value)
{
return(_parameters.Contains(value));
}
public int IndexOf(FbParameter value)
{
return(_parameters.IndexOf(value));
}