C# SqlDataAccess.CreateParameter方法代码示例

        /// <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 an individual Resource String from the database
        /// </summary>
        /// <param name="resourceId"></param>
        /// <param name="resourceSet"></param>       
        /// <param name="cultureName"></param>
        /// <returns></returns>
        public string GetResourceString(string resourceId, string resourceSet, string cultureName)
        {
            SetError();

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

            object result;
            using (var data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString))
            {
                result = data.ExecuteScalar("select Value 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));
            }

            return result as string;
        }

        /// <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>
        /// Renames all property keys for a given property prefix. So this routine updates
        /// lblName.Text, lblName.ToolTip to lblName2.Text, lblName2.ToolTip if the property
        /// is changed from lblName to lblName2.
        /// </summary>
        /// <param name="Property"></param>
        /// <param name="NewProperty"></param>
        /// <param name="ResourceSet"></param>
        /// <returns></returns>
        public bool RenameResourceProperty(string Property, string NewProperty, string ResourceSet)
        {
            using (SqlDataAccess Data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString))
            {
                Property += ".";
                NewProperty += ".";
                string PropertyQuery = Property + "%";
                int Result = Data.ExecuteNonQuery("update " + DbResourceConfiguration.Current.ResourceTableName + " set ResourceId=replace(resourceid,@Property,@NewProperty) where [email protected] and ResourceId like @PropertyQuery",
                                                  Data.CreateParameter("@Property", Property),
                                                  Data.CreateParameter("@NewProperty", NewProperty),
                                                  Data.CreateParameter("@ResourceSet", ResourceSet),
                                                  Data.CreateParameter("@PropertyQuery", PropertyQuery));
                if (Result == -1)
                {
                    ErrorMessage = Data.ErrorMessage;
                    return false;
                }
            }

            return true;
        }

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

        /// <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>
        /// Deletes a specific resource ID based on ResourceId, ResourceSet and Culture.
        /// If an empty culture is passed the entire group is removed (ie. all locales).
        /// </summary>
        /// <param name="resourceId">Resource Id to delete</param>
        /// <param name="cultureName">language ID - if empty all languages are deleted</param>e
        /// <param name="resourceSet">The resource set to remove</param>
        /// <returns></returns>
        public bool DeleteResource(string resourceId, string cultureName = null, string resourceSet = null)
        {
            int Result = 0;

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

            using (SqlDataAccess Data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString))
            {
                if (!string.IsNullOrEmpty(cultureName))
                    // Delete the specific entry only
                    Result = Data.ExecuteNonQuery("delete from " + DbResourceConfiguration.Current.ResourceTableName +
                                                  " where [email protected] and [email protected] and [email protected]",
                                                  Data.CreateParameter("@ResourceId", resourceId),
                                                  Data.CreateParameter("@LocaleId", cultureName),
                                                  Data.CreateParameter("@ResourceSet", resourceSet));
                else
                    // If we're deleting the invariant entry - delete ALL of the languages for this key
                    Result = Data.ExecuteNonQuery("delete from " + DbResourceConfiguration.Current.ResourceTableName +
                                                  " where [email protected] and [email protected]",
                                                  Data.CreateParameter("@ResourceId", resourceId),
                                                  Data.CreateParameter("@ResourceSet", resourceSet));

                if (Result == -1)
                {
                    ErrorMessage = Data.ErrorMessage;
                    return false;
                }
            }

            return true;
        }

        /// <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 either local or global resources in a Web app
        /// 
        /// Fields:
        /// ResourceId,Value,LocaleId,ResourceSet,Type
        /// </summary>
        /// <param name="ResourceSet"></param>
        /// <returns></returns>
        public DataTable GetAllResources(bool LocalResources)
        {
            SqlDataAccess Data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString);

            string Sql = string.Empty;

            Sql = "select ResourceId,Value,LocaleId,ResourceSet,Type,TextFile,BinFile,FileName,Comment from " + DbResourceConfiguration.Current.ResourceTableName +
                  " where ResourceSet " +
                  (!LocalResources ? "not" : string.Empty) + " like @ResourceSet ORDER by ResourceSet,LocaleId";

            DataTable dt = Data.ExecuteTable("TResources",
                                             Sql,
                                            Data.CreateParameter("@ResourceSet", "%.%"));

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

            return dt;
        }

        /// <summary>
        /// Persists resources to the database - first wipes out all resources, then writes them back in
        /// from the ResourceSet
        /// </summary>
        /// <param name="ResourceList"></param>
        /// <param name="CultureName"></param>
        /// <param name="BaseName"></param>
        public bool GenerateResources(IDictionary ResourceList, string CultureName, string BaseName, bool DeleteAllResourceFirst)
        {
            if (ResourceList == null)
                throw new InvalidOperationException("No Resources");

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


            using (SqlDataAccess Data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString))
            {
                if (!Data.BeginTransaction())
                    return false;
                // Set transaction to be shared by other methods
                Transaction = Data.Transaction;
                try
                {
                    // First delete all resources for this resource set
                    if (DeleteAllResourceFirst)
                    {
                        int Result = Data.ExecuteNonQuery("delete " + DbResourceConfiguration.Current.ResourceTableName + " where [email protected] and [email protected]",
                                                          Data.CreateParameter("@LocaleId", CultureName),
                                                          Data.CreateParameter("@ResourceSet", BaseName));
                        if (Result == -1)
                        {
                            Data.RollbackTransaction();
                            return false;
                        }
                    }
                    // Now add them all back in one by one
                    foreach (DictionaryEntry Entry in ResourceList)
                    {
                        if (Entry.Value != null)
                        {
                            int Result = 0;
                            if (DeleteAllResourceFirst)
                                Result = AddResource(Entry.Key.ToString(), Entry.Value, CultureName, BaseName, null);
                            else
                                Result = UpdateOrAdd(Entry.Key.ToString(), Entry.Value, CultureName, BaseName, null);
                            if (Result == -1)
                            {
                                Data.RollbackTransaction();
                                return false;
                            }
                        }
                    }
                }
                catch
                {
                    Data.RollbackTransaction();
                    return false;
                }
                Data.CommitTransaction();
            }

            // Clear out the resources
            ResourceList = null;

            return true;
        }

        /// <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
//.........这里部分代码省略.........

        /// <summary>
        /// Checks to see if a resource exists in the resource store
        /// </summary>
        /// <param name="ResourceId"></param>
        /// <param name="Value"></param>
        /// <param name="CultureName"></param>
        /// <param name="ResourceSet"></param>
        /// <returns></returns>
        public bool ResourceExists(string ResourceId, string CultureName, string ResourceSet)
        {
            if (CultureName == null)
                CultureName = string.Empty;

            object Result = null;
            using (SqlDataAccess Data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString))
            {
                Result = Data.ExecuteScalar("select ResourceId from " + DbResourceConfiguration.Current.ResourceTableName + " where [email protected] and [email protected] and [email protected] group by ResourceId",
                                            Data.CreateParameter("@ResourceId", ResourceId),
                                            Data.CreateParameter("@LocaleId", CultureName),
                                            Data.CreateParameter("@ResourceSet", ResourceSet));
                if (Result == null)
                    return false;
            }

            return true;
        }

        /// <summary>
        /// Renames a resource set. Useful if you need to move a local page resource set
        /// to a new page. ResourceSet naming for local resources is application relative page path:
        /// 
        /// test.aspx
        /// subdir/test.aspx
        /// 
        /// Global resources have a simple name
        /// </summary>
        /// <param name="OldResourceSet">Name of the existing resource set</param>
        /// <param name="NewResourceSet">Name to set the resourceset name to</param>
        /// <returns></returns>
        public bool RenameResourceSet(string OldResourceSet, string NewResourceSet)
        {
            using (SqlDataAccess Data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString))
            {
                int Result = Data.ExecuteNonQuery("update " + DbResourceConfiguration.Current.ResourceTableName + " set [email protected] where [email protected]",
                                                  Data.CreateParameter("@NewResourceSet", NewResourceSet),
                                                  Data.CreateParameter("@OldResourceSet", OldResourceSet));
                if (Result == -1)
                {
                    ErrorMessage = Data.ErrorMessage;
                    return false;
                }
                if (Result == 0)
                {
                    SetError(Resources.Resources.No_matching_Recordset_found_);
                    return false;
                }
            }

            return true;
        }

        /// <summary>
        /// Deletes an entire resource set from the database. Careful with this function!
        /// </summary>
        /// <param name="ResourceSet"></param>
        /// <returns></returns>
        public bool DeleteResourceSet(string ResourceSet)
        {
            if (string.IsNullOrEmpty(ResourceSet))
                return false;

            int Result = -1;

            using (SqlDataAccess Data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString))
            {
                Result = Data.ExecuteNonQuery("delete from " + DbResourceConfiguration.Current.ResourceTableName + " where [email protected]",
                                              Data.CreateParameter("@ResourceSet", ResourceSet));
                if (Result < 0)
                {
                    ErrorMessage = Data.ErrorMessage;
                    return false;
                }
                if (Result > 0)
                    return true;
            }

            return false;
        }

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