备份数据 其实就是将数据从一个数据库清除并且获取插入到另一个数据库中,我在这里取名为Archive 。并且写入到文件中 存住恢复的Insert 语句。
关键代码:
/// <summary>
/// 备份数据
/// </summary>
/// <param name="tablename">表名</param>
/// <param name="sw">文件流</param>
/// <param name="selectsql"></param>
/// <param name="_selectsql">从本地数据查询需要备份数据的SQL</param>
/// <param name="updateordeletesql"></param>
/// <returns></returns>
public static bool ArchiveTables(string tablename, StreamWriter sw, string selectsql, string _selectsql, string updateordeletesql)
{
try
{
DataTable schemaDT = new DataTable();
//获取该表的列
schemaDT =dataManager.GetTable(string.Format("SELECT * FROM sys.columns WHERE [object_id]=object_id('{0}')", tablename));
DataTable dt = new DataTable();
dt = dataManager.GetTable(selectsql);
//DataRow[] dataRow = new DataRow[schemaDT.Rows.Count];
foreach (DataRow dr in schemaDT.Rows)
{
if ((bool)dr["is_identity"])//是否为自动递增的列
{
dt.Columns[dr["name"].ToString()].AutoIncrement = true;
}
}
#region 事务
if ((dt != null) && (dt.Rows.Count > 0))
{
if (!PurgeForArchive)
{
#region 不仅Purge还要Archive 需要创建数据表
string sqlInsert = "INSERT INTO " + tablename + SetNum + " " + _selectsql;
SqlConnection s1 = new SqlConnection(ArchiveConnectionString);//连接Archive数据库执行创建表
SqlConnection s2 = new SqlConnection(BPMSServerDaemon.bpmsConfiguration.ConnectionString);
SqlCommand sc1 = new SqlCommand(sqlInsert, s1);//备份
SqlCommand sc2 = new SqlCommand(updateordeletesql, s2);//清除
s1.Open();
s2.Open();
SqlTransaction sqlTran1 = s1.BeginTransaction();
SqlTransaction sqlTran2 = s2.BeginTransaction();
try
{
sc1.Transaction = sqlTran1;
sc2.Transaction = sqlTran2;
sc1.ExecuteNonQuery();
sc2.ExecuteNonQuery();
sqlTran1.Commit();
sqlTran2.Commit();
}
catch (SqlException ex)
{
sqlTran1.Rollback();
sqlTran2.Rollback();
s1.Close();
s2.Close();
logManager.writeLogAddTimeStamp("Achive Failed, " + ex.Message, 1);
}
finally
{
sqlTran1.Dispose();
sqlTran2.Dispose();
sc1.Transaction = null;
sc2.Transaction = null;
}
#endregion
}
else
{
#region 只做Purge 清除
SqlConnection s2 = new SqlConnection(ConnectionString);
SqlCommand sc2 = new SqlCommand(updateordeletesql, s2);
s2.Open();
SqlTransaction sqlTran2 = s2.BeginTransaction();
try
{
sc2.Transaction = sqlTran2;
sc2.ExecuteNonQuery();
sqlTran2.Commit();
}
catch (SqlException ex)
{
sqlTran2.Rollback();
s2.Close();
logManager.writeLogAddTimeStamp("Database Purge Failed, " + ex.Message, 1);
}
finally
{
sqlTran2.Dispose();
sc2.Transaction = null;
}
#endregion
}
#region Write Detail to file 创建可以恢复数据的SQL文档
#region organize Col Header
String strInsert = "";
bool[] bStringCol = new bool[dt.Columns.Count]; //表中每列是否为字符串类型的数组
strInsert += "INSERT INTO " + tablename + " (";
for (int i = 0; i < dt.Columns.Count; i++)
{
if (dt.Columns[i].AutoIncrement) continue;//自增列不写
strInsert += dt.Columns[i].ColumnName + ",";//添加的列名
Type coltype = dt.Columns[i].DataType;
if ((coltype == typeof(System.Int16)) || (coltype == typeof(System.Int32)) || (coltype == typeof(System.Int64)) ||
(coltype == typeof(System.Double)) || (coltype == typeof(System.Boolean)) || (coltype == typeof(System.Byte))
)
{
bStringCol[i] = false;
}
else
{
bStringCol[i] = true;
}
}
strInsert = strInsert.Substring(0, strInsert.Length - 1); //去除最后一个逗号
strInsert += ") VALUES (";
#endregion
#region write reocrds
int rowcount = dt.Rows.Count;//多少条
for (int k = 0; k < rowcount; k++)
{
DataRow dr = dt.Rows[k];
string sbTmp = "";
for (int i = 0; i < bStringCol.Length; i++)
{
if (dt.Columns[i].AutoIncrement) continue;//自增列不写
if (dr[i] is DBNull)
{
sbTmp += "NULL,";
}
else
{
if (bStringCol[i])
{
sbTmp += "'" + APTServer.Common.ConvertSingleQuotes(dr[i].ToString()) + "',";
}
else
{
sbTmp += dr[i].ToString() + ",";
}
}
}//获取一行的值写入sql
sbTmp = sbTmp.Remove(sbTmp.Length - 1, 1);
sbTmp += ");";
sw.WriteLine(strInsert + sbTmp.ToString());//拼接出一条
if ((k % APTServer.Common.OnceCount) == 0)
{
Thread.Sleep(1);
}
}
#endregion
#endregion
}
#endregion
return true;
}
catch (Exception ex)
{
logManager.writeLogAddTimeStamp(ex.Message, 1);
return false;
}
}
其他代码:
我这人不喜欢太多废话 直接上代码 慢慢看,有些已经注释写清楚了,我已经尽力简化代码了。有基础的应该可以看懂。
using System.Collections;
using System.Threading;
using System.Data.SqlClient;
using System.Data;
using System.IO;
获取当前要备份的数据量
#region 统计APA备份数据量
BPMSServerDaemon.logManager.writeLogAddTimeStamp("Getting APA Achive List", 2);
string sql_apa = "";
string[] Tables = new string[] { "DATimeCard", "OAT" };//想要备份的表名
foreach (string tb in Tables)
{
switch (tb)
{
case "ProductionDeviceSummary":
sql_apa = "SELECT StartTime RecordDate FROM " + tb + " WHERE(StartTime <= DATEADD(dd, " + (-BPMSServerDaemon.bpmsConfiguration.archiveSettins.ReserveDays) + ", GETDATE())) group by StartTime;";
break;
default:
sql_apa = "SELECT RecordDate RecordDate FROM " + tb + " WHERE(RecordDate <= DATEADD(dd, " + (-BPMSServerDaemon.bpmsConfiguration.archiveSettins.ReserveDays) + ", GETDATE())) group by RecordDate;";
break;
}
try
{
DataTable dt = dataManager.GetTable(sql_apa);//从数据库中查询出要备份的数据
if ((dt != null) && (dt.Rows.Count > 0))
{
foreach (DataRow dr in dt.Rows)
{
ArchiveRestore.ArchiveTable archiveTable = new ArchiveRestore.ArchiveTable();
archiveTable.RecordDate = (DateTime)dr["RecordDate"];
archiveTable.tableName = tb;
APAlist.Add(archiveTable);
}
}
}
catch (SqlException)
{
}
}
logManager.writeLog("Getting APA Achive List ok", 2);
ArchiveRestore.ArchivingAPAlist.Clear();
lock (ArchiveRestore.ArchivingAPAlist.SyncRoot)
{
for (int i = 0; i < APAlist.Count; i++)
{
ArchiveRestore.ArchivingAPAlist.Add(APAlist[i]);
}
}
logManager.writeLog("Archiving APA list count: " + ArchiveRestore.ArchivingAPAlist.Count, 2);
#endregion
写方法 开启线程
public static ArrayList ArchivingAPAlist = new ArrayList();
public static string[] Tables = new string[] { "FactoryEquipmentReport","FactoryLaborReport"};
private Thread ArchiveThread;
public bool running = true;
public static ArrayList deletelist = new ArrayList();
public static string ArchiveConnectionString = "";
public static int SetNum = 10000; //表的套数初始值为10000
public static string VersionID;
public static object locker = new object();//创建锁
public ArchiveRestore()
{
ArchiveThread = new Thread(new ThreadStart(ArchivingAPA));
ArchiveThread.Start();
}
public void ArchivingAPA()
{
while (running)
{
try
{
Thread.Sleep(100); //大资源占用线程,通过时间片给其他进程机会
if (!running)
{
break;
}
ArrayList APAList = new ArrayList();//APAList
lock (ArchivingAPAlist.SyncRoot)
{
if (ArchivingAPAlist.Count == 0)
{
Thread.Sleep(1000);
continue;
}
for (int i = 0; i < ArchivingAPAlist.Count; i++)
{
APAList.Add(ArchivingAPAlist[i]);
}
}
if (APAList.Count == 0) continue;
//若只是清除 则无需创建备份数据库表
bool result = true;
if (!archiveSettins.PurgeForArchive)
{
result = Initialize();
}
else
{
result = true;
}
DateTime filetime = DateTime.Now;
if (result == false)
{
logManager.writeLogAddTimeStamp(String.Format("Create archive database table failed! Please check if the archive database is configured correctly!"), 1);
lock (ArchivingOrderList.SyncRoot)
{
ArchivingOrderList.Clear();
}
break;
}
else
{
try
{
#region 一套表生成一个文件,用以备份
StreamWriter sw = null;
//若盘符不存在 则使用默认路径
if (!Directory.Exists(BPMSServerDaemon.bpmsConfiguration.archiveSettins.ArchiveFolder.Substring(0, 2)))
{
string MSG = "About ArchiveFolder:Disk path " + archiveSettins.ArchiveFolder.Substring(0, 2) + " does not exist.Use the default path:" + APTServer.Common.GetDriverLetterFromPath(Application.StartupPath) + @":\Server\Archives\ArchivedDBs";
archiveSettins.ArchiveFolder = APTServer.Common.GetDriverLetterFromPath(Application.StartupPath) + @":\Server\Archives\ArchivedDBs";
logManager.writeLogAddTimeStamp(MSG, 1);
}
string filepath = archiveSettins.ArchiveFolder + "\\" + DateTime.Now.Year + ((DateTime.Now.Month.ToString()).Length == 1 ? "0" + DateTime.Now.Month.ToString() : DateTime.Now.Month.ToString());
string filename = filepath + "\\" + .archiveSettins.ArchiveDataBase + "_APA" + SetNum + ".bpmsarc"
if (archiveSettins.PurgeForArchive)
{
filename = filepath + "\\" + DataBase + "_OnlyPurge_APA.bpmsarc";
}
if (!Directory.Exists(filepath))
{
Directory.CreateDirectory(filepath);
}
if (!File.Exists(filename))
{
FileStream fs = new FileStream(filename, FileMode.Create);
sw = new StreamWriter(fs);
sw.WriteLine("<?xml version=\"1.0\"?>");
}
else
{
sw = new StreamWriter(filename, true);
}
#endregion
sw.WriteLine("<Archive_APA");
sw.Write(" StartArchiveDate=\"" + DateTime.Now.ToLongDateString());
sw.WriteLine(">");
int sleepCount = 0;
#region APA
var tableName = "";
foreach (ArchiveTable ati in APAList)
{
if (ati == null) continue;
if (DateTime.Now >= filetime.AddMinutes(30))
{
lock (ArchivingAPAlist.SyncRoot)
{
ArchivingAPAlist.Clear();
}
logManager.writeLogAddTimeStamp("Archiving APA is more than 30 minutes, now quit.The number of Archived: " + sleepCount, 2);
break;
}
sleepCount++;
if (sleepCount % 100 == 0) Thread.Sleep(10);
lock (ArchivingAPAlist.SyncRoot)
{
ArchivingAPAlist.Remove(ati);
if (bpmsConfiguration.DebugFlag)
{
logManager.writeLogAddTimeStamp("Count for ArchivingAPAlist is : " + ArchivingAPAlist.Count.ToString(), 2);
}
}
if (tableName != "") {
if (tableName != ati.tableName) {
sw.WriteLine("]]>");
sw.WriteLine("</table_" + tableName + ">");
tableName = ati.tableName;
sw.WriteLine("<table_" + tableName + ">");
sw.WriteLine("<![CDATA[");
}
}
else{
tableName = ati.tableName;
sw.WriteLine("<table_" + ati.tableName + ">");
sw.WriteLine("<![CDATA[");
}
if (ArchiveRestore.ApaNewArchiveOneTable(ati.RecordDate, ati.tableName, sw, filetime, true))
{
//logManager.writeLogAddTimeStamp("The index: " + ati.index + " from " + ati.tableName + " has been archived successfully!", 2);
}
else
{
logManager.writeLogAddTimeStamp("ArchivingAPA ->The index: " + ati.index + " from " + ati.tableName + " has something wrong", 1);
}
Thread.Sleep(100);
}
#endregion
if (tableName != "") {
sw.WriteLine("]]>");
sw.WriteLine("</table_" + tableName + ">");
}
sw.WriteLine("</Archive_APA>");
sw.Flush();
sw.Close();
logManager.writeLogAddTimeStamp(" The number of Archived: " + sleepCount, 2);
}
catch (ThreadAbortException)
{
break;
}
catch (Exception ex)
{
logManager.writeLogAddTimeStamp("ArchiveThread error! " + ex.Message, 1);
}
}
}
catch (Exception ex)
{
logManager.writeLogAddTimeStamp("ArchiveThread error! " + ex.Message, 1);
}
}
}
创建Archive的初始数据表
public bool Initialize()
{
bool result = false;
try
{
lock (locker) {
if (!BPMSServerDaemon.bpmsConfiguration.archiveSettins.PurgeForArchive)
{
//判断时候是否可以连接Archive数据库
string sql = "select max(SetNum) from SetNumHistory";
DataTable dt = GetTable(sql);
if (dt == null)
{
logManager.writeLogAddTimeStamp(String.Format("Create archive database table failed! Please check if the archive database is configured correctly!"), 1);
return false;
}
if (dt != null && dt.Rows[0][0] != null && dt.Rows[0][0].ToString() != "")
{
SetNum = Convert.ToInt32(dt.Rows[0][0].ToString());
/*判断SetNum的各个表中是否含有已经达到极限值
* 若达到,则SetNum+1
*/
bool flag = false;
foreach (string tb in Tables)
{
DataTable Count_dt = GetTable("select count(*) from " + tb + SetNum + "");
int OrderPieceCount = Convert.ToInt32(Count_dt.Rows[0][0].ToString());
if (OrderPieceCount > BPMSServerDaemon.bpmsConfiguration.archiveSettins.MaxTBPieceCount * 1000000)
{
//如果有 代表需要重新创建一套表
flag = true;
}
}
if (flag)
{
SetNum++;
/*判断SetNumSum是否达到了Web配置的最大套数值。
* 达到就需要清理最小Setnum的表*/
DataTable _dt = GetTable("select count(*) from SetNumHistory where IsRemoved != 1");
int SetNumSum = Convert.ToInt32(_dt.Rows[0][0].ToString());
if (SetNumSum >= BPMSServerDaemon.bpmsConfiguration.archiveSettins.MaxSetNum)
{
DropMinSetNumTable();
}
#region 创建一套数据表
CreateSetNumTB(SetNum);
#endregion
}
}
else
{
#region 创建一套数据表
CreateSetNumTB(SetNum);
#endregion
}
#endregion
}
#region 创建 ActiveHistory表
string tb_sql = @" if object_id('ArchiveHistory') is null
create table ArchiveHistory
(
ArchiveIndex int IDENTITY(1,1) PRIMARY KEY NOT NULL ,
OrderIndex int,
OrderID varchar(50) ,
SetNum int,
VersionID varchar(50),
ArchiveTime datetime
) ";
dataManager.ExecSQL(tb_sql);
#endregion
result = true;
}
}
catch (Exception ex)
{
logManager.writeLogAddTimeStamp(ex.Message, 1);
}
return result;
}
/// <summary>
/// 创建一套Archive Table 备份表格
/// </summary>
/// <param name="SetNum"></param>
public void CreateSetNumTB(int SetNum)
{
try
{
string createTB = "";
foreach (string tb in Tables)
{
createTB += "if object_id('" + tb + SetNum + "') is null select * into " + tb + SetNum + " from [bpmstestlink].[" + BPMSServerDaemon.bpmsConfiguration.DataBase + "].[dbo].[" + tb + "] where 1=2;\r\n";
#region 查询创建主键
DataTable dt1 = BPMSServerDaemon.dataManager.GetTable("EXEC sp_pkeys " + tb + "");
if (dt1 != null && dt1.Rows.Count > 0)
{
foreach (DataRow dr in dt1.Rows)
{
string PK_Name = dr["PK_Name"].ToString() + SetNum;
string COLUMN_NAME = dr["COLUMN_NAME"].ToString();
createTB += @"if not exists (select * from sys.indexes where name='" + PK_Name + "') alter table " + tb + SetNum + " Add constraint [" + PK_Name + "] primary key ([" + COLUMN_NAME + "]);\r\n";
}
}
#endregion
#region 查询创建索引
dt1 = BPMSServerDaemon.dataManager.GetTable("EXEC Sp_helpindex " + tb + "");
if (dt1 != null && dt1.Rows.Count > 0)
{
foreach (DataRow dr in dt1.Rows)
{
string TBIndex_Name = dr["index_name"].ToString() + SetNum;
string TBIndex_Description = dr["index_description"].ToString();
string TBIndex_Keys = dr["index_keys"].ToString();
string[] ar = TBIndex_Keys.Split(',');
TBIndex_Keys = "";
foreach (string a in ar)
{
if (a.ToLower() == "index")
{
TBIndex_Keys += "[" + a + "],";
}
else if (a == "OrderIndex(-)")
{
TBIndex_Keys += "OrderIndex,";
} else if (a== "ReportIndex(-)") {
TBIndex_Keys += "ReportIndex,";
}
else
{
TBIndex_Keys += a + ",";
}
}
TBIndex_Keys = TBIndex_Keys.Substring(0, TBIndex_Keys.Length - 1);
createTB += @"if not exists (select * from sys.indexes where name='" + TBIndex_Name + "') create nonclustered index " + TBIndex_Name + @"
on " + tb + SetNum + "(" + TBIndex_Keys + ");\r\n";
}
}
#endregion
}
#region 添加SetNum历史记录
DataTable dt2 = BPMSServerDaemon.dataManager.GetTable("select VersionID from Version");
if (dt2 != null && dt2.Rows.Count > 0)
{
VersionID = dt2.Rows[0][0].ToString();
createTB += string.Format(" insert into SetNumHistory values(' " + SetNum + " ','" + VersionID + " ',getdate(),0)");
ArchiveExecSQL(ArchiveConnectionString, createTB);
}
#endregion
logManager.writeLogAddTimeStamp("Archive table created successfully...", 2);
}
catch (Exception ex)
{
logManager.writeLogAddTimeStamp("Create Archive Table failed!" + ex.Message, 1);
}
}
/// <summary>
/// 备份表数据 APA
/// </summary>
/// <param name="Archiveindex"></param>
/// <param name="tablename"></param>
/// <param name="sw"></param>
/// <returns></returns>
public static bool ApaNewArchiveOneTable(object RecordDate, string tablename, StreamWriter sw)
{
if ((RecordDate == null) || (tablename == null)) return false;
try
{
string selectsql = "";
string _selectsql = "";
string updateordeletesql = "";
switch (tablename)
{
case "tablename":
selectsql = string.Format("SELECT * FROM {1} WHERE StartTime='{0}'", RecordDate, tablename);
_selectsql = string.Format("SELECT * FROM [bpmstestlink].[" + DataBase + "].[dbo].[{1}] WHERE StartTime='{0}'", RecordDate, tablename);
updateordeletesql = string.Format("DELETE FROM {1} WHERE StartTime='{0}'", RecordDate, tablename);
break;
default:
selectsql = string.Format("SELECT * FROM {1} WHERE RecordDate='{0}'", RecordDate, tablename);
_selectsql = string.Format("SELECT * FROM [bpmstestlink].[" + DataBase + "].[dbo].[{1}] WHERE RecordDate='{0}'", RecordDate, tablename);
updateordeletesql = string.Format("DELETE FROM {1} WHERE RecordDate='{0}'", RecordDate, tablename);
break;
}
return ArchiveTables(tablename, sw, selectsql, _selectsql, updateordeletesql);
}
catch (Exception ex)
{
logManager.writeLogAddTimeStamp(ex.Message, 1);
return false;
}
}
/// <summary>
/// 删除多出套数的表
/// </summary>
public void DropMinSetNumTable()
{
try
{
DataTable minSetdt = GetTable("select min(SetNum) from SetNumHistory where IsRemoved != 1");
int minSetNum = Convert.ToInt32(minSetdt.Rows[0][0].ToString());
string dropSql = "";
foreach (string tb in Tables)
{
dropSql += "Drop table " + tb + minSetNum + ";";
}
dropSql += "Update SetNumHistory set IsRemoved = 1 where SetNum = " + minSetNum + ";";
ArchiveExecSQL(ArchiveConnectionString, dropSql);
}
catch (Exception ex)
{
logManager.writeLogAddTimeStamp("Drop Archive minSetNum table failed!" + ex.Message, 1);
}
}