Build comma seperated string from the struct in C#

Question

I have the following struct in C# class

public struct Employee
        {
            public const string EMPID = "EMP_ID";
            public const string FName = "FIRST_NAME";
            public const string LNAME = "LAST_NAME";
            public const string DEPTID = "DEPT_ID";

        }

Is there an easy way to build a string as follows

const string mainquery="INSERT INTO EMP(EMP_ID,FIRST_NAME,LAST_NAME,DEPT_ID) VALUES(:EMP_ID,:FIRST_NAME,:LAST_NAME,:DEPT_ID)"

Instead of doing as follows and then concatenating it.

const string EMP_COLS=
                EMPLOYEE.EMPID + "," +
                EMPLOYEE.FNAME + "," +
        EMPLOYEE.LNAME + "," +
                EMPLOYEE.DEPTID;

            const string EMP_Values=
                EMPLOYEE.EMPID + ":" +
                EMPLOYEE.FNAME + ":" +
        EMPLOYEE.LNAME + ":" +
                EMPLOYEE.DEPTID;

cliss · Accepted Answer · 2010-06-15T13:41:36.543

4

You could try something like this:

StringBuilder sb = new StringBuilder();
var fields = typeof(Employee).GetFields();
for (int i = 0; i < fields.Length; ++i)
{
    sb.Append(fields[i].GetValue(new Employee()));
    if (i < fields.Length - 1)
    {
        sb.Append(',');
    }
}

string result = sb.ToString();
// The above will be "EMP_ID,FIRST_NAME,LAST_NAME,DEPT_ID"

Edit: Note that above I'm assuming you've got using directives for both System.Reflection and System.Text.

edited Jun 15 '10 at 13:41

answered Jun 15 '10 at 13:35

cliss

626
7
8

1

But will that guarantee that the order is consistent? It would be bad if for some reason the outuputted order changed and data was inserted into the wrong columns. – Walter Jun 15 '10 at 13:56
That's an excellent point Walter--there isn't any guarantee. That said, I believe there's an *implicit* "guarantee" that the order is as-defined order in the struct. Not a place to be rolling the dice though, to your point. – cliss Jun 15 '10 at 14:02
You should never depend on the order, but considering the result wanted (as seen in the mainquery declared above) I don't think the order would matter anyway, you'll just get it out once and then use the same list in both parts of the mainquery... – Hans Olsson Jun 15 '10 at 20:36

score 1 · Answer 2 · answered Jun 15 '10 at 13:31

If the struct held readonly properties rather than public consts you could do it with Reflection. You could call Type.GetProperties, loop through them all and call them to get out the values and insert the values into a List<string> and then join them with string.Join(", ", myList); or something.
You'd then get the first part of the string as EMP_ID, FIRST_NAME, LAST_NAME, DEPT_ID and you'd need to make a copy of that and add the :s as needed and you'd have both parts.

score 1 · Answer 3 · answered Jun 15 '10 at 13:38

you can use the string.Format(string format, object[] arg) Method as follows:

string query = string.Format("INSERT INTO EMP({0},{1},{2},{3}) VALUES(:{0},:{1},:{2},:{3})",
                Employee.EMPID,
                Employee.FNAME,
                Employee.LNAME,
                Employee.DEPTID);

Hope that helps.

score 0 · Answer 4 · edited May 23 '17 at 12:03

0

You will need some kind of mapping dictionary, like here, or you could just use some ORM tool to do the mapping for you.

edited May 23 '17 at 12:03

Community

1
1

answered Jun 15 '10 at 13:30

SWeko

30,434
10
71
106

score 0 · Answer 5 · answered Jun 15 '10 at 13:37

        string[] values = new[] {
            "EMP_ID",
            "FIRST_NAME",
            "LAST_NAME",
            "DEPT_ID",
        };

        var columns = string.Join(",", values);
        var parameters = ":" + string.Join(",:", values);

        var sql = string.Format("INSERT INTO EMP({0}) VALUES({1})",
            columns,
            parameters);

score 0 · Answer 6 · edited Aug 22 '12 at 19:00

0

string[] cols = typeof(Employee).GetFields()
                 .Select(f => f.GetValue(null).ToString()).ToArray();

Console.WriteLine(string.Format(
      "INSERT INTO EMP({0}) VALUES(:{1})", 
      string.Join(",", cols), 
      string.Join(",:", cols)));

edited Aug 22 '12 at 19:00

Brad Rem

6,036
2
25
50

answered Jun 15 '10 at 13:48

Sorin Comanescu

4,829
3
29
38

Build comma seperated string from the struct in C#

6 Answers6