最近太忙.忙着项目收关,忙着新项目的标书,今天下班前刚好有些时间
写一个了关SQL Server 数据库附加分离的类.经过测式可以用,希望大家还用得上
有空又加了一个添加用户的功能
修改为类库如下
Code
/**//// <summary>
/// SQL Server 数据库附加分离
/// 作者:天纯蓝
/// </summary>
public class SQL_DBCommon
{
Members#region Members
SQL 语句#region SQL 语句
附加分离 SQL#region 附加分离 SQL
/**//// <summary>
/// 附加数据库SQL语句
/// @dbname:附加数据库名
/// @filename1:附加数据文件名称
/// @filename2:附加数据日志名称
/// </summary>
private string SQL_SP_ATTACH_DB = "EXEC SP_ATTACH_DB @dbname = '{0}', @filename1 = '{1}',@filename2='{2}'";
/**//// <summary>
/// 分离数据库SQL语句
/// {0}:分离数据库名
/// </summary>
private string SQL_SP_DETACH_DB = "EXEC SP_DETACH_DB {0}";
#endregion
添加删除用户 SQL#region 添加删除用户 SQL
/**//// <summary>
/// 添加登录用户
/// {0}:用户名
/// {1}:密码
/// {2}:默认数据库
/// </summary>
private string SQL_SP_AddLOGIN = "EXEC SP_AddLOGIN '{0}', '{1}', '{2}'";
/**//// <summary>
/// 删除登录用户
/// {0}:用户名
/// </summary>
private string SQL_SP_DROPLOGIN = "EXEC SP_DROPLOGIN '{0}'";
/**//// <summary>
/// 使其成为当前数据库的合法用户(必须在当前库中操作)
/// {0}:用户名
/// </summary>
private string SQL_SP_GRANTDBACCESS = "EXEC SP_GRANTDBACCESS N'{0}'";
/**//// <summary>
/// 授予对自己数据库的所有权限(必须在当前库中操作)
/// {0}:用户名
/// </summary>
private string SQL_SP_ADDROLEMEMBER = "EXEC SP_ADDROLEMEMBER N'db_owner', N'{0}'";
/**//// <summary>
/// 移除对数据库的访问权限(必须在当前库中操作)
/// {0}:用户名
/// </summary>
private string SQL_SP_REVOKEDBACCESS = "EXEC SP_REVOKEDBACCESS N'{0}'";
/**//// <summary>
/// 当前库是否存在该用户(必须在当前库中操作)
/// {0}:用户名
/// </summary>
private string SQL_USEREXIT = "SELECT count(*) FROM dbo.sysusers where [name] = '{0}'";
/**//// <summary>
/// 是否存在该用户(必须在当前库中操作)
/// </summary>
private string SQL_MASTER_USEREXIT = "SELECT count(*) FROM dbo.sysxlogins where [name] = '{0}'";
#endregion
#endregion
数据库成员#region 数据库成员
/**//// <summary>
/// 数据库地址
/// </summary>
private string m_DB_DataSource;
/**//// <summary>
/// 数据库地址
/// </summary>
public string DB_DataSource
{
get { return m_DB_DataSource; }
set { m_DB_DataSource = value; }
}
/**//// <summary>
/// 用户名
/// </summary>
private string m_UserID;
/**//// <summary>
/// 用户名
/// </summary>
public string UserID
{
get { return m_UserID; }
set { m_UserID = value; }
}
/**//// <summary>
/// 密码
/// </summary>
private string m_Pwd;
/**//// <summary>
/// 密码
/// </summary>
public string Pwd
{
get { return m_Pwd; }
set { m_Pwd = value; }
}
#endregion
#endregion
Construtors#region Construtors
public SQL_DBCommon()
{
}
public SQL_DBCommon(string dbsource,string userid,string pwd)
{
this.m_DB_DataSource = dbsource;
this.m_UserID = userid;
this.m_Pwd = pwd;
}
#endregion
Methods#region Methods
Private Methods#region Private Methods
附加分离数据库#region 附加分离数据库
/**//// <summary>
/// 附加数据库
/// </summary>
/// <returns></returns>
private bool m_AttachDB(string DBName,string MDBFile,string LOGFile)
{
try
{
string sql = string.Empty;
if (File.Exists(MDBFile))
{
sql = string.Format(SQL_SP_ATTACH_DB, DBName, MDBFile, LOGFile);
SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog=master;User ID={1};PWD={2}", m_DB_DataSource, m_UserID, m_Pwd));
using (conn)
{
lock (conn)
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
return true;
}
catch
{
return false;
}
}
/**//// <summary>
/// 分离数据库
/// </summary>
/// <param name="DBName"></param>
/// <returns></returns>
private bool m_DetachDB(string DBName)
{
try
{
string sql = string.Empty;
sql = string.Format(SQL_SP_DETACH_DB, DBName);
SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog=master;User ID={1};PWD={2}", m_DB_DataSource, m_UserID, m_Pwd));
using (conn)
{
lock (conn)
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.Close();
}
}
return true;
}
catch
{
return false;
}
}
#endregion
登陆用户判断#region 登陆用户判断
/**//// <summary>
/// 登陆用户是否存在
/// -1:异常失败
/// 0:不存在
/// 1:存在
/// </summary>
/// <param name="LoginName"></param>
/// <returns></returns>
private int m_Master_UserExit(string LoginName)
{
try
{
SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog=master;User ID={1};PWD={2}", m_DB_DataSource, m_UserID, m_Pwd));
using (conn)
{
string execSQL = string.Format(SQL_MASTER_USEREXIT, LoginName);
conn.Open();
SqlCommand cmd = new SqlCommand(execSQL, conn);
object obj = cmd.ExecuteScalar();
conn.Close();
return (int)obj;
}
}
catch
{
return -1;
}
}
/**//// <summary>
/// 数据用户是否存在
/// -1:异常失败
/// 0:不存在
/// 1:存在
/// </summary>
/// <param name="LoginName">用户名</param>
/// <param name="DB">数据库</param>
/// <returns></returns>
private int m_DB_UserExit(string LoginName,string DB)
{
try
{
SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog={1};User ID={2};PWD={3}", m_DB_DataSource, DB, m_UserID, m_Pwd));
using (conn)
{
string execSQL = string.Format(SQL_MASTER_USEREXIT, LoginName);
conn.Open();
SqlCommand cmd = new SqlCommand(execSQL, conn);
object obj = cmd.ExecuteScalar();
conn.Close();
return (int)obj;
}
}
catch
{
return -1;
}
}
#endregion
添加删除登录用户#region 添加删除登录用户
/**//// <summary>
/// 添加登陆用户
/// </summary>
/// <param name="LoginName">新增登陆用户名</param>
/// <param name="pwd">新增登陆用户密码</param>
/// <param name="DBName">数据库名</param>
/// <returns></returns>
private bool m_Add_Login(string LoginName, string pwd, string DBName)
{
try
{
SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog=master;User ID={1};PWD={2}", m_DB_DataSource, m_UserID, m_Pwd));
using (conn)
{
string execSQL = string.Format(SQL_SP_AddLOGIN, LoginName, pwd, DBName);
conn.Open();
SqlCommand cmd = new SqlCommand(execSQL, conn);
cmd.ExecuteNonQuery(); ;
conn.Close();
return true;
}
}
catch
{
return false;
}
}
/**//// <summary>
/// 删除登陆用户
/// </summary>
/// <param name="LoginName">登陆用户名</param>
/// <returns></returns>
private bool m_Del_Login(string LoginName)
{
try
{
SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog=master;User ID={1};PWD={2}", m_DB_DataSource, m_UserID, m_Pwd));
using (conn)
{
string execSQL = string.Format(SQL_SP_DROPLOGIN, LoginName);
conn.Open();
SqlCommand cmd = new SqlCommand(execSQL, conn);
cmd.ExecuteNonQuery(); ;
conn.Close();
return true;
}
}
catch
{
return false;
}
}
#endregion
分配移除登陆用户权限#region 分配移除登陆用户权限
/**//// <summary>
/// 分配登录用户权限
/// </summary>
/// <param name="LoginName">登陆用户名</param>
/// <param name="DBName">数据库</param>
/// <returns></returns>
private bool m_GrantDBAccess(string LoginName,string DBName)
{
try
{
SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog={1};User ID={2};PWD={3}", m_DB_DataSource, DBName, m_UserID, m_Pwd));
using (conn)
{
string execSQL1 = string.Format(SQL_SP_GRANTDBACCESS, LoginName);
string execSQL2 = string.Format(SQL_SP_ADDROLEMEMBER, LoginName);
conn.Open();
SqlCommand cmd1 = new SqlCommand(execSQL1, conn);
cmd1.ExecuteNonQuery();
SqlCommand cmd2 = new SqlCommand(execSQL2, conn);
cmd2.ExecuteNonQuery();
conn.Close();
return true;
}
}
catch
{
return false;
}
}
/**//// <summary>
/// 移除对数据库的访问权限
/// </summary>
/// <param name="LoginName">登陆用户名</param>
/// <param name="DBName">数据库</param>
/// <returns></returns>
private bool m_RevokeDBAccess(string LoginName, string DBName)
{
try
{
SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog={1};User ID={2};PWD={3}", m_DB_DataSource, DBName, m_UserID, m_Pwd));
using (conn)
{
string execSQL = string.Format(SQL_SP_REVOKEDBACCESS, LoginName);
conn.Open();
SqlCommand cmd = new SqlCommand(execSQL, conn);
cmd.ExecuteNonQuery();
conn.Close();
return true;
}
}
catch
{
return false;
}
}
#endregion
#endregion
Public Methods#region Public Methods
/**//// <summary>
/// 附加数据库
/// </summary>
/// <param name="DBName">string:附加的数据库名称</param>
/// <param name="MDBFile">string:附加数据库全址</param>
/// <param name="LOGFile">string:附加数据库日志文件全址</param>
/// <returns></returns>
public bool AttachDB(string DBName, string MDBFile, string LOGFile)
{
return m_AttachDB(DBName, MDBFile, LOGFile);
}
/**//// <summary>
/// 分离数据库
/// </summary>
/// <param name="DBName">分离的数据库名称</param>
/// <returns></returns>
public bool DetachDB(string DBName)
{
return m_DetachDB(DBName);
}
/**//// <summary>
/// 给附加的数据库添加新的登陆用户
/// </summary>
/// <param name="DBName">string:附加的数据库名称</param>
/// <param name="LoginName">string:用户名称</param>
/// <param name="Pwd">string:密码</param>
/// <returns></returns>
public bool AddUser(string DBName, string LoginName, string Pwd)
{
//取用户是否创建
int masterUserFlag = m_Master_UserExit(LoginName);
if (masterUserFlag == 0)//没有该用户
{
//新增登录用户
m_Add_Login(LoginName, Pwd, DBName);
//分配用户数据库及权限
m_GrantDBAccess(LoginName, DBName);
return true;
}
else if (masterUserFlag == -1)//数据库异常
{
return false;
}
else
{
int userFlag = m_DB_UserExit(LoginName, DBName);
if (userFlag == 1)//如果存在移除法重新分配
{
//移除对数据库的访问权限
m_RevokeDBAccess(LoginName, DBName);
}
else
{
if (userFlag == -1)
return false;
}
//分配用户数据库及权限
m_GrantDBAccess(LoginName, DBName);
return true;
}
}
#endregion
#endregion
}
/**//// <summary>
/// SQL Server 数据库附加分离
/// 作者:天纯蓝
/// </summary>
public class SQL_DBCommon
{
Members#region Members
SQL 语句#region SQL 语句
附加分离 SQL#region 附加分离 SQL
/**//// <summary>
/// 附加数据库SQL语句
/// @dbname:附加数据库名
/// @filename1:附加数据文件名称
/// @filename2:附加数据日志名称
/// </summary>
private string SQL_SP_ATTACH_DB = "EXEC SP_ATTACH_DB @dbname = '{0}', @filename1 = '{1}',@filename2='{2}'";
/**//// <summary>
/// 分离数据库SQL语句
/// {0}:分离数据库名
/// </summary>
private string SQL_SP_DETACH_DB = "EXEC SP_DETACH_DB {0}";
#endregion
添加删除用户 SQL#region 添加删除用户 SQL
/**//// <summary>
/// 添加登录用户
/// {0}:用户名
/// {1}:密码
/// {2}:默认数据库
/// </summary>
private string SQL_SP_AddLOGIN = "EXEC SP_AddLOGIN '{0}', '{1}', '{2}'";
/**//// <summary>
/// 删除登录用户
/// {0}:用户名
/// </summary>
private string SQL_SP_DROPLOGIN = "EXEC SP_DROPLOGIN '{0}'";
/**//// <summary>
/// 使其成为当前数据库的合法用户(必须在当前库中操作)
/// {0}:用户名
/// </summary>
private string SQL_SP_GRANTDBACCESS = "EXEC SP_GRANTDBACCESS N'{0}'";
/**//// <summary>
/// 授予对自己数据库的所有权限(必须在当前库中操作)
/// {0}:用户名
/// </summary>
private string SQL_SP_ADDROLEMEMBER = "EXEC SP_ADDROLEMEMBER N'db_owner', N'{0}'";
/**//// <summary>
/// 移除对数据库的访问权限(必须在当前库中操作)
/// {0}:用户名
/// </summary>
private string SQL_SP_REVOKEDBACCESS = "EXEC SP_REVOKEDBACCESS N'{0}'";
/**//// <summary>
/// 当前库是否存在该用户(必须在当前库中操作)
/// {0}:用户名
/// </summary>
private string SQL_USEREXIT = "SELECT count(*) FROM dbo.sysusers where [name] = '{0}'";
/**//// <summary>
/// 是否存在该用户(必须在当前库中操作)
/// </summary>
private string SQL_MASTER_USEREXIT = "SELECT count(*) FROM dbo.sysxlogins where [name] = '{0}'";
#endregion
#endregion
数据库成员#region 数据库成员
/**//// <summary>
/// 数据库地址
/// </summary>
private string m_DB_DataSource;
/**//// <summary>
/// 数据库地址
/// </summary>
public string DB_DataSource
{
get { return m_DB_DataSource; }
set { m_DB_DataSource = value; }
}
/**//// <summary>
/// 用户名
/// </summary>
private string m_UserID;
/**//// <summary>
/// 用户名
/// </summary>
public string UserID
{
get { return m_UserID; }
set { m_UserID = value; }
}
/**//// <summary>
/// 密码
/// </summary>
private string m_Pwd;
/**//// <summary>
/// 密码
/// </summary>
public string Pwd
{
get { return m_Pwd; }
set { m_Pwd = value; }
}
#endregion
#endregion
Construtors#region Construtors
public SQL_DBCommon()
{
}
public SQL_DBCommon(string dbsource,string userid,string pwd)
{
this.m_DB_DataSource = dbsource;
this.m_UserID = userid;
this.m_Pwd = pwd;
}
#endregion
Methods#region Methods
Private Methods#region Private Methods
附加分离数据库#region 附加分离数据库
/**//// <summary>
/// 附加数据库
/// </summary>
/// <returns></returns>
private bool m_AttachDB(string DBName,string MDBFile,string LOGFile)
{
try
{
string sql = string.Empty;
if (File.Exists(MDBFile))
{
sql = string.Format(SQL_SP_ATTACH_DB, DBName, MDBFile, LOGFile);
SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog=master;User ID={1};PWD={2}", m_DB_DataSource, m_UserID, m_Pwd));
using (conn)
{
lock (conn)
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
return true;
}
catch
{
return false;
}
}
/**//// <summary>
/// 分离数据库
/// </summary>
/// <param name="DBName"></param>
/// <returns></returns>
private bool m_DetachDB(string DBName)
{
try
{
string sql = string.Empty;
sql = string.Format(SQL_SP_DETACH_DB, DBName);
SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog=master;User ID={1};PWD={2}", m_DB_DataSource, m_UserID, m_Pwd));
using (conn)
{
lock (conn)
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.Close();
}
}
return true;
}
catch
{
return false;
}
}
#endregion
登陆用户判断#region 登陆用户判断
/**//// <summary>
/// 登陆用户是否存在
/// -1:异常失败
/// 0:不存在
/// 1:存在
/// </summary>
/// <param name="LoginName"></param>
/// <returns></returns>
private int m_Master_UserExit(string LoginName)
{
try
{
SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog=master;User ID={1};PWD={2}", m_DB_DataSource, m_UserID, m_Pwd));
using (conn)
{
string execSQL = string.Format(SQL_MASTER_USEREXIT, LoginName);
conn.Open();
SqlCommand cmd = new SqlCommand(execSQL, conn);
object obj = cmd.ExecuteScalar();
conn.Close();
return (int)obj;
}
}
catch
{
return -1;
}
}
/**//// <summary>
/// 数据用户是否存在
/// -1:异常失败
/// 0:不存在
/// 1:存在
/// </summary>
/// <param name="LoginName">用户名</param>
/// <param name="DB">数据库</param>
/// <returns></returns>
private int m_DB_UserExit(string LoginName,string DB)
{
try
{
SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog={1};User ID={2};PWD={3}", m_DB_DataSource, DB, m_UserID, m_Pwd));
using (conn)
{
string execSQL = string.Format(SQL_MASTER_USEREXIT, LoginName);
conn.Open();
SqlCommand cmd = new SqlCommand(execSQL, conn);
object obj = cmd.ExecuteScalar();
conn.Close();
return (int)obj;
}
}
catch
{
return -1;
}
}
#endregion
添加删除登录用户#region 添加删除登录用户
/**//// <summary>
/// 添加登陆用户
/// </summary>
/// <param name="LoginName">新增登陆用户名</param>
/// <param name="pwd">新增登陆用户密码</param>
/// <param name="DBName">数据库名</param>
/// <returns></returns>
private bool m_Add_Login(string LoginName, string pwd, string DBName)
{
try
{
SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog=master;User ID={1};PWD={2}", m_DB_DataSource, m_UserID, m_Pwd));
using (conn)
{
string execSQL = string.Format(SQL_SP_AddLOGIN, LoginName, pwd, DBName);
conn.Open();
SqlCommand cmd = new SqlCommand(execSQL, conn);
cmd.ExecuteNonQuery(); ;
conn.Close();
return true;
}
}
catch
{
return false;
}
}
/**//// <summary>
/// 删除登陆用户
/// </summary>
/// <param name="LoginName">登陆用户名</param>
/// <returns></returns>
private bool m_Del_Login(string LoginName)
{
try
{
SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog=master;User ID={1};PWD={2}", m_DB_DataSource, m_UserID, m_Pwd));
using (conn)
{
string execSQL = string.Format(SQL_SP_DROPLOGIN, LoginName);
conn.Open();
SqlCommand cmd = new SqlCommand(execSQL, conn);
cmd.ExecuteNonQuery(); ;
conn.Close();
return true;
}
}
catch
{
return false;
}
}
#endregion
分配移除登陆用户权限#region 分配移除登陆用户权限
/**//// <summary>
/// 分配登录用户权限
/// </summary>
/// <param name="LoginName">登陆用户名</param>
/// <param name="DBName">数据库</param>
/// <returns></returns>
private bool m_GrantDBAccess(string LoginName,string DBName)
{
try
{
SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog={1};User ID={2};PWD={3}", m_DB_DataSource, DBName, m_UserID, m_Pwd));
using (conn)
{
string execSQL1 = string.Format(SQL_SP_GRANTDBACCESS, LoginName);
string execSQL2 = string.Format(SQL_SP_ADDROLEMEMBER, LoginName);
conn.Open();
SqlCommand cmd1 = new SqlCommand(execSQL1, conn);
cmd1.ExecuteNonQuery();
SqlCommand cmd2 = new SqlCommand(execSQL2, conn);
cmd2.ExecuteNonQuery();
conn.Close();
return true;
}
}
catch
{
return false;
}
}
/**//// <summary>
/// 移除对数据库的访问权限
/// </summary>
/// <param name="LoginName">登陆用户名</param>
/// <param name="DBName">数据库</param>
/// <returns></returns>
private bool m_RevokeDBAccess(string LoginName, string DBName)
{
try
{
SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog={1};User ID={2};PWD={3}", m_DB_DataSource, DBName, m_UserID, m_Pwd));
using (conn)
{
string execSQL = string.Format(SQL_SP_REVOKEDBACCESS, LoginName);
conn.Open();
SqlCommand cmd = new SqlCommand(execSQL, conn);
cmd.ExecuteNonQuery();
conn.Close();
return true;
}
}
catch
{
return false;
}
}
#endregion
#endregion
Public Methods#region Public Methods
/**//// <summary>
/// 附加数据库
/// </summary>
/// <param name="DBName">string:附加的数据库名称</param>
/// <param name="MDBFile">string:附加数据库全址</param>
/// <param name="LOGFile">string:附加数据库日志文件全址</param>
/// <returns></returns>
public bool AttachDB(string DBName, string MDBFile, string LOGFile)
{
return m_AttachDB(DBName, MDBFile, LOGFile);
}
/**//// <summary>
/// 分离数据库
/// </summary>
/// <param name="DBName">分离的数据库名称</param>
/// <returns></returns>
public bool DetachDB(string DBName)
{
return m_DetachDB(DBName);
}
/**//// <summary>
/// 给附加的数据库添加新的登陆用户
/// </summary>
/// <param name="DBName">string:附加的数据库名称</param>
/// <param name="LoginName">string:用户名称</param>
/// <param name="Pwd">string:密码</param>
/// <returns></returns>
public bool AddUser(string DBName, string LoginName, string Pwd)
{
//取用户是否创建
int masterUserFlag = m_Master_UserExit(LoginName);
if (masterUserFlag == 0)//没有该用户
{
//新增登录用户
m_Add_Login(LoginName, Pwd, DBName);
//分配用户数据库及权限
m_GrantDBAccess(LoginName, DBName);
return true;
}
else if (masterUserFlag == -1)//数据库异常
{
return false;
}
else
{
int userFlag = m_DB_UserExit(LoginName, DBName);
if (userFlag == 1)//如果存在移除法重新分配
{
//移除对数据库的访问权限
m_RevokeDBAccess(LoginName, DBName);
}
else
{
if (userFlag == -1)
return false;
}
//分配用户数据库及权限
m_GrantDBAccess(LoginName, DBName);
return true;
}
}
#endregion
#endregion
}