C# SqlDataAccess.ExecuteTable方法代码示例

        /// <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 DataTable GetAllResourcesForCulture(string resourceSet, string cultureName)
        {
            if (cultureName == null)
                cultureName = string.Empty;

            using (var data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString))
            {
                return data.ExecuteTable("TResources",
                                         "select ResourceId, Value from " + DbResourceConfiguration.Current.ResourceTableName + " where [email protected] and [email protected]",
                                         data.CreateParameter("@ResourceSet", resourceSet),
                                         data.CreateParameter("@LocaleId", cultureName));
            }
        }

        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>
        /// Returns all available resource sets
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllResourceSets(ResourceListingTypes Type)
        {
            SqlDataAccess Data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString);
            DataTable dt = null;

            if (Type == ResourceListingTypes.AllResources)
                dt = Data.ExecuteTable("TResourcesets", "select ResourceSet as ResourceSet from " + DbResourceConfiguration.Current.ResourceTableName + " group by ResourceSet");
            else if (Type == ResourceListingTypes.LocalResourcesOnly)
                dt = Data.ExecuteTable("TResourcesets", "select ResourceSet as ResourceSet from " + DbResourceConfiguration.Current.ResourceTableName + " where resourceset like @ResourceSet group by ResourceSet",
                                 Data.CreateParameter("@ResourceSet", "%.%"));
            else if (Type == ResourceListingTypes.GlobalResourcesOnly)
                dt = Data.ExecuteTable("TResourcesets", "select ResourceSet as ResourceSet from " + DbResourceConfiguration.Current.ResourceTableName + " where resourceset not like @ResourceSet group by ResourceSet",
                                 Data.CreateParameter("@ResourceSet", "%.%"));

            if (dt == null)
                ErrorMessage = Data.ErrorMessage;

            return dt;
        }

        /// <summary>
        /// Gets all the locales for a specific resource set.
        /// 
        /// Returns a table named TLocaleIds (LocaleId field)
        /// </summary>
        /// <param name="ResourceSet"></param>
        /// <returns></returns>
        public DataTable GetAllLocaleIds(string resourceSet)
        {
            if (resourceSet == null)
                resourceSet = string.Empty;

            using (SqlDataAccess data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString))
            {
                return data.ExecuteTable("TLocaleIds", "select LocaleId,'' as Language from " + DbResourceConfiguration.Current.ResourceTableName +
                                                       " where [email protected] group by LocaleId",
                                         data.CreateParameter("@ResourceSet", resourceSet));
            }
        }

        /// <summary>
        /// Returns all available resource ids for a given resource set in all languages.
        /// 
        /// Returns a DataTable called TResoureIds with ResourecId and HasValue fields
        /// HasValue returns whether there are any entries in any culture for this
        /// resourceId
        /// </summary>
        /// <param name="resourceSet"></param>
        /// <returns></returns>
        public DataTable GetAllResourceIds(string resourceSet)
        {
            using (var data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString))
            {
                string sql =
                    @"select resourceId,CAST( MAX( 
	  case  
		WHEN len( CAST(Value as varchar(max))) > 0 THEN 1
		ELSE 0
	  end ) as Bit) as HasValue
	  	from " + DbResourceConfiguration.Current.ResourceTableName +
                    @" where [email protected] 
	group by ResourceId";

                var dt = data.ExecuteTable("TResourceIds", sql,
                                         data.CreateParameter("@ResourceSet", resourceSet));
                if (dt == null)
                {
                    SetError(data.ErrorMessage);
                    return null;
                }

                return dt;
            }
        }

        /// <summary>
        /// Returns a data table of all the resources for all locales. The result is in a 
        /// table called TResources that contains all fields of the table. The table is
        /// ordered by LocaleId.
        /// 
        /// This version returns ALL resources
        /// 
        /// Fields:
        /// ResourceId,Value,LocaleId,ResourceSet,Type
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllResources()
        {
            DataTable dt;
            using (SqlDataAccess data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString))
            {
                string sql = "select ResourceId,Value,LocaleId,ResourceSet,Type,TextFile,BinFile,FileName,Comment from " +
                             DbResourceConfiguration.Current.ResourceTableName +
                             " ORDER by ResourceSet,LocaleId";

                dt = data.ExecuteTable("TResources", sql, data.CreateParameter("@ResourceSet", "%.%"));

                if (dt == null)
                {
                    SetError(data.ErrorMessage);
                    return null;
                }
            }

            return dt;
        }

 public void CheckDataBase()
 {
     SqlDataAccess db = new SqlDataAccess(STR_ConnectionString);
     var tb = db.ExecuteTable("localizations","select * from localizations");
     Console.WriteLine(tb.Rows.Count);
 }

 public void DataBase()
 {
     SqlDataAccess db = new SqlDataAccess("DevSamples");
     var tb = db.ExecuteTable("localizations","select * from localizations");
     Console.WriteLine(tb.Rows.Count);
 }