C#附加SQL2005数据库详细代码

王朝学院·作者佚名  2009-12-01
窄屏简体版  字體: |||超大  

方法一:

1.如果附加数据库时,没有指定逻辑名,则在SQL2005上显示为“全路径,且为大写”。比如,数据库文件D:MyTest.mdf和D:MyTest.ldf,附加到SQL服务器上时,如果没有指定逻辑名,则在SQL2005上显示为“D:MYTEST.MDF”。此时,C#的连接字符串为“DataSource=dsName;AttatchDBFilename=”D:\MyTest.mdf”;User ID=id;Password=pw”;

2.如果附加数据库时,指定了逻辑名,则在SQL2005上显示为“逻辑名”,此时,C#的连接字符串为“DataSource=dsName;AttatchDBFilename=”D:\MyTest.mdf”;Initial Catalog=aa_LogicName;User ID=id;Password=pw”;

3.被附加的数据库名称不可随意更改;否则容易出现错误。

方法二:

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();

方法三详细代码:

using System;

using System.Collections.Generic;

using System.Windows.Forms;

using System.Data.SqlClient;

using System.Data;

using System.ServiceProcess;

namespace AdminZJC.DataBaseControl

{

/// <summary>

/// 数据库操作控制类

/// </summary>

public class DataBaseControl

{

/// <summary>

/// 数据库连接字符串

/// </summary>

public string ConnectionString;

/// <summary>

/// SQL操作语句/存储过程

/// </summary>

public string StrSQL;

/// <summary>

/// 实例化一个数据库连接对象

/// </summary>

private SqlConnection Conn;

/// <summary>

/// 实例化一个新的数据库操作对象Comm

/// </summary>

private SqlCommand Comm;

/// <summary>

/// 要操作的数据库名称

/// </summary>

public string DataBaseName;

/// <summary>

/// 数据库文件完整地址

/// </summary>

public string DataBase_MDF;

/// <summary>

/// 数据库日志文件完整地址

/// </summary>

public string DataBase_LDF;

/// <summary>

/// 备份文件名

/// </summary>

public string DataBaseOfBackupName;

/// <summary>

/// 备份文件路径

/// </summary>

public string DataBaseOfBackupPath;

/// <summary>

/// 执行创建/修改数据库和表的操作

/// </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("数据库操作成功!", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

catch (Exception ex)

{

MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

finally

{

Conn.Close();

}

}

/// <summary>

/// 附加数据库

/// </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("附加数据库成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

catch (Exception ex)

{

MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

finally

{

Conn.Close();

}

}

/// <summary>

/// 分离数据库

/// </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("分离数据库成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

catch (Exception ex)

{

MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

finally

{

Conn.Close();

}

}

/// <summary>

/// 备份数据库

/// </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("备份数据库成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

catch (Exception ex)

{

MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

finally

{

Conn.Close();

}

}

/// <summary>

/// 还原数据库

/// </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("还原数据库成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

catch (Exception ex)

{

MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

finally

{

Conn.Close();

}

}

}

}

/*

///调用事例:

#----------------------------------------------------还原数据库---------------------------------------------------------#

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 Files\Microsoft SQL Server\MSSQL\Data\";

DBC.ReplaceDataBase();

}

#--------------------------------------------------附加数据库---------------------------------------------------------#

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 Files\Microsoft SQL Server\MSSQL\Data\MyDatabase_Data.MDF";

DBC.DataBase_LDF = @"D:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDatabase_Log.LDF";

DBC.AddDataBase();

}

#--------------------------------------备份数据库--------------------------------------------------------------------#

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 Files\Microsoft SQL Server\MSSQL\Data\";

DBC.BackupDataBase();

}

#----------------------------------------分离数据库-----------------------------------------------------------------#

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();

}

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航