public void Delete(Core.Business.InstrumentRecommend instrumentRecommend)
{
SqlServerUtility sql = new SqlServerUtility();
sql.AddParameter("@Id", SqlDbType.UniqueIdentifier, instrumentRecommend.Id);
sql.ExecuteSql(SqlDeleteInstrumentRecommend);
}
public void SetRotation(string strPicture, int iRotation)
{
// Continue only if it's a picture files
if (!Util.Utils.IsPicture(strPicture))
{
return;
}
string strSQL = "";
try
{
string strPic = strPicture;
DatabaseUtility.RemoveInvalidChars(ref strPic);
long lPicId = AddPicture(strPicture, iRotation);
if (lPicId >= 0)
{
strSQL = String.Format("update tblPicture set iRotation={0} where strFile like '{1}'", iRotation, strPic);
SqlServerUtility.ExecuteNonQuery(_connection, strSQL);
}
}
catch (Exception ex)
{
Log.Error("MediaPortal.Picture.Database exception err:{0} stack:{1}", ex.Message, ex.StackTrace);
}
}
public void Insert(Core.Business.InstrumentRecommend instrumentRecommend)
{
SqlServerUtility sql = new SqlServerUtility();
sql.AddParameter("@Autor", SqlDbType.UniqueIdentifier, instrumentRecommend.Autor);
sql.AddParameter("@RecommendDate", SqlDbType.DateTime, instrumentRecommend.RecommendDate);
sql.AddParameter("@Commend", SqlDbType.NVarChar, instrumentRecommend.Commend);
SqlDataReader reader = sql.ExecuteSqlReader(SqlInsertInstrumentRecommend);
if (reader != null && !reader.IsClosed && reader.Read())
{
if (!reader.IsDBNull(0))
{
instrumentRecommend.Id = reader.GetGuid(0);
}
reader.Close();
}
else
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
}
}
public void EncodeLikeStringTest()
{
Assert.AreEqual <string>(null, SqlServerUtility.EncodeLikeString(null));
Assert.AreEqual <string>(" \t \r \n ", SqlServerUtility.EncodeLikeString(" \t \r \n "));
Assert.AreEqual <string>("abcd", SqlServerUtility.EncodeLikeString("abcd"));
Assert.AreEqual <string>("a[%]b[_]c[[]d]e[^]f", SqlServerUtility.EncodeLikeString("a%b_c[d]e^f"));
}
/// <summary>
/// Guarda um User com os seus dados na Base de Dados
/// </summary>
public override void saveUser(User user, string password)
{
Hashtable param = new Hashtable();
param.Add("@user_id", user.UserId);
if (password != string.Empty)
{
param.Add("@user_pass", hashPassword(password));
}
param.Add("@user_ruler_id", user.RulerId);
param.Add("@user_nick", user.Nick);
param.Add("@user_skin", user.Skin);
param.Add("@user_lang", user.Lang);
param.Add("@user_website", user.Website);
param.Add("@user_avatar", user.Avatar);
param.Add("@user_msn", user.Msn);
param.Add("@user_icq", user.Icq);
param.Add("@user_jabber", user.Jabber);
param.Add("@user_aim", user.Aim);
param.Add("@user_yahoo", user.Yahoo);
param.Add("@user_signature", user.Signature);
param.Add("@user_imagesDir", user.ImagesDir);
param.Add("@user_rank", user.EloRanking);
SqlServerUtility.executeNonQuery("OrionsBelt_UsersUpdateUser", param);
}
public void Insert(Core.Business.ExhibitionInstrument exhibitionInstrument)
{
SqlServerUtility sql = new SqlServerUtility();
sql.AddParameter("@InstrumentID", SqlDbType.UniqueIdentifier, exhibitionInstrument.InstrumentID);
sql.AddParameter("@ExhibitionDate", SqlDbType.DateTime, exhibitionInstrument.ExhibitionDate);
sql.AddParameter("@SequenceNumber", SqlDbType.Int, exhibitionInstrument.SequenceNumber);
sql.AddParameter("@IsShowinHomePage", SqlDbType.Int, exhibitionInstrument.IsShowinHomePage);
sql.AddOutputParameter("@Id", SqlDbType.UniqueIdentifier);
SqlDataReader reader = sql.ExecuteSPReader("usp_InsertExhibitionInstrument");
if (reader != null && !reader.IsClosed && reader.Read())
{
if (!reader.IsDBNull(0))
{
exhibitionInstrument.Id = reader.GetGuid(0);
}
reader.Close();
}
else
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
}
}
public void Delete(Core.Business.ExhibitionInstrument exhibitionInstrument)
{
SqlServerUtility sql = new SqlServerUtility();
sql.AddParameter("@Id", SqlDbType.UniqueIdentifier, exhibitionInstrument.Id);
sql.ExecuteSP("usp_DeleteExhibitionInstrument");
}
public void Save_blob_and_retrieve_it_fetches_it_from_contents_column()
{
SqlServerUtility.RemoveAllTablesFromDefaultDatabase(TestDatabases.SqlServerLobTests);
var configuration = new Configuration();
configuration.SetProperty("connection.provider", typeof(ExternalBlobDriverConnectionProvider).AssemblyQualifiedName);
configuration.SetProperty("dialect", typeof(MsSql2008Dialect).AssemblyQualifiedName);
configuration.SetProperty("connection.driver_class", typeof(SqlClientDriver).AssemblyQualifiedName);
configuration.SetProperty("connection.connection_string", TestDatabases.SqlServerLobTests);
configuration.SetProperty("connection.lob.external.provider", typeof(ByteArrayConnectionProvider).AssemblyQualifiedName);
configuration.SetProperty("show_sql", "true");
configuration.AddAssembly(typeof(Image).Assembly);
new SchemaExport(configuration).Execute(false, true, false);
ISessionFactory sessionFactory = configuration.BuildSessionFactory();
var image = new Image
{
FileName = "test.txt",
ContentType = "text/plain",
Size = 10,
Contents = Blob.Create(new byte[] { 1, 2, 3, 4 }),
Title = "test"
};
Guid imageId;
using (ISession session = sessionFactory.OpenSession())
{
using (ITransaction tx = session.BeginTransaction())
{
session.Save(image);
tx.Commit();
imageId = image.Id;
}
}
using (ISession session = sessionFactory.OpenSession())
{
using (ITransaction tx = session.BeginTransaction())
{
var imageFromDb = session.Get <Image>(imageId);
using (Stream streamReader = imageFromDb.Contents.OpenReader())
{
var buffer = new byte[1024];
int length = streamReader.Read(buffer, 0, buffer.Length);
Assert.Equal(4, length);
Assert.Equal(1, buffer[0]);
Assert.Equal(2, buffer[1]);
Assert.Equal(3, buffer[2]);
Assert.Equal(4, buffer[3]);
}
}
}
}
/// <summary>
/// Verifica se existe um user com o mail passado
/// </summary>
public override bool checkUser(string mail)
{
Hashtable param = new Hashtable();
param.Add("@user_mail", mail);
return(SqlServerUtility.checkResults("OrionsBelt_UsersCheckUserMail", param));
}
/// <summary>Actualiza a data do ltimo login</summary>
public override void updateLastLogin(string userMail)
{
Hashtable param = new Hashtable();
param.Add("@user_mail", userMail);
SqlServerUtility.executeNonQuery("OrionsBelt_UsersUpdateLastLogin", param);
}
/// <summary>Regista uma nova notícia</summary>
protected override void Register(Entry entry)
{
Hashtable parameters = new Hashtable();
parameters.Add("@title", entry.Title);
parameters.Add("@content", entry.Content);
SqlServerUtility.executeNonQuery("OrionsBelt_InsertNews", parameters);
}
/// <summary>
/// Regista um User
/// </summary>
public override void registerUser(string nick, string mail, string pass)
{
Hashtable param = new Hashtable();
param.Add("@user_mail", mail);
param.Add("@user_pass", hashPassword(pass));
param.Add("@user_nick", nick);
param.Add("@user_lang", CultureModule.RequestLanguage);
SqlServerUtility.executeNonQuery("OrionsBelt_UsersRegisterUser", param);
}
/// <summary>Obtm todos os utilizadores com a role passada</summary>
public override int[] getUsers(int role)
{
Hashtable param = new Hashtable();
param.Add("@role", role);
DataSet ds = SqlServerUtility.getFromDB("OrionsBelt_UsersGetUsersIdByRole", param);
return(fetchUsersIds(ds));
}
/// <summary>Obtm um scan dado o seu id</summary>
public override Scan GetScan(int id)
{
Hashtable parameters = new Hashtable();
parameters.Add("@id", id);
DataSet ds = SqlServerUtility.getFromDB("OrionsBelt_GetScansById", parameters);
return(DataSetToScans(ds)[0]);
}
/// <summary>Obtém as notícias existentes por linguagem </summary>
protected override NewsList GetNewsFromDBByLang(string lang)
{
Hashtable parameters = new Hashtable();
parameters.Add("@lang", lang);
DataSet ds = SqlServerUtility.getFromDB("OrionsBelt_GetNewsByLang", parameters);
return(NewsFromDataSet(ds));
}
private void CreateTables()
{
SqlServerUtility.AddTable(_connection, "tblFolderPath",
"CREATE TABLE tblFolderPath ( idPath int IDENTITY(1,1) NOT NULL, strPath varchar(2048))");
SqlServerUtility.AddPrimaryKey(_connection, "tblFolderPath", "idPath");
SqlServerUtility.AddTable(_connection, "tblFolderSetting",
"CREATE TABLE tblFolderSetting ( idSetting int IDENTITY(1,1) NOT NULL, idPath int NOT NULL, tagName varchar(2048), tagValue varchar(2048))");
SqlServerUtility.AddPrimaryKey(_connection, "tblFolderSetting", "idSetting");
}
public static void AfterFeature()
{
// Remove all registered users from the database
using (var sqlServerDb = new SqlServerUtility(ConfigurationHelpers.ConnectionString))
{
var sqlServerCmd = "DELETE FROM [dbo].[AspNetUsers];";
var sqlServerParas = new List <SqlQueryParameter>();
sqlServerDb.Delete(sqlServerCmd, sqlServerParas);
}
}
public void Update(Core.Business.InstrumentRecommend instrumentRecommend)
{
SqlServerUtility sql = new SqlServerUtility();
sql.AddParameter("@Autor", SqlDbType.UniqueIdentifier, instrumentRecommend.Autor);
sql.AddParameter("@RecommendDate", SqlDbType.DateTime, instrumentRecommend.RecommendDate);
sql.AddParameter("@Commend", SqlDbType.NVarChar, instrumentRecommend.Commend);
sql.AddParameter("@Id", SqlDbType.UniqueIdentifier, instrumentRecommend.Id);
sql.ExecuteSql(SqlUpdateInstrumentRecommend);
}
/// <summary>Obtm todos os scans de um planeta</summary>
public override Scan[] GetScans(Planet scanner)
{
Hashtable parameters = new Hashtable();
parameters.Add("@targetPlanetOwner", scanner.Id);
DataSet ds = SqlServerUtility.getFromDB("OrionsBelt_GetScansByPlanetId", parameters);
return(DataSetToScans(ds));
}
public void Update(Core.Business.ExhibitionInstrument exhibitionInstrument)
{
SqlServerUtility sql = new SqlServerUtility();
sql.AddParameter("@InstrumentID", SqlDbType.UniqueIdentifier, exhibitionInstrument.InstrumentID);
sql.AddParameter("@ExhibitionDate", SqlDbType.DateTime, exhibitionInstrument.ExhibitionDate);
sql.AddParameter("@SequenceNumber", SqlDbType.Int, exhibitionInstrument.SequenceNumber);
sql.AddParameter("@IsShowinHomePage", SqlDbType.Int, exhibitionInstrument.IsShowinHomePage);
sql.AddParameter("@Id", SqlDbType.UniqueIdentifier, exhibitionInstrument.Id);
sql.ExecuteSP("usp_UpdateExhibitionInstrument");
}
/// <summary>
/// Salva a informação da excepção
/// </summary>
/// <param name="exceptionInfo">objecto que encapsula a informação da excepção</param>
public override void save(ExceptionInfo exceptionInfo)
{
Hashtable parameters = new Hashtable();
parameters.Add("@name", exceptionInfo.Name);
parameters.Add("@message", exceptionInfo.Message);
parameters.Add("@stackTrace", exceptionInfo.StackTrace);
parameters.Add("@date", exceptionInfo.Date);
SqlServerUtility.executeNonQuery("OrionsBelt_ExceptionLogSave", parameters);
}
/// <summary>Regista um scan</summary>
public override void Register(Scan scan)
{
MemoryStream stream = new MemoryStream();
formatter.Serialize(stream, scan);
byte[] data = stream.ToArray();
Hashtable parameters = new Hashtable();
parameters.Add("@sourcePlanetId", scan.SourcePlanetId);
parameters.Add("@data", data);
SqlServerUtility.executeNonQuery("OrionsBelt_InsertScans", parameters);
}
public override string checkUser(int id)
{
Hashtable param = new Hashtable();
param.Add("@user_id", id);
DataSet ds = SqlServerUtility.getFromDB("OrionsBelt_UsersGetMailFromId", param);
if (ds.Tables.Count == 0)
{
throw new AlnitakException("O Dataset deveria ter pelo menos 1 resultado @ SqlServerUserUtility::checkUser(int id)");
}
return(ds.Tables[0].Rows[0]["user_mail"].ToString());
}
public override bool resetPassword(string userMail, string newPassword)
{
Hashtable param = new Hashtable();
param.Add("@user_mail", userMail);
bool result = SqlServerUtility.checkResults("OrionsBelt_UsersCheckUserMail", param);
if (result)
{
param.Add("@user_password", newPassword);
SqlServerUtility.executeNonQuery("OrionsBelt_UsersResetPassword", param);
}
return(result);
}
public override string getAvatar(int rulerId)
{
Hashtable param = new Hashtable();
param.Add("@ruler_id", rulerId);
DataSet ds = SqlServerUtility.getFromDB("OrionsBelt_UsersGetAvatar", param);
if (ds.Tables[0].Rows.Count != 1)
{
return(User.DefaultAvatar);
}
string s = (string)ds.Tables[0].Rows[0]["user_avatar"];
return(s == string.Empty?User.DefaultAvatar:s);
}
public IList <Core.Business.ExhibitionInstrument> GetExhibitionInstrumentByPage(string sqlwhere, int pagenumber, int pagesize)
{
IList <Core.Business.ExhibitionInstrument> exhibitionInstrumentlist = new List <Core.Business.ExhibitionInstrument>();
SqlServerUtility sql = new SqlServerUtility();
sql.AddParameter("@Tables", SqlDbType.VarChar, "ExhibitionInstrument");
sql.AddParameter("@PK", SqlDbType.VarChar, "Id");
sql.AddParameter("@PageNumber", SqlDbType.Int, pagenumber);
sql.AddParameter("@PageSize", SqlDbType.Int, pagesize);
//sql.AddParameter("@LawSortID", SqlDbType.UniqueIdentifier, sortId);
sql.AddParameter("@Filter", SqlDbType.VarChar, sqlwhere);
SqlDataReader reader = sql.ExecuteSPReader("Paging_RowCount");
if (reader != null)
{
while (reader.Read())
{
Core.Business.ExhibitionInstrument exhibitionInstrument = new Core.Business.ExhibitionInstrument();
if (!reader.IsDBNull(0))
{
exhibitionInstrument.Id = reader.GetGuid(0);
}
if (!reader.IsDBNull(1))
{
exhibitionInstrument.InstrumentID = reader.GetGuid(1);
}
if (!reader.IsDBNull(2))
{
exhibitionInstrument.ExhibitionDate = reader.GetDateTime(2);
}
if (!reader.IsDBNull(3))
{
exhibitionInstrument.SequenceNumber = reader.GetInt32(3);
}
if (!reader.IsDBNull(4))
{
exhibitionInstrument.IsShowinHomePage = reader.GetInt32(4);
}
exhibitionInstrument.MarkOld();
exhibitionInstrumentlist.Add(exhibitionInstrument);
}
reader.Close();
}
return(exhibitionInstrumentlist);
}
public void DeletePicture(string strPicture)
{
string strSQL = "";
try
{
string strPic = strPicture;
DatabaseUtility.RemoveInvalidChars(ref strPic);
strSQL = String.Format("delete from tblPicture where strFile like '{0}'", strPic);
SqlServerUtility.ExecuteNonQuery(_connection, strSQL);
}
catch (Exception ex)
{
Log.Error("MediaPortal.Picture.Database exception deleting picture err:{0} stack:{1}", ex.Message, ex.StackTrace);
}
}
public Core.Business.ExhibitionInstrument Select(Guid id)
{
SqlServerUtility sql = new SqlServerUtility();
sql.AddParameter("@Id", SqlDbType.UniqueIdentifier, id);
SqlDataReader reader = sql.ExecuteSPReader("usp_SelectExhibitionInstrument");
if (reader != null && !reader.IsClosed && reader.Read())
{
Core.Business.ExhibitionInstrument exhibitionInstrument = new Core.Business.ExhibitionInstrument();
if (!reader.IsDBNull(0))
{
exhibitionInstrument.Id = reader.GetGuid(0);
}
if (!reader.IsDBNull(1))
{
exhibitionInstrument.InstrumentID = reader.GetGuid(1);
}
if (!reader.IsDBNull(2))
{
exhibitionInstrument.ExhibitionDate = reader.GetDateTime(2);
}
if (!reader.IsDBNull(3))
{
exhibitionInstrument.SequenceNumber = reader.GetInt32(3);
}
if (!reader.IsDBNull(4))
{
exhibitionInstrument.IsShowinHomePage = reader.GetInt32(4);
}
reader.Close();
return(exhibitionInstrument);
}
else
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
return(null);
}
}
public void DeleteFolderSetting(string path, string Key, bool withPath)
{
if (path == null)
{
return;
}
if (path == string.Empty)
{
return;
}
if (Key == null)
{
return;
}
if (Key == string.Empty)
{
return;
}
try
{
string pathFiltered = Utils.RemoveTrailingSlash(path);
string keyFiltered = Key;
DatabaseUtility.RemoveInvalidChars(ref pathFiltered);
DatabaseUtility.RemoveInvalidChars(ref keyFiltered);
int PathId = AddPath(pathFiltered);
if (PathId < 0)
{
return;
}
string strSQL = String.Format("delete from tblFolderSetting where idPath={0} and tagName ='{1}'", PathId,
keyFiltered);
SqlServerUtility.ExecuteNonQuery(_connection, strSQL);
if (withPath)
{
strSQL = String.Format("delete from tblPath where idPath={0}", PathId);
SqlServerUtility.ExecuteNonQuery(_connection, strSQL);
}
}
catch (Exception ex)
{
Log.Error(ex);
}
}
public override string[] getAllSectionsRolesFromDB(int section_id)
{
Hashtable parameters = new Hashtable();
parameters.Add("@section_id", section_id.ToString());
DataSet roles = SqlServerUtility.getFromDB("OrionsBelt_SectionsGetAllSectionsRoles", parameters);
int results = roles.Tables[0].Rows.Count;
string[] rolesArray = new string[results];
for (int i = 0; i < results; ++i)
{
DataRow row = roles.Tables[0].Rows[i];
rolesArray[i] = (string)row["roles_roleName"];
}
return(rolesArray);
}
