C# Data.SqlDataAccess类代码示例

        public void DataReaderToIEnumerableObjectTest()
        {
            using (SqlDataAccess data = new SqlDataAccess(STR_TestDataConnection))
            {
                DbDataReader reader = data.ExecuteReader("select top 1 * from ApplicationLog");
                reader.Close();

                Stopwatch sw = new Stopwatch();
                sw.Start();

                reader = data.ExecuteReader("select * from ApplicationLog");
                Assert.IsNotNull(reader, "Reader null: " + data.ErrorMessage);
                var entries = DataUtils.DataReaderToIEnumerable<WebLogEntry>(reader);
                foreach (var entry in entries)
                {
                    string name = entry.Message;
                }
                sw.Stop();

                // run again to check for connections not closed
                reader = data.ExecuteReader("select * from ApplicationLog");
                Assert.IsNotNull(reader, "Reader null: " + data.ErrorMessage);
                entries = DataUtils.DataReaderToIEnumerable<WebLogEntry>(reader);
                foreach (var entry in entries)
                {
                    string name = entry.Message;
                }

                Console.WriteLine("DataReaderToIEnumerable: " + sw.ElapsedMilliseconds.ToString() + " ms");
            }
        }

 /// <summary>
 /// Override with specific connection string
 /// </summary>
 /// <param name="nameOrConnectionString"></param>
 /// <param name="providerName"></param>
 public EfCodeFirstContext(string connectionString)
     : base(connectionString)
 {
     //if(!string.IsNullOrEmpty(providerName))
     //    Db = new SqlDataAccess(connectionString, providerName);
     //else
     Db = new SqlDataAccess(connectionString);
 }

 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 void ExecuteNonQueryTest()
        {
            using (var data = new SqlDataAccess(STR_ConnectionString))
            {
                var count = data.ExecuteNonQuery("update Customers set [email protected] where [email protected]",
                                                 1, DateTime.Now);

                Assert.IsTrue(count > -1, data.ErrorMessage);
                Assert.IsTrue(count > 0, "No record found to update");

                Assert.IsTrue(count == 1, "Invalid number of records updated.");
            }
        }

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

        public void QueryWithNoMatchingDataTest()
        {
            SqlDataAccess data = new SqlDataAccess(STR_ConnectionString);

            // no records returned from query
            var entries = data.Query<WebLogEntry>("select * from ApplicationLog where 1=2");

            var ent = entries.ToList();
            Console.WriteLine(ent.Count);

            Assert.IsNotNull(entries, "IEnumerable should not be null - only null on failure.");
        }

        public void QueryToCustomer()
        {
            using (var data = new SqlDataAccess(STR_ConnectionString))
            {
                var custList = data.Query<Customer>("select * from customers where LastName like @0", "S%");

                Assert.IsNotNull(custList, data.ErrorMessage);

                foreach (var customer in custList)
                {
                    Console.WriteLine(customer.Company + " " + customer.Entered);
                }
            }
        }

 public void QueryException()
 {
     using (var data = new SqlDataAccess(STR_ConnectionString)
     {
         ThrowExceptions = true
     })
     {
         try
         {
             var logEntries = data.Query<WebLogEntry>("select * from ApplicationLogggg");
             Assert.Fail("Invalid Sql Statement should not continue");
         }
         catch (Exception ex)
         {
             Console.WriteLine("Error caught correctly: " + ex.Message);
         }
     }
 }

        public void NewParametersTableTest()
        {
            var data = new SqlDataAccess(STR_ConnectionString);

            // warmup
            data.ExecuteScalar("select top1 id from ApplicationLog");

            //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 table = data.ExecuteTable("TLogs",
                "select * from ApplicationLog where entered > @0 and entered < @1 order by Entered",
                DateTime.Now.AddYears(-115), DateTime.Now.AddYears(-1));

            Assert.IsNotNull(table, data.ErrorMessage);

            Console.WriteLine(table.Rows.Count);
            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine(((DateTime) row["Entered"]));
            }
            swatch.Stop();
            Console.WriteLine(swatch.ElapsedMilliseconds + "ms");
        }

        /// <summary>
        /// Adds a resource to the Localization Table
        /// </summary>
        /// <param name="resourceId"></param>
        /// <param name="value"></param>
        /// <param name="cultureName"></param>
        /// <param name="resourceSet"></param>
        /// <param name="Type"></param>
        /// <param name="Filename"></param>
        /// <param name="valueIsFileName">if true the Value property is a filename to import</param>
        public int AddResource(string resourceId, object value, string cultureName, string resourceSet, string comment, bool valueIsFileName)
        {
            string Type = string.Empty;

            if (cultureName == null)
                cultureName = string.Empty;

            if (string.IsNullOrEmpty(resourceId))
            {
                ErrorMessage = "No ResourceId specified. Can't add resource";
                return -1;
            }

            SqlDataAccess Data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString);

            if (Transaction != null)
                Data.Transaction = Transaction;

            if (value != null && !(value is string))
            {
                Type = value.GetType().AssemblyQualifiedName;
                try
                {
                    LosFormatter output = new LosFormatter();
                    StringWriter writer = new StringWriter();
                    output.Serialize(writer, value);
                    value = writer.ToString();
                }
                catch (Exception ex)
                {
                    ErrorMessage = ex.Message;
                    return -1;
                }
            }
            else
                Type = string.Empty;

            byte[] BinFile = null;
            string TextFile = null;
            string FileName = string.Empty;

            if (valueIsFileName)
            {
                FileInfoFormat FileData = null;
                try
                {
                    FileData = GetFileInfo(value as string);
                }
                catch (Exception ex)
                {
                    ErrorMessage = ex.Message;
                    return -1;
                }

                Type = "FileResource";
                value = FileData.ValueString;
                FileName = FileData.FileName;

                if (FileData.FileFormatType == FileFormatTypes.Text)
                    TextFile = FileData.TextContent;
                else
                    BinFile = FileData.BinContent;
            }

            if (value == null)
                value = string.Empty;

            DbParameter BinFileParm = Data.CreateParameter("@BinFile", BinFile, DbType.Binary);
            DbParameter TextFileParm = Data.CreateParameter("@TextFile", TextFile);

            string Sql = "insert into " + DbResourceConfiguration.Current.ResourceTableName + " (ResourceId,Value,LocaleId,Type,Resourceset,BinFile,TextFile,Filename,Comment) Values (@ResourceID,@Value,@LocaleId,@Type,@ResourceSet,@BinFile,@TextFile,@FileName,@Comment)";
            if (Data.ExecuteNonQuery(Sql,
                                   Data.CreateParameter("@ResourceId", resourceId),
                                   Data.CreateParameter("@Value", value),
                                   Data.CreateParameter("@LocaleId", cultureName),
                                   Data.CreateParameter("@Type", Type),
                                   Data.CreateParameter("@ResourceSet", resourceSet),
                                   BinFileParm, TextFileParm,
                                   Data.CreateParameter("@FileName", FileName),
                                   Data.CreateParameter("@Comment", comment)) == -1)
            {
                ErrorMessage = Data.ErrorMessage;
                return -1;
            }

            return 1;
        }

        /// <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;
        }