How to take Backup of Database or Table to SQL file and Restore it using C# in ASP.Net

Last Reply on Jun 09, 2017 07:59 AM By dharmendr

Posted on Jun 05, 2017 08:46 AM

hi

refer below thread;

How to take backup of Table data from Database to text file using C# and VB.Net in ASP.Net

now I want run this txt (file that I uplaod from database)file into database how I can do it?

best regards

neda

Posted on Jun 06, 2017 07:19 AM Modified on on Jun 08, 2017 08:20 AM

Hi nedash,

Refer the below code. First you need to read the txt file from FileUpload control using StreamReader and then execute the code to insert the record to table.

C#

protected void Restore(object sender, EventArgs e)
{
    StreamReader sr = new StreamReader(FileUpload1.PostedFile.InputStream);
    string data = sr.ReadToEnd();
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[1].ConnectionString);
    SqlCommand cmd = new SqlCommand(data, con);
    cmd.CommandType = CommandType.Text;
    cmd.Connection = con;
    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();
}

VB.Net

Protected Sub Restore(sender As Object, e As EventArgs)
	Dim sr As New StreamReader(FileUpload1.PostedFile.InputStream)
	Dim data As String = sr.ReadToEnd()
	Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings(1).ConnectionString)
	Dim cmd As New SqlCommand(data, con)
	cmd.CommandType = CommandType.Text
	cmd.Connection = con
	con.Open()
	cmd.ExecuteNonQuery()
	con.Close()
End Sub

Posted on Jun 09, 2017 07:59 AM Modified on on Jun 13, 2017 04:39 AM

Hi nedash,

That is the problem you are going to face as the query is generated by considering the column as string. But the actual datatype is int so cant convert datatype varchar to numeric.

So i have another solution for generating script of table with data using SQL Server Management Objects (SMO).

For this you need too add dll from the below path in your project.

32-bit: C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies

64-bit: C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies

For SQL Server 2016

32-bit: C:\Program Files\Microsoft SQL Server\130\SDK\Assemblies

64-bit: C:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies

You need to add references to:

  • Microsoft.SqlServer.ConnectionInfo.dll

  • Microsoft.SqlServer.Smo.dll

  • Microsoft.SqlServer.Management.Sdk.Sfc.dll

  • Microsoft.SqlServer.SqlEnum.dll

 Namespaces

using System;
using System.Configuration;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using Microsoft.SqlServer.Management.Smo;

C#

protected void Page_Load(object sender, EventArgs e)
{
    CreateScriptTable("master", "Customers", ConfigurationManager.ConnectionStrings[1].ConnectionString);
    CreateScriptDataBase("LocationDB", ConfigurationManager.ConnectionStrings[1].ConnectionString);
}

/// <summary>
/// Create single Table script with Data of specified DataBase and Table.
/// </summary>
/// <param name="dataBaseName">DataBase Name</param>
/// <param name="tableName">Table Name</param>
/// <param name="connectionString">Connection String</param>
public void CreateScriptTable(string dataBaseName, string tableName, string connectionString)
{
    SqlConnection con = new SqlConnection(connectionString);
    ServerConnection serverConnection = new ServerConnection(con);
    Server server = new Server(serverConnection);
    Database database = server.Databases["" + dataBaseName + ""];
    if (database != null)
    {
        Scripter scripter = new Scripter(server);
        scripter.Options.ScriptData = true;
        scripter.Options.ScriptSchema = true;
        scripter.Options.ScriptDrops = false;
        var sb = new System.Text.StringBuilder();
        foreach (Microsoft.SqlServer.Management.Smo.Table table in database.Tables)
        {
            if (table.Name.ToLower() == tableName.ToLower())
            {
                sb.Append("DROP TABLE " + table.Name);
                sb.Append(Environment.NewLine);
                foreach (string s in scripter.EnumScript(new Urn[] { table.Urn }))
                {
                    sb.Append(s);
                    sb.Append(Environment.NewLine);
                }
                string folder = Server.MapPath("~/Scripts/");
                string filename = folder + tableName + ".sql";
                System.IO.StreamWriter fs = System.IO.File.CreateText(filename);
                fs.Write(sb);
                fs.Close();
            }
        }
    }
}

/// <summary>
/// Create all Table script with Data of specified DataBase.
/// </summary>
/// <param name="dataBaseName">DataBase Name</param>
/// <param name="connectionString">Connection String</param>
public void CreateScriptDataBase(string dataBaseName, string connectionString)
{
    SqlConnection con = new SqlConnection(connectionString);
    ServerConnection serverConnection = new ServerConnection(con);
    Server server = new Server(serverConnection);
    Database database = server.Databases["" + dataBaseName + ""];
    if (database != null)
    {
        Scripter scripter = new Scripter(server);
        scripter.Options.ScriptData = true;
        scripter.Options.ScriptSchema = true;
        scripter.Options.ScriptDrops = false;
        var sb = new System.Text.StringBuilder();
        foreach (Microsoft.SqlServer.Management.Smo.Table table in database.Tables)
        {
            sb.Append("DROP TABLE " + table.Name);
            sb.Append(Environment.NewLine);
            foreach (string s in scripter.EnumScript(new Urn[] { table.Urn }))
            {
                sb.Append(s);
                sb.Append(Environment.NewLine);
            }
            string folder = Server.MapPath("~/Scripts/");
            string filename = folder + dataBaseName + ".sql";
            System.IO.StreamWriter fs = System.IO.File.CreateText(filename);
            fs.Write(sb);
            fs.Close();
        }
    }
}

Now use the below code to generate the script in sql file and for restore use the previously provided code by reading the sql file.