Hi Danny,
Just to clarify I mean using an Fbcommand object , setting it up with the
parameters and then getting the SQL you would run out of the command.
Because I'm doing this multiple times with different calls that I'm including
in the execute block (and because I'm using data from a DataTable), I have it
setup in a more generic way:
StringBuilder batchSQL = new StringBuilder();
string execProcedure = "EXECUTE PROCEDURE EXAMPLE_" + source.TableName + "(";
DataColumnCollection columnDef = source.Columns;
foreach (DataRow row in source.Rows)
{
batchSQL.Append(execProcedure);
int count = 0;
foreach (string columnName in columnNames)
{
if (count++ != 0) // comma between columns.
{
batchSQL = batchSQL.Append(",");
}
string escapedData = null;
if (columnDef[columnName].DataType ==
Type.GetType("System.DateTime"))
{
escapedData = "'" +
((DateTime)row[columnName]).ToString("yyyy-MM-dd HH:mm:ss") + "'";
}
else if (columnDef[columnName].DataType ==
Type.GetType("System.String"))
{
escapedData = row[columnName].ToString();
if (escapedData != "")
{
escapedData = escapedData.Replace("'", "''");
escapedData = "'" + escapedData + "'";
}
}
else // Covers Boolean, Byte, Int16, Int64, Decimal.
{
escapedData = row[columnName].ToString();
}
// handle NULLs
if (escapedData == "")
{
batchSQL = batchSQL.Append("NULL");
}
else
{
batchSQL = batchSQL.Append(escapedData);
}
}
batchSQL.Append(");\r\n");
}
return batchSQL.ToString();
If I needed to do more with it I'd clean it up and make it more generic again,
but it works fine for what I need to do
Cheers,
Matt
-----Original Message-----
From: Danny Gorton [mailto:da...@absolutepowerandcontrol.com]
Sent: Friday, 28 January 2011 5:42 AM
To: For users and developers of the Firebird .NET providers
Cc: Matthew Parslow
Subject: Re: [Firebird-net-provider] Execute block...
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
>
>
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
