DbUtility

DbUtility

public static void BackupSQLServerDB( string connectionString, string backupPath)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
// string sql = "use master;backup database " + conn.Database + " to disk='" + backupPath + "'";
string sql = " use master;backup database " + conn.Database + " to disk=' " + backupPath + " ' with init; " ;
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandTimeout = 0 ;
conn.Open();
cmd.ExecuteNonQuery();
}
}
}
public static void RestoreSQLServerDB( string connectionString, string backupPath)
{
using (SqlConnection spConn = new SqlConnection(connectionString))
{
SqlCommand spCmd = new SqlCommand( " use master;SELECT spid FROM sysprocesses,sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.name=' " + spConn.Database + " ' " , spConn);
spConn.Open();
// KILL DataBase Process
List < short > spList = new List < short > ();
SqlDataReader dr = spCmd.ExecuteReader();
while (dr.Read())
{
spList.Add(dr.GetInt16( 0 ));
}
dr.Close();
for ( int i = 0 ; i < spList.Count; i ++ )
{
spCmd = new SqlCommand( string .Format( " KILL {0} " , spList[i]), spConn);
spCmd.ExecuteNonQuery();
}
using (SqlConnection conn = new SqlConnection(connectionString))
{
// string sql = "use master;restore database " + conn.Database + " From disk='" + backupPath + "'";
string sql = " use master;restore database " + conn.Database + " From disk = ' " + backupPath + " ' with replace; " ;
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandTimeout = 0 ;
conn.Open();
cmd.ExecuteNonQuery();
}
}
}
}

    /// <summary>
    /// make sure there's no open connections to your db before calling this method!
    /// </summary>
    /// <param name="tempDBPath">临时文件路径</param>
    /// <param name="connectionString">connection string to your db</param>
    /// <param name="mdwfilename">FULL name of an MDB file you want to compress.</param>
    public static void CompactOfficeAccessDB(string tempDBPath, string connectionString, string mdwfilename)
    {
        //create an inctance of a Jet Replication Object
        object objJRO = Activator.CreateInstance(Type.GetTypeFromProgID("JRO.JetEngine"));

        //filling Parameters array
        //cnahge "Jet OLEDB:Engine Type=5" to an appropriate value or leave it as is if you db is JET4X format (access 2000,2002)
        //(yes, jetengine5 is for JET4X, no misprint here)
        object[] oParams = new object[] { connectionString, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + tempDBPath + ";Jet OLEDB:Engine Type=5" };

        //invoke a CompactDatabase method of a JRO object
        //pass Parameters array
        objJRO.GetType().InvokeMember("CompactDatabase", System.Reflection.BindingFlags.InvokeMethod, null, objJRO, oParams);

        //database is compacted now to a new file tempDBPath
        //let's copy it over an old one and delete it
        System.IO.File.Delete(mdwfilename);
        System.IO.File.Move(tempDBPath, mdwfilename);

        //clean up (just in case)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(objJRO);
        objJRO = null;
    }

posted on 2011-05-31 19:41 RockyLOMO 阅读(...) 评论(...) 编辑 收藏