我自己写的一个操作数据库的类

来源：互联网发布：淘宝水星家纺是正品吗编辑：程序博客网时间：2024/04/30 22:00

我自己写的一个操作数据库的类 using System;using System.Collections.Generic;using System.Windows.Forms;using System.Data.SqlClient;using System.Data;using System.ServiceProcess; namespace DataBaseOperation{ /// <summary> /// 数据库操作控制类 /// </summary> public class DataBaseControl { #region 数据库操作的各种字段 /// <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; #endregion #region 数据库操作的各种方法 /// <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(); } } #endregion #region 调用的方法 /* ///调用事例：　　　还原数据库 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(); } */ #endregion }}