C Operation Method of Establishing Database and Attaching Database
- 2021-10-25 07:40:34
- OfStack
This paper describes the operation method of creating database and attaching database by C #. Share it for your reference, as follows:
/// <summary>
/// Attached database method
/// </summary>
/// <param name="strSql"> Connection database string, connection master System database </param>
/// <param name="DataName"> Database name </param>
/// <param name="strMdf"> Database file MDF Path of </param>
/// <param name="strLdf"> Database file LDF Path of </param>
/// <param name="path"> Installation directory </param>
private void CreateDataBase( string strSql, string DataName, string strMdf, string strLdf, string path)
{
SqlConnection myConn = new SqlConnection(strSql);
String str = null ;
try
{
str = " EXEC sp_attach_db @dbname='"+DataName+"',@filename1='"+strMdf+"',@filename2='"+strLdf+"'";
SqlCommand myCommand = new SqlCommand(str, myConn);
myConn.Open();
myCommand.ExecuteNonQuery();
MessageBox.Show(" The database was installed successfully! Click OK to continue ");// Need Using System.Windows.Forms
}
catch(Exception e)
{
MessageBox.Show(" Database installation failed! " + e.Message+"\n\n"+" You can attach data manually ");
System.Diagnostics.Process.Start(path);// Open the installation directory
}
finally
{
myConn.Close();
}
}
public override void Install(System.Collections.IDictionary stateSaver)
{
string server = this.Context.Parameters["server"];// Server name
string uid = this.Context.Parameters["user"];//SQlServer User name
string pwd = this.Context.Parameters["pwd"];// Password
string path = this.Context.Parameters["targetdir"];// Installation directory
string strSql = "server=" + server + ";uid=" + uid + ";pwd=" + pwd + ";database=master";// Connection database string
string DataName = "JXC";// Database name
string strMdf = path + @"JXC.mdf";//MDF File path, please note that the file name should be the same as the database file name just added 1 Sample!
string strLdf = path + @"jxc_log.ldf";//LDF File path
base.Install(stateSaver);
this.CreateDataBase(strSql, DataName, strMdf, strLdf, path);// Start creating a database
}
/// <summary>
/// Test connection
/// </summary>
/// <param name="serverName"></param>
/// <param name="dbName"></param>
/// <param name="userName"></param>
/// <param name="password"></param>
private SqlConnection TestConnection(string serverName, string dbName, string userName, string password)
{
string connectionString = GetConnectionString(serverName, dbName, userName, password);
SqlConnection connection = new SqlConnection(connectionString);
try
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
return connection;
}
catch
{
CloseConnection(connection);
throw new InstallException(" Installation failed !\n Error in database configuration , Please configure the information correctly! ");
}
}
/// <summary>
/// Get the connection string
/// </summary>
/// <param name="serverName"></param>
/// <param name="dbName"></param>
/// <param name="userName"></param>
/// <param name="password"></param>
/// <returns></returns>
private string GetConnectionString(string serverName, string dbName, string userName, string password)
{
string connectionString = "Data Source={0};Initial Catalog={1};User ID={2};Password={3}";
connectionString = string.Format(connectionString, serverName, dbName, userName, password);
return connectionString;
}
/// <summary>
/// Create a database
/// </summary>
/// <param name="serverName"></param>
/// <param name="dbName"></param>
/// <param name="userName"></param>
/// <param name="password"></param>
/// <param name="connection"></param>
/// <param name="stateSaver"></param>
public int CreateDataBase(SqlConnection connection)
{
int result = -1;
connection.ChangeDatabase("master");
string createDBSql = @" if Exists(select 1 from sysdatabases where [name]=N'{0}')
begin
drop database {0}
end
GO
CREATE DATABASE {0} ";
createDBSql = string.Format(createDBSql, _dbName);
// Because there are Go In SQLCommand I don't know each other, so I use Go For the separator sql Statement
char[] split = new char[] { 'G', 'O' };
string[] sqlList = createDBSql.Split(split);
SqlCommand command = null;
try
{
command = connection.CreateCommand();
command.CommandType = System.Data.CommandType.Text;
foreach (string sqlItem in sqlList)
{
if (sqlItem.Length > 2)
{
command.CommandText = sqlItem;
result = command.ExecuteNonQuery();
}
}
return result;
}
catch
{
CloseConnection(connection);
command.Dispose();
throw new InstallException(" Installation failed !\n Incorrect database configuration !");
}
}
/// <summary>
/// Separation SQL Statement
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
private string[] splitSql(string sql)
{
Regex regex = new Regex("^GO", RegexOptions.IgnoreCase | RegexOptions.Multiline);
string[] sqlList = regex.Split(sql.ToUpper());
return sqlList;
}
For more readers interested in C # related content, please check the topics on this site: "Summary of Thread Use Skills in C # Programming", "Summary of C # Operating Excel Skills", "Summary of XML File Operation Skills in C #", "C # Common Control Usage Tutorial", "WinForm Control Usage Tutorial", "C # Data Structure and Algorithm Tutorial", "C # Array Operation Skills Summary" and "C # Object-Oriented Programming Introduction Tutorial"
I hope this article is helpful to everyone's C # programming.