SQL Server的Exception Handling

   (
   @user_name NVARCHAR(256),
   @role_name NVARCHAR(256)
   )
  AS
  
  DECLARE @user_id VARCHAR(50)
  DECLARE @role_id VARCHAR(50)
  
  DECLARE @error_number INT
  DECLARE @error_serverity INT
  DECLARE @error_state INT
  
  BEGIN TRY
  
  SELECT @user_id = [USER_ID] FROM dbo.T_USERS WHERE LOWERED_USER_NAME = LOWER(@user_name)
  
  IF(@user_id IS NULL)
   BEGIN
   RAISERROR (50003,16,1)
   END
  
  SELECT @role_id = [ROLE_ID] FROM dbo.T_ROLES WHERE LOWERED_ROLE_NAME = LOWER(@role_name)
  
  IF(@role_id IS NULL)
   BEGIN
   RAISERROR (50004,16,1)
   END
  
  IF(EXISTS(SELECT * FROM T_USERS_IN_ROLES WHERE [USER_ID] = @user_id AND ROLE_ID = @role_id))
   BEGIN
   RAISERROR (50005,16,1)
   END
  
  INSERT INTO dbo.T_USERS_IN_ROLES([USER_ID],ROLE_ID) VALUES(@user_id,@role_id)
  
  END TRY
  
  BEGIN CATCH
  
   SET @error_number = ERROR_NUMBER()
   SET @error_serverity =ERROR_SEVERITY()
   SET @error_state = ERROR_STATE()
   RAISERROR(@error_number,@error_serverity,@error_state)
  END CATCH
  
  · Delete Data: P_CLEAR_DATA
  
  CREATE Procedure P_CLEAR_DATA
  AS
  
   DELETE FROM dbo.T_USERS_IN_ROLES
   DELETE FROM dbo.T_USERS
   DELETE FROM dbo.T_ROLES
  
  GO
  
  4． Common Function：Utility. ExecuteCommand
  
  
  
  private const string connectionStringName = "TestDb";
  public static bool ExecuteCommand(string procedureName, Dictionary<string, object> parameters)
   {
   ConnectionStringSettings connectionStringSection = ConfigurationManager.ConnectionStrings[connectionStringName];
   DbProviderFactory dbProviderFactory = DbProviderFactories.GetFactory(connectionStringSection.ProviderName);
   using (DbConnection connection = dbProviderFactory.CreateConnection())
   {
   connection.ConnectionString = connectionStringSection.ConnectionString;
   connection.Open();
   DbCommand command = connection.CreateCommand();
   command.CommandText = procedureName;
   command.CommandType = CommandType.StoredProcedure;
   DbParameter parameter;
   foreach (KeyValuePair<string, object> param in parameters)
   {
   parameter = dbProviderFactory.CreateParameter();
   parameter.ParameterName = param.Key;
   parameter.DbType = DbType.String;
   parameter.Value = param.Value;
   command.Parameters.Add(parameter);
   }
   DbTransaction transation = connection.BeginTransaction();
   command.Transaction = transation;
   try
   {
   command.ExecuteNonQuery();
   transation.Commit();
   return true;
   }
   catch
   {
   transation.Rollback();
   throw;
   }
   }
  }
  
  
  
  
  5． Create User, Create Role, Add User In Role, Delete All Data
  
  
   private const string ErrorUserExists = "This user is already existent";
   private const string ErrorRoleExists = "This role is already existent";
   private const string ErrorUserNotExists = "This user does not exist";
   private const string ErrorRoleNotExists = "This role does not exist";
   private const string ErrorUserInRole = "This user is already in the role";
  
   public static bool CreateUser(string userName)
   {
   string procedureName = "P_USERS_I";
   Dictionary<string, object> parameters = new Dictionary<string, object>();
   parameters.Add("user_id", Guid.NewGuid().ToString());

（编辑：IT资讯之家 www.it55.com）