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

For this you need to generate the script with insert and update query of table to text file and save it and whenever needed read the text file and run those query from code behind to insert to the table.

Note:

In Web application downloads are sent to browser and then browser raises File Download Box.

Hence it is not possible to control where the file will be downloaded as the user has full control on whether he wants to download or not or save in some location.

C#

string primaryKey, Insertqry, updateAdd;
string cnString = ConfigurationManager.ConnectionStrings[1].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
    Run(cnString, "Customers");
    DownloadScriptFile();
}

private void DownloadScriptFile()
{
    // Read the file for download.
    System.IO.FileStream fs = System.IO.File.Open(Server.MapPath("~/Script.txt"), System.IO.FileMode.Open);
    byte[] btFile = new byte[fs.Length];
    fs.Read(btFile, 0, Convert.ToInt32(fs.Length));
    fs.Close();
    // After reading the file delete it.
    File.Delete(Server.MapPath("~/Script.txt"));
    Response.AddHeader("Content-disposition", "attachment; filename=Script.txt");
    Response.ContentType = "application/octet-stream";
    Response.BinaryWrite(btFile);
    Response.End();
}

public void Run(string cnString, string TableName)
{
    string IDValues = "", insqry, upqry;
    int i = 0;
    SqlDataReader rdr;
    SqlConnection con = new SqlConnection();
    SqlCommand cmd = new SqlCommand();
    string cnnString = cnString;
    con = new SqlConnection(cnnString);
    cmd = new SqlCommand("SELECT * FROM " + TableName, con);
    primaryKey = GetprimaryKey(TableName, cnString);
    insqry = "";
    con.Open();

    rdr = cmd.ExecuteReader();
    if (rdr != null)
    {
        while (rdr.Read())
        {
            // i this variable counts the total number of record
            i = i + 1;// once a query is written, next it is available should be empty to build query again
            updateAdd = "";
            insqry = "";

            string celldata = "", coulmenName = "";
            for (int j = 0; j < rdr.FieldCount; j++)
            {
                // this empty at each reader to assign new values celldata contains data or value of field Like (1,Idrees) column contain the Name of Cell Like ID,Name
                if (j > 0)
                {
                    // this condition is used for "," in insert and update query
                    {
                        coulmenName += "," + rdr.GetName(j).ToString();
                        celldata += ",'" + rdr[j].ToString() + "'";
                    }
                }
                else
                {
                    coulmenName += rdr.GetName(j).ToString();
                    celldata += "'" + rdr[j].ToString() + "'";
                }
                if (primaryKey == rdr.GetName(j).ToString())
                {
                    IDValues = rdr[j].ToString();
                }
                if (IDValues != null)
                {
                    //Generates the update Query
                    upqry = UpdateQuery(coulmenName, celldata, primaryKey, IDValues);
                    updateAdd += upqry;
                    //Generates the Insert Query
                    insqry = InsertQuery(coulmenName, celldata, TableName);
                }
            }

            WriteScripts(TableName, insqry, updateAdd, IDValues, primaryKey, i);
        }
    }
}

public void WriteScripts(string tableName, string insertqry, string updateqry, string IDvalues, string PrimaryKey, int i)
{
    string script = "";
    updateqry = "UPDATE " + tableName + " SET " + updateqry + " WHERE " + PrimaryKey + " = '" + IDvalues + "'";
    int index = updateqry.LastIndexOf(",");
    string updatqry = updateqry.Remove(index, 1);
    if (i == 1)
    {
        //if will be first time executed and all required variables are declared and next all times else condition will be executed
        script += "DECLARE @updateCount INT;" + Environment.NewLine;
        script += "DECLARE @insertCount INT;" + Environment.NewLine;
        script += "DECLARE @count INT;" + Environment.NewLine;
        script += "SET @updateCount = 0;" + Environment.NewLine;
        script += "SET @insertCount = 0;" + Environment.NewLine;
        script += "SELECT @count = COUNT(*) FROM [" + tableName + "] WHERE [" + PrimaryKey + "] = '" + IDvalues + "'" + Environment.NewLine;
        script += "IF @count = 0" + Environment.NewLine;
        script += "BEGIN" + Environment.NewLine;
        script += "    " + insertqry + "" + Environment.NewLine;
        script += "    " + "SET @insertCount = @insertCount + 1 " + Environment.NewLine;
        script += "END" + Environment.NewLine;
        script += "ELSE" + Environment.NewLine;
        script += "BEGIN" + Environment.NewLine;
        script += "    " + updatqry + "" + Environment.NewLine;
        script += "    " + "SET @updateCount = @updateCount + 1 " + Environment.NewLine;
        script += "END" + Environment.NewLine;
        //script += "GO" + Environment.NewLine;
        script += "--------------------------" + Environment.NewLine;
    }
    else
    {
        script += "SELECT @count = COUNT(*) FROM [" + tableName + "] WHERE [" + PrimaryKey + "] = '" + IDvalues + "'" + Environment.NewLine;
        script += "IF @count = 0" + Environment.NewLine;
        script += "BEGIN " + Environment.NewLine;
        script += "    " + insertqry + "" + Environment.NewLine;
        script += "    " + "SET @insertCount = @insertCount + 1 " + Environment.NewLine;
        script += "END" + Environment.NewLine;
        script += "ELSE" + Environment.NewLine;
        script += "BEGIN " + Environment.NewLine;
        script += "    " + updatqry + "" + Environment.NewLine;
        script += "    " + "SET @updateCount = @updateCount + 1 " + Environment.NewLine;
        script += "END" + Environment.NewLine;
        //script += "GO" + Environment.NewLine;
        script += "--------------------------" + Environment.NewLine;
    }
    // Generate the txt file and saved in projects folder.
    StreamWriter sw = new StreamWriter(Server.MapPath("~/Script.txt"), true, Encoding.UTF8);
    sw.Write(script);
    sw.Close();
}

public string GetprimaryKey(string tableName, string cnnString)
{
    string ID = "";
    SqlDataReader sdr;
    SqlConnection con = new SqlConnection();
    SqlCommand cmd = new SqlCommand();
    con = new SqlConnection(cnString);
    con.Open();    // sp_pkeys is SQL server default store procedure you just pass it only table Name it will return primary key column
    cmd = new SqlCommand("sp_pkeys", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@table_name", SqlDbType.NVarChar).Value = tableName;
    sdr = cmd.ExecuteReader();
    while (sdr.Read())
    {
        // the primary key column reside at 4 index
        ID = sdr[3].ToString();
    }
    return ID;
}

public string InsertQuery(string coulmenName, string celldata, string TableName)
{
    return Insertqry = "INSERT INTO " + TableName + "(" + coulmenName + ")VALUES(" + celldata + ")";
}

public string UpdateQuery(string coulmenName, string celldata, string Name, string Value)
{
    string IDName, IDValue, Ud = "", name = "", values = "";
    IDName = Name;
    IDValue = Value;
    if (IDName != null)
    {
        int indexcolumn = coulmenName.LastIndexOf(",");
        int indexValues = celldata.LastIndexOf(",");
        if (indexcolumn > 0 && indexValues > 0)
        {
            coulmenName = coulmenName.Substring(indexcolumn);
            celldata = celldata.Substring(indexValues);
            name = coulmenName.Replace(",", "");
            values = celldata.Replace(",", "");
            if (name != IDName && values != IDValue)
            {
                Ud = name + "=" + values + ",";
            }
        }
        else
        {
            name = coulmenName;
            values = celldata;
            if (name != IDName && values != IDValue)
            {
                Ud = name + "=" + values + ",";
            }
        }
    }
    return Ud;
}

VB.Net

Private primaryKey As String, Insertqry As String, updateAdd As String
Private cnString As String = ConfigurationManager.ConnectionStrings(1).ConnectionString
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    Run(cnString, "Customers")
    DownloadScriptFile()
End Sub

Private Sub DownloadScriptFile()
    ' Read the file for download.
    Dim fs As System.IO.FileStream = System.IO.File.Open(Server.MapPath("~/Script.txt"), System.IO.FileMode.Open)
    Dim btFile As Byte() = New Byte(fs.Length - 1) {}
    fs.Read(btFile, 0, Convert.ToInt32(fs.Length))
    fs.Close()
    ' After reading the file delete it.
    File.Delete(Server.MapPath("~/Script.txt"))
    Response.AddHeader("Content-disposition", "attachment; filename=Script.txt")
    Response.ContentType = "application/octet-stream"
    Response.BinaryWrite(btFile)
    Response.[End]()
End Sub

Public Sub Run(cnString As String, TableName As String)
    Dim IDValues As String = "", insqry As String, upqry As String
    Dim i As Integer = 0
    Dim rdr As SqlDataReader
    Dim con As New SqlConnection()
    Dim cmd As New SqlCommand()
    Dim cnnString As String = cnString
    con = New SqlConnection(cnnString)
    cmd = New SqlCommand(Convert.ToString("SELECT * FROM ") & TableName, con)
    primaryKey = GetprimaryKey(TableName, cnString)
    insqry = ""
    con.Open()

    rdr = cmd.ExecuteReader()
    If rdr IsNot Nothing Then
        While rdr.Read()
            ' i this variable counts the total number of record
            i = i + 1
            ' once a query is written, next it is available should be empty to build query again
            updateAdd = ""
            insqry = ""

            Dim celldata As String = "", coulmenName As String = ""
            For j As Integer = 0 To rdr.FieldCount - 1
                ' this empty at each reader to assign new values celldata contains data or value of field Like (1,Idrees) column contain the Name of Cell Like ID,Name
                If j > 0 Then
                    ' this condition is used for "," in insert and update query
                    If True Then
                        coulmenName += "," + rdr.GetName(j).ToString()
                        celldata += ",'" + rdr(j).ToString() + "'"
                    End If
                Else
                    coulmenName += rdr.GetName(j).ToString()
                    celldata += "'" + rdr(j).ToString() + "'"
                End If
                If primaryKey = rdr.GetName(j).ToString() Then
                    IDValues = rdr(j).ToString()
                End If
                If IDValues IsNot Nothing Then
                    'Generates the update Query
                    upqry = UpdateQuery(coulmenName, celldata, primaryKey, IDValues)
                    updateAdd += upqry
                    'Generates the Insert Query
                    insqry = InsertQuery(coulmenName, celldata, TableName)
                End If
            Next

            WriteScripts(TableName, insqry, updateAdd, IDValues, primaryKey, i)
        End While
    End If
End Sub

Public Sub WriteScripts(tableName As String, insertqry As String, updateqry As String, IDvalues As String, PrimaryKey As String, i As Integer)
    Dim script As String = ""
    updateqry = (Convert.ToString((Convert.ToString((Convert.ToString((Convert.ToString("UPDATE ") & tableName) + "SET ") & updateqry) + " WHERE ") & PrimaryKey) + " = '") & IDvalues) + "'"
    Dim index As Integer = updateqry.LastIndexOf(",")
    Dim updatqry As String = updateqry.Remove(index, 1)
    If i = 1 Then
        'if will be first time executed and all required variables are declared and next all times else condition will be executed
        script += "DECLARE @updateCount INT;" + Environment.NewLine
        script += "DECLARE @insertCount INT;" + Environment.NewLine
        script += "DECLARE @count INT;" + Environment.NewLine
        script += "SET @updateCount = 0;" + Environment.NewLine
        script += "SET @insertCount = 0;" + Environment.NewLine
        script += (Convert.ToString((Convert.ToString((Convert.ToString("SELECT @count = COUNT(*) FROM [") & tableName) + "] WHERE [") & PrimaryKey) + "] = '") & IDvalues) + "'" + Environment.NewLine
        script += "IF @count = 0" + Environment.NewLine
        script += "BEGIN" + Environment.NewLine
        script += (Convert.ToString("    ") & insertqry) + "" + Environment.NewLine
        script += "    " + "SET @insertCount = @insertCount + 1 " + Environment.NewLine
        script += "END" + Environment.NewLine
        script += "ELSE" + Environment.NewLine
        script += "BEGIN" + Environment.NewLine
        script += (Convert.ToString("    ") & updatqry) + "" + Environment.NewLine
        script += "    " + "SET @updateCount = @updateCount + 1 " + Environment.NewLine
        script += "END" + Environment.NewLine
        script += "GO" + Environment.NewLine
        script += "--------------------------" + Environment.NewLine
    Else
        script += (Convert.ToString((Convert.ToString((Convert.ToString("SELECT @count = COUNT(*) FROM [") & tableName) + "] WHERE [") & PrimaryKey) + "] = '") & IDvalues) + "'" + Environment.NewLine
        script += "IF @count = 0" + Environment.NewLine
        script += "BEGIN " + Environment.NewLine
        script += (Convert.ToString("    ") & insertqry) + "" + Environment.NewLine
        script += "    " + "SET @insertCount = @insertCount + 1 " + Environment.NewLine
        script += "END" + Environment.NewLine
        script += "ELSE" + Environment.NewLine
        script += "BEGIN " + Environment.NewLine
        script += (Convert.ToString("    ") & updatqry) + "" + Environment.NewLine
        script += "    " + "SET @updateCount = @updateCount + 1 " + Environment.NewLine
        script += "END" + Environment.NewLine
        script += "GO" + Environment.NewLine
        script += "--------------------------" + Environment.NewLine
    End If
    ' Generate the txt file and saved in projects folder.
    Dim sw As New StreamWriter(Server.MapPath("~/Script.txt"), True, Encoding.UTF8)
    sw.Write(script)
    sw.Close()
End Sub

Public Function GetprimaryKey(tableName As String, cnnString As String) As String
    Dim ID As String = ""
    Dim sdr As SqlDataReader
    Dim con As New SqlConnection()
    Dim cmd As New SqlCommand()
    con = New SqlConnection(cnString)
    con.Open()
    ' sp_pkeys is SQL server default store procedure you just pass it only table Name it will return primary key column
    cmd = New SqlCommand("sp_pkeys", con)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add("@table_name", SqlDbType.NVarChar).Value = tableName
    sdr = cmd.ExecuteReader()
    While sdr.Read()
        ' the primary key column reside at 4 index
        ID = sdr(3).ToString()
    End While
    Return ID
End Function

Public Function InsertQuery(coulmenName As String, celldata As String, TableName As String) As String
    Return Insertqry = (Convert.ToString((Convert.ToString((Convert.ToString("INSERT INTO ") & TableName) + "(") & coulmenName) + ")VALUES(") & celldata) + ")"
End Function

Public Function UpdateQuery(coulmenName As String, celldata As String, Name As String, Value As String) As String
    Dim IDName As String, IDValue As String, Ud As String = "", name_2 As String = "", values As String = ""
    IDName = Name
    IDValue = Value
    If IDName IsNot Nothing Then
        Dim indexcolumn As Integer = coulmenName.LastIndexOf(",")
        Dim indexValues As Integer = celldata.LastIndexOf(",")
        If indexcolumn > 0 AndAlso indexValues > 0 Then
            coulmenName = coulmenName.Substring(indexcolumn)
            celldata = celldata.Substring(indexValues)
            name_2 = coulmenName.Replace(",", "")
            values = celldata.Replace(",", "")
            If name_2 <> IDName AndAlso values <> IDValue Then
                Ud = (Convert.ToString(name_2 & Convert.ToString("=")) & values) + ","
            End If
        Else
            name_2 = coulmenName
            values = celldata
            If name_2 <> IDName AndAlso values <> IDValue Then
                Ud = (Convert.ToString(name_2 & Convert.ToString("=")) & values) + ","
            End If
        End If
    End If
    Return Ud
End Function

Hi nedash,

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

C#

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;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        CreateScriptTable("master", "Customers", ConfigurationManager.ConnectionStrings[1].ConnectionString);
        CreateScriptDataBase("LoginDB", 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 + ""];
        Scripter scripter = new Scripter(server);
        scripter.Options.ScriptData = true;
        scripter.Options.ScriptSchema = true;
        scripter.Options.ScriptDrops = false;
        var sb = new System.Text.StringBuilder();
        Microsoft.SqlServer.Management.Smo.Table table = database.Tables["" + tableName + ""];
        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 + ""];
        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();
    }
}