Build SQL query using ArrayList

Yes, it would be possible to write something like that though, as well as the column name and value, you'd also need to record whether the column was numeric or not so you'd know whether to wrap the value in single quotes or not.

Here's something I've quickly thrown together on these lines. As it's possible that the table and column names could be reserved words in whatever RDMS you're using the only safe thing to do is to wrap all names in square brackets:

using System;

using System.Collections;

class ColumnData

{

public string ColumnName {get; set;}

public object ColumnValue {get; set;}

public bool IsNumeric {get; set;}

public ColumnData(string name, object value, bool numeric)

{

ColumnName = name;

ColumnValue = value;

IsNumeric = numeric;

}

class Test

{

static void Main()

{

ColumnData cd = new ColumnData("Name", "Rani", false);

ColumnData cd2 = new ColumnData("ID", 1, true);

ColumnData cd3 = new ColumnData("Status", null, false);

ArrayList list = new ArrayList();

list.Add(cd);

list.Add(cd2);

list.Add(cd3);

string command = BuildInsertCommand(list, "Person");

Console.WriteLine(command);

Console.ReadKey();

}

static string BuildInsertCommand(ArrayList list, string tableName)

{

string[] columns = new string[list.Count];

for(int i = 0; i < list.Count; i++) columns[i] = "[" + ((ColumnData)list[i]).ColumnName +"]";

string columnStr = String.Join(", ", columns);

string[] values = new string[list.Count];

for(int i = 0; i < list.Count; i++)

{

ColumnData cd = (ColumnData)list[i];

if (cd.ColumnValue == null)

{

values[i] = "null";

}

else if (cd.IsNumeric)

{

values[i] = cd.ColumnValue.ToString();

}

else

{

values[i] = "'" + cd.ColumnValue.ToString() + "'";

}

string valueStr = String.Join(", ", values);

return String.Format("INSERT INTO [{0}] ({1}) VALUES ({2})", tableName, columnStr, valueStr);

}

The output of this program is:

INSERT INTO [Person] ([Name], [ID], [Status]) VALUES ('Rani', 1, null)

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes14y ago
Accepted answer
Yes, it would be possible to write something like that though, as well as the column name and value, you'd also need to record whether the column was numeric or not so you'd know whether to wrap the value in single quotes or not.

Here's something I've quickly thrown together on these lines. As it's possible that the table and column names could be reserved words in whatever RDMS you're using the only safe thing to do is to wrap all names in square brackets:

using System;
using System.Collections;

class ColumnData
{
public string ColumnName {get; set;}
public object ColumnValue {get; set;}
public bool IsNumeric {get; set;}

public ColumnData(string name, object value, bool numeric)
{
ColumnName = name;
ColumnValue = value;
IsNumeric = numeric;
}
}

class Test
{
static void Main()
{
ColumnData cd = new ColumnData("Name", "Rani", false);
ColumnData cd2 = new ColumnData("ID", 1, true);
ColumnData cd3 = new ColumnData("Status", null, false);
ArrayList list = new ArrayList();
list.Add(cd);
list.Add(cd2);
list.Add(cd3);
string command = BuildInsertCommand(list, "Person");
Console.WriteLine(command);
Console.ReadKey();
}

static string BuildInsertCommand(ArrayList list, string tableName)
{
string[] columns = new string[list.Count];
for(int i = 0; i < list.Count; i++) columns[i] = "[" + ((ColumnData)list[i]).ColumnName +"]";
string columnStr = String.Join(", ", columns);
string[] values = new string[list.Count];
for(int i = 0; i < list.Count; i++)
{
ColumnData cd = (ColumnData)list[i];
if (cd.ColumnValue == null)
{
values[i] = "null";
}
else if (cd.IsNumeric)
{
values[i] = cd.ColumnValue.ToString();
}
else
{
values[i] = "'" + cd.ColumnValue.ToString() + "'";
}
}
string valueStr = String.Join(", ", values);

return String.Format("INSERT INTO [{0}] ({1}) VALUES ({2})", tableName, columnStr, valueStr);
}
}

The output of this program is:

INSERT INTO [Person] ([Name], [ID], [Status]) VALUES ('Rani', 1, null)
Vulpes14y ago
Yes, that's what's happening above:

static string BuildInsertCommand(ArrayList list, string tableName) {...}
rani m mk14y ago
Can we have the arraylist object passed to a method along with the tablename