static void Main(string[] args) { using (var c = new EntityConnection("name=AdventureWorksEntities")) { c.StateChange += EStateChange; var cmd = "SELECT VALUE C FROM AdventureWorksEntities.Contatos AS C WHERE C.ContactID=@ContactID"; using (var k = new EntityCommand(cmd, c)) { k.Parameters.AddWithValue("ContactID", 1); c.Open(); var dr = k.ExecuteReader(CommandBehavior.SequentialAccess); if (dr.Read()) //while se existir mais de um registro { Console.WriteLine(dr["Nome"]); } if (c.State != ConnectionState.Closed) c.Close(); } } Console.ReadKey(); }
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 void Test1() { string SELECT_PERSONS_ALL = "SELECT p.id as person_id, p.fname, p.lname, ph.id as phone_id , " + "ph.phonevalue as phonevalue, a.id as address_id , a.addressvalue as addressvalue " + "FROM Entities.People as p INNER JOIN Entities.Addresses as a ON a.personid = p.id " + "INNER JOIN Entities.Phones as ph ON ph.personid = p.id"; EntityConnection m_connection = new EntityConnection("name=Entities"); List<Mock.PhoneBook.Phone> list = new List<Mock.PhoneBook.Phone>(); m_connection.Open(); Infrastructure.PhoneBook.IMockConvertor convertor = new ERDBArch.Modules.PhoneBook.BLL.PersonConvertor(); using (EntityCommand cmd = new EntityCommand(SELECT_PERSONS_ALL, m_connection)) { using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) { while (reader.Read()) { System.Diagnostics.Debug.WriteLine(reader["person_id"]); } } } m_connection.Close(); }
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(); } }
private AlcionaDataBaseConnection() { EntityConnection connection = new EntityConnection(); string connectionString = ConfigurationManager.ConnectionStrings["AlcionaEntities"].ConnectionString; connection.ConnectionString = connectionString; connection.Open(); _context = new AlcionaEntities(connection, false); ((IObjectContextAdapter)_context).ObjectContext.CommandTimeout = 3600; }
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; }
internal static void ExecuteESqlQuery(string cxString, string query) { EntityConnection connection = new EntityConnection(cxString); EntityCommand command = new EntityCommand(query, connection); connection.Open(); try { command.ExecuteReader(CommandBehavior.SequentialAccess).Dump<EntityDataReader>(); } finally { connection.Close(); } }
static DictionaryEntities InitializeThreadProc() { string providerString = "Data Source=Dictionary.sdf"; string providerName = "System.Data.SqlServerCe.3.5"; // Initialize the EntityConnectionStringBuilder. EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder(); entityBuilder.Provider = providerName; entityBuilder.ProviderConnectionString = providerString; entityBuilder.Metadata = @"Res://ER/"; var connection = new EntityConnection(entityBuilder.ToString()); connection.Open(); var entities = new DictionaryEntities(connection); return entities; }
/// <summary> /// Gets the DB connection /// connection parameters are decrypted /// </summary> /// <returns>An Entity (Framework) connection</returns> public static EntityConnection GetEntityConnection(string serverURI) { EntityConnection ans = null; EntityConnection conn = null; try { // Initialize the EntityConnectionStringBuilder. EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder(); // Set the provider name. entityBuilder.Provider = "MySql.Data.MySqlClient"; // Set the provider-specific connection string. entityBuilder.ProviderConnectionString = GetSqlBuilder(serverURI).ToString(); // Set the Metadata location. entityBuilder.Metadata = @"res://*/Entities.BourseEntities.csdl|res://*/Entities.BourseEntities.ssdl|res://*/Entities.BourseEntities.msl"; string st = entityBuilder.ToString(); conn = new EntityConnection(entityBuilder.ToString()); conn.Open(); // Just testing the connection. conn.Close(); // CA2000 prevention ans = conn; conn = null; return ans; } catch (Exception) { throw; } finally { if (conn != null) { conn.Close(); } } }
protected int GetEntityCount(string esqlQuery) { int count = 0; using (var connection = new EntityConnection(AboutInheritanceEntitiesConnectionString)) { connection.Open(); using (var command = new EntityCommand(esqlQuery, connection)) { using (var reader = command.ExecuteReader(CommandBehavior.SequentialAccess)) { while (reader.Read()) { count = count + 1; } } } } return count; }
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[] 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 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 void Verify_querying_database_with_EntityClient_works() { const string commandText = "SELECT C.CompanyName, C.Location FROM NorthwindEntities.Customers AS C WHERE C.CustomerID LIKE @CustomerID"; using (var connection = new EntityConnection(NorthwindEntitiesConnectionString)) { connection.Open(); using (var command = new EntityCommand(commandText, connection)) { command.Parameters.AddWithValue("CustomerID", "A%"); using (var reader = command.ExecuteReader(CommandBehavior.SequentialAccess)) { foreach (var expectedResult in expectedResults) { reader.Read(); Assert.Equal(expectedResult.Name, reader["CompanyName"]); Assert.Equal(expectedResult.Location, ((DbGeography)reader["Location"]).AsText()); } Assert.False(reader.Read()); } } } Console.WriteLine(); }
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; }
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(); } }
public Model.File Remove(string id) { if (id == null) { throw new Exception("Ivalid ID"); } Model.File removedFile = null; try { var cn = new EntityConnection("name=FlowerEntities"); cn.Open(); using (var scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted })) { removedFile = removeFromDB(id, cn); scope.Complete(); } if (removedFile != null) { removeFile(removedFile.ID); } } catch { throw; } finally { } return removedFile; }
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; }
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 void ファイル削除DB() { var domain = new FileDomain(@".\TestCase\FileStore\"); var newId = domain.Regist(@"TestCase\Data\Test_Text.txt"); var cn = new EntityConnection("name=FlowerEntities"); cn.Open(); domain.removeFromDB(newId, cn); using (var entities = new Model.FlowerEntities()) { Assert.True(entities.Files.Count() == 0); } return; }
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; }
private VersionResult[] ReadChartPoints(string FileName) { Contract.Requires(!String.IsNullOrWhiteSpace(FileName)); Contract.Ensures(Contract.Result<VersionResult[]>() != null); VersionResult[] res; var ConnectionString = "data source='" + FileName + "'";//; mode='Read Only';"; doesn't work // read the sql DB var entityConnectionString = new EntityConnectionStringBuilder { Metadata = "res://*/ClousotCacheModel.csdl|res://*/ClousotCacheModel.ssdl|res://*/ClousotCacheModel.msl", Provider = "System.Data.SqlServerCe.3.5", ProviderConnectionString = ConnectionString }.ToString(); using (var connection = new EntityConnection(entityConnectionString)) { connection.Open(); using (var objectContext = new ClousotCacheEntities(connection)) { objectContext.ContextOptions.LazyLoadingEnabled = true; // avoid having to manually load every method var resList = new List<VersionResult>(); #if false var versionMethods = objectContext.VersionBindings.GroupBy(b => b.Version); var versionResults = objectContext.VersionResults.ToDictionary(r => r.Version); foreach (var versionMethod in versionMethods) { VersionResult versionResult; // Sanity check if (versionResults.TryGetValue(versionMethod.Key, out versionResult) && versionResult.Methods == versionMethod.LongCount()) versionResult.Complete(); // previously computed values can be reused else { if (versionResult != null) // the entry needs an update objectContext.DeleteObject(versionResult); versionResult = new VersionResult(versionMethod.Key, versionMethod.Select(b => b.Method)); // recompute objectContext.VersionResults.AddObject(versionResult); } resList.Add(versionResult); } objectContext.SaveChanges(); foreach (var versionResult in resList) objectContext.Detach(versionResult); #endif res = resList.ToArray(); #if false var points = objectContext.VersionBindings.Include("Method").GroupBy(b => b.Version, b => b.Method, ChartPoint.Create); res = points.OrderBy(m => m.Version).ToArray(); #endif } connection.Close(); } return res; }
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 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 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 void UserDefinedFunction() { using (EntityConnection conn = new EntityConnection("name=testEntities")) { conn.Open(); string query = @"SELECT e.FirstName AS Name FROM testEntities.Employees AS e WHERE testModel.Store.spFunc(e.Id, '') = 6"; using (EntityCommand cmd = new EntityCommand(query, conn)) { EntityDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess); Assert.IsTrue(reader.Read()); Assert.AreEqual("Scooby", reader[0]); } } }