Build SQL query using ArrayList

Question

Hi friends,

I was wondering if we can write a method which takes an arraylist object(Column name,column values) and sql table name to build a sql query.... I have not used Arraylist as of now and i need your help to do this please...

Thanks

Vulpes · 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 string columnStr = String.Join(", ", columns); string[] values = new string[list.Count]; for(int i = 0; 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)

Vulpes · Answer

Yes, that's what's happening above: static string BuildInsertCommand(ArrayList list, string tableName) {...}

rani m mk · Answer

Can we have the arraylist object passed to a method along with the tablename

Build SQL query using ArrayList

Answers (3)