dbinitiationhelper.cs

抽象工厂的典型实现

using System;
using System.Data;
using System.Data.Common;

#region 版权声明
///
/// 版权所有(C)2005,2006  作者：漆巧林。保留所有权利, [e-mail], [e-mail]
/// 
/// 作者不对本代码提供任何保证，因此，对于使用该代码带来的损害或者潜在的损害，作者不承担责任。
/// 在满足如下的条件下，你可以自由使用该代码：
/// 1. 非商业应用
/// 2. 保留本版权声明
/// 要进行商业应用，必须得到作者的书面许可。
/// 你可以修改和自由发布本代码，条件是保留前述的版权声明。
/// 
#endregion

namespace DataAccess
{

	/// <summary>
	/// 处理数据库初始化（创建数据库/用户）的接口
	/// </summary>
	public interface IDbInitiationHelper
	{
		void CreateUser(String userName, String password);
		void DropUser(String userName);

		void CreateDatabase(String dbName, String owner);
		void DropDatabase(String dbName);

		bool IsUserExisted(string userName);
		bool IsDatabaseExisted(string dbName);
	}

	/// <summary>
	/// Postgres数据库初始化助手类
	/// </summary>
	public class NpgsqlInitiationHelper : IDbInitiationHelper
	{
		private Npgsql.NpgsqlConnection connection;

		public NpgsqlInitiationHelper(String connectionString)
		{
			this.connection = new Npgsql.NpgsqlConnection(connectionString);
		}

		public NpgsqlInitiationHelper(IDbConnection connection)
		{
			this.connection = (Npgsql.NpgsqlConnection)connection;
		}

		#region IDbInitiationHelper 成员

		/// <summary>
		/// 创建指定的用户
		/// </summary>
		/// <param name="userName">要创建的用户名</param>
		/// <param name="password">用户密码</param>
		/// <exception cref="">数据库操作失败将会引发异常</exception>
		public void CreateUser(String userName, String password)
		{
			try
			{
				DropUser(userName);
			}
			catch(Exception)
			{
			}

			Npgsql.NpgsqlCommand cmd = this.connection.CreateCommand();
			cmd.CommandText = String.Format("CREATE USER \"{0}\" ENCRYPTED PASSWORD '{1}' NOCREATEDB NOCREATEUSER", userName, password);

			try
			{
				this.connection.Open();
				cmd.ExecuteNonQuery();
			}
			finally
			{
				this.connection.Close();
			}
		}

		/// <summary>
		/// 删除指定用户
		/// </summary>
		/// <param name="userName">用户名称</param>
		public void DropUser(String userName)
		{
			Npgsql.NpgsqlCommand cmd = this.connection.CreateCommand();
			cmd.CommandText = String.Format("DROP USER \"{0}\"", userName);
			try
			{
				this.connection.Open();
				cmd.ExecuteNonQuery();
			}
			finally
			{
				this.connection.Close();
			}
		}

		/// <summary>
		/// 创建数据库
		/// </summary>
		/// <param name="dbName">要创建的数据库名称</param>
		/// <param name="owner">所有者</param>
		public void CreateDatabase(String dbName, String owner)
		{
			try
			{
				DropDatabase(dbName);
			}
			catch(Exception)
			{
			}

			Npgsql.NpgsqlCommand cmd = this.connection.CreateCommand();
			cmd.CommandText = String.Format("CREATE DATABASE \"{0}\" OWNER = {1} ENCODING = 'EUC_CN' TEMPLATE template0", dbName, owner);
			try
			{
				this.connection.Open();
				cmd.ExecuteNonQuery();
			}
			finally
			{
				this.connection.Close();
			}
		}

		/// <summary>
		/// 删除指定的数据库
		/// </summary>
		/// <param name="dbName">数据库名称</param>
		public void DropDatabase(String dbName)
		{
			Npgsql.NpgsqlCommand cmd = this.connection.CreateCommand();
			cmd.CommandText = String.Format("DROP DATABASE \"{0}\"", dbName);
			try
			{
				this.connection.Open();
				cmd.ExecuteNonQuery();
			}
			finally
			{
				this.connection.Close();
			}
		}

		public bool IsUserExisted(string userName)
		{
			return false;
		}

		public bool IsDatabaseExisted(string dbName)
		{
			return false;
		}

		#endregion
	}


	public class SqlInitiationHelper : IDbInitiationHelper
	{
		private System.Data.SqlClient.SqlConnection connection;
		public SqlInitiationHelper(string connectionString)
		{
			this.connection = new System.Data.SqlClient.SqlConnection(connectionString);
		}

		public SqlInitiationHelper(IDbConnection connection)
		{
			this.connection = (System.Data.SqlClient.SqlConnection)connection;
		}
		#region IDbInitiationHelper 成员

		/// <summary>
		/// 创建用户
		/// </summary>
		/// <param name="userName">创建的用户名</param>
		/// <param name="password">用户密码</param>
		public void CreateUser(String userName, String password)
		{
			System.Data.SqlClient.SqlCommand cmd = this.connection.CreateCommand();
			cmd.CommandText = string.Format("sp_addlogin {0},{1},master",userName,password);
			try
			{
				this.connection.Open();
				cmd.ExecuteNonQuery();
			}
			finally
			{
				this.connection.Close();
			}
		}
		/// <summary>
		/// 删除用户
		/// </summary>
		/// <param name="userName">用户名</param>
		public void DropUser(String userName)
		{
			System.Data.SqlClient.SqlCommand cmd = this.connection.CreateCommand();
			cmd.CommandText = "sp_droplogin " + userName;
			try
			{
				this.connection.Open();
				cmd.ExecuteNonQuery();
			}
			finally
			{
				this.connection.Close();
			}
		}
		/// <summary>
		/// 创建数据库
		/// </summary>
		/// <param name="dbName">要创建的数据库名称/param>
		/// <param name="owner">所有者</param>
		public void CreateDatabase(String dbName, String owner)
		{
			try
			{
				DropDatabase(dbName);
			}
			catch(Exception)
			{
			}

			System.Data.SqlClient.SqlCommand cmd = this.connection.CreateCommand();
			cmd.CommandText = "CREATE DATABASE "+dbName;
			try
			{
				this.connection.Open();
				cmd.ExecuteNonQuery();
			}
			finally
			{
				this.connection.Close();
			}
			cmd.CommandText = string.Format("sp_changedbowner {0}",owner);
			try
			{
				this.connection.Open();
				this.connection.ChangeDatabase(dbName);
				cmd.ExecuteNonQuery();
			}
			finally
			{
				this.connection.Close();
			}
		}
		/// <summary>
		/// 删除数据库
		/// </summary>
		/// <param name="dbName">删除的数据库名称</param>
		public void DropDatabase(String dbName)
		{
			System.Data.SqlClient.SqlCommand cmd = this.connection.CreateCommand();
			cmd.CommandText = "DROP DATABASE " + dbName;
			try
			{
				this.connection.Open();
				cmd.ExecuteNonQuery();
			}
			finally
			{
				this.connection.Close();
			}
		}

		public bool IsUserExisted(string userName)
		{
			// TODO:  添加 SqlInitiationHelper.IsUserExisted 实现
			return false;
		}

		public bool IsDatabaseExisted(string dbName)
		{
			// TODO:  添加 SqlInitiationHelper.IsDatabaseExisted 实现
			return false;
		}

		#endregion
	}

	/// <summary>
	/// Odbc数据访问初始化助手类
	/// </summary>
	public class OdbcInitiationHelper : IDbInitiationHelper
	{
		private System.Data.Odbc.OdbcConnection connection;

		public OdbcInitiationHelper(string connectionString)
		{
			this.connection = new System.Data.Odbc.OdbcConnection(connectionString);
		}

		public OdbcInitiationHelper(IDbConnection connection)
		{
			this.connection = (System.Data.Odbc.OdbcConnection)connection;
		}
		#region IDbInitiationHelper 成员

		public void CreateUser(String userName, String password)
		{
			// TODO:  添加 OdbcInitiationHelper.CreateUser 实现
		}

		public void DropUser(String userName)
		{
			// TODO:  添加 OdbcInitiationHelper.DropUser 实现
		}

		public void CreateDatabase(String dbName, String owner)
		{
			// TODO:  添加 OdbcInitiationHelper.CreateDatabase 实现
		}

		public void DropDatabase(String dbName)
		{
			// TODO:  添加 OdbcInitiationHelper.DropDatabase 实现
		}

		public bool IsUserExisted(string userName)
		{
			// TODO:  添加 OdbcInitiationHelper.IsUserExisted 实现
			return false;
		}

		public bool IsDatabaseExisted(string dbName)
		{
			// TODO:  添加 OdbcInitiationHelper.IsDatabaseExisted 实现
			return false;
		}

		#endregion
	}


	/// <summary>
	/// OleDb数据访问初始化助手类
	/// </summary>
	public class OleDbInitiationHelper : IDbInitiationHelper
	{
		private System.Data.OleDb.OleDbConnection connection;

		public OleDbInitiationHelper(string connectionString)
		{
			this.connection = new System.Data.OleDb.OleDbConnection(connectionString);
		}

		public OleDbInitiationHelper(IDbConnection connection)
		{
			this.connection = (System.Data.OleDb.OleDbConnection)connection;
		}
		#region IDbInitiationHelper 成员

		public void CreateUser(String userName, String password)
		{
			// TODO:  添加 OleDbInitiationHelper.CreateUser 实现
		}

		public void DropUser(String userName)
		{
			// TODO:  添加 OleDbInitiationHelper.DropUser 实现
		}

		public void CreateDatabase(String dbName, String owner)
		{
			// TODO:  添加 OleDbInitiationHelper.CreateDatabase 实现
		}

		public void DropDatabase(String dbName)
		{
			// TODO:  添加 OleDbInitiationHelper.DropDatabase 实现
		}

		public bool IsUserExisted(string userName)
		{
			// TODO:  添加 OleDbInitiationHelper.IsUserExisted 实现
			return false;
		}

		public bool IsDatabaseExisted(string dbName)
		{
			// TODO:  添加 OleDbInitiationHelper.IsDatabaseExisted 实现
			return false;
		}

		#endregion
	}

	/// <summary>
	/// Oracle数据库初始化助手类
	/// </summary>
	public class OracleInitiationHelper : IDbInitiationHelper
	{
		System.Data.OracleClient.OracleConnection connection;

		public OracleInitiationHelper(string connectionString)
		{
			this.connection = new System.Data.OracleClient.OracleConnection(connectionString);
		}

		public OracleInitiationHelper(IDbConnection connection)
		{
			this.connection = (System.Data.OracleClient.OracleConnection)connection;
		}
		#region IDbInitiationHelper 成员

		public void CreateUser(String userName, String password)
		{
			// TODO:  添加 OracleInitiationHelper.CreateUser 实现
		}

		public void DropUser(String userName)
		{
			// TODO:  添加 OracleInitiationHelper.DropUser 实现
		}

		public void CreateDatabase(String dbName, String owner)
		{
			// TODO:  添加 OracleInitiationHelper.CreateDatabase 实现
		}

		public void DropDatabase(String dbName)
		{
			// TODO:  添加 OracleInitiationHelper.DropDatabase 实现
		}

		public bool IsUserExisted(string userName)
		{
			// TODO:  添加 OracleInitiationHelper.IsUserExisted 实现
			return false;
		}

		public bool IsDatabaseExisted(string dbName)
		{
			// TODO:  添加 OracleInitiationHelper.IsDatabaseExisted 实现
			return false;
		}

		#endregion
	}
}