CODEWITHSUNDEEP
c#asp.netsql-serverxmllinq
Equilibrium
Equilibrium

Reputation: 163

Export multiple tables from SQL Server to XML for restore

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

Upvotes: 6

Views: 4393

Answers (2)

sandipmatsagar
sandipmatsagar

Reputation: 35

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

Upvotes: -2

Syed Mohd Mohsin Akhtar
Syed Mohd Mohsin Akhtar

Reputation: 451

One of the example to the 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.xml which can be later used to restore the backup

Upvotes: 3

Related Questions