Insert null to mysql C# through stringBuilder

Question

When I run the following code, null does not get stored in the database and instead I get an empty string. is there a way around it?

    string ConnectionString = "server=localhost; password = p@ss1234; user = root; database=DB ";
        StringBuilder sCommand = new StringBuilder("INSERT INTO mytable (Name) VALUES ");
        string A = null;
        using (MySqlConnection mConnection = new MySqlConnection(ConnectionString))
        {
            List<string> Rows = new List<string>();
            for (int i = 0; i < 100000; i++)
            {
                Rows.Add(string.Format("('{0}')", A));
            }
            sCommand.Append(string.Join(",", Rows));
            sCommand.Append(";");
            mConnection.Open();
            using (MySqlCommand myCmd = new MySqlCommand(sCommand.ToString(), mConnection))
            {
                myCmd.CommandType = CommandType.Text;
                myCmd.ExecuteNonQuery();
            }
        }

please don't do this - use a parameterized query! you are asking for sql injection attacks! — Daniel A. White
– Daniel A. White, Commented Nov 22, 2016 at 20:53
you also might consider using a bulk insert operation if your mysql library supports it. — Daniel A. White
– Daniel A. White, Commented Nov 22, 2016 at 20:54
your string builder is not doing anything productive either. — Daniel A. White
– Daniel A. White, Commented Nov 22, 2016 at 20:54

Wouter · Accepted Answer · 2016-11-22 23:44:42Z

2

Replace this:

string.Format("('{0}')", A));

with this:

A == null ? "(null)" : string.Format("('{0}')", A));

Update:

Use a formatter:

string.Format(new SqlFormatter(), "({0}, {1})", null, A);

Where the formatter:

public class SqlFormatter : IFormatProvider, ICustomFormatter
{
    public object GetFormat(Type formatType)
    {
        if (formatType == typeof(ICustomFormatter))
            return this;
        else
            return null;
    }

    public string Format(string format, object arg, IFormatProvider formatProvider)
    {
        return arg == null ? "null" : string.Format("'{0}'", arg);
    }
}

edited Nov 22, 2016 at 23:44

answered Nov 22, 2016 at 20:58

Wouter

3,03623 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BigFish Over a year ago

thanks, that worked, but not exactly the way I wanted it. because if I have another string like Rows.Add(string.Format("('{0}', '{1}')", "(null)", "Mark"));

Collectives™ on Stack Overflow

Insert null to mysql C# through stringBuilder

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related