254 lines
12 KiB
C#
254 lines
12 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Web;
|
|
using System.Timers;
|
|
using System.IO;
|
|
using System.Data.SqlClient;
|
|
using static BackupService.BackupService;
|
|
using System.Text;
|
|
using System.Data;
|
|
using System.IO.Compression;
|
|
|
|
namespace BackupService
|
|
{
|
|
public class BackupWorker
|
|
{
|
|
private string dataPath = null;
|
|
private Timer timer = null;
|
|
private string fromTable = null;
|
|
|
|
public BackupWorker()
|
|
{
|
|
timer = new Timer();
|
|
timer.AutoReset = false;
|
|
timer.Interval = 1000;
|
|
timer.Enabled = false;
|
|
timer.Elapsed += new ElapsedEventHandler(DoWork);
|
|
}
|
|
|
|
public string DataPath
|
|
{
|
|
get { return dataPath; }
|
|
set { dataPath = value; }
|
|
}
|
|
|
|
public void Begin(string fromTable = null)
|
|
{
|
|
this.fromTable = fromTable;
|
|
timer.Start();
|
|
}
|
|
|
|
public void BackupOneTable(string tableName)
|
|
{
|
|
var nowtime = DateTime.UtcNow.AddHours(8);
|
|
|
|
try
|
|
{
|
|
using (var conn = new SqlConnection("Data Source=sql5060.site4now.net;Initial Catalog=DB_A48A46_thinkyu;Persist Security Info=True;User ID=DB_A48A46_thinkyu_admin;Password=VUPm4ej03xu3"))
|
|
{
|
|
conn.Open();
|
|
var cmd = new SqlCommand();
|
|
var da = new SqlDataAdapter();
|
|
cmd.Connection = conn;
|
|
cmd.CommandText = @"SELECT INFORMATION_SCHEMA.TABLES.TABLE_NAME, Count(sys.identity_columns.Column_ID) as HAS_IDENTITY
|
|
FROM INFORMATION_SCHEMA.TABLES
|
|
LEFT JOIN sys.identity_columns on OBJECT_NAME(object_id) = INFORMATION_SCHEMA.TABLES.TABLE_NAME
|
|
WHERE TABLE_TYPE <> 'VIEW' and INFORMATION_SCHEMA.TABLES.TABLE_NAME = @TABLE_NAME
|
|
GROUP BY INFORMATION_SCHEMA.TABLES.TABLE_NAME";
|
|
cmd.Parameters.AddWithValue("TABLE_NAME", tableName);
|
|
|
|
var tableList = new List<TableItem>();
|
|
|
|
using (var reader = cmd.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
tableList.Add(new TableItem() { TableName = Convert.ToString(reader["TABLE_NAME"]), HasIdentity = Convert.ToInt32(reader["HAS_IDENTITY"]) == 1 });
|
|
}
|
|
}
|
|
|
|
foreach (var table in tableList)
|
|
{
|
|
var output = new StringBuilder();
|
|
var columnnames = new List<string>();
|
|
cmd.CommandText = "select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@TABLE_NAME order by ORDINAL_POSITION ";
|
|
cmd.Parameters.Clear();
|
|
cmd.Parameters.AddWithValue("TABLE_NAME", table.TableName);
|
|
da.SelectCommand = cmd;
|
|
var columns = new DataTable();
|
|
da.Fill(columns);
|
|
foreach (DataRow row in columns.Rows)
|
|
{
|
|
columnnames.Add(string.Format("[{0}]", row["COLUMN_NAME"]));
|
|
}
|
|
var columnlist = string.Join(", ", columnnames.ToArray());
|
|
|
|
output.AppendFormat("-- BACKUP DATE {0:yyyy/MM/dd HH:mm}\r\n", nowtime);
|
|
output.AppendFormat("-- TABLE [{0}]\r\n", table.TableName);
|
|
|
|
if (table.HasIdentity)
|
|
output.AppendFormat("set identity_insert [{0}] on;\r\n", table.TableName);
|
|
|
|
cmd.CommandText = string.Format("select * from [{0}]", table.TableName);
|
|
cmd.CreateParameter();
|
|
using (var reader = cmd.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
var sql = string.Format("insert into [{0}] ({1}) values (", table.TableName, columnlist);
|
|
var values = new List<string>();
|
|
foreach (DataRow col in columns.Rows)
|
|
{
|
|
var colname = Convert.ToString(col["COLUMN_NAME"]);
|
|
var datatype = Convert.ToString(col["DATA_TYPE"]).ToLower();
|
|
var value = reader[colname];
|
|
|
|
if (value == DBNull.Value)
|
|
values.Add("null");
|
|
else if (datatype == "char" || datatype == "varchar")
|
|
values.Add(string.Format("'{0}'", value));
|
|
else if (datatype == "nvarchar" || datatype == "nchar")
|
|
values.Add(string.Format("N'{0}'", value));
|
|
else if (datatype == "nvarchar" || datatype == "datetime")
|
|
values.Add(string.Format("'{0:yyyy/MM/dd HH:mm:ss.fff}'", value));
|
|
else if (datatype == "bit")
|
|
values.Add(string.Format("{0}", value));
|
|
else
|
|
values.Add(string.Format("{0}", value));
|
|
}
|
|
sql += string.Join(", ", values.ToArray()) + ");";
|
|
output.AppendLine(sql);
|
|
}
|
|
}
|
|
|
|
if (table.HasIdentity)
|
|
output.AppendFormat("set identity_insert [{0}] off;\r\n", table.TableName);
|
|
|
|
var buffer = Encoding.UTF8.GetBytes(output.ToString());
|
|
|
|
using (FileStream stream = new FileStream(string.Format("{0}/{1}.gz", dataPath, table.TableName), FileMode.Create, FileAccess.Write))
|
|
{
|
|
using (GZipStream gZip = new GZipStream(stream, CompressionMode.Compress))
|
|
{
|
|
gZip.Write(buffer, 0, buffer.Length);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ErrorLogger.WriteException(ex);
|
|
}
|
|
}
|
|
|
|
private void DoWork(object sender, ElapsedEventArgs e)
|
|
{
|
|
var nowtime = DateTime.UtcNow.AddHours(8);
|
|
|
|
try
|
|
{
|
|
using (var conn = new SqlConnection("Data Source=sql5060.site4now.net;Initial Catalog=DB_A48A46_thinkyu;Persist Security Info=True;User ID=DB_A48A46_thinkyu_admin;Password=VUPm4ej03xu3"))
|
|
{
|
|
conn.Open();
|
|
var cmd = new SqlCommand();
|
|
var da = new SqlDataAdapter();
|
|
cmd.Connection = conn;
|
|
cmd.CommandText = @"SELECT INFORMATION_SCHEMA.TABLES.TABLE_NAME, Count(sys.identity_columns.Column_ID) as HAS_IDENTITY
|
|
FROM INFORMATION_SCHEMA.TABLES
|
|
LEFT JOIN sys.identity_columns on OBJECT_NAME(object_id) = INFORMATION_SCHEMA.TABLES.TABLE_NAME
|
|
WHERE TABLE_TYPE <> 'VIEW'
|
|
GROUP BY INFORMATION_SCHEMA.TABLES.TABLE_NAME";
|
|
|
|
var tableList = new List<TableItem>();
|
|
|
|
using (var reader = cmd.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
string tableName = Convert.ToString(reader["TABLE_NAME"]);
|
|
bool hasIdentity = Convert.ToInt32(reader["HAS_IDENTITY"]) == 1;
|
|
|
|
if (string.IsNullOrEmpty(this.fromTable) || tableName.CompareTo(this.fromTable) >= 0) {
|
|
tableList.Add(new TableItem() { TableName = tableName, HasIdentity = hasIdentity });
|
|
}
|
|
}
|
|
}
|
|
|
|
foreach (var table in tableList)
|
|
{
|
|
var output = new StringBuilder();
|
|
var columnnames = new List<string>();
|
|
cmd.CommandText = "select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@TABLE_NAME order by ORDINAL_POSITION ";
|
|
cmd.Parameters.Clear();
|
|
cmd.Parameters.AddWithValue("TABLE_NAME", table.TableName);
|
|
da.SelectCommand = cmd;
|
|
var columns = new DataTable();
|
|
da.Fill(columns);
|
|
foreach (DataRow row in columns.Rows)
|
|
{
|
|
columnnames.Add(string.Format("[{0}]", row["COLUMN_NAME"]));
|
|
}
|
|
var columnlist = string.Join(", ", columnnames.ToArray());
|
|
|
|
output.AppendFormat("-- BACKUP DATE {0:yyyy/MM/dd HH:mm}\r\n", nowtime);
|
|
output.AppendFormat("-- TABLE [{0}]\r\n", table.TableName);
|
|
|
|
if (table.HasIdentity)
|
|
output.AppendFormat("set identity_insert [{0}] on;\r\n", table.TableName);
|
|
|
|
cmd.CommandText = string.Format("select * from [{0}]", table.TableName);
|
|
cmd.CreateParameter();
|
|
using (var reader = cmd.ExecuteReader())
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
var sql = string.Format("insert into [{0}] ({1}) values (", table.TableName, columnlist);
|
|
var values = new List<string>();
|
|
foreach (DataRow col in columns.Rows)
|
|
{
|
|
var colname = Convert.ToString(col["COLUMN_NAME"]);
|
|
var datatype = Convert.ToString(col["DATA_TYPE"]).ToLower();
|
|
var value = reader[colname];
|
|
|
|
if (value == DBNull.Value)
|
|
values.Add("null");
|
|
else if (datatype == "char" || datatype == "varchar")
|
|
values.Add(string.Format("'{0}'", value));
|
|
else if (datatype == "nvarchar" || datatype == "nchar")
|
|
values.Add(string.Format("N'{0}'", value));
|
|
else if (datatype == "nvarchar" || datatype == "datetime")
|
|
values.Add(string.Format("'{0:yyyy/MM/dd HH:mm:ss.fff}'", value));
|
|
else if (datatype == "bit")
|
|
values.Add(string.Format("{0}", value));
|
|
else
|
|
values.Add(string.Format("{0}", value));
|
|
}
|
|
sql += string.Join(", ", values.ToArray()) + ");";
|
|
output.AppendLine(sql);
|
|
}
|
|
}
|
|
|
|
if (table.HasIdentity)
|
|
output.AppendFormat("set identity_insert [{0}] off;\r\n", table.TableName);
|
|
|
|
var buffer = Encoding.UTF8.GetBytes(output.ToString());
|
|
|
|
using (FileStream stream = new FileStream(string.Format("{0}/{1}.gz", dataPath, table.TableName), FileMode.Create, FileAccess.Write))
|
|
{
|
|
using (GZipStream gZip = new GZipStream(stream, CompressionMode.Compress))
|
|
{
|
|
gZip.Write(buffer, 0, buffer.Length);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ErrorLogger.WriteException(ex);
|
|
}
|
|
}
|
|
}
|
|
} |