Member Avatar for serkan sendur
serkan sendur 821 Banned

How to insert big html markup with white spaces(i.e. line breaks) and single and double quotation marks to sql server :

To insert that big html markup to ms sql server, i tried to copy the markup and paste it to the sql server table cell first. It didnt work, it pasted only the part until the first line break. Then i tried to insert it as a parameter in t-sql query, but i couldnt add it as a parameter since the text was impossible to pass to a string variable in c#. Then i tried to directly put it in a query in sql server management studio, but this time the parameter had some quotation marks which had to be replaced. My working solution was this : i created a label and assigned the markup to its text property in design time. And i passed its text property as a parameter to the insert sql statement.

  • 1 Contributor
  • 0 Replies
  • 67 Views

Dani AI

Generated

A reliable way to store large HTML (with line breaks and quotes) is to keep the column as NVARCHAR(MAX) (or VARCHAR(MAX) if you do not need Unicode) and send the content to SQL Server via a parameterized command. Parameters preserve newlines and both single and double quotes without manual escaping and avoid SQL injection or literal-length problems that come from embedding big markup directly in a query. As a more robust alternative to the Label.Text workaround used by , explicitly create a SqlParameter and set its type/size instead of building a long SQL literal or using AddWithValue.

Example C# pattern (trimmed to the essentials):

using (var conn = new SqlConnection(connString))
{
    conn.Open();
    using (var cmd = new SqlCommand(
        "INSERT INTO Pages(HtmlContent) VALUES(@html)", conn))
    {
        var p = new SqlParameter("@html", SqlDbType.NVarChar, -1) { Value = htmlString };
        cmd.Parameters.Add(p);
        cmd.ExecuteNonQuery();
    }
}

SQL side: define the column as NVARCHAR(MAX):

CREATE TABLE Pages
(
    Id INT IDENTITY PRIMARY KEY,
    HtmlContent NVARCHAR(MAX) NULL
);

If you ever must paste or edit directly in SSMS, open the cell in the text editor (the grid cell editor) or update via a parameterized script—pasting into the grid can behave oddly across SSMS versions. If you embed literals in T-SQL instead, prefix with N'...' and double single quotes to escape them. Finally, if stored HTML comes from users, sanitize or encode it on output to prevent XSS.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.