Geek asks and answers

Export multiple tables from SQL Server to XML for recovery

I have a C # / ASP.NET web application. I want to export all database table data with the same unique key ( Company ID). All tables have this key (different for each company). I want to do this for a future backup. Is this possible with Linq or classic C #? How can I back up and restore?

+5
source share
2 answers

One example of a solution is

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);
    }
}

}

this will create SelectiveDatabaseBackup.xmlwhich can later be used to restore the backup

+2
source 
               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);
                }
-2
source

More articles:


All Articles