Insert LOTS of values in using dbClient

Question

Currently I'm trying to insert a lot of values in my new project, but I can't figure out how to do that exactly. My 'simplest' SQL query is as follows, and this function works like it should:

dbClient.setQuery("INSERT INTO `rooms` (`roomtype`, `caption`, `owner`, `description`,     `category`, `state`, `users_now`, `users_max`, `model_name`, `public_ccts`, `score`, `tags`, `icon_bg`, `icon_fg`, `icon_items`, `password`, `wallpaper`, `floor`, `landscape`, `allow_pets`, `allow_pets_eat`, `allow_walkthrough`, `allow_hidewall`, `wallthick`, `floorthick`, `achievement`, `group_id`, `game_id`, `mute_settings`, `ban_settings`, `kick_settings`) VALUES " +
"('private', 'VIP CADEAU: Penthouse', @username, 'Ontvang deze kamer GRATIS bij het lid worden van VIP (http://wonderhotel.nl/vip).', 11, 'open', 0, 25, 'model_i', '', 1, '', 1, 0, '', '', '0.0', '0.0', '0.0', '1', '0', '0', '0', -2, -2, 0, 0, 0, '0', '1', '1');");
dbClient.addParameter("username", Session.GetHabbo().Username);
dbClient.runQuery();     

But now I would like to insert more than 1 value, with the following query:

INSERT INTO `rooms` (`roomtype`, `caption`, `owner`, `description`, `category`, `state`, `users_now`, `users_max`, `model_name`, `public_ccts`, `score`, `tags`, `icon_bg`, `icon_fg`, `icon_items`, `password`, `wallpaper`, `floor`, `landscape`, `allow_pets`, `allow_pets_eat`, `allow_walkthrough`, `allow_hidewall`, `wallthick`, `floorthick`, `achievement`, `group_id`, `game_id`, `mute_settings`, `ban_settings`, `kick_settings`) VALUES
('private', 'Penthouse', @username, 'Ontvang deze kamer GRATIS bij het lid worden van VIP (http://wonderhotel.nl/vip).', 11, 'open', 7, 25, 'model_i', '', 1, '', 1, 0, '', '', '0.0', '0.0', '0.0', '1', '0', '0', '0', -2, -2, 0, 0, 0, '0', '1', '1'),
('private', 'Penthouse', @username, 'Ontvang deze kamer GRATIS bij het lid worden van VIP (http://wonderhotel.nl/vip).', 11, 'open', 7, 25, 'model_i', '', 1, '', 1, 0, '', '', '0.0', '0.0', '0.0', '1', '0', '0', '0', -2, -2, 0, 0, 0, '0', '1', '1'),
('private', 'Penthouse', @username, 'Ontvang deze kamer GRATIS bij het lid worden van VIP (http://wonderhotel.nl/vip).', 11, 'open', 7, 25, 'model_i', '', 1, '', 1, 0, '', '', '0.0', '0.0', '0.0', '1', '0', '0', '0', -2, -2, 0, 0, 0, '0', '1', '1'),
('private', 'Penthouse', @username, 'Ontvang deze kamer GRATIS bij het lid worden van VIP (http://wonderhotel.nl/vip).', 11, 'open', 7, 25, 'model_i', '', 1, '', 1, 0, '', '', '0.0', '0.0', '0.0', '1', '0', '0', '0', -2, -2, 0, 0, 0, '0', '1', '1'),
('private', 'Penthouse', @username, 'Ontvang deze kamer GRATIS bij het lid worden van VIP (http://wonderhotel.nl/vip).', 11, 'open', 7, 25, 'model_i', '', 1, '', 1, 0, '', '', '0.0', '0.0', '0.0', '1', '0', '0', '0', -2, -2, 0, 0, 0, '0', '1', '1'),

And thousands of other rows that follow after this...

But I have absolutely no idea how to run adapt my code (dbClient) to make it work.... A logical but messy solution could be:

dbClient.setQuery("INSERT INTO `rooms` (`roomtype`, `caption`, `owner`, `description`,     `category`, `state`, `users_now`, `users_max`, `model_name`, `public_ccts`, `score`, `tags`, `icon_bg`, `icon_fg`, `icon_items`, `password`, `wallpaper`, `floor`, `landscape`, `allow_pets`, `allow_pets_eat`, `allow_walkthrough`, `allow_hidewall`, `wallthick`, `floorthick`, `achievement`, `group_id`, `game_id`, `mute_settings`, `ban_settings`, `kick_settings`) VALUES " +
    "('private', 'VIP CADEAU: Penthouse', @username, 'Ontvang deze kamer GRATIS bij het lid worden van VIP (http://wonderhotel.nl/vip).', 11, 'open', 0, 25, 'model_i', '', 1, '', 1, 0, '', '', '0.0', '0.0', '0.0', '1', '0', '0', '0', -2, -2, 0, 0, 0, '0', '1', '1');");
dbClient.setQuery("INSERT INTO `rooms` (`roomtype`, `caption`, `owner`, `description`,     `category`, `state`, `users_now`, `users_max`, `model_name`, `public_ccts`, `score`, `tags`, `icon_bg`, `icon_fg`, `icon_items`, `password`, `wallpaper`, `floor`, `landscape`, `allow_pets`, `allow_pets_eat`, `allow_walkthrough`, `allow_hidewall`, `wallthick`, `floorthick`, `achievement`, `group_id`, `game_id`, `mute_settings`, `ban_settings`, `kick_settings`) VALUES " +
    "('private', 'VIP CADEAU: Penthouse', @username, 'Ontvang deze kamer GRATIS bij het lid worden van VIP (http://wonderhotel.nl/vip).', 11, 'open', 0, 25, 'model_i', '', 1, '', 1, 0, '', '', '0.0', '0.0', '0.0', '1', '0', '0', '0', -2, -2, 0, 0, 0, '0', '1', '1');");

But I don't want to repeat the 'INSERT INTO' over a 1000 times in my code..

Answer 1

Options:

• loop, using the simple code lots of times with different parameters
• use a tool that can do that for you (dapper has some close-to-basic tricks there, and most ORMs will allow simple list-based inserts)
• for very large inserts, "bulk copy" if your provider supports it - perhaps using FastMember to turn a list into an IDataReader

Answer 2

You just need multiple INSERT statements:

INSERT INTO `rooms` (`roomtype`, `caption`, `owner`, `description`, `category`, `state`, `users_now`, `users_max`, `model_name`, `public_ccts`, `score`, `tags`, `icon_bg`, `icon_fg`, `icon_items`, `password`, `wallpaper`, `floor`, `landscape`, `allow_pets`, `allow_pets_eat`, `allow_walkthrough`, `allow_hidewall`, `wallthick`, `floorthick`, `achievement`, `group_id`, `game_id`, `mute_settings`, `ban_settings`, `kick_settings`) VALUES
('private', 'Penthouse', @username, 'Ontvang deze kamer GRATIS bij het lid worden van VIP (http://wonderhotel.nl/vip).', 11, 'open', 7, 25, 'model_i', '', 1, '', 1, 0, '', '', '0.0', '0.0', '0.0', '1', '0', '0', '0', -2, -2, 0, 0, 0, '0', '1', '1');

INSERT INTO `rooms` (`roomtype`, `caption`, `owner`, `description`, `category`, `state`, `users_now`, `users_max`, `model_name`, `public_ccts`, `score`, `tags`, `icon_bg`, `icon_fg`, `icon_items`, `password`, `wallpaper`, `floor`, `landscape`, `allow_pets`, `allow_pets_eat`, `allow_walkthrough`, `allow_hidewall`, `wallthick`, `floorthick`, `achievement`, `group_id`, `game_id`, `mute_settings`, `ban_settings`, `kick_settings`) VALUES
('private', 'Penthouse', @username, 'Ontvang deze kamer GRATIS bij het lid worden van VIP (http://wonderhotel.nl/vip).', 11, 'open', 7, 25, 'model_i', '', 1, '', 1, 0, '', '', '0.0', '0.0', '0.0', '1', '0', '0', '0', -2, -2, 0, 0, 0, '0', '1', '1');