public void DataReaderToListTest() { using (SqlDataAccess data = new SqlDataAccess(STR_TestDataConnection)) { DbDataReader reader = data.ExecuteReader("select top 1 * from ApplicationLog"); Assert.IsNotNull(reader, data.ErrorMessage); reader.Close(); Stopwatch sw = new Stopwatch(); sw.Start(); reader = data.ExecuteReader("select * from ApplicationLog"); Assert.IsNotNull(reader, "Reader null: " + data.ErrorMessage); var entries = DataUtils.DataReaderToObjectList<WebLogEntry>(reader); foreach (var entry in entries) { string name = entry.Message; } sw.Stop(); Console.WriteLine("DataReaderToList: " + sw.ElapsedMilliseconds.ToString() + " ms"); } }
public void DataReaderToObjectTest() { using (SqlDataAccess data = new SqlDataAccess(STR_TestDataConnection)) { IDataReader reader = data.ExecuteReader("select top 1 * from ApplicationLog"); Assert.IsNotNull(reader, "Couldn't access Data reader. " + data.ErrorMessage); Assert.IsTrue(reader.Read(), "Couldn't read from DataReader"); WebLogEntry entry = new WebLogEntry(); DataUtils.DataReaderToObject(reader, entry, null); Assert.IsNotNull(entry.Message, "Entry Message should not be null"); Assert.IsTrue(entry.ErrorLevel != ErrorLevels.None, "Entry Error level should not be None (error)"); } }
public static void Initialize(TestContext testContext) { DatabaseInitializer.InitializeDatabase(); // warm up data connection SqlDataAccess data = new SqlDataAccess(STR_ConnectionString); var readr = data.ExecuteReader("select top 1 * from Customers"); readr.Read(); readr.Close(); // warm up DLR load time dynamic ddata = data; string err = ddata.ErrorMessage; }
public void ExecuteReaderTest() { using (var data = new SqlDataAccess(STR_ConnectionString)) { var reader = data.ExecuteReader("select * from customers"); Assert.IsTrue(reader.HasRows); while (reader.Read()) { Console.WriteLine((string)reader["LastName"] + " " + (DateTime)reader["Entered"]); } } }
public void BasicDataReaderTimerTests() { var data = new SqlDataAccess(STR_ConnectionString); var reader = data.ExecuteReader("select * from wws_items"); Assert.IsNotNull(reader, "Query Failure: " + data.ErrorMessage); StringBuilder sb = new StringBuilder(); //reader.Read(); //sb.AppendLine(dreader.sku);// + " " + dreader.descript + " " + dreader.price.ToString("n2")); //reader.Close(); //reader = data.ExecuteReader("select * from wws_items"); //Assert.IsNotNull(reader, "Query Failure: " + data.ErrorMessage); //dreader = new DynamicDataReader(reader); //sb.Clear(); Stopwatch watch = new Stopwatch(); watch.Start(); while (reader.Read()) { string sku = reader["sku"] as string; string descript = reader["descript"] as string; decimal? price; object t = reader["Price"]; if (t == DBNull.Value) price = null; else price = (decimal)t; sb.AppendLine(sku + " " + descript + " " + price.Value.ToString("n2")); } watch.Stop(); reader.Close(); Console.WriteLine(watch.ElapsedMilliseconds.ToString()); Console.WriteLine(sb.ToString()); }
/// <summary> /// Gets all the Resourecs and ResourceIds for a given resource set and Locale /// /// returns a table "TResource" ResourceId, Value fields /// </summary> /// <param name="resourceSet"></param> /// <param name="cultureName"></param> /// <returns></returns> public virtual List<ResourceIdItem> GetAllResourcesForCulture(string resourceSet, string cultureName) { if (cultureName == null) cultureName = string.Empty; using (var data = new SqlDataAccess(Configuration.ConnectionString)) { var reader = data.ExecuteReader( "select ResourceId, Value from " + Configuration.ResourceTableName + " where ResourceSet=@ResourceSet and LocaleId=@LocaleId", data.CreateParameter("@ResourceSet", resourceSet), data.CreateParameter("@LocaleId", cultureName)); if (reader == null) return null; var ids = new List<ResourceIdItem>(); while (reader.Read()) { string id = reader["ResourceId"] as string; if (id != null) ids.Add(new ResourceIdItem() { ResourceId = id, Value = reader["Value"] }); } return ids; } }
public void ExecuteDataReaderToListManualTest() { SqlDataAccess data = new SqlDataAccess(STR_ConnectionString); var swatch = new Stopwatch(); swatch.Start(); var entries = new List<WebLogEntry>(); var reader = data.ExecuteReader("select * from ApplicationLog"); while (reader.Read()) { WebLogEntry entry = new WebLogEntry(); entry.Details = reader["Details"] as string; entry.Entered = (DateTime) reader["Entered"]; entry.ErrorLevel = (ErrorLevels) reader["ErrorLevel"]; entry.Id = (int) reader["id"]; entry.IpAddress = reader["IpAddress"] as string; entry.Message = reader["Message"] as string; entry.PostData = reader["PostData"] as string; entry.QueryString = reader["QueryString"] as string; entry.Referrer = reader["Referrer"] as string; entry.RequestDuration = (decimal) reader["RequestDuration"]; entry.Url = reader["Url"] as string; entry.UserAgent = reader["UserAgent"] as string; entries.Add(entry); } reader.Close(); swatch.Stop(); Console.WriteLine(swatch.ElapsedMilliseconds); Console.WriteLine(entries.Count); }
public void NewParametersxecuteDynamicTest() { using (var data = new SqlDataAccess(STR_ConnectionString)) { var swatch = Stopwatch.StartNew(); var reader = data.ExecuteReader( "select * from ApplicationLog where entered > @0 and entered < @1 order by Entered", DateTime.Now.AddYears(-115), DateTime.Now.AddYears(-1)); dynamic dreader = new DynamicDataReader(reader); Assert.IsNotNull(reader, data.ErrorMessage); int readerCount = 0; while (reader.Read()) { DateTime date = (DateTime) dreader.Entered; // reader.Entered; Console.WriteLine(date); readerCount++; } swatch.Stop(); Console.WriteLine(readerCount); Console.WriteLine(swatch.ElapsedMilliseconds + "ms"); } }
public void NewParametersReaderTest() { var data = new SqlDataAccess(STR_ConnectionString); var swatch = Stopwatch.StartNew(); var reader = data.ExecuteReader("select * from ApplicationLog where entered > @0 and entered < @1 order by Entered", DateTime.Now.AddYears(-115), DateTime.Now.AddYears(-1)); Assert.IsNotNull(reader, data.ErrorMessage); int readerCount = 0; while (reader.Read()) { string Message = reader["Message"] as string; string Details = reader["Details"] as string; Console.WriteLine(((DateTime) reader["Entered"])); readerCount++; } swatch.Stop(); Console.WriteLine(readerCount); Console.WriteLine(swatch.ElapsedMilliseconds + "ms"); }
public void ExecuteDataReaderWithNoMatchingDataTest() { SqlDataAccess data = new SqlDataAccess(STR_ConnectionString); // no records returned from query var reader = data.ExecuteReader("select * from ApplicationLog where 1=2"); Assert.IsNotNull(reader, "Reader is null and shouldn't be"); }
/// <summary> /// Returns a specific set of resources for a given culture and 'resource set' which /// in this case is just the virtual directory and culture. /// </summary> /// <param name="cultureName"></param> /// <param name="resourceSet"></param> /// <returns></returns> public IDictionary GetResourceSet(string cultureName, string resourceSet) { if (cultureName == null) cultureName = string.Empty; string resourceFilter; resourceFilter = " ResourceSet=@ResourceSet"; var resources = new Dictionary<string, object>(); using (var data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString)) { DbDataReader reader; if (string.IsNullOrEmpty(cultureName)) reader = data.ExecuteReader("select ResourceId,Value,Type,BinFile,TextFile,FileName from " + DbResourceConfiguration.Current.ResourceTableName + " where " + resourceFilter + " and (LocaleId is null OR LocaleId = '') order by ResourceId", data.CreateParameter("@ResourceSet", resourceSet)); else reader = data.ExecuteReader("select ResourceId,Value,Type,BinFile,TextFile,FileName from " + DbResourceConfiguration.Current.ResourceTableName + " where " + resourceFilter + " and LocaleId=@LocaleId order by ResourceId", data.CreateParameter("@ResourceSet", resourceSet), data.CreateParameter("@LocaleId", cultureName)); if (reader == null) { SetError(data.ErrorMessage); return resources; } try { while (reader.Read()) { object resourceValue = reader["Value"] as string; string resourceType = reader["Type"] as string; if (!string.IsNullOrWhiteSpace(resourceType)) { try { // FileResource is a special type that is raw file data stored // in the BinFile or TextFile data. Value contains // filename and type data which is used to create: String, Bitmap or Byte[] if (resourceType == "FileResource") resourceValue = LoadFileResource(reader); else { LosFormatter formatter = new LosFormatter(); resourceValue = formatter.Deserialize(resourceValue as string); } } catch { // ignore this error resourceValue = null; } } else { if (resourceValue == null) resourceValue = string.Empty; } resources.Add(reader["ResourceId"].ToString(), resourceValue); } } catch (Exception ex) { SetError(ex.GetBaseException().Message); return resources; } finally { // close reader and connection reader.Close(); } } return resources; }
/// <summary> /// Returns an object from the Resources. Use this for any non-string /// types. While this method can be used with strings GetREsourceString /// is much more efficient. /// </summary> /// <param name="resourceId"></param> /// <param name="resourceSet"></param> /// <param name="cultureName"></param> /// <returns></returns> public object GetResourceObject(string resourceId, string resourceSet, string cultureName) { object result = null; SetError(); if (cultureName == null) cultureName = string.Empty; var data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString); DbDataReader reader = data.ExecuteReader("select Value,Type from " + DbResourceConfiguration.Current.ResourceTableName + " where ResourceId=@ResourceId and ResourceSet=@ResourceSet and LocaleId=@LocaleId", data.CreateParameter("@ResourceId", resourceId), data.CreateParameter("@ResourceSet", resourceSet), data.CreateParameter("@LocaleId", cultureName)); if (reader == null) return null; if (reader.HasRows) { reader.Read(); string Type = reader["Type"] as string; if (string.IsNullOrEmpty(Type)) result = reader["Value"] as string; else { LosFormatter Formatter = new LosFormatter(); result = Formatter.Deserialize(reader["Value"] as string); } } reader.Dispose(); return result; }
/// <summary> /// Returns all the resource strings for all cultures. /// </summary> /// <param name="resourceId"></param> /// <param name="resourceSet"></param> /// <returns></returns> public Dictionary<string, string> GetResourceStrings(string resourceId, string resourceSet) { var Resources = new Dictionary<string, string>(); var data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString); using (DbDataReader reader = data.ExecuteReader("select Value,LocaleId from " + DbResourceConfiguration.Current.ResourceTableName + " where ResourceId=@ResourceId and ResourceSet=@ResourceSet order by LocaleId", data.CreateParameter("@ResourceId", resourceId), data.CreateParameter("@ResourceSet", resourceSet))) { if (reader == null) return null; while (reader.Read()) { Resources.Add(reader["LocaleId"] as string, reader["Value"] as string); } reader.Dispose(); } return Resources; }
/// <summary> /// Returns a resource item that returns both the Value and Comment to the /// fields to the client. /// </summary> /// <param name="resourceId"></param> /// <param name="resourceSet"></param> /// <param name="cultureName"></param> /// <returns></returns> public ResourceItem GetResourceItem(string resourceId, string resourceSet, string cultureName) { ErrorMessage = string.Empty; if (cultureName == null) cultureName = string.Empty; using (SqlDataAccess data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString)) { using (IDataReader reader = data.ExecuteReader("select ResourceId, Value,Comment from " + DbResourceConfiguration.Current.ResourceTableName + " where ResourceId=@ResourceId and ResourceSet=@ResourceSet and LocaleId=@LocaleId", data.CreateParameter("@ResourceId", resourceId), data.CreateParameter("@ResourceSet", resourceSet), data.CreateParameter("@LocaleId", cultureName))) { if (reader == null || !reader.Read()) return null; ResourceItem item = new ResourceItem() { ResourceId = reader["ResourceId"] as string, Value = reader["Value"] as string, Comment = reader["Comment"] as string }; reader.Close(); return item; } } }
/// <summary> /// Returns a fully normalized list of resources that contains the most specific /// locale version for the culture provided. /// /// This means that this routine walks the resource hierarchy and returns /// the most specific value in this order: de-ch, de, invariant. /// </summary> /// <param name="cultureName"></param> /// <param name="resourceSet"></param> /// <returns></returns> public Dictionary<string, object> GetResourceSetNormalizedForLocaleId(string cultureName, string resourceSet) { if (cultureName == null) cultureName = string.Empty; Dictionary<string, object> resDictionary = new Dictionary<string, object>(); SqlDataAccess data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString); DbDataReader reader = null; string sql = @"select resourceId, LocaleId, Value, Type, BinFile, TextFile, FileName from " + DbResourceConfiguration.Current.ResourceTableName + @" where ResourceSet=@ResourceSet and (LocaleId = '' {0} ) order by ResourceId, LocaleId DESC"; // use like parameter or '' if culture is empty/invariant string localeFilter = string.Empty; List<DbParameter> parameters = new List<DbParameter>(); parameters.Add(data.CreateParameter("@ResourceSet", resourceSet)); if (!string.IsNullOrEmpty(cultureName)) { localeFilter += " OR LocaleId = @LocaleId"; parameters.Add(data.CreateParameter("@LocaleId", cultureName)); // *** grab shorter version if (cultureName.Contains("-")) { localeFilter += " OR LocaleId = @LocaleId1"; parameters.Add(data.CreateParameter("@LocaleId1", cultureName.Split('-')[0])); } } sql = string.Format(sql, localeFilter); reader = data.ExecuteReader(sql, parameters.ToArray()); if (reader == null) { SetError(data.ErrorMessage); return resDictionary; } try { string lastResourceId = "xxxyyy"; while (reader.Read()) { // only pick up the first ID returned - the most specific locale string resourceId = reader["ResourceId"].ToString(); if (resourceId == lastResourceId) continue; lastResourceId = resourceId; // Read the value into this object resourceValue = null; resourceValue = reader["Value"] as string; string resourceType = reader["Type"] as string; if (!string.IsNullOrWhiteSpace(resourceType)) { // FileResource is a special type that is raw file data stored // in the BinFile or TextFile data. Value contains // filename and type data which is used to create: String, Bitmap or Byte[] if (resourceType == "FileResource") resourceValue = LoadFileResource(reader); else { LosFormatter Formatter = new LosFormatter(); resourceValue = Formatter.Deserialize(resourceValue as string); } } else { if (resourceValue == null) resourceValue = string.Empty; } resDictionary.Add(resourceId, resourceValue); } } catch { } finally { // close reader and connection reader.Close(); data.CloseConnection(); } return resDictionary; }
public void NewParametersExecuteEntityTest() { using (var data = new SqlDataAccess(STR_ConnectionString)) { //var cmd = data.CreateCommand("select * from ApplicationLog where entered > @0 and entered > @1",CommandType.Text, DateTime.Now.AddYears(-10), DateTime.Now.AddYears(-)); //var table = data.ExecuteTable("TLogs", cmd); var swatch = Stopwatch.StartNew(); var entries = data.ExecuteReader<WebLogEntry>("select * from ApplicationLog where entered > @0 and entered < @1 order by Entered", DateTime.Now.AddYears(-115), DateTime.Now.AddYears(-1)); var logEntries = entries.ToList(); Assert.IsNotNull(logEntries, data.ErrorMessage); Console.WriteLine(logEntries.Count); foreach (var logEntry in logEntries) { Console.WriteLine(logEntry.Entered); } swatch.Stop(); Console.WriteLine(swatch.ElapsedMilliseconds + "ms"); } }