creating insert statement using arrays
-
i want to create a insert statement for inserting data in a table. Here the column names are stored in one array and the values are stored in another array. Any idea will be of great help for me. thanx in advance all ur help
Create a query with parameters that have the same name as the fields, like: "insert into aTable (Some, Other) values (@Some, @Other)" Create a command object with the query, and add parameter objects for the parameters. Do you have the data type for the fields?
--- b { font-weight: normal; }
-
i want to create a insert statement for inserting data in a table. Here the column names are stored in one array and the values are stored in another array. Any idea will be of great help for me. thanx in advance all ur help
public string[] GenInsertCmd(DataTable table) { string inscmd = "insert into " + table.TableName.Trim().ToLower() + " ("; foreach(DataColumn col in table.Columns) { inscmd += col.ColumnName + ","; } inscmd = inscmd.Substring(0,inscmd.Length - 1) + ") values ("; string[] sqlcmd = new string[table.Rows.Count]; int i = 0; foreach(DataRow row in table.Rows) { sqlcmd[i] = ""; foreach(DataColumn col in table.Columns) { switch (col.DataType.Name.Trim().ToLower()) { case "string": try { sqlcmd[i] += "'" + ((string)row[col]).Trim().Replace("'","\"") + "',"; } catch { sqlcmd[i] += "null,"; } break; case "datetime": try { sqlcmd[i] += "'" + ((DateTime)row[col]).ToString("yyyy-MM-dd hh:mm:ss.sss") + "',"; } catch { sqlcmd[i] += "null,"; } break; case "boolean": try { sqlcmd[i] += ((bool)row[col] == true ? "1," : "0,"); } catch { sqlcmd[i] += "null,"; } break; case "byte[]": sqlcmd[i] += "null" + ","; break; default: if (row[col].ToString().Trim() == "") sqlcmd[i] += "null,"; else sqlcmd[i] += row[col].ToString().Trim() + ","; break; } } sqlcmd[i] = inscmd + sqlcmd[i].Substring(0,sqlcmd[i].Length - 1) + ")"; i += 1; } return sqlcmd; }
-
public string[] GenInsertCmd(DataTable table) { string inscmd = "insert into " + table.TableName.Trim().ToLower() + " ("; foreach(DataColumn col in table.Columns) { inscmd += col.ColumnName + ","; } inscmd = inscmd.Substring(0,inscmd.Length - 1) + ") values ("; string[] sqlcmd = new string[table.Rows.Count]; int i = 0; foreach(DataRow row in table.Rows) { sqlcmd[i] = ""; foreach(DataColumn col in table.Columns) { switch (col.DataType.Name.Trim().ToLower()) { case "string": try { sqlcmd[i] += "'" + ((string)row[col]).Trim().Replace("'","\"") + "',"; } catch { sqlcmd[i] += "null,"; } break; case "datetime": try { sqlcmd[i] += "'" + ((DateTime)row[col]).ToString("yyyy-MM-dd hh:mm:ss.sss") + "',"; } catch { sqlcmd[i] += "null,"; } break; case "boolean": try { sqlcmd[i] += ((bool)row[col] == true ? "1," : "0,"); } catch { sqlcmd[i] += "null,"; } break; case "byte[]": sqlcmd[i] += "null" + ","; break; default: if (row[col].ToString().Trim() == "") sqlcmd[i] += "null,"; else sqlcmd[i] += row[col].ToString().Trim() + ","; break; } } sqlcmd[i] = inscmd + sqlcmd[i].Substring(0,sqlcmd[i].Length - 1) + ")"; i += 1; } return sqlcmd; }