/// <summary>
/// Gets a count of rows in the mp_LetterInfo table.
/// </summary>
public static int GetCount(Guid siteGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_LetterInfo ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = @SiteGuid ;");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@SiteGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteGuid.ToString();
return(Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams)));
}
public static IDataReader GetAllImages(int moduleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_GalleryImages ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ModuleID = @ModuleID ;");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@ModuleID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_LetterInfo table.
/// </summary>
/// <param name="letterInfoGuid"> letterInfoGuid </param>
public static IDataReader GetOne(Guid letterInfoGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_LetterInfo ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("LetterInfoGuid = @LetterInfoGuid ;");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@LetterInfoGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = letterInfoGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static bool DeleteByUser(Guid userGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_UserPages ");
sqlCommand.Append("WHERE UserGuid = @UserGuid; ");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@UserGuid", FbDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = userGuid.ToString();
int rowsAffected = FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
/// <summary>
/// Gets a count of rows in the mp_ContactFormMessage table.
/// </summary>
public static int GetCount(Guid moduleGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_ContactFormMessage ");
sqlCommand.Append("WHERE ModuleGuid = @ModuleGuid ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@ModuleGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleGuid.ToString();
return Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets a count of rows for a particular player in the doan_MediaTracks table.
/// </summary>
/// <param name="playerID">The ID of the player.</param>
/// <returns>The count of rows.</returns>
public static int GetCountByPlayer(int playerId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_MediaTrack ");
sqlCommand.Append("WHERE PlayerID = @PlayerID ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@PlayerID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = playerId;
return(Convert.ToInt32(FBSqlHelper.ExecuteScalar(
ConnectionString.GetReadConnectionString(),
sqlCommand.ToString(),
arParams)));
}
public int GetCountUnfinishedByType(string taskType)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_TaskQueue ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("UPPER(SerializedTaskType) LIKE UPPER(@TaskType) ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@TaskType", FbDbType.VarChar, 255);
arParams[0].Value = taskType + "%";
return(Convert.ToInt32(AdoHelper.ExecuteScalar(
readConnectionString,
sqlCommand.ToString(),
arParams)));
}
/// <summary>
/// Gets a count of rows in the mp_UserLocation table.
/// </summary>
/// <param name="siteGuid"> siteGuid </param>
public int GetCountBySite(Guid siteGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT Count(*) ");
sqlCommand.Append("FROM mp_UserLocation ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = @SiteGuid ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@SiteGuid", FbDbType.Char, 36);
arParams[0].Value = siteGuid.ToString();
return(Convert.ToInt32(AdoHelper.ExecuteScalar(
readConnectionString,
sqlCommand.ToString(),
arParams)));
}
public ActionResult Pay(int id)
{
try
{
var INVOICE_ID = new FbParameter("INVOICE_ID", FbDbType.Integer);
// инициализируем параметры значениями
INVOICE_ID.Value = id;
// выполняем ХП
db.Database.ExecuteSqlCommand(
"EXECUTE PROCEDURE SP_PAY_FOR_INOVICE(@INVOICE_ID)",
INVOICE_ID);
// возвращаем успех в формате JSON
return(Json(true));
}
catch (Exception ex)
{
// возвращаем ошибку в формате JSON
return(Json(new { error = ex.Message }));
}
}
/// <summary>
/// Gets an IDataReader with one row from the mp_UserLocation table.
/// </summary>
/// <param name="rowID"> rowID </param>
public DbDataReader GetOne(Guid rowID)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_UserLocation ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("RowID = @RowID ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@RowID", FbDbType.Char, 36);
arParams[0].Value = rowID.ToString();
return(AdoHelper.ExecuteReader(
readConnectionString,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_GeoCountry table.
/// </summary>
/// <param name="countryISOCode2"> countryISOCode2 </param>
public async Task <DbDataReader> GetByISOCode2(string countryISOCode2)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_GeoCountry ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ISOCode2 = @ISOCode2 ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@ISOCode2", FbDbType.Char, 2);
arParams[0].Value = countryISOCode2;
return(await AdoHelper.ExecuteReaderAsync(
readConnectionString,
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Deletes a row from the mp_UserLocation table. Returns true if row deleted.
/// </summary>
/// <param name="rowID"> rowID </param>
/// <returns>bool</returns>
public bool Delete(Guid rowID)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_UserLocation ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("RowID = @RowID ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@RowID", FbDbType.Char, 36);
arParams[0].Value = rowID.ToString();
int rowsAffected = AdoHelper.ExecuteNonQuery(
writeConnectionString,
sqlCommand.ToString(),
arParams);
return(rowsAffected > -1);
}
/// <summary>
/// Gets an IDataReader with rows from the mp_BannedIPAddresses table.
/// </summary>
/// <param name="ipAddress"> ipAddress </param>
public static IDataReader GeByIpAddress(string ipAddress)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_BannedIPAddresses ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("BannedIP = @BannedIP ;");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@BannedIP", FbDbType.VarChar, 50);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = ipAddress;
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
/// <summary>
/// Gets an IDataReader with one row from the mp_BannedIPAddresses table.
/// </summary>
/// <param name="rowID"> rowID </param>
public static IDataReader GetOne(int rowId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_BannedIPAddresses ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("RowID = @RowID ;");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@RowID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = rowId;
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
public async Task <bool> DeleteBySite(Guid siteGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_SiteFolders ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("SiteGuid = @SiteGuid ;");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@SiteGuid", FbDbType.VarChar, 36);
arParams[0].Value = siteGuid.ToString();
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
public static IDataReader GetWebPart(Guid webPartId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_WebParts ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("WebPartID = @WebPartID ;");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@WebPartID", FbDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = webPartId.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static bool Delete(int roleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_Roles ");
sqlCommand.Append("WHERE RoleID = @RoleID AND RoleName <> 'Admins' AND RoleName <> 'Content Administrators' AND RoleName <> 'Authenticated Users' AND RoleName <> 'Role Admins' ;");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@RoleID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = roleId;
int rowsAffected = FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
public async Task <bool> DeleteUserRolesByRole(int roleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_UserRoles ");
sqlCommand.Append("WHERE RoleID = @RoleID ;");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@RoleID", FbDbType.Integer);
arParams[0].Value = roleId;
int rowsAffected = await AdoHelper.ExecuteNonQueryAsync(
writeConnectionString,
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
public static IDataReader GetLink(int itemId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_Links ");
sqlCommand.Append("WHERE ItemID = @ItemID ;");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@ItemID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = itemId;
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static bool DeleteLink(int itemId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_Links ");
sqlCommand.Append("WHERE ItemID = @ItemID ;");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@ItemID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = itemId;
int rowsAffected = FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
public static void RemoveFromModule(int moduleId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE ");
sqlCommand.Append("FROM mp_SurveyModules ");
sqlCommand.Append("WHERE ModuleId = @ModuleId ");
sqlCommand.Append("; ");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@ModuleID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleId;
FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
}
public static IDataReader SchemaScriptHistoryGetSchemaScriptHistory(int id)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_SchemaScriptHistory ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ID = @ID ;");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@ID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = id;
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static int GetCountOfSiteRoles(int siteId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT COUNT(*) ");
sqlCommand.Append("FROM mp_Roles ");
sqlCommand.Append("WHERE SiteID = @SiteID ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
return(Convert.ToInt32(FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams)));
}
public DbDataReader GetSiteRoles(int siteId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("r.RoleID, ");
sqlCommand.Append("r.SiteID, ");
sqlCommand.Append("r.RoleName, ");
sqlCommand.Append("r.DisplayName, ");
sqlCommand.Append("r.SiteGuid, ");
sqlCommand.Append("r.RoleGuid, ");
sqlCommand.Append("COUNT(ur.UserID) As MemberCount ");
sqlCommand.Append("FROM mp_Roles r ");
sqlCommand.Append("LEFT OUTER JOIN mp_UserRoles ur ");
sqlCommand.Append("ON ur.RoleID = r.RoleID ");
sqlCommand.Append("WHERE r.SiteID = @SiteID ");
sqlCommand.Append("GROUP BY ");
sqlCommand.Append("r.RoleID, ");
sqlCommand.Append("r.SiteID, ");
sqlCommand.Append("r.RoleName, ");
sqlCommand.Append("r.DisplayName, ");
sqlCommand.Append("r.SiteGuid, ");
sqlCommand.Append("r.RoleGuid ");
sqlCommand.Append("ORDER BY r.DisplayName ");
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));
}
/// <summary>
/// Gets the previous page Guid in the survey
/// </summary>
/// <param name="pageGuid">Current page Guid</param>
/// <returns></returns>
public static Guid GetPreviousPageGuid(Guid pageGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT FIRST 1 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 DESC ");
sqlCommand.Append("; ");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@PageGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = pageGuid.ToString();
object id = FBSqlHelper.ExecuteScalar(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
if (id == null)
{
return(Guid.Empty);
}
return(new Guid(id.ToString()));
}
private bool cadastrarPiquet()
{
bool retorno;
FbConnection fbConn = new FbConnection(frmHome.strConn);
string queryInsert = string.Format("INSERT INTO PIQUET (NUMERO,NOME, ID_FAZENDA) VALUES (@NUMERO,@NOME,{0})",
frmHome.IDFazendaSelecionada);
FbCommand fbCmdInsert = new FbCommand();
//PARAMETROS
FbParameter[] prmParametro = new FbParameter[2];
prmParametro[0] = new FbParameter("@NUMERO", txtNumeroPiquet.Text);
prmParametro[1] = new FbParameter("@NOME", txtNomePiquet.Text);
foreach (FbParameter p in prmParametro)
{
fbCmdInsert.Parameters.Add(p);
}
try
{
fbConn.Open();
fbCmdInsert.Connection = fbConn;
fbCmdInsert.CommandType = CommandType.Text;
fbCmdInsert.CommandText = queryInsert;
fbCmdInsert.ExecuteNonQuery();
retorno = true;
}
catch (FbException fbex)
{
MessageBox.Show("Erro ao acessar o Banco de Dados: " + fbex.Message, "Erro");
retorno = false;
}
finally
{
fbConn.Close();
}
return(retorno);
}
public static IDataReader GetByLetter(Guid letterInfoGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT ");
sqlCommand.Append("ls.Guid, ");
sqlCommand.Append("ls.SiteGuid, ");
sqlCommand.Append("ls.LetterInfoGuid, ");
sqlCommand.Append("ls.UserGuid, ");
sqlCommand.Append("ls.IsVerified, ");
sqlCommand.Append("ls.VerifyGuid, ");
sqlCommand.Append("ls.BeginUtc, ");
sqlCommand.Append("ls.UseHtml, ");
sqlCommand.Append("ls.IpAddress, ");
sqlCommand.Append("COALESCE(u.Email, ls.Email) As Email, ");
sqlCommand.Append("u.Email AS UserEmail, ");
sqlCommand.Append("COALESCE(u.Name, ls.Email) AS Name, ");
sqlCommand.Append("u.FirstName, ");
sqlCommand.Append("u.LastName ");
sqlCommand.Append("FROM mp_LetterSubscribe ls ");
sqlCommand.Append("LEFT OUTER JOIN mp_Users u ");
sqlCommand.Append("ON ");
sqlCommand.Append("u.UserGuid = ls.UserGuid ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ls.LetterInfoGuid = @LetterInfoGuid ");
sqlCommand.Append(";");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@LetterInfoGuid", FbDbType.Char, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = letterInfoGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetReadConnectionString(),
sqlCommand.ToString(),
arParams));
}
public static bool DeleteModuleDefinitionFromSites(int moduleDefId)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("DELETE FROM mp_SiteModuleDefinitions ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("ModuleDefID = @ModuleDefID ;");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@ModuleDefID", FbDbType.Integer);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = moduleDefId;
int rowsAffected = FBSqlHelper.ExecuteNonQuery(
GetConnectionString(),
sqlCommand.ToString(),
arParams);
return(rowsAffected > 0);
}
public static IDataReader GetModuleDefinition(
Guid featureGuid)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * ");
sqlCommand.Append("FROM mp_ModuleDefinitions ");
sqlCommand.Append("WHERE ");
sqlCommand.Append("Guid = @FeatureGuid ;");
FbParameter[] arParams = new FbParameter[1];
arParams[0] = new FbParameter("@FeatureGuid", FbDbType.VarChar, 36);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = featureGuid.ToString();
return(FBSqlHelper.ExecuteReader(
GetConnectionString(),
sqlCommand.ToString(),
arParams));
}
public void Execute()
{
using (var conn = new FbConnection(ConnectionString))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = @"insert into foobar values (@cnt)";
var p = new FbParameter()
{
ParameterName = "@cnt"
};
cmd.Parameters.Add(p);
for (var i = 0; i < Count; i++)
{
p.Value = i;
cmd.ExecuteNonQuery();
}
}
}
}
