Hi nedash,
Your column value contains single quote like B's Beverages. So the issue cames in the query.
You need ro replace the single quote to double single quote while generating the query to resolve the error.
while (sdr.Read())
{
i = i + 1;
updateAdd = "";
insertQry = "";
string celldata = "", coulmenName = "";
for (int j = 0; j < sdr.FieldCount; j++)
{
if (j > 0)
{
coulmenName += "," + sdr.GetName(j).ToString();
if (!string.IsNullOrEmpty(sdr[j].ToString()))
{
celldata += ",N'" + sdr[j].ToString().Replace("'", "''") + "'";
}
else
{
celldata += ",'" + sdr[j].ToString().Replace("'", "''") + "'";
}
}
else
{
coulmenName += sdr.GetName(j).ToString();
if (!string.IsNullOrEmpty(sdr[j].ToString()))
{
celldata += "N'" + sdr[j].ToString().Replace("'", "''") + "'";
}
else
{
celldata += "'" + sdr[j].ToString().Replace("'", "''") + "'";
}
}
if (primaryKey == sdr.GetName(j).ToString())
{
iDValues = sdr[j].ToString();
}
if (iDValues != null)
{
updateQry = UpdateQuery(coulmenName, celldata, primaryKey, iDValues);
updateAdd += updateQry;
insertQry = InsertQuery(coulmenName, celldata, tableName);
}
}
WriteScripts(tableName, insertQry, updateAdd, iDValues, primaryKey, i);
}
Refer the updated code.
public string GenerateCreateTableScript()
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DigimasterConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = @"DECLARE @object_name SYSNAME
, @object_id INT
, @SQL NVARCHAR(MAX)
SELECT
@object_name = '[' + OBJECT_SCHEMA_NAME(o.[object_id]) + '].[' + OBJECT_NAME([object_id]) + ']'
, @object_id = [object_id]
FROM (SELECT [object_id] = OBJECT_ID('dbo.Documentry_Info', 'U')) o
SELECT @SQL = 'DROP TABLE Documentry_Info
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((
SELECT CHAR(13) + ' , [' + c.name + '] ' +
CASE WHEN c.is_computed = 1
THEN 'AS ' + OBJECT_DEFINITION(c.[object_id], c.column_id)
ELSE
CASE WHEN c.system_type_id != c.user_type_id
THEN '[' + SCHEMA_NAME(tp.[schema_id]) + '].[' + tp.name + ']'
ELSE '[' + UPPER(tp.name) + ']'
END +
CASE
WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary')
THEN '(' + CASE WHEN c.max_length = -1
THEN 'MAX'
ELSE CAST(c.max_length AS VARCHAR(5))
END + ')'
WHEN tp.name IN ('nvarchar', 'nchar')
THEN '(' + CASE WHEN c.max_length = -1
THEN 'MAX'
ELSE CAST(c.max_length / 2 AS VARCHAR(5))
END + ')'
WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')
THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
WHEN tp.name = 'decimal'
THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
ELSE ''
END +
CASE WHEN c.collation_name IS NOT NULL AND c.system_type_id = c.user_type_id
THEN ' COLLATE ' + c.collation_name
ELSE ''
END +
CASE WHEN c.is_nullable = 1
THEN ' NULL'
ELSE ' NOT NULL'
END +
CASE WHEN c.default_object_id != 0
THEN ' CONSTRAINT [' + OBJECT_NAME(c.default_object_id) + ']' +
' DEFAULT ' + OBJECT_DEFINITION(c.default_object_id)
ELSE ''
END +
CASE WHEN cc.[object_id] IS NOT NULL
THEN ' CONSTRAINT [' + cc.name + '] CHECK ' + cc.[definition]
ELSE ''
END +
CASE WHEN c.is_identity = 1
THEN ' IDENTITY(' + CAST(IDENTITYPROPERTY(c.[object_id], 'SeedValue') AS VARCHAR(5)) + ',' +
CAST(IDENTITYPROPERTY(c.[object_id], 'IncrementValue') AS VARCHAR(5)) + ')'
ELSE ''
END
END
FROM sys.columns c WITH(NOLOCK)
JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id
LEFT JOIN sys.check_constraints cc WITH(NOLOCK)
ON c.[object_id] = cc.parent_object_id
AND cc.parent_column_id = c.column_id
WHERE c.[object_id] = @object_id
ORDER BY c.column_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 7, ' ') +
ISNULL((SELECT '
, CONSTRAINT [' + i.name + '] PRIMARY KEY ' +
CASE WHEN i.index_id = 1
THEN 'CLUSTERED'
ELSE 'NONCLUSTERED'
END +' (' + (
SELECT STUFF(CAST((
SELECT ', [' + COL_NAME(ic.[object_id], ic.column_id) + ']' +
CASE WHEN ic.is_descending_key = 1
THEN ' DESC'
ELSE ''
END
FROM sys.index_columns ic WITH(NOLOCK)
WHERE i.[object_id] = ic.[object_id]
AND i.index_id = ic.index_id
FOR XML PATH(N''), TYPE) AS NVARCHAR(MAX)), 1, 2, '')) + ')'
FROM sys.indexes i WITH(NOLOCK)
WHERE i.[object_id] = @object_id
AND i.is_primary_key = 1), '') + CHAR(13) + ');'
SELECT @SQL ";
cmd.Connection = con;
con.Open();
string query = Convert.ToString(cmd.ExecuteScalar());
con.Close();
return query;
}
public void DownloadScript()
{
string iDValues = "", insertQry, updateQry;
int i = 0;
string conString = ConfigurationManager.ConnectionStrings["DigimasterConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(conString);
SqlCommand cmd = new SqlCommand("select * from Customers", con);
primaryKey = GetPrimaryKey(tableName, conString);
insertQry = "";
con.Open();
if (File.Exists(Server.MapPath("~/" + tableName + ".sql")))
{
File.Delete(Server.MapPath("~/" + tableName + ".sql"));
}
// Writing Create Table Script.
StreamWriter sw = new StreamWriter(Server.MapPath("~/" + tableName + ".sql"), true, Encoding.UTF8);
sw.Write(GenerateCreateTableScript() + Environment.NewLine);
sw.Close();
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
i = i + 1;
updateAdd = "";
insertQry = "";
string celldata = "", coulmenName = "";
for (int j = 0; j < sdr.FieldCount; j++)
{
if (j > 0)
{
coulmenName += "," + sdr.GetName(j).ToString();
if (!string.IsNullOrEmpty(sdr[j].ToString()))
{
celldata += ",N'" + sdr[j].ToString().Replace("'", "''") + "'";
}
else
{
celldata += ",'" + sdr[j].ToString().Replace("'", "''") + "'";
}
}
else
{
coulmenName += sdr.GetName(j).ToString();
if (!string.IsNullOrEmpty(sdr[j].ToString()))
{
celldata += "N'" + sdr[j].ToString().Replace("'", "''") + "'";
}
else
{
celldata += "'" + sdr[j].ToString().Replace("'", "''") + "'";
}
}
if (primaryKey == sdr.GetName(j).ToString())
{
iDValues = sdr[j].ToString();
}
if (iDValues != null)
{
updateQry = UpdateQuery(coulmenName, celldata, primaryKey, iDValues);
updateAdd += updateQry;
insertQry = InsertQuery(coulmenName, celldata, tableName);
}
}
WriteScripts(tableName, insertQry, updateAdd, iDValues, primaryKey, i);
}
// Download the sql script file.
if (File.Exists(Server.MapPath("~/" + tableName + ".sql")))
{
byte[] bytes = File.ReadAllBytes(Server.MapPath("~/" + tableName + ".sql"));
File.Delete(Server.MapPath("~/" + tableName + ".sql"));
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = ContentType;
Response.AppendHeader("Content-Disposition", "attachment; filename=" + tableName + ".sql");
Response.BinaryWrite(bytes);
Response.Flush();
Response.End();
}
}
public string GetPrimaryKey(string tableName, string cnnString)
{
string iD = "";
SqlConnection con = new SqlConnection(cnnString);
SqlCommand cmd = new SqlCommand("sp_pkeys", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@table_name", SqlDbType.NVarChar).Value = tableName;
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
iD = sdr[3].ToString();
}
con.Close();
return iD;
}
public void WriteScripts(string tableName, string insertqry, string updateQuery, string iDValues, string PrimaryKey, int i)
{
string script = "";
updateQuery = "UPDATE " + tableName + " SET " + updateQuery + " WHERE " + PrimaryKey + " = ' " + iDValues + "'";
int index = updateQuery.LastIndexOf(",");
string updatqry = updateQuery.Remove(index, 1);
if (i == 1)
{
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 += "SET IDENTITY_INSERT " + tableName + " ON" + Environment.NewLine;
script += insertqry + " " + Environment.NewLine;
script += "SET IDENTITY_INSERT " + tableName + " OFF" + 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;
StreamWriter sw = new StreamWriter(Server.MapPath("~/" + tableName + ".sql"), true, Encoding.UTF8);
sw.Write(script);
sw.Close();
}
else
{
script += "SELECT @count = COUNT(*) FROM [" + tableName + "] WHERE [" + PrimaryKey + "] = '" + iDValues + "'" + Environment.NewLine;
//script += "IF @count = 0" + Environment.NewLine;
script += "BEGIN " + Environment.NewLine;
script += "SET IDENTITY_INSERT " + tableName + " ON" + Environment.NewLine;
script += insertqry + "" + Environment.NewLine;
script += "SET IDENTITY_INSERT " + tableName + " OFF" + 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;
StreamWriter sw = new StreamWriter(Server.MapPath("~/" + tableName + ".sql"), true, Encoding.UTF8);
sw.Write(script);
sw.Close();
}
}
public string InsertQuery(string columnName, string cellData, string tableName)
{
return "INSERT INTO " + tableName + " (" + columnName + ") VALUES (" + cellData + ")";
}
public string UpdateQuery(string columnName, string cellData, string Names, string value)
{
string iDName, iDValue, ud = "", name = "", values = "";
iDName = Names;
iDValue = value;
if (iDName != null)
{
int indexcolumn = columnName.LastIndexOf(",");
int indexValues = cellData.LastIndexOf(",");
if (indexcolumn > 0 && indexValues > 0)
{
columnName = columnName.Substring(indexcolumn);
cellData = cellData.Substring(indexValues);
name = columnName.Replace(",", "");
values = cellData.Replace(",", "");
if (name != iDName && values != iDValue)
{
ud = name + "=" + values + ",";
}
}
else
{
name = columnName;
values = cellData;
if (name != iDName && values != iDValue)
{
ud = name + "=" + values + ",";
}
}
}
return ud;
}
