将多个表从SQL Server导出到XML以进行恢复

Question

我在C# / ASP.NET中有一个web应用程序，我想用相同的唯一键(Company ID)导出所有数据库表的数据。所有的表都有这个键(每个公司都不一样)。为了将来的备份，我想这样做。是否可以使用Linq或经典的C#？如何实现这种备份和恢复？

Answer 1

该解决方案的一个示例是

using System.Data.SqlClient;
using System.Data;
using System.IO;

namespace ConsoleApplication4
{
class Program
{
    static void Main(string[] args)
    {
        var connStr = "Data Source=MOHSINWIN8PRO\\SQLEXPRESS;Initial Catalog=AB2EDEMO;Integrated Security=True";
        var xmlFileData = "";
        DataSet ds = new DataSet();
        var tables = new[] {"Hospital", "Patient"};
        foreach (var table in tables)
        {

            var query = "SELECT * FROM "+ table +" WHERE (Hospital_Code = 'Hosp1')";
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand(query, conn);
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            conn.Close();
            conn.Dispose();
            xmlFileData+=ds.GetXml();
        }
        File.WriteAllText("D://SelectiveDatabaseBackup.xml",xmlFileData);
    }
}

}

这将创建SelectiveDatabaseBackup.xml，稍后可将其用于恢复备份

Answer 2

               SaveFileDialog SD = new SaveFileDialog();
                if (SD.ShowDialog() != System.Windows.Forms.DialogResult.Cancel)
                {
                    System.IO.StreamWriter SW = new System.IO.StreamWriter(SD.FileName);
                    foreach (string str in checkedListBox1.CheckedItems)
                    {
                        string XMLSTR = //Heads.funtions.ConvertDatatableToXML(
                                        Heads.funtions.fire_qry_for_string(string.Format("Select * from {0} FOR XML AUTO,TYPE, ELEMENTS ,ROOT('{0}')", str)); // , str);
                        SW.Write(XMLSTR);
                        SW.WriteLine("###TABLEEND###");
                    }
                    SW.Close();
                    //_CommonClasses._Cls_Alerts.ShowAlert("Clean Up Completed...!!!", "CleanUP", MessageBoxIcon.Information);
                }