using
System;
using System.Collections.Generic; using System.Windows.Forms; using System.Data.SqlClient; using System.Data; using System.ServiceProcess; namespace AdminZJC.DataBaseControl { /// <summary> /// 數(shù)據(jù)庫操作控制類 /// </summary> public class DataBaseControl { /// <summary> /// 數(shù)據(jù)庫連接字符串 /// </summary> public string ConnectionString; /// <summary> /// SQL操作語句/存儲(chǔ)過程 /// </summary> public string StrSQL; /// <summary> /// 實(shí)例化一個(gè)數(shù)據(jù)庫連接對(duì)象 /// </summary> private SqlConnection Conn; /// <summary> /// 實(shí)例化一個(gè)新的數(shù)據(jù)庫操作對(duì)象Comm /// </summary> private SqlCommand Comm; /// <summary> /// 要操作的數(shù)據(jù)庫名稱 /// </summary> public string DataBaseName; /// <summary> /// 數(shù)據(jù)庫文件完整地址 /// </summary> public string DataBase_MDF; /// <summary> /// 數(shù)據(jù)庫日志文件完整地址 /// </summary> public string DataBase_LDF; /// <summary> /// 備份文件名 /// </summary> public string DataBaseOfBackupName; /// <summary> /// 備份文件路徑 /// </summary> public string DataBaseOfBackupPath; /// <summary> /// 執(zhí)行創(chuàng)建/修改數(shù)據(jù)庫和表的操作 /// </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( "數(shù)據(jù)庫操作成功!" , "信息提示" , MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show(ex.Message, "信息提示" , MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { Conn.Close(); } } /// <summary> /// 附加數(shù)據(jù)庫 /// </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( "附加數(shù)據(jù)庫成功" , "信息提示" , MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show(ex.Message, "信息提示" , MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { Conn.Close(); } } /// <summary> /// 分離數(shù)據(jù)庫 /// </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( "分離數(shù)據(jù)庫成功" , "信息提示" , MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show(ex.Message, "信息提示" , MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { Conn.Close(); } } /// <summary> /// 備份數(shù)據(jù)庫 /// </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( "備份數(shù)據(jù)庫成功" , "信息提示" , MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show(ex.Message, "信息提示" , MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { Conn.Close(); } } /// <summary> /// 還原數(shù)據(jù)庫 /// </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( "還原數(shù)據(jù)庫成功" , "信息提示" , MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show(ex.Message, "信息提示" , MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { Conn.Close(); } } } } /* ///調(diào)用事例: 還原數(shù)據(jù)庫 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(); } 附加數(shù)據(jù)庫 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(); } 備份數(shù)據(jù)庫 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(); } 分離數(shù)據(jù)庫 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(); }
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號(hào)聯(lián)系: 360901061
您的支持是博主寫作最大的動(dòng)力,如果您喜歡我的文章,感覺我的文章對(duì)您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點(diǎn)擊下面給點(diǎn)支持吧,站長非常感激您!手機(jī)微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點(diǎn)擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
