Generate SQL File from database and Save SQL file to client pc using C# and VB.Net in ASP.Net

Hi nedash,

You can not do that. As this feature is controlled by browser. So you need to save the file in server and download the saved file and save in local pc. Refer the below code.

C#

protected void Page_Load(object sender, EventArgs e)
{
    //CreateScriptTable("master", "Customers", ConfigurationManager.ConnectionStrings["constr"].ConnectionString);
    //DownloadTableScriptFile("Customers");
    CreateScriptDataBase("Northwind", ConfigurationManager.ConnectionStrings["constr"].ConnectionString);
    DownloadDataBaseScriptFile("Northwind");
}

private void DownloadTableScriptFile(string tableName)
{
    string filePath = Server.MapPath("~/Scripts/") + tableName + ".sql";
    // Read the file for download.
    System.IO.FileStream fs = System.IO.File.Open(filePath, 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(filePath);
    Response.AddHeader("Content-disposition", "attachment; filename=" + tableName + ".sql");
    Response.ContentType = "application/octet-stream";
    Response.BinaryWrite(btFile);
    Response.End();
}

private void DownloadDataBaseScriptFile(string dataBase)
{
    string filePath = Server.MapPath("~/Scripts/") + dataBase + ".sql";
    // Read the file for download.
    System.IO.FileStream fs = System.IO.File.Open(filePath, 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(filePath);
    Response.AddHeader("Content-disposition", "attachment; filename=" + dataBase + ".sql");
    Response.ContentType = "application/octet-stream";
    Response.BinaryWrite(btFile);
    Response.End();
}

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

VB.Net

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    'CreateScriptTable("master", "Customers", ConfigurationManager.ConnectionStrings("constr").ConnectionString)
    'DownloadTableScriptFile("Customers")
    CreateScriptDataBase("Northwind", ConfigurationManager.ConnectionStrings("constr").ConnectionString)
    DownloadDataBaseScriptFile("Northwind")
End Sub

Private Sub DownloadTableScriptFile(tableName As String)
    Dim filePath As String = (Server.MapPath("~/Scripts/") & tableName) + ".sql"
    ' Read the file for download.
    Dim fs As System.IO.FileStream = System.IO.File.Open(filePath, 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(filePath)
    Response.AddHeader("Content-disposition", (Convert.ToString("attachment; filename=") & tableName) + ".sql")
    Response.ContentType = "application/octet-stream"
    Response.BinaryWrite(btFile)
    Response.[End]()
End Sub

Private Sub DownloadDataBaseScriptFile(dataBase As String)
    Dim filePath As String = (Server.MapPath("~/Scripts/") & dataBase) + ".sql"
    ' Read the file for download.
    Dim fs As System.IO.FileStream = System.IO.File.Open(filePath, 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(filePath)
    Response.AddHeader("Content-disposition", (Convert.ToString("attachment; filename=") & dataBase) + ".sql")
    Response.ContentType = "application/octet-stream"
    Response.BinaryWrite(btFile)
    Response.[End]()
End Sub

''' <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 Sub CreateScriptTable(dataBaseName As String, tableName As String, connectionString As String)
    Dim con As New SqlConnection(connectionString)
    Dim serverConnection As New ServerConnection(con)
    Dim server__1 As New Server(serverConnection)
    Dim database As Database = server__1.Databases((Convert.ToString("") & dataBaseName) + "")
    If database IsNot Nothing Then
        Dim scripter As New Scripter(server__1)
        scripter.Options.ScriptData = True
        scripter.Options.ScriptSchema = True
        scripter.Options.ScriptDrops = False
        Dim sb = New System.Text.StringBuilder()
        For Each table As Microsoft.SqlServer.Management.Smo.Table In database.Tables
            If table.Name.ToLower() = tableName.ToLower() Then
                sb.Append("DROP TABLE " + table.Name)
                sb.Append(Environment.NewLine)
                For Each s As String In scripter.EnumScript(New Urn() {table.Urn})
                    sb.Append(s)
                    sb.Append(Environment.NewLine)
                Next
                Dim folder As String = Server.MapPath("~/Scripts/")
                Dim filename As String = (folder & tableName) + ".sql"
                Dim fs As System.IO.StreamWriter = System.IO.File.CreateText(filename)
                fs.Write(sb)
                fs.Close()
            End If
        Next
    End If
End Sub

''' <summary>
''' Create all Table script with Data of specified DataBase.
''' </summary>
''' <param name="dataBaseName">DataBase Name</param>
''' <param name="connectionString">Connection String</param>
Public Sub CreateScriptDataBase(dataBaseName As String, connectionString As String)
    Dim con As New SqlConnection(connectionString)
    Dim serverConnection As New ServerConnection(con)
    Dim server As New Server(serverConnection)
    Dim database As Database = server.Databases((Convert.ToString("") & dataBaseName) + "")
    If database IsNot Nothing Then
        Dim scripter As New Scripter(server)
        scripter.Options.ScriptData = True
        scripter.Options.ScriptSchema = True
        scripter.Options.ScriptDrops = False
        Dim sb = New System.Text.StringBuilder()
        For Each table As Microsoft.SqlServer.Management.Smo.Table In database.Tables
            sb.Append("DROP TABLE " + table.Name)
            sb.Append(Environment.NewLine)
            For Each s As String In scripter.EnumScript(New Urn() {table.Urn})
                sb.Append(s)
                sb.Append(Environment.NewLine)
            Next
            Dim folder As String = HttpContext.Current.Server.MapPath("~/Scripts/")
            Dim filename As String = (folder & dataBaseName) + ".sql"
            Dim fs As System.IO.StreamWriter = System.IO.File.CreateText(filename)
            fs.Write(sb)
            fs.Close()
        Next
    End If
End Sub