BigFish BigFish - 13 days ago 4
C# Question

Insert null to mysql C# through stringBuilder

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

Answer

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