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.
