Method one:
1. If no logical name is specified when attaching the database, it will be displayed as "full path in uppercase" on SQL2005. For example, when the database files D:MyTest.mdf and D:MyTest.ldf are attached to the SQL server, if no logical name is specified, they will be displayed as "D:MYTEST.MDF" on SQL2005. At this time, the C# connection string is "DataSource=dsName;AttatchDBFilename="D:MyTest.mdf";User ID=id;Password=pw";
2. If a logical name is specified when attaching the database, it will be displayed as "logical name" on SQL2005. At this time, the connection string of C# is "DataSource=dsName;AttatchDBFilename="D:MyTest.mdf";Initial Catalog =aa_LogicName;User ID=id;Password=pw”;
3. The attached database name cannot be changed at will; otherwise errors may easily occur.
Method two:
string DbPath=System.Environment.CurrentDirectory +@"Demo_Data.MDF ";
string LogPath=System.Environment.CurrentDirectory +@"Demo_Log.LDF ";
string StrSql="exec sp_attach_db @dbname='supmark',@filename1='"+DbPath+"',@filename2='"+LogPath+"'";
string strcon="Server=(local);Integrated Security=SSPI;Database=master";
SqlConnection cn=new SqlConnection(strcon);
SqlCommand cmd =new SqlCommand (StrSql,cn);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
Method three detailed code:
using System;
using System.Collections.Generic;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data;
using System.ServiceProcess;
namespace AdminZJC.DataBaseControl
{
/// <summary>
/// Database operation control class
/// </summary>
public class DataBaseControl
{
/// <summary>
/// Database connection string
/// </summary>
public string ConnectionString;
/// <summary>
///SQL operation statement/stored procedure
/// </summary>
public string StrSQL;
/// <summary>
/// Instantiate a database connection object
/// </summary>
private SqlConnection Conn;
/// <summary>
/// Instantiate a new database operation object Comm
/// </summary>
private SqlCommand Comm;
/// <summary>
/// The name of the database to be operated on
/// </summary>
public string DataBaseName;
/// <summary>
/// Complete address of database file
/// </summary>
public string DataBase_MDF;
/// <summary>
/// Complete address of database log file
/// </summary>
public string DataBase_LDF;
/// <summary>
/// Backup file name
/// </summary>
public string DataBaseOfBackupName;
/// <summary>
/// Backup file path
/// </summary>
public string DataBaseOfBackupPath;
/// <summary>
/// Perform operations to create/modify databases and tables
/// </summary>
public void DataBaseAndTableControl()
{
try
{
Conn = new SqlConnection(ConnectionString);
Conn.Open();
Comm = new SqlCommand();
Comm.Connection = Conn;
Comm.CommandText = StrSQL;
Comm.CommandType = CommandType.Text;
Comm.ExecuteNonQuery();
MessageBox.Show("Database operation successful!", "Information prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
Conn.Close();
}
}
/// <summary>
/// Attached database
/// </summary>
public void AddDataBase()
{
try
{
Conn = new SqlConnection(ConnectionString);
Conn.Open();
Comm = new SqlCommand();
Comm.Connection = Conn;
Comm.CommandText = "sp_attach_db";
Comm.Parameters.Add(new SqlParameter(@"dbname", SqlDbType.NVarChar));
Comm.Parameters[@"dbname"].Value = DataBaseName;
Comm.Parameters.Add(new SqlParameter(@"filename1", SqlDbType.NVarChar));
Comm.Parameters[@"filename1"].Value = DataBase_MDF;
Comm.Parameters.Add(new SqlParameter(@"filename2", SqlDbType.NVarChar));
Comm.Parameters[@"filename2"].Value = DataBase_LDF;
Comm.CommandType = CommandType.StoredProcedure;
Comm.ExecuteNonQuery();
MessageBox.Show("Database attached successfully", "Information prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
Conn.Close();
}
}
/// <summary>
/// Separate database
/// </summary>
public void DeleteDataBase()
{
try
{
Conn = new SqlConnection(ConnectionString);
Conn.Open();
Comm = new SqlCommand();
Comm.Connection = Conn;
Comm.CommandText = @"sp_detach_db";
Comm.Parameters.Add(new SqlParameter(@"dbname", SqlDbType.NVarChar));
Comm.Parameters[@"dbname"].Value = DataBaseName;
Comm.CommandType = CommandType.StoredProcedure;
Comm.ExecuteNonQuery();
MessageBox.Show("Database separation successful", "Information prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
Conn.Close();
}
}
/// <summary>
/// Back up database
/// </summary>
public void BackupDataBase()
{
try
{
Conn = new SqlConnection(ConnectionString);
Conn.Open();
Comm = new SqlCommand();
Comm.Connection = Conn;
Comm.CommandText = "use master;backup database @dbname to disk = @backupname;";
Comm.Parameters.Add(new SqlParameter(@"dbname", SqlDbType.NVarChar));
Comm.Parameters[@"dbname"].Value = DataBaseName;
Comm.Parameters.Add(new SqlParameter(@"backupname", SqlDbType.NVarChar));
Comm.Parameters[@"backupname"].Value = @DataBaseOfBackupPath + @DataBaseOfBackupName;
Comm.CommandType = CommandType.Text;
Comm.ExecuteNonQuery();
MessageBox.Show("Database backup successful", "Information prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
Conn.Close();
}
}
/// <summary>
///Restore database
/// </summary>
public void ReplaceDataBase()
{
try
{
string BackupFile = @DataBaseOfBackupPath + @DataBaseOfBackupName;
Conn = new SqlConnection(ConnectionString);
Conn.Open();
Comm = new SqlCommand();
Comm.Connection = Conn;
Comm.CommandText = "use master;restore database @DataBaseName From disk = @BackupFile with replace;";
Comm.Parameters.Add(new SqlParameter(@"DataBaseName", SqlDbType.NVarChar));
Comm.Parameters[@"DataBaseName"].Value = DataBaseName;
Comm.Parameters.Add(new SqlParameter(@"BackupFile", SqlDbType.NVarChar));
Comm.Parameters[@"BackupFile"].Value = BackupFile;
Comm.CommandType = CommandType.Text;
Comm.ExecuteNonQuery();
MessageBox.Show("Database restored successfully", "Information prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
Conn.Close();
}
}
}
}
/*
///Call example:
#------------------------------------------------ ---Restore database------------------------------------------------ ------------#
private void button0_Click(object sender, EventArgs e)
{
DataBaseControl DBC = new DataBaseControl();
DBC.ConnectionString = "Data Source=(local);User id=sa;Password=123456; Initial Catalog=master";
DBC.DataBaseName = "MyDatabase";
DBC.DataBaseOfBackupName = @"back.bak";
DBC.DataBaseOfBackupPath = @"D:Program FilesMicrosoft SQL ServerMSSQLData";
DBC.ReplaceDataBase();
}
#------------------------------------------------ -Additional database------------------------------------------------- ----------#
private void button1_Click_1(object sender, EventArgs e)
{
DataBaseControl DBC = new DataBaseControl();
DBC.ConnectionString = "Data Source=(local);User id=sa;Password=123456; Initial Catalog=master";
DBC.DataBaseName = "MyDatabase";
DBC.DataBase_MDF = @"D:Program FilesMicrosoft SQL ServerMSSQLDataMyDatabase_Data.MDF";
DBC.DataBase_LDF = @"D:Program FilesMicrosoft SQL ServerMSSQLDataMyDatabase_Log.LDF";
DBC.AddDataBase();
}
#----------------------------------------Backup database--------- -------------------------------------------------- ---------#
private void button2_Click(object sender, EventArgs e)
{
DataBaseControl DBC = new DataBaseControl();
DBC.ConnectionString = "Data Source=(local);User id=sa;Password=123456; Initial Catalog=master";
DBC.DataBaseName = "MyDatabase";
DBC.DataBaseOfBackupName = @"back.bak";
DBC.DataBaseOfBackupPath = @"D:Program FilesMicrosoft SQL ServerMSSQLData";
DBC.BackupDataBase();
}
#---------------------------------------------Separate database------ -------------------------------------------------- --------#
private void button3_Click(object sender, EventArgs e)
{
DataBaseControl DBC = new DataBaseControl();
DBC.ConnectionString = "Data Source=(local);User id=sa;Password=123456; Initial Catalog=master";
DBC.DataBaseName = "MyDatabase";
DBC.DeleteDataBase();