Build SQL query using ArrayList

Question

Hi friends I was wondering if we can write a method which takes an arraylist object(Column namecolumn 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