0

My requirement is to create a common C# method which takes a numeric datatype (including nullable) as argument and returns the value as string or "NULL" if the variable is null or 0.

The main purpose of this method is to generate an INSERT SQL statement, where if a parameter value is 0 it should insert a NULL in the database.

I have tried the following code but not very sure if it can handle all numeric data types in C#. i.e. int, int?, short, short?, double, double?

I ran some examples and it seems to work fine, but I don't want to miss something basic and cause issues in production environment.

public string GetNumericForSQL<T>(object obj, string textIfZero = "NULL")
{
    string res = "NULL";

    //Check if obj is Nullable numeric data type
    if (default(T) == null)
    {
        string temp = (obj ?? 0).ToString();
        if (temp == "0")
            res = textIfZero;
        else
            res = temp;
    }
    else
    {
        res = obj.ToString();
    }
    return res;
}
Peter Csala
  • 17,736
  • 16
  • 35
  • 75
MSBI-Geek
  • 628
  • 10
  • 25
  • 3
    Generating SQL statements on the fly like this is almost always a bad idea... – DavidG Sep 06 '21 at 10:05
  • How do you want it to handle decimals? – smoksnes Sep 06 '21 at 10:05
  • 3
    Use properly parameterized statements and you don't have to worry about this. Don't reinvent Dapper and then do it badly. – Jeroen Mostert Sep 06 '21 at 10:06
  • You aren't building SQL statements by concatenating strings, are you? If so, this is a x-y question. – Fildor Sep 06 '21 at 10:06
  • `GetNumericForSQL("0")` will return `NULL` check this link and add it to condition https://stackoverflow.com/questions/1749966/c-sharp-how-to-determine-whether-a-type-is-a-number – Abdelrahman Gobarah Sep 06 '21 at 10:13
  • all, thanks for your comments... I have to do it this way for inserting multiple rows in a child table, sorry I couldn't find a better solution as MySQL doesn't support table as input parameter and I don't want to call the stored procedure repeatedly. Please suggest a better method for inserting child table data into MySql from .NET Web API – MSBI-Geek Sep 06 '21 at 10:21
  • You don't have to pass a table as a parameter, you can pass the individual columns as parameters. You can still generate an `INSERT` statement if you like, but don't pass the values as literals interpolated into a string; generate the statement with parameter placeholders instead. Again, though, Dapper would do most of this boilerplate for you. You can use `DynamicParameters` if the columns aren't known in advance. The only scenario where interpolated values are unavoidable is if the database doesn't support a parameter at the spot you need a variable (like a table name), which is rare. – Jeroen Mostert Sep 06 '21 at 10:26
  • I'm using this method only to insert multiple child table rows in one go. To my stored procedure I'm passing master table data as parameters and child table rows as comma separated string then splitting and inserting them in the child table. Every insert roughly there could be 20 to 50 child rows and only 3 columns. So I thought it's better for performance, and I have seen it working well too. And I'm using ADO.NET not EF. – MSBI-Geek Sep 06 '21 at 10:32
  • As per what @JeroenMostert mentioned, it really seems like Dapper will already handle this for you: https://stackoverflow.com/questions/9481678/how-to-create-arguments-for-a-dapper-query-dynamically – sr28 Sep 06 '21 at 10:53

0 Answers0