(MVC) MVC (2018)

DataBase dumper on Classic ASP.NET with serialize DB to .CSV and using ICSharpCode.SharpZipLib

In this page I describe my old Database dumper on Classic ASP.NET, I include it to my new project programmer.expert.



This dumper contains 5 files. First three files is a Classic ASP NET form. This is frontend of my DbDumper:


1. Frontend Backup.aspx


   1:  <%@ Page Title="" Language="vb" AutoEventWireup="false" MasterPageFile="~/Admin/M1.Master" CodeBehind="~/Admin/Backup.aspx.vb" Inherits="Programmer_expert_www_1.Backup1" %>
   2:  <%@ Register Src="~/Admin/BackupTableItem.ascx" TagPrefix="uc1" TagName="BackupTableItem" %>
   3:   
   4:  <asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="server">
   5:  </asp:Content>
   6:  <asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
   7:      
   8:      <asp:ImageButton PostBackUrl="~/admin/Backup.aspx" ToolTip="Delete old backup" ID="DelImageButton" runat="server" ImageUrl="~/images/del.png" OnClick="DelImageButton_Click" />
   9:      <asp:LinkButton Enabled="True" ID="DelLinkButton" runat="server" OnClick="DelLinkButton_Click">Delete old backup</asp:LinkButton>
  10:      <br />
  11:      <br />
  12:      <asp:ImageButton ID="ImageButtonBackupConfig" OnClick="ImageButtonBackupConfig_Click" ToolTip="Create Web.Config backup" runat="server" ImageUrl="~/images/plus.png" CommandName="Web.Config" />
  13:      &nbsp;<asp:ImageButton ID="ImageButtonDownloadConfig" ToolTip="Download Web.Config" PostBackUrl="~/GetZip.ashx?File=Web.Config" runat="server" ImageUrl="~/images/Down-gray.gif" Enabled="false" />&nbsp;
  14:      <asp:LinkButton ID="LinkButtonDownloadConfig" Enabled="false" ForeColor="Gray" ToolTip="Download Web.Config" CommandName="Web.Config" PostBackUrl="~/GetZip.ashx?File=Web.Config" runat="server">Web.Config</asp:LinkButton>
  15:      <br />
  16:      <asp:ImageButton ID="ImageButtonBackupUpload" OnClick="ImageButtonBackupUpload_Click" ToolTip="Create UploadedFiles backup" runat="server" ImageUrl="~/images/plus.png" CommandName="UploadedFiles" />
  17:      &nbsp;<asp:ImageButton ID="ImageButtonDownloadUpload" ToolTip="Download UploadedFiles" PostBackUrl="~/GetZip.ashx?File=UploadedFiles" runat="server" ImageUrl="~/images/Down-gray.gif" Enabled="false" />&nbsp;
  18:      <asp:LinkButton ID="LinkButtonDownloadUpload" Enabled="false" ForeColor="Gray" ToolTip="Download UploadedFiles" CommandName="UploadedFiles" PostBackUrl="~/GetZip.ashx?File=UploadedFiles" runat="server">Uploaded Files</asp:LinkButton>
  19:      &nbsp;(*)<br />
  20:      <br />
  21:      <table border="0" cellpadding="1">
  22:          <asp:DataList runat="server" ID="DataList1">
  23:              <ItemTemplate>
  24:                  <uc1:BackupTableItem runat="server" id="BackupTableItem1" />
  25:              </ItemTemplate>
  26:          </asp:DataList>
  27:      </table>
  28:      <span style="color:gray">(*) DB-Table.Zip saved to folder '~/App_Data/Backup', User Upload their data to '~/App_Data/UserID', this point create full backup of '~/App_Data/'</span>
  29:      <br /><asp:Label ID="Lerr1" runat="server" ForeColor="Red" Text=""></asp:Label>
  30:  </asp:Content>


   1:  '------------------------------------------------------------------------------
   2:  ' <auto-generated>
   3:  '     This code was generated by a tool.
   4:  '
   5:  '     Changes to this file may cause incorrect behavior and will be lost if
   6:  '     the code is regenerated. 
   7:  ' </auto-generated>
   8:  '------------------------------------------------------------------------------
   9:   
  10:  Option Strict On
  11:  Option Explicit On
  12:   
  13:   
  14:  Partial Public Class Backup1
  15:      
  16:      '''<summary>
  17:      '''DelImageButton control.
  18:      '''</summary>
  19:      '''<remarks>
  20:      '''Auto-generated field.
  21:      '''To modify move field declaration from designer file to code-behind file.
  22:      '''</remarks>
  23:      Protected WithEvents DelImageButton As Global.System.Web.UI.WebControls.ImageButton
  24:      
  25:      '''<summary>
  26:      '''DelLinkButton control.
  27:      '''</summary>
  28:      '''<remarks>
  29:      '''Auto-generated field.
  30:      '''To modify move field declaration from designer file to code-behind file.
  31:      '''</remarks>
  32:      Protected WithEvents DelLinkButton As Global.System.Web.UI.WebControls.LinkButton
  33:      
  34:      '''<summary>
  35:      '''ImageButtonBackupConfig control.
  36:      '''</summary>
  37:      '''<remarks>
  38:      '''Auto-generated field.
  39:      '''To modify move field declaration from designer file to code-behind file.
  40:      '''</remarks>
  41:      Protected WithEvents ImageButtonBackupConfig As Global.System.Web.UI.WebControls.ImageButton
  42:      
  43:      '''<summary>
  44:      '''ImageButtonDownloadConfig control.
  45:      '''</summary>
  46:      '''<remarks>
  47:      '''Auto-generated field.
  48:      '''To modify move field declaration from designer file to code-behind file.
  49:      '''</remarks>
  50:      Protected WithEvents ImageButtonDownloadConfig As Global.System.Web.UI.WebControls.ImageButton
  51:      
  52:      '''<summary>
  53:      '''LinkButtonDownloadConfig control.
  54:      '''</summary>
  55:      '''<remarks>
  56:      '''Auto-generated field.
  57:      '''To modify move field declaration from designer file to code-behind file.
  58:      '''</remarks>
  59:      Protected WithEvents LinkButtonDownloadConfig As Global.System.Web.UI.WebControls.LinkButton
  60:      
  61:      '''<summary>
  62:      '''ImageButtonBackupUpload control.
  63:      '''</summary>
  64:      '''<remarks>
  65:      '''Auto-generated field.
  66:      '''To modify move field declaration from designer file to code-behind file.
  67:      '''</remarks>
  68:      Protected WithEvents ImageButtonBackupUpload As Global.System.Web.UI.WebControls.ImageButton
  69:      
  70:      '''<summary>
  71:      '''ImageButtonDownloadUpload control.
  72:      '''</summary>
  73:      '''<remarks>
  74:      '''Auto-generated field.
  75:      '''To modify move field declaration from designer file to code-behind file.
  76:      '''</remarks>
  77:      Protected WithEvents ImageButtonDownloadUpload As Global.System.Web.UI.WebControls.ImageButton
  78:      
  79:      '''<summary>
  80:      '''LinkButtonDownloadUpload control.
  81:      '''</summary>
  82:      '''<remarks>
  83:      '''Auto-generated field.
  84:      '''To modify move field declaration from designer file to code-behind file.
  85:      '''</remarks>
  86:      Protected WithEvents LinkButtonDownloadUpload As Global.System.Web.UI.WebControls.LinkButton
  87:      
  88:      '''<summary>
  89:      '''DataList1 control.
  90:      '''</summary>
  91:      '''<remarks>
  92:      '''Auto-generated field.
  93:      '''To modify move field declaration from designer file to code-behind file.
  94:      '''</remarks>
  95:      Protected WithEvents DataList1 As Global.System.Web.UI.WebControls.DataList
  96:      
  97:      '''<summary>
  98:      '''Lerr1 control.
  99:      '''</summary>
 100:      '''<remarks>
 101:      '''Auto-generated field.
 102:      '''To modify move field declaration from designer file to code-behind file.
 103:      '''</remarks>
 104:      Protected WithEvents Lerr1 As Global.System.Web.UI.WebControls.Label
 105:  End Class


   1:  Imports System.Data.SqlClient
   2:  Imports System.IO
   3:  Imports System.Xml
   4:   
   5:  Public Class Backup1
   6:      Inherits System.Web.UI.Page
   7:   
   8:      Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
   9:          If Not IsPostBack Then
  10:              Dim DB1 As New ProgrammerExpertDBContext
  11:              Dim Tables = DB1.Database.SqlQuery(Of String)("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where @p=@p order by TABLE_Name", New SqlParameter("@p", "1")).ToList()
  12:              DataList1.DataSource = Tables
  13:              DataList1.DataBind()
  14:          End If
  15:      End Sub
  16:   
  17:      Private Sub DataList1_ItemDataBound(sender As Object, e As DataListItemEventArgs) Handles DataList1.ItemDataBound
  18:          If e.Item.DataItem IsNot Nothing Then
  19:              Dim CurBackupTableItem As BackupTableItem = TryCast(e.Item.Controls(1), BackupTableItem)
  20:              Dim ImageButton1 As ImageButton = CurBackupTableItem.FindControl("ImageButton1")
  21:              Dim ImageButton2 As ImageButton = CurBackupTableItem.FindControl("ImageButton2")
  22:              Dim LinkButton1 As LinkButton = CurBackupTableItem.FindControl("LinkButton1")
  23:              ImageButton1.CommandName = e.Item.DataItem
  24:              ImageButton2.PostBackUrl = "~/GetZip.ashx?File=" & e.Item.DataItem
  25:              LinkButton1.CommandName = e.Item.DataItem
  26:              LinkButton1.PostBackUrl = "~/GetZip.ashx?File=" & e.Item.DataItem
  27:              LinkButton1.Text = e.Item.DataItem
  28:          End If
  29:      End Sub
  30:   
  31:      Protected Sub DelImageButton_Click(sender As Object, e As ImageClickEventArgs) Handles DelImageButton.Click
  32:          ClearFolder()
  33:      End Sub
  34:   
  35:      Protected Sub DelLinkButton_Click(sender As Object, e As EventArgs) Handles DelLinkButton.Click
  36:          ClearFolder()
  37:      End Sub
  38:   
  39:      Private Sub ClearFolder()
  40:          Dim RootDir As String = Server.MapPath("~/App_Data/Backup")
  41:          Dim di As System.IO.DirectoryInfo = New DirectoryInfo(RootDir)
  42:   
  43:          For Each file As FileInfo In di.GetFiles()
  44:              file.Delete()
  45:          Next
  46:   
  47:          For Each dir As DirectoryInfo In di.GetDirectories()
  48:              dir.Delete(True)
  49:          Next
  50:      End Sub
  51:   
  52:      Protected Sub ImageButtonBackupConfig_Click(sender As Object, e As ImageClickEventArgs) Handles ImageButtonBackupConfig.Click
  53:          Try
  54:              Dim OutDataDir As String = Server.MapPath("~/App_Data/Backup/")
  55:              Dim OutFileName As String = OutDataDir & "web.config.zip"
  56:              Dim InFileName As String = Server.MapPath("~/web.config")
  57:              Zip.AddFileToZip(OutFileName, InFileName)
  58:              ImageButtonDownloadConfig.Enabled = True
  59:              ImageButtonDownloadConfig.ImageUrl = "~/images/Down-green.png"
  60:              LinkButtonDownloadConfig.ForeColor = System.Drawing.Color.Black
  61:              LinkButtonDownloadConfig.Enabled = True
  62:          Catch ex As Exception
  63:              Lerr1.Text = ex.Message
  64:          End Try
  65:   
  66:      End Sub
  67:   
  68:      Protected Sub ImageButtonBackupUpload_Click(sender As Object, e As ImageClickEventArgs) Handles ImageButtonBackupUpload.Click
  69:          Try
  70:              Dim OutDataDir As String = Server.MapPath("~/App_Data/Backup/")
  71:              Dim UploadDir As String = Server.MapPath("~/App_Data/") 'recursion
  72:              Dim OutFileName As String = System.IO.Path.Combine(OutDataDir, "UP_" & ImageButtonBackupUpload.CommandName & ".zip")
  73:              Dim CN = New SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer").ConnectionString)
  74:              CN.Open()
  75:              Zip.ZipFolder(OutFileName, UploadDir)
  76:              ImageButtonBackupUpload.Enabled = True
  77:              ImageButtonBackupUpload.ImageUrl = "~/images/Down-green.png"
  78:              LinkButtonDownloadUpload.ForeColor = System.Drawing.Color.Black
  79:              LinkButtonDownloadUpload.Enabled = True
  80:          Catch ex As Exception
  81:              Lerr1.Text = ex.Message
  82:          End Try
  83:   
  84:      End Sub
  85:   
  86:  End Class

2. UserControl BackupTableItem

   1:  <%@ Control Language="vb" AutoEventWireup="false" CodeBehind="~/Admin/BackupTableItem.ascx.vb" Inherits="Programmer_expert_www_1.BackupTableItem" %>
   2:   
   3:  <td>
   4:      <asp:ImageButton PostBackUrl="~/admin/Backup.aspx" OnClick="ImageButton_Click" ID="ImageButton1" ToolTip="Create backup" runat="server" ImageUrl="~/images/plus.png" CommandName="aspnet_Applications" Width="16px" />
   5:  </td>
   6:  <td>
   7:      <asp:ImageButton ID="ImageButton2" ToolTip="Download backup" PostBackUrl="~/GetZip.ashx?File=" runat="server" ImageUrl="~/images/Down-gray.gif" Enabled="false" />
   8:      <asp:LinkButton ID="LinkButton1" Enabled="false" ForeColor="Gray" ToolTip="Download backup" CommandName="aspnet_Applications" PostBackUrl="~/GetZip.ashx?File=" runat="server">aspnet_Applications</asp:LinkButton>
   9:      <asp:Label ID="Lerr1" runat="server" ForeColor="Red" Text=""></asp:Label>
  10:  </td>


   1:  '------------------------------------------------------------------------------
   2:  ' <auto-generated>
   3:  '     This code was generated by a tool.
   4:  '
   5:  '     Changes to this file may cause incorrect behavior and will be lost if
   6:  '     the code is regenerated. 
   7:  ' </auto-generated>
   8:  '------------------------------------------------------------------------------
   9:   
  10:  Option Strict On
  11:  Option Explicit On
  12:   
  13:   
  14:  Partial Public Class BackupTableItem
  15:   
  16:      '''<summary>
  17:      '''ImageButton1 control.
  18:      '''</summary>
  19:      '''<remarks>
  20:      '''Auto-generated field.
  21:      '''To modify move field declaration from designer file to code-behind file.
  22:      '''</remarks>
  23:      Protected WithEvents ImageButton1 As Global.System.Web.UI.WebControls.ImageButton
  24:   
  25:      '''<summary>
  26:      '''ImageButton2 control.
  27:      '''</summary>
  28:      '''<remarks>
  29:      '''Auto-generated field.
  30:      '''To modify move field declaration from designer file to code-behind file.
  31:      '''</remarks>
  32:      Protected WithEvents ImageButton2 As Global.System.Web.UI.WebControls.ImageButton
  33:   
  34:      '''<summary>
  35:      '''LinkButton1 control.
  36:      '''</summary>
  37:      '''<remarks>
  38:      '''Auto-generated field.
  39:      '''To modify move field declaration from designer file to code-behind file.
  40:      '''</remarks>
  41:      Protected WithEvents LinkButton1 As Global.System.Web.UI.WebControls.LinkButton
  42:   
  43:      '''<summary>
  44:      '''Lerr1 control.
  45:      '''</summary>
  46:      '''<remarks>
  47:      '''Auto-generated field.
  48:      '''To modify move field declaration from designer file to code-behind file.
  49:      '''</remarks>
  50:      Protected WithEvents Lerr1 As Global.System.Web.UI.WebControls.Label
  51:  End Class


   1:  Imports System.Data.SqlClient
   2:   
   3:  Public Class BackupTableItem
   4:      Inherits System.Web.UI.UserControl
   5:   
   6:      Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
   7:   
   8:      End Sub
   9:   
  10:      Protected Sub ImageButton_Click(sender As Object, e As ImageClickEventArgs) Handles ImageButton1.Click
  11:          Try
  12:              Dim Button = CType(sender, System.Web.UI.WebControls.ImageButton)
  13:              Dim CMD As String = Button.CommandName
  14:              Dim DataDir As String = Server.MapPath("~/App_Data/Backup/")
  15:              Dim DBFileName As String = System.IO.Path.Combine(DataDir, "DB_" & CMD & ".csv")
  16:              Dim CN = New SqlConnection(ConfigurationManager.ConnectionStrings("ProgrammerExpertContainer").ConnectionString)
  17:              CN.Open()
  18:              DBDumper.DumpTableToFile(CN, CMD, DBFileName)
  19:              Zip.AddFileToZip(DBFileName.Replace(".csv", ".zip"), DBFileName)
  20:              ImageButton2.Enabled = True
  21:              ImageButton2.ImageUrl = "~/images/Down-green.png"
  22:              ImageButton2.PostBackUrl = "~/GetZip.ashx?File=" & CMD
  23:              LinkButton1.Enabled = True
  24:              LinkButton1.ForeColor = System.Drawing.Color.Black
  25:              LinkButton1.PostBackUrl = "~/GetZip.ashx?File=" & CMD
  26:          Catch ex As Exception
  27:              Lerr1.Text = ex.Message
  28:          End Try
  29:      End Sub
  30:  End Class

3. Handler BackupTableItem

   1:  <%@ WebHandler Language="VB" CodeBehind="GetZip.ashx.vb" Class="Programmer_expert_www_1.GetZip" %>


   1:  Imports System.Web
   2:  Imports System.Web.Services
   3:   
   4:  Public Class GetZip
   5:      Implements System.Web.IHttpHandler
   6:   
   7:      Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
   8:   
   9:          Dim UserRootDir As String = context.Server.MapPath("~/App_Data/Backup/")
  10:          Dim FileName As String = System.IO.Path.Combine(UserRootDir, "DB_" & context.Request.QueryString("File") & ".zip")
  11:   
  12:          If context.Request.QueryString("File").ToString() = "UploadedFiles" Then
  13:              FileName = FileName.Replace("DB_", "UP_")
  14:          ElseIf context.Request.QueryString("File").ToLower() = "web.config" Then
  15:              FileName = FileName.Replace("DB_", "")
  16:          End If
  17:   
  18:          If System.IO.File.Exists(FileName) Then
  19:              Dim file As String = "attachment; filename=" & context.Request.QueryString("File") & ".zip"
  20:              context.Response.ContentType = "application/zip"
  21:              context.Response.AppendHeader("content-disposition", file)
  22:              context.Response.WriteFile(FileName)
  23:          Else
  24:              context.Response.ContentType = "plain/txt"
  25:              context.Response.Write("No file")
  26:          End If
  27:      End Sub
  28:   
  29:      ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
  30:          Get
  31:              Return False
  32:          End Get
  33:      End Property
  34:  End Class

4. Serialize Table to CSV

I use this my old module Serialize Table to CSV

5. Zipper files adn folser

   1:  Imports ICSharpCode.SharpZipLib.Core
   2:  Imports ICSharpCode.SharpZipLib.Zip
   3:  Imports System.IO
   4:   
   5:  Public Class Zip
   6:      Public Shared Sub ZipFolder(ByVal OutPathname As String, ByVal FolderName As String)
   7:          If File.Exists(OutPathname) Then
   8:              File.Delete(OutPathname)
   9:          End If
  10:          Dim FsOut As FileStream = File.Create(OutPathname)
  11:          Dim ZipStream As ZipOutputStream = New ZipOutputStream(FsOut)
  12:          ZipStream.SetLevel(3)
  13:          Dim FolderOffset As Integer = FolderName.Length + (If(FolderName.EndsWith("\"), 0, 1))
  14:          CompressFolder(FolderName, ZipStream, FolderOffset)
  15:          ZipStream.IsStreamOwner = True
  16:          ZipStream.Close()
  17:      End Sub
  18:   
  19:      Private Shared Sub CompressFolder(ByVal Path As String, ByVal ZipStream As ZipOutputStream, ByVal FolderOffset As Integer)
  20:          Dim Files As String() = Directory.GetFiles(Path)
  21:   
  22:          For Each Filename As String In Files
  23:              Dim FI As FileInfo = New FileInfo(Filename)
  24:              Dim EntryName As String = Filename.Substring(FolderOffset)
  25:              EntryName = ZipEntry.CleanName(EntryName)
  26:              Dim NewEntry As ZipEntry = New ZipEntry(EntryName)
  27:              NewEntry.DateTime = FI.LastWriteTime
  28:              NewEntry.Size = FI.Length
  29:              ZipStream.PutNextEntry(NewEntry)
  30:              Dim Buffer As Byte() = New Byte(4095) {}
  31:   
  32:              Using StreamReader As FileStream = File.OpenRead(Filename)
  33:                  StreamUtils.Copy(StreamReader, ZipStream, Buffer)
  34:              End Using
  35:              ZipStream.CloseEntry()
  36:          Next
  37:   
  38:          Dim Folders As String() = Directory.GetDirectories(Path)
  39:          For Each folder As String In Folders
  40:              CompressFolder(folder, ZipStream, FolderOffset) 'recursion
  41:          Next
  42:      End Sub
  43:   
  44:      Public Shared Sub AddFileToZip(ByVal zipFile As String, ByVal fileToAdd As String)
  45:          If File.Exists(zipFile) Then
  46:              File.Delete(zipFile)
  47:          End If
  48:          Using ZipStream As ZipOutputStream = New ZipOutputStream(File.Create(zipFile))
  49:              Dim Buffer As Byte() = New Byte(4095) {}
  50:              Dim Entry As ZipEntry = New ZipEntry(System.IO.Path.GetFileName(fileToAdd))
  51:              Entry.DateTime = DateTime.Now
  52:              ZipStream.PutNextEntry(entry)
  53:              Using FS As FileStream = File.OpenRead(fileToAdd)
  54:                  Dim SourceBytes As Integer = 0
  55:                  Do
  56:                      SourceBytes = FS.Read(Buffer, 0, Buffer.Length)
  57:                      ZipStream.Write(Buffer, 0, SourceBytes)
  58:                  Loop While SourceBytes > 0
  59:              End Using
  60:              ZipStream.Finish()
  61:              ZipStream.Close()
  62:              ZipStream.Dispose()
  63:          End Using
  64:      End Sub
  65:  End Class


Comments ( )
<00>  <01>  <02>  <03>  <04>  <05>  <06>  <07>  <08>  <09>  <10>  <11>  <12>  <13>  <14>  <15>  <16>  <17>  <18>  <19> 
Link to this page: http://www.vb-net.com/DbDumper/index.htm
<SITEMAP>  <MVC>  <ASP>  <NET>  <DATA>  <KIOSK>  <FLEX>  <SQL>  <NOTES>  <LINUX>  <MONO>  <FREEWARE>  <DOCS>  <ENG>  <MAIL ME>  <ABOUT ME>  < THANKS ME>