if the with keyword is bad (for C#), is this a nice alternative?

Question

var insInvoice = new NpgsqlCommand(
 @"INSERT INTO invoice_detail(
 invoice_id,
 invoice_detail_id,
 product_id,
 qty,
 price,
 amount)
 VALUES (
 :_invoice_id,
 :_invoice_detail_id,
 :_product_id,
 :_qty,
 :_price,
 :_qty * :_price)", c);


with(var p = insInvoice.Parameters)
{
 p.Add("_invoice_id", NpgsqlDbType.Uuid, 0, "invoice_id");
 p.Add("_invoice_detail_id", NpgsqlDbType.Uuid, 0, "invoice_detail_id");
 p.Add("_product_id", NpgsqlDbType.Uuid, 0, "product_id");
 p.Add("_qty", NpgsqlDbType.Integer, 0, "qty");
 p.Add("_price", NpgsqlDbType.Numeric, 0, "price");
}

---

kludge:

for(var p = insInvoice.Parameters; false;)
{       
 p.Add("_invoice_id", NpgsqlDbType.Uuid, 0, "invoice_id");
 p.Add("_invoice_detail_id", NpgsqlDbType.Uuid, 0, "invoice_detail_id");
 p.Add("_product_id", NpgsqlDbType.Uuid, 0, "product_id");
 p.Add("_qty", NpgsqlDbType.Integer, 0, "qty");
 p.Add("_price", NpgsqlDbType.Numeric, 0, "price");  
}

Answer 1

I have done something similar:

var insInvoice = new NpgsqlCommand(...);
insInvoice.Parameters.With(p => {
    p.Add("_invoice_id", NpgsqlDbType.Uuid, 0, "invoice_id");
    ...
});

Answer 2

What's the point of it, to scope the "p" variable?

In that case just create a scope with curly braces and remove the with-statement all together, move the variable declaration of "p" into the new scope. This way the "p" variable is only available within the scope of the curly braces. This you can do today without needing any syntactic sugar. I'm not sure this helps in readability though, just remove the braces all together and go with just the variable.

//with(var p = insInvoice.Parameters)
{
  var p = insInvoice.Parameters;
  p.Add("_invoice_id", NpgsqlDbType.Uuid, 0, "invoice_id");
  p.Add("_invoice_detail_id", NpgsqlDbType.Uuid, 0, "invoice_detail_id");
  p.Add("_product_id", NpgsqlDbType.Uuid, 0, "product_id");
  p.Add("_qty", NpgsqlDbType.Integer, 0, "qty");
  p.Add("_price", NpgsqlDbType.Numeric, 0, "price");
}

Answer 3

As you've got an "Add" method with the right form, you can use a collection initialiser for the parameters:

var insInvoice = new NpgsqlCommand(sql)
{
    Parameters = 
    {
        { "_invoice_id", NpgsqlDbType.Uuid, 0, "invoice_id" },
        { "_invoice_detail_id", NpgsqlDbType.Uuid, 0, "invoice_detail_id" },
        { "_qty", NpgsqlDbType.Integer, 0, "qty" },
        { "_price", NpgsqlDbType.Numeric, 0, "price" }
    }
};

For more on object and collection initializers, you can download chapter 8 of C# in Depth for free.

Answer 4

If you're really interested in reducing your code down to something more readable by removing the noise and "ceremony" from it, there are bigger fish to fry in your example.

In this case, using a combination of extension methods, some static readonly metadata, params arrays, and so on, you should be able to auto-generate the SQL for that insert statement rather than having to code it by hand. Why should any given insert look more complex than this:

db.Insert(Invoices.Table,
      new Value(Invoices.ID, "{something-something}"),
      new Value(Invoices.Quantity, 52),
      /*... and so on*/);

No need to write a big SQL insert by hand. Parameter names chosen automatically based on column names. Name type and size information all bound together in those static members of Invoices so you can't get them wrong. Names and values specified next to each other at the point of the call. All this could be yours!

Optionally, declare the structure of your database in some neat XML structure, and write a tool which generates two things from that XML: 1. the DDL to set up the database and 2. the C# metadata for every table and its columns. Now to extend the DB structure, just edit the XML and re-run the tool, and it gives you instant easy access to the schema from your code.

Before you know it, you've got your own cheap-and-cheerful budget version of Linq to SQL!

(Obviously this has strayed way off-topic, but why not treat yourself to an easy way of writing insert statements this Christmas?)