public static void ReadContents()
{
using (EntityConnection cn = new EntityConnection("name=DESEDMEntities"))
{
cn.Open();
string query = "SELECT VALUE DESContent FROM DESEDMEntities.DESContents AS DESContent";
using (EntityCommand cmd = cn.CreateCommand())
{
cmd.CommandText = query;
// Finally, get the data reader and process records.
using (EntityDataReader dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
Console.WriteLine("Count: " + dr.FieldCount);
Console.WriteLine("Row:" + dr.HasRows);
if (dr.HasRows)
{
while (dr.Read())
{
Console.WriteLine("****RECORD*****");
Console.WriteLine("ID: {0}", dr["Id"]);
Console.WriteLine("Input File Name: {0}", dr["InputFileName"]);
Console.WriteLine("Output File Name: {0}", dr["OutputFileName"]);
}
}
}
}
}
}
private static void FunWithEntityDataReader()
{
// Make a connection object, based on our *.config file.
using (EntityConnection cn = new EntityConnection("name=AutoLotEntities"))
{
cn.Open();
// Now build an Entity SQL query.
string query = "SELECT VALUE car FROM AutoLotEntities.Cars AS car";
// Create a command object.
using (EntityCommand cmd = cn.CreateCommand())
{
cmd.CommandText = query;
// Finally, get the data reader and process records.
using (EntityDataReader dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (dr.Read())
{
Console.WriteLine("***** RECORD *****");
Console.WriteLine("ID: {0}", dr["CarID"]);
Console.WriteLine("Make: {0}", dr["Make"]);
Console.WriteLine("Color: {0}", dr["Color"]);
Console.WriteLine("Pet Name: {0}", dr["CarNickname"]);
Console.WriteLine();
}
}
}
}
}
public static void RunESQLExample()
{
System.Console.WriteLine("\nUsing Entity SQL");
var esqlQuery = @"SELECT order.SalesOrderID, order.OrderDate, order.DueDate, order.ShipDate FROM AdventureWorksEntities.SalesOrderHeaders AS order where order.SalesOrderID = 43661";
using (var conn = new EntityConnection("name=AdventureWorksEntities"))
{
conn.Open();
// Create an EntityCommand.
using (EntityCommand cmd = conn.CreateCommand())
{
cmd.CommandText = esqlQuery;
// Execute the command.
using (EntityDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
// Start reading results.
while (rdr.Read())
{
System.Console.WriteLine("\nSalesOrderID: {0} \nOrderDate: {1} \nDueDate: {2} \nShipDate: {3}", rdr[0], rdr[1], rdr[2], rdr[3]);
}
}
}
conn.Close();
}
}
public Genre[] GetGenres()
{
Genre[] genres = null;
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("SELECT g.genreid, g.genre1 FROM tunesEntities.titel AS a");
stringBuilder.Append(" INNER JOIN tunesEntities.genre AS g ON a.genreid = g.genreid");
stringBuilder.Append(" INNER JOIN tunesEntities.lieder AS t ON a.TitelID = t.TitelID");
stringBuilder.Append(" WHERE t.Liedpfad IS NOT NULL");
stringBuilder.Append(" GROUP BY g.genreid, g.genre1");
stringBuilder.Append(" ORDER BY g.genre1");
using (System.Data.EntityClient.EntityConnection entityConnection =
new System.Data.EntityClient.EntityConnection(this.ConnectionString))
{
try
{
entityConnection.Open();
using (EntityCommand entityCommand = entityConnection.CreateCommand())
{
List<Genre> genreCollection = null;
entityCommand.CommandText = stringBuilder.ToString();
// Execute the command.
using (EntityDataReader dataReader =
entityCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
// Start reading results.
while (dataReader.Read())
{
IDataReader dataR = dataReader;
if (genreCollection == null)
{
genreCollection = new List<Genre>();
}
Genre genre = new Genre
{
Id = dataReader.GetInt32("genreid", false, 0),
Name = dataReader.GetString("genre1", false, string.Empty)
};
genreCollection.Add(genre);
}
}
if (genreCollection != null)
{
genres = genreCollection.ToArray();
}
}
}
finally
{
entityConnection.Close();
}
}
return genres;
}
static void Main(string[] args)
{
using (EntityConnection ec = new EntityConnection("name=VS2010Entities"))
{
ec.Open();
String sql = "SELECT VALUE PESSOA FROM VS2010Entities.PESSOA";
using (EntityCommand ek = ec.CreateCommand())
{
ek.CommandText = sql;
using (EntityDataReader edr = ek.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (edr.Read())
{
Console.WriteLine("{0} - {1} - {2}", edr["COD_PESSOA"], edr["NOME_PESSOA"], edr["SEXO_PESSOA"]);
}
}
}
}
Console.ReadKey();
}
public Playlist GetPlaylistById(int playlistId, string userName)
{
Playlist playlist = null;
if (string.IsNullOrEmpty(userName) == false)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("SELECT p.ListId, p.ListName, p.User, p.Guid, pe.EntryId, pe.sortorder, pe.Guid as EntryGuid, t.LiedID, t.Lied, t.Dauer, a.Guid as AlbumId, i.Interpret FROM tunesEntities.playlist AS p");
stringBuilder.Append(" LEFT JOIN tunesEntities.playlistentries AS pe ON p.ListId = pe.PlaylistId");
stringBuilder.Append(" LEFT JOIN tunesEntities.lieder AS t ON pe.LiedId = t.LiedID");
stringBuilder.Append(" LEFT JOIN tunesEntities.titel AS a ON t.TitelID = a.TitelID");
stringBuilder.Append(" LEFT JOIN tunesEntities.interpreten AS i ON a.InterpretID = i.InterpretID");
stringBuilder.Append(" WHERE p.ListId = @playlistId");
stringBuilder.Append(" AND p.User = @userName");
string sql = stringBuilder.ToString();
using (System.Data.EntityClient.EntityConnection entityConnection =
new System.Data.EntityClient.EntityConnection(this.ConnectionString))
{
try
{
entityConnection.Open();
using (EntityCommand entityCommand = entityConnection.CreateCommand())
{
EntityParameter trackIdParam = new EntityParameter();
trackIdParam.ParameterName = "playlistId";
trackIdParam.Value = playlistId;
entityCommand.Parameters.Add(trackIdParam);
EntityParameter user = new EntityParameter();
user.ParameterName = "userName";
user.Value = userName;
entityCommand.Parameters.Add(user);
entityCommand.CommandText = sql;
// Execute the command.
using (EntityDataReader dataReader = entityCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
// Start reading results.
while (dataReader.Read())
{
if (playlist == null)
{
playlist = new Playlist
{
Id = dataReader.GetInt32("ListId", false, 0),
Name = dataReader.GetString("ListName", false, string.Empty),
UserName = dataReader.GetString("User", false, string.Empty),
Guid = dataReader.GetGuid("Guid", false, Guid.Empty),
};
}
int entryId = dataReader.GetInt32("EntryId", true, 0);
if (entryId > 0)
{
PlaylistEntry entry = new PlaylistEntry
{
Id = entryId,
SortOrder = dataReader.GetInt32("sortorder", true, 0),
Guid = dataReader.GetGuid("EntryGuid", true, Guid.Empty),
TrackId = dataReader.GetInt32("LiedID", true, 0),
Name = dataReader.GetString("Lied", true, string.Empty),
Duration = dataReader.GetTimeSpan("Dauer", true, TimeSpan.MinValue),
AlbumId = dataReader.GetGuid("AlbumId", false, Guid.Empty),
Artist = dataReader.GetString("Interpret", true, string.Empty)
};
playlist.Entries.Add(entry);
}
}
}
}
}
finally
{
entityConnection.Close();
}
}
}
return playlist;
}
public ICollection<Guid> GetPlaylistImageIdsById(int playlistId, string userName, int limit)
{
Collection<Guid> imageIds = null;
if (string.IsNullOrEmpty(userName) == false)
{
StringBuilder stringBuilder = new StringBuilder();
//stringBuilder.Append("SELECT a.Guid, COUNT(a.Guid) AS Number FROM tunesEntities.playlist AS p");
stringBuilder.Append("SELECT a.Guid FROM tunesEntities.playlist AS p");
stringBuilder.Append(" LEFT JOIN tunesEntities.playlistentries AS pe ON p.ListId = pe.PlaylistId");
stringBuilder.Append(" LEFT JOIN tunesEntities.lieder AS t ON pe.LiedId = t.LiedID");
stringBuilder.Append(" LEFT JOIN tunesEntities.titel AS a ON t.TitelID = a.TitelID");
stringBuilder.Append(" WHERE p.ListId = @playlistId");
stringBuilder.Append(" AND p.User = @userName");
stringBuilder.Append(" ORDER BY pe.sortorder");
stringBuilder.Append(" LIMIT @limit ");
string sql = stringBuilder.ToString();
using (System.Data.EntityClient.EntityConnection entityConnection =
new System.Data.EntityClient.EntityConnection(this.ConnectionString))
{
try
{
entityConnection.Open();
using (EntityCommand entityCommand = entityConnection.CreateCommand())
{
EntityParameter idParam = new EntityParameter();
idParam.ParameterName = "playlistId";
idParam.Value = playlistId;
entityCommand.Parameters.Add(idParam);
EntityParameter user = new EntityParameter();
user.ParameterName = "userName";
user.Value = userName;
entityCommand.Parameters.Add(user);
EntityParameter limitParam = new EntityParameter();
limitParam.ParameterName = "limit";
limitParam.Value = limit;
entityCommand.Parameters.Add(limitParam);
entityCommand.CommandText = sql;
// Execute the command.
using (EntityDataReader dataReader = entityCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
while (dataReader.Read())
{
if (imageIds == null)
{
imageIds = new Collection<Guid>();
}
imageIds.Add(dataReader.GetGuid("Guid", true, Guid.Empty));
}
}
}
}
finally
{
entityConnection.Close();
}
}
}
return imageIds;
}
public ICollection<Track> GetTracksByFilters(Filter filter)
{
Collection<Track> tracks = null;
if (filter != null)
{
//filter.Value = "17,25,5,14";
var names = new int[] { 17, 25, 5, 14 };
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("SELECT t.LiedID, t.Track, t.Lied ,t.Dauer");
stringBuilder.Append(" FROM tunesEntities.lieder AS t");
stringBuilder.Append(" INNER JOIN tunesEntities.titel AS a ON t.TitelID = a.TitelID");
stringBuilder.Append(" WHERE t.Liedpfad IS NOT NULL");
//stringBuilder.Append(" AND a.genreid IN (17,25,5,14)");
switch (filter.Mode)
{
case FilterMode.Genre:
stringBuilder.Append(" AND a.genreid IN {" + filter.Value + "}");
//stringBuilder.Append(" AND a.genreid IN (@filterValue)");
break;
case FilterMode.Year:
break;
default:
break;
}
string sql = stringBuilder.ToString();
using (System.Data.EntityClient.EntityConnection entityConnection =
new System.Data.EntityClient.EntityConnection(this.ConnectionString))
{
try
{
entityConnection.Open();
using (EntityCommand entityCommand = entityConnection.CreateCommand())
{
//EntityParameter filterValue = new EntityParameter();
//filterValue.ParameterName = "filterValue";
//filterValue.Value = filter.Value;
//entityCommand.Parameters.Add(filterValue);
entityCommand.CommandText = sql;
// Execute the command.
using (EntityDataReader dataReader = entityCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
// Start reading results.
while (dataReader.Read())
{
if (tracks == null)
{
tracks = new Collection<Track>();
}
Track track = new Track
{
Id = dataReader.GetInt32("LiedID", false, 0)
};
tracks.Add(track);
}
}
}
}
finally
{
entityConnection.Close();
}
}
}
return tracks;
}
public Track GetTrackById(int trackId)
{
Track track = null;
string audioDirectory = this.AudioDirectory;
if (string.IsNullOrEmpty(audioDirectory) == false)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("SELECT t.LiedID, t.Track, t.Lied ,t.Dauer, t.Liedpfad, t.guid, a.TitelID, a.Titel1, a.Guid as AlbumId, i.Interpret FROM tunesEntities.lieder AS t");
stringBuilder.Append(" JOIN tunesEntities.titel AS a ON a.TitelID = t.TitelID");
stringBuilder.Append(" JOIN tunesEntities.interpreten AS i ON a.InterpretID = i.InterpretID");
stringBuilder.Append(" WHERE t.LiedId = @trackid");
string sql = stringBuilder.ToString();
using (System.Data.EntityClient.EntityConnection entityConnection =
new System.Data.EntityClient.EntityConnection(this.ConnectionString))
{
try
{
entityConnection.Open();
using (EntityCommand entityCommand = entityConnection.CreateCommand())
{
EntityParameter trackIdParam = new EntityParameter();
trackIdParam.ParameterName = "trackid";
trackIdParam.Value = trackId;
entityCommand.Parameters.Add(trackIdParam);
entityCommand.CommandText = sql;
// Execute the command.
using (EntityDataReader dataReader = entityCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
if (dataReader.Read() == true)
{
track = new Track
{
Id = dataReader.GetInt32("LiedID", false, 0),
TrackNumber = dataReader.GetInt32("Track", false, 0),
Name = dataReader.GetString("Lied", false, string.Empty),
Duration = dataReader.GetTimeSpan("Dauer", true, TimeSpan.MinValue),
Guid = dataReader.GetGuid("guid", false, Guid.Empty),
Album = new Album
{
Id = dataReader.GetInt32("TitelID", false, 0),
Title = dataReader.GetString("Titel1", false, string.Empty),
AlbumId = dataReader.GetGuid("AlbumId", false, Guid.Empty),
Artist = new Artist
{
Name = dataReader.GetString("Interpret", false, string.Empty)
}
}
};
}
}
}
}
finally
{
entityConnection.Close();
}
}
}
return track;
}
public string GetAudioFileNameByGuid(Guid guid)
{
string fileName = null;
string audioDirectory = this.AudioDirectory;
if (string.IsNullOrEmpty(audioDirectory) == false && guid != null && guid != Guid.Empty)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("SELECT t.Liedpfad FROM tunesEntities.lieder AS t");
stringBuilder.Append(" WHERE t.guid = @guid");
string sql = stringBuilder.ToString();
using (System.Data.EntityClient.EntityConnection entityConnection =
new System.Data.EntityClient.EntityConnection(this.ConnectionString))
{
try
{
entityConnection.Open();
using (EntityCommand entityCommand = entityConnection.CreateCommand())
{
EntityParameter guidParam = new EntityParameter();
guidParam.ParameterName = "guid";
guidParam.Value = guid.ToString();
entityCommand.Parameters.Add(guidParam);
entityCommand.CommandText = sql;
// Execute the command.
using (EntityDataReader dataReader = entityCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
if (dataReader.Read() == true)
{
fileName = GetTrackFilePath(dataReader, audioDirectory);
}
}
}
}
finally
{
entityConnection.Close();
}
}
}
return fileName;
}
public Album[] GetAlbums(Query query)
{
Album[] albums = null;
if (query == null)
{
query = new Query
{
PageIndex = 0,
PageSize = 1
};
}
query.PageSize = query.PageSize == 0 ? 1 : query.PageSize;
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("SELECT i.InterpretID, i.Interpret, i.Interpret_Lang ,a.TitelID, a.Titel1, a.Guid as AlbumId FROM tunesEntities.titel AS a");
stringBuilder.Append(" INNER JOIN tunesEntities.interpreten AS i ON a.InterpretID = i.InterpretID");
stringBuilder.Append(" INNER JOIN tunesEntities.lieder AS t ON a.TitelID = t.TitelID");
stringBuilder.Append(" WHERE t.Liedpfad IS NOT NULL");
stringBuilder.Append(" GROUP BY i.InterpretID, i.Interpret, i.Interpret_Lang ,a.TitelID, a.Titel1, a.Guid");
if (query.SortByCondition != null && query.SortByCondition.Id == 1)
{
stringBuilder.Append(" ORDER BY a.Titel1");
}
else
{
stringBuilder.Append(" ORDER BY i.Interpret, a.Titel1");
}
stringBuilder.Append(" SKIP @skip LIMIT @limit ");
string sql = stringBuilder.ToString();
using (System.Data.EntityClient.EntityConnection entityConnection =
new System.Data.EntityClient.EntityConnection(this.ConnectionString))
{
try
{
entityConnection.Open();
using (EntityCommand entityCommand = entityConnection.CreateCommand())
{
EntityParameter skip = new EntityParameter();
skip.ParameterName = "skip";
skip.Value = query.PageIndex;
entityCommand.Parameters.Add(skip);
EntityParameter limit = new EntityParameter();
limit.ParameterName = "limit";
limit.Value = query.PageSize;
entityCommand.Parameters.Add(limit);
List<Album> albumCollection = null;
entityCommand.CommandText = sql;
// Execute the command.
using (EntityDataReader dataReader = entityCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
// Start reading results.
while (dataReader.Read())
{
if (albumCollection == null)
{
albumCollection = new List<Album>();
}
Album album = new Album
{
Artist = new Artist
{
Id = dataReader.GetInt32("InterpretID", false, 0),
Name = dataReader.GetString("Interpret", false, string.Empty),
SortName = dataReader.GetString("Interpret_Lang", true, string.Empty)
},
Id = dataReader.GetInt32("TitelID", false, 0),
Title = dataReader.GetString("Titel1", false, string.Empty),
AlbumId = dataReader.GetGuid("AlbumId", false, Guid.Empty)
};
albumCollection.Add(album);
}
}
if (albumCollection != null)
{
albums = albumCollection.ToArray();
}
}
}
finally
{
entityConnection.Close();
}
}
return albums;
}
public Album[] GetNewestAlbums(int limit)
{
Album[] albums = null;
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("SELECT i.InterpretID, i.Interpret, i.Interpret_Lang ,a.TitelID, a.Titel1, a.Guid as AlbumId FROM tunesEntities.titel AS a");
stringBuilder.Append(" INNER JOIN tunesEntities.interpreten AS i ON a.InterpretID = i.InterpretID");
stringBuilder.Append(" ORDER BY a.TitelID DESC");
stringBuilder.Append(" LIMIT @limit ");
string sql = stringBuilder.ToString();
using (System.Data.EntityClient.EntityConnection entityConnection =
new System.Data.EntityClient.EntityConnection(this.ConnectionString))
{
try
{
entityConnection.Open();
using (EntityCommand entityCommand = entityConnection.CreateCommand())
{
EntityParameter limitParam = new EntityParameter();
limitParam.ParameterName = "limit";
limitParam.Value = limit;
entityCommand.Parameters.Add(limitParam);
List<Album> albumCollection = null;
entityCommand.CommandText = sql;
// Execute the command.
using (EntityDataReader dataReader = entityCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
// Start reading results.
while (dataReader.Read())
{
if (albumCollection == null)
{
albumCollection = new List<Album>();
}
Album album = new Album
{
Artist = new Artist
{
Id = dataReader.GetInt32("InterpretID", false, 0),
Name = dataReader.GetString("Interpret", false, string.Empty),
SortName = dataReader.GetString("Interpret_Lang", true, string.Empty)
},
Id = dataReader.GetInt32("TitelID", false, 0),
Title = dataReader.GetString("Titel1", false, string.Empty),
AlbumId = dataReader.GetGuid("AlbumId", false, Guid.Empty)
};
albumCollection.Add(album);
}
}
if (albumCollection != null)
{
albums = albumCollection.ToArray();
}
}
}
finally
{
entityConnection.Close();
}
}
return albums;
}
public int GetNumberOfPlayableAlbums()
{
int iNumberofAlbums = -1;
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("SELECT COUNT(DISTINCT a.TitelID) FROM tunesEntities.titel AS a");
stringBuilder.Append(" INNER JOIN tunesEntities.lieder AS t ON a.TitelID = t.TitelID");
stringBuilder.Append(" WHERE t.Liedpfad IS NOT NULL");
string sql = stringBuilder.ToString();
using (System.Data.EntityClient.EntityConnection entityConnection =
new System.Data.EntityClient.EntityConnection(this.ConnectionString))
{
try
{
entityConnection.Open();
using (EntityCommand entityCommand = entityConnection.CreateCommand())
{
entityCommand.CommandText = sql;
object obj = entityCommand.ExecuteScalar();
if (obj is int)
{
iNumberofAlbums = (int)obj;
}
}
}
finally
{
entityConnection.Close();
}
}
return iNumberofAlbums;
}
public Playlist GetPlaylistByIdWithNumberOfEntries(int playlistId, string userName)
{
Playlist playlist = null;
if (string.IsNullOrEmpty(userName) == false)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("SELECT p.ListId, p.ListName, p.guid, COUNT(pe.PlaylistId) as Number ");
stringBuilder.Append(" FROM tunesEntities.playlist AS p");
stringBuilder.Append(" LEFT JOIN tunesEntities.playlistentries AS pe ON p.ListId = pe.PlaylistId");
stringBuilder.Append(" WHERE p.ListId = @playlistId");
stringBuilder.Append(" AND p.User = @userName");
stringBuilder.Append(" GROUP BY p.listid, p.ListName, p.guid");
string sql = stringBuilder.ToString();
using (System.Data.EntityClient.EntityConnection entityConnection =
new System.Data.EntityClient.EntityConnection(this.ConnectionString))
{
try
{
entityConnection.Open();
using (EntityCommand entityCommand = entityConnection.CreateCommand())
{
EntityParameter id = new EntityParameter();
id.ParameterName = "playlistId";
id.Value = playlistId;
entityCommand.Parameters.Add(id);
EntityParameter user = new EntityParameter();
user.ParameterName = "userName";
user.Value = userName;
entityCommand.Parameters.Add(user);
entityCommand.CommandText = sql;
// Execute the command.
using (EntityDataReader dataReader = entityCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
if (dataReader.Read() == true)
{
playlist = new Playlist
{
Id = dataReader.GetInt32("ListId", false, 0),
Name = dataReader.GetString("ListName", false, string.Empty),
Guid = dataReader.GetGuid("guid", false, Guid.Empty),
NumberEntries = dataReader.GetInt32("Number", false, 0)
};
}
}
}
}
catch(Exception ex)
{
}
finally
{
entityConnection.Close();
}
}
}
return playlist;
}
internal static EntityCommand CreateFilteredCommand(EntityConnection connection, string sql, string orderByClause, EntityStoreSchemaFilterObjectTypes queryTypes, List<EntityStoreSchemaFilterEntry> filters, string[] filterAliases)
{
EntityCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandTimeout = 0;
if (filters.Count == 0)
{
if (!string.IsNullOrEmpty(orderByClause))
{
command.CommandText = sql + Environment.NewLine + orderByClause;
}
else
{
command.CommandText = sql;
}
return command;
}
StringBuilder whereClause = new StringBuilder();
foreach (string alias in filterAliases)
{
StringBuilder allows = new StringBuilder();
StringBuilder excludes = new StringBuilder();
foreach (EntityStoreSchemaFilterEntry entry in filters)
{
// only apply filters of the correct type
if ((queryTypes & entry.Types) == 0)
{
continue;
}
if (entry.Effect == EntityStoreSchemaFilterEffect.Allow)
{
AddFilterEntry(command, allows, alias, entry);
}
else
{
Debug.Assert(entry.Effect == EntityStoreSchemaFilterEffect.Exclude, "did you add new value?");
AddFilterEntry(command, excludes, alias, entry);
}
}
if (allows.Length != 0)
{
if (whereClause.Length != 0)
{
whereClause.Append(Environment.NewLine);
whereClause.Append("AND");
whereClause.Append(Environment.NewLine);
}
whereClause.Append("(");
whereClause.Append(allows);
whereClause.Append(")");
}
if (excludes.Length != 0)
{
if (whereClause.Length != 0)
{
whereClause.Append(Environment.NewLine);
whereClause.Append("AND");
whereClause.Append(Environment.NewLine);
}
whereClause.Append("NOT (");
whereClause.Append(excludes);
whereClause.Append(")");
}
}
// did we end up with a where clause?
StringBuilder sqlStatement = new StringBuilder(sql);
if (whereClause.Length != 0)
{
sqlStatement.Append(Environment.NewLine);
sqlStatement.Append("WHERE");
sqlStatement.Append(Environment.NewLine);
sqlStatement.Append(whereClause);
}
if (!string.IsNullOrEmpty(orderByClause))
{
sqlStatement.Append(Environment.NewLine);
sqlStatement.Append(orderByClause);
}
command.CommandText = sqlStatement.ToString();
return command;
}
public Playlist[] GetPlaylistsByUserName(string userName, int limit)
{
Playlist[] playlists = null;
if (!string.IsNullOrEmpty(userName))
{
bool hasLimit = false;
if (limit > 0)
{
hasLimit = true;
}
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("SELECT p.ListId, p.ListName, p.User, p.guid FROM tunesEntities.playlist AS p");
stringBuilder.Append(" WHERE p.User = @userName");
stringBuilder.Append(" ORDER BY p.ListName");
if (hasLimit)
{
stringBuilder.Append(" LIMIT @limit ");
}
string sql = stringBuilder.ToString();
using (System.Data.EntityClient.EntityConnection entityConnection =
new System.Data.EntityClient.EntityConnection(this.ConnectionString))
{
try
{
entityConnection.Open();
using (EntityCommand entityCommand = entityConnection.CreateCommand())
{
EntityParameter user = new EntityParameter();
user.ParameterName = "userName";
user.Value = userName;
entityCommand.Parameters.Add(user);
EntityParameter limitParam = new EntityParameter();
limitParam.ParameterName = "limit";
limitParam.Value = limit;
entityCommand.Parameters.Add(limitParam);
List<Playlist> playlistCollection = null;
entityCommand.CommandText = sql;
// Execute the command.
using (EntityDataReader dataReader = entityCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
// Start reading results.
while (dataReader.Read())
{
if (playlistCollection == null)
{
playlistCollection = new List<Playlist>();
}
Playlist playlist = new Playlist
{
Id = dataReader.GetInt32("ListId", false, 0),
Name = dataReader.GetString("ListName", false, string.Empty),
UserName = dataReader.GetString("User", false, string.Empty),
Guid = dataReader.GetGuid("guid", true, Guid.Empty)
};
playlistCollection.Add(playlist);
}
}
if (playlistCollection != null)
{
playlists = playlistCollection.ToArray();
}
}
}
finally
{
entityConnection.Close();
}
}
}
return playlists;
}
public CoverImage GetImage(Guid imageId, bool asThumbnail = false)
{
CoverImage image = null;
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("SELECT a.{0}, a.PictureFormat, a.ErstellDatum, a.MutationDatum");
stringBuilder.Append(" FROM tunesEntities.titel AS a");
stringBuilder.Append(" WHERE a.guid = @imageId");
string field = asThumbnail ? "thumbnail" : "cover";
string sql = string.Format(CultureInfo.InvariantCulture, stringBuilder.ToString(), field);
using (System.Data.EntityClient.EntityConnection entityConnection =
new System.Data.EntityClient.EntityConnection(this.ConnectionString))
{
try
{
entityConnection.Open();
using (EntityCommand entityCommand = entityConnection.CreateCommand())
{
EntityParameter id = new EntityParameter();
id.ParameterName = "imageId";
id.Value = imageId.ToString();
entityCommand.Parameters.Add(id);
entityCommand.CommandText = sql;
using (EntityDataReader dataReader = entityCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
if (dataReader.Read())
{
image = new CoverImage
{
Cover = dataReader.GetBytes(field, true, null),
Extension = dataReader.GetString("PictureFormat", true,String.Empty),
ModifiedSince = dataReader.GetDateTime("MutationDatum", true, DateTime.MinValue)
};
}
}
}
}
finally
{
entityConnection.Close();
}
}
return image;
}
public Album GetAlbumById(int albumId)
{
Album album = null;
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("SELECT i.InterpretID, i.Interpret, i.Interpret_Lang ,a.TitelID, a.Titel1, a.Guid as AlbumId, a.ErschDatum, g.genreid, g.genre1");
stringBuilder.Append(" FROM tunesEntities.titel AS a");
stringBuilder.Append(" INNER JOIN tunesEntities.interpreten AS i ON a.InterpretID = i.InterpretID");
stringBuilder.Append(" LEFT JOIN tunesEntities.genre AS g ON a.genreid = g.genreid");
stringBuilder.Append(" WHERE a.titelid = @albumId");
string sql = stringBuilder.ToString();
using (System.Data.EntityClient.EntityConnection entityConnection =
new System.Data.EntityClient.EntityConnection(this.ConnectionString))
{
try
{
entityConnection.Open();
using (EntityCommand entityCommand = entityConnection.CreateCommand())
{
EntityParameter id = new EntityParameter();
id.ParameterName = "albumid";
id.Value = albumId;
entityCommand.Parameters.Add(id);
entityCommand.CommandText = sql;
using (EntityDataReader dataReader = entityCommand.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
if (dataReader.Read())
{
album = new Album
{
Artist = new Artist
{
Id = dataReader.GetInt32("InterpretID", false, 0),
Name = dataReader.GetString("Interpret", false, string.Empty),
SortName = dataReader.GetString("Interpret_Lang", true, string.Empty)
},
Id = dataReader.GetInt32("TitelID", false, 0),
Title = dataReader.GetString("Titel1", false, string.Empty),
AlbumId = dataReader.GetGuid("AlbumId", false, Guid.Empty),
Year = dataReader.GetInt32("ErschDatum", true, 0),
Genre = new Genre
{
Id = dataReader.GetInt32("genreid", true, 0),
Name = dataReader.GetString("genre1", true, string.Empty)
}
};
}
}
GetAlbumTracksByTitelId(album, entityConnection);
}
}
finally
{
entityConnection.Close();
}
}
return album;
}
private void ejemploentity()
{
using (EntityConnection conn = new EntityConnection("name=travelEntitiesGeneral"))
{
conn.Open();
EntityCommand cmd = conn.CreateCommand();
cmd.CommandText = @"select c.BlogID from travelEntitiesGeneral.Blogs as c where c.BlogPosts.Count > 0";
EntityDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
while (reader.Read())
{
Console.WriteLine("BlogID = {0}", reader["BlogID"]);
}
conn.Close();
}
}