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(); } }