Wednesday, May 11, 2011

Table Backup and Restore in asp.net

Demo.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DataModifiation.aspx.cs"
    Inherits="Real_Estate_DataModifiation" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="lblMessage" runat="Server" EnableViewState="False" ForeColor="Red"></asp:Label>
        <table style="border: 1px solid #cccccc; border-collapse: collapse">
            <tr>
                <td style="vertical-align: top; border-right: 1px solid #cccccc; padding-left: 15px;
                    padding-top: 10px">
                    <b>Database Tables</b>
                </td>
                <td style="vertical-align: top; border-right: 1px solid #cccccc; padding-left: 15px;
                    padding-top: 10px">
                    <b>Download Backups</b>
                </td>
                <td style="vertical-align: top; padding-left: 15px; padding-top: 10px">
                    <b>Upload Backup</b>
                </td>
            </tr>
            <tr>
                <td style="vertical-align: top; border-right: 1px solid #cccccc; padding: 15px;">
                    <table>
                        <tr>
                            <td>
                                <asp:ListBox ID="ListBox1" runat="Server" DataTextField="table_name" DataValueField="table_name"
                                    Style="width: 200px;" Rows="10"></asp:ListBox>
                            </td>
                        </tr>
                        <tr>
                            <td>
                                <span onclick="return confirm('Are you sure to backup selected table?')">
                                    <asp:Button ID="btnBackup" runat="Server" Text="Backup Table" OnClick="BackUpNow"
                                        CssClass="smallbutton2" /></span> <span onclick="return confirm('Are you sure to restore selected table?')">
                                            <asp:Button ID="btnRestore" runat="Server" Text="Restore Table" OnClick="RestoreNow"
                                                CssClass="smallbutton2" /></span>
                            </td>
                        </tr>
                    </table>
                </td>
                <td style="vertical-align: top; border-right: 1px solid #cccccc; padding: 15px;">
                    <asp:Literal ID="backupList" runat="server"></asp:Literal>
                </td>
                <td style="vertical-align: top; padding: 15px;">
                    <asp:Literal ID="uploadMessage" runat="server"></asp:Literal>
                    Â
                    <br />
                    <asp:FileUpload ID="FileUpload1" runat="server" /><br />
                    <asp:Button ID="btnUpload" runat="server" OnClick="uploadBackup" Text="Upload Backup File" />
                </td>
            </tr>
        </table>
    </div>
    </form>
</body>
</html>
Demo.aspx.cs
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.IO;

public partial class Real_Estate_DataModifiation : System.Web.UI.Page
{
    string connectionstring = "Data Source=EASY-857AC062F0\\SQLEXPRESS;Initial Catalog=CAS;Integrated Security=True";
    string backupfolder = "Real_Estate/mybackups";

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            PopulateDatabaseTables();
        }
        listBackups();
    }

    private void PopulateDatabaseTables()
    {
        string tableName = string.Empty;
        string sql = "SELECT *, name AS table_name " + " FROM sys.tables WHERE Type = 'U' ORDER BY table_name";
        using (SqlConnection conn = new SqlConnection(connectionstring))
        {
            using (DataTable table = new DataTable())
            {
                using (SqlDataAdapter dAd = new SqlDataAdapter(sql, conn)) { dAd.Fill(table); }
                ListBox1.DataSource = table; ListBox1.DataBind();
            }
        }
    }

    protected void BackUpNow(object sender, EventArgs e)
    {
        string tableName = ListBox1.SelectedValue;
        using (DataSet dSetBackup = new DataSet())
        {
            using (SqlConnection conn = new SqlConnection(connectionstring))
            {
                using (SqlDataAdapter dAd = new SqlDataAdapter("select * from " + tableName, conn))
                {
                    dAd.Fill(dSetBackup, tableName);
                }
            }
            if (!Directory.Exists(Server.MapPath(backupfolder))) { Directory.CreateDirectory(Server.MapPath(backupfolder)); }
            dSetBackup.WriteXml(Server.MapPath(backupfolder + "/" + tableName + ".xml"), XmlWriteMode.WriteSchema);
            lblMessage.Text = "Backup for table <b>" + tableName + "</b> successful!<br />";
        }
        listBackups();
    }

    protected void RestoreNow(object sender, EventArgs e)
    {
        string tableName = ListBox1.SelectedValue;
        if (File.Exists(Server.MapPath(backupfolder + "/" + tableName + ".xml")))
        {
            string xmlFile = Server.MapPath(backupfolder + "/" + tableName + ".xml");

            DataSet ds = new DataSet();

            ds.ReadXml(xmlFile);
            DataTable dt = new DataTable();
            dt = ds.Tables[0];

            SqlConnection conn = new SqlConnection(connectionstring);
            conn.Open();
            SqlCommand comm = new SqlCommand("truncate table " + tableName, conn);
            comm.ExecuteNonQuery();
            conn.Close();

            SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionstring, SqlBulkCopyOptions.KeepIdentity);
            bulkCopy.DestinationTableName = tableName;
            bulkCopy.WriteToServer(dt);

            lblMessage.Text += "Restore of table <b>" + tableName + "</b> successful!<br />";
        }
        else
        {
            lblMessage.Text += "Table <b>" + tableName + "</b> has not been backed up yet, so cannot be restored.<br />";

        }
    }

    protected void listBackups()
    {
        string fPath = Server.MapPath(backupfolder);
        if (Directory.Exists(fPath))
        {
            string filelinks = "";
            DirectoryInfo dir = new DirectoryInfo(fPath);
            FileInfo[] files = dir.GetFiles("*.xml");
            foreach (FileInfo file in files)
            {
                filelinks += "<tr><td style=\"border-top:1px solid #cccccc;padding:5px;\"><a href=\"" + backupfolder + "/" + file.Name + "\">" + file.Name + "</a></td><td style=\"padding:5px;border-left:1px solid #cccccc;border-top:1px solid #cccccc;\">" + file.LastWriteTime + "</td></tr>";
            }
            if (filelinks == "")
            {
                backupList.Text = "No backups have been made yet.";
            }
            else
            {
                backupList.Text = "Click to download a backupfile below <br>(right click and select 'save target as')<br /><br /><table style=\"border:1px solid #cccccc\"><tr><td style=\"padding:5px;\"><b>Backup File</b></td><td style=\"padding:5px;border-left:1px solid #cccccc;border-top:1px solid #cccccc;\"><b>Date Crated</b></td></tr>" + filelinks + "</table>";
            }
        }
        else
        {
            backupList.Text = "No backups have been made yet.";
        }
    }

    protected void uploadBackup(object sender, EventArgs e)
    {
        HttpPostedFile myFile = FileUpload1.PostedFile;

        if (myFile != null)
        {
            string backupPath = Server.MapPath(backupfolder) + "\\" + Path.GetFileName(myFile.FileName);
            myFile.SaveAs(backupPath);
            lblMessage.Text = "Backup file " + Path.GetFileName(myFile.FileName) + " uploaded successfully";
            listBackups();
        }
        else
        {
            lblMessage.Text = "No File was uploaded";
        }
    }
}

Posted by at

No comments :

Post a Comment

Subscribe to: Post Comments ( Atom )