C# SqlDataAccess.ExecuteNonQuery方法代码示例

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

        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.");
            }
        }

        /// <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>
        /// Updates an existing resource in the Localization table
        /// </summary>
        /// <param name="ResourceId"></param>
        /// <param name="Value"></param>
        /// <param name="CultureName"></param>
        /// <param name="ResourceSet"></param>
        /// <param name="Type"></param>
        public int UpdateResource(string ResourceId, object Value, string CultureName, string ResourceSet, string Comment, bool ValueIsFileName)
        {
            string Type = string.Empty;
            if (CultureName == null)
                CultureName = string.Empty;

            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;

                if (Value == null)
                    Value = 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;

            // Set up Binfile and TextFile parameters which are set only for
            // file values - otherwise they'll pass as Null values.
            DbParameter BinFileParm = Data.CreateParameter("@BinFile", BinFile, DbType.Binary);

            DbParameter TextFileParm = Data.CreateParameter("@TextFile", TextFile);

            int Result = 0;

            string Sql = "update " + DbResourceConfiguration.Current.ResourceTableName + " set [email protected], [email protected], [email protected],[email protected],[email protected], [email protected] " +
                         "where [email protected] AND [email protected] and [email protected]";
            Result = Data.ExecuteNonQuery(Sql,
                               Data.CreateParameter("@ResourceId", ResourceId),
                               Data.CreateParameter("@Value", Value),
                               Data.CreateParameter("@Type", Type),
                               Data.CreateParameter("@LocaleId", CultureName),
                               Data.CreateParameter("@ResourceSet", ResourceSet),
                                BinFileParm, TextFileParm,
                               Data.CreateParameter("@FileName", FileName),
                               Data.CreateParameter("@Comment", Comment)
                               );
            if (Result == -1)
            {
                ErrorMessage = Data.ErrorMessage;
                return -1;
            }

            return Result;
        }

        /// <summary>
        /// Create a backup of the localization database.
        /// 
        /// Note the table used is the one specified in the DbResourceConfiguration.Current.ResourceTableName
        /// </summary>
        /// <param name="BackupTableName">Table of the backup table. Null creates a _Backup table.</param>
        /// <returns></returns>
        public bool CreateBackupTable(string BackupTableName)
        {
            if (BackupTableName == null)
                BackupTableName = DbResourceConfiguration.Current.ResourceTableName + "_Backup";

            var data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString);

            data.ExecuteNonQuery("drop table " + BackupTableName);
            if (data.ExecuteNonQuery("select * into " + BackupTableName + " from " + DbResourceConfiguration.Current.ResourceTableName) < 0)
            {
                ErrorMessage = data.ErrorMessage;
                return false;
            }

            return true;
        }

        /// <summary>
        /// Restores the localization table from a backup table by first wiping out 
        /// </summary>
        /// <param name="backupTableName"></param>
        /// <returns></returns>
        public bool RestoreBackupTable(string backupTableName)
        {
            if (backupTableName == null)
                backupTableName = DbResourceConfiguration.Current.ResourceTableName + "_Backup";

            using (var data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString))
            {

                data.BeginTransaction();

                if (data.ExecuteNonQuery("delete from " + DbResourceConfiguration.Current.ResourceTableName) < 0)
                {
                    data.RollbackTransaction();
                    ErrorMessage = data.ErrorMessage;
                    return false;
                }

                string sql =
    @"insert into {0}
  (ResourceId,Value,LocaleId,ResourceSet,Type,BinFile,TextFile,FileName,Comment) 
   select ResourceId,Value,LocaleId,ResourceSet,Type,BinFile,TextFile,FileName,Comment from {1}";

                sql = string.Format(sql, DbResourceConfiguration.Current.ResourceTableName, backupTableName);

                if (data.ExecuteNonQuery(sql) < 0)
                {
                    data.RollbackTransaction();
                    ErrorMessage = data.ErrorMessage;
                    return false;
                }

                data.CommitTransaction();
            }

            return true;
        }

        /// <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>
        /// 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>
        /// 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>
        /// Renames a given resource in a resource set. Note all languages will be renamed
        /// </summary>
        /// <param name="ResourceId"></param>
        /// <param name="NewResourceId"></param>
        /// <param name="ResourceSet"></param>
        /// <returns></returns>
        public bool RenameResource(string ResourceId, string NewResourceId, string ResourceSet)
        {
            SqlDataAccess Data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString);

            int Result = Data.ExecuteNonQuery("update " + DbResourceConfiguration.Current.ResourceTableName + " set [email protected] where [email protected] AND [email protected]",
                               Data.CreateParameter("@ResourceId", ResourceId),
                               Data.CreateParameter("@NewResourceId", NewResourceId),
                               Data.CreateParameter("@ResourceSet", ResourceSet));
            if (Result == -1)
            {
                ErrorMessage = Data.ErrorMessage;
                return false;
            }
            if (Result == 0)
            {
                ErrorMessage = "Invalid ResourceId";
                return false;
            }

            return true;
        }