Matt,I use a StringBuilder in some cases too. The CommandText is after all a string, so you must compose it somehow. Stringbuilder is a good option, especially when combined with parameterized queries.
Have you seen this? http://www.firebirdfaq.org/faq336/I'm not sure what you mean by 'getting SQL out of Firebird objects' so I'll not address that.
As for multiple inserts in one BLOCK, my previous example assumed that you were familiar with EXECUTE BLOCK, but couldn't get it working with the .NET provider. With this in mind you can create a method that loops on your set of data (objects or whatever) and builds your inserts inside a BLOCK EXECUTE and fires the whole thing off to the server. I've done this with UPDATES too. Here's an example using parameterized queries and a variable number of objects of a hypothetical type, MyObj. I'm not 100% sure of how Firebird caches queries, but a parameterized one should cache better than queries composed directly from data (with quoted values, et al), plus it should help secure your code from SQL-type injections.
<code>
DbCommand cmd = Connection.CreateCommand();
StringBuilder insert = new StringBuilder();
StringBuilder paramList = new StringBuilder();
int i = 0;
bool first = true;
string comma = "";
foreach (MyObj o in myObjects)
{
string prop1 = string.Format("prop1{0}", i);
string prop2 = string.Format("prop2{0}", i);
string prop3 = string.Format("prop3{0}", i);
if (first)
{
first = false;
}
else
{
comma = ",";
}
paramList.AppendFormat("{0}{1} BIGINT = ?", comma, prop1);
paramList.AppendFormat(",{0} FLOAT = ?", prop2);
paramList.AppendFormat(",{0} FLOAT = ?", prop3);
insert.Append("INSERT INTO MYTABLE (COL1,COL2,COL3) VALUES (");
insert.AppendFormat(":{0},:{1},:{2},:{3},:{4});{5}", prop1, prop2,
prop3, Environment.NewLine);
addParam(cmd, string.Format(":{0}", prop1), DbType.Int64, o.Prop1);
addParam(cmd, string.Format(":{0}", prop2), DbType.Single, o.Prop2);
addParam(cmd, string.Format(":{0}", prop3), DbType.Single, o.Prop3);
i++;
}
insert.AppendLine("END");
insert.Insert(0, string.Format(") AS BEGIN{0}", Environment.NewLine));
insert.Insert(0, paramList.ToString());
insert.Insert(0, "EXECUTE BLOCK (");
cmd.CommandType = CommandType.Text;
cmd.CommandText = insert.ToString();
return cmd;
</code>
Danny
On 01/26/2011 08:12 PM, Matthew Parslow wrote:
Hi Guys - note that this doesn't cover my case of wanting to run many inserts. I am currently manually parsing the data (quoting strings, and escaping quotes) and assembling the SQL with a StringBuilder If there's a better way to do it, that would be great, but it doesn't seem that there's a way to get SQL out of firebird objects (or any other database objects, to be fair). My problem was with the overhead for each command on a handheld device. By running a block execute instead of individual procedure calls, the time elapsed has gone from 60+ seconds to about 5 Cheers, Matt -----Original Message----- From: da...@absolutepowerandcontrol.com [mailto:da...@absolutepowerandcontrol.com] Sent: Sunday, 23 January 2011 3:09 AM To: For users and developers of the Firebird .NET providers Subject: Re: [Firebird-net-provider] Execute block... Here is a snippet of code from my Firebird provider. This function just creates and returns the command object, but it shows how I use EXECUTE BLOCK. You'll note that 'id' is set by the generator, not as an inbound parameter. <code> DbCommand cmd = Connection.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = @"EXECUTE BLOCK (name VARCHAR(32) = ?) RETURNS (id BIGINT) AS BEGIN id = GEN_ID(ECOPCONFIGURATIONID, 1); INSERT INTO ECOPCONFIGURATION (ID,NAME,ISACTIVE) VALUES (:id,:name,0); SUSPEND; END"; //'SUSPEND' is required for return values/output params. This causes the server to wait for the client to request the next line addParam(cmd,":name", DbType.AnsiString, name); return cmd; </code> HTH, Danny -------- Original Message -------- Subject: [Firebird-net-provider] Execute block... From: Adrián_Avila_Mtz.<adrian.avila....@gmail.com> Date: Sat, January 22, 2011 9:36 am To: "'For users and developers of the Firebird .NET providers'" <firebird-net-provider@lists.sourceforge.net> Is there a way to execute a block with FB data provider. ------------------------------------------------------------------------------ Special Offer-- Download ArcSight Logger for FREE (a $49 USD value)! Finally, a world-class log management solution at an even better price-free! Download using promo code Free_Logger_4_Dev2Dev. Offer expires February 28th, so secure your free ArcSight Logger TODAY! http://p.sf.net/sfu/arcsight-sfd2d _______________________________________________ Firebird-net-provider mailing list Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider ------------------------------------------------------------------------------ Special Offer-- Download ArcSight Logger for FREE (a $49 USD value)! Finally, a world-class log management solution at an even better price-free! Download using promo code Free_Logger_4_Dev2Dev. Offer expires February 28th, so secure your free ArcSight Logger TODAY! http://p.sf.net/sfu/arcsight-sfd2d _______________________________________________ Firebird-net-provider mailing list Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider WARNING: This email and any attachments may be confidential and subject to copyright. If you are not the intended recipient, you must not disclose or use the information contained in this email. If you have received this email in error, please notify the sender and permanently delete the email immediately. Linfox Pty Ltd and its related companies do not warrant that this email and any attachments are free of errors, viruses or tampering by third parties, and will not be responsible for loss or damage resulting (either directly or indirectly) from any such error, virus or tampering. If this is a commercial email within the meaning of the Spam Act, you may unsubscribe from future commercial emails by emailing unsubscr...@linfox.com ------------------------------------------------------------------------------ Special Offer-- Download ArcSight Logger for FREE (a $49 USD value)! Finally, a world-class log management solution at an even better price-free! Download using promo code Free_Logger_4_Dev2Dev. Offer expires February 28th, so secure your free ArcSight Logger TODAY! http://p.sf.net/sfu/arcsight-sfd2d _______________________________________________ Firebird-net-provider mailing list Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
<<attachment: danny.vcf>>
------------------------------------------------------------------------------ Special Offer-- Download ArcSight Logger for FREE (a $49 USD value)! Finally, a world-class log management solution at an even better price-free! Download using promo code Free_Logger_4_Dev2Dev. Offer expires February 28th, so secure your free ArcSight Logger TODAY! http://p.sf.net/sfu/arcsight-sfd2d
_______________________________________________ Firebird-net-provider mailing list Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
