C# SqlDataAccess.ExecuteReader方法代码示例

        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 [email protected] and [email protected]",
                        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 = " [email protected]";

            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 [email protected] 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 [email protected] and [email protected] and [email protected]",
                               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 [email protected] and [email protected] 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 [email protected] and [email protected] and [email protected]",
                                   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 [email protected] 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
//.........这里部分代码省略.........