C# – How to get the generated id from an inserted row using ExecuteScalar

ado.netcoracle

I know that in Oracle I can get the generated id (or any other column) from an inserted row as an output parameter.
Ex:

insert into foo values('foo','bar') returning id into :myOutputParameter

Is there a way to do the same, but using ExecuteScalar instead of ExecuteNonQuery?

I don't want to use output parameters or stored procedures.

ps: I'm using Oracle, not sql server!!!

Best Answer

If you are on oracle, you have to use ExecuteNonQuery and ResultParameter. There is no way to write this as query.

using (OracleCommand cmd = con.CreateCommand()) {
    cmd.CommandText = "insert into foo values('foo','bar') returning id into :myOutputParameter";
    cmd.Parameters.Add(new OracleParameter("myOutputParameter", OracleDbType.Decimal), ParameterDirection.ReturnValue);
    cmd.ExecuteNonQuery(); // an INSERT is always a Non Query
    return Convert.ToDecimal(cmd.Parameters["myOutputParameter"].Value);
}

Related Solutions

C# – How to get the index of the current iteration of a foreach loop

Ian Mercer posted a similar solution as this on Phil Haack's blog:

foreach (var item in Model.Select((value, i) => new { i, value }))
{
    var value = item.value;
    var index = item.i;
}

This gets you the item (item.value) and its index (item.i) by using this overload of LINQ's Select:

the second parameter of the function [inside Select] represents the index of the source element.

The new { i, value } is creating a new anonymous object.

Heap allocations can be avoided by using ValueTuple if you're using C# 7.0 or later:

foreach (var item in Model.Select((value, i) => ( value, i )))
{
    var value = item.value;
    var index = item.i;
}

You can also eliminate the item. by using automatic destructuring:

<ol>
foreach ((MyType value, Int32 i) in Model.Select((value, i) => ( value, i )))
{
    <li id="item_@i">@value</li>
}
</ol>

C# – Should ‘using’ directives be inside or outside the namespace

There is actually a (subtle) difference between the two. Imagine you have the following code in File1.cs:

// File1.cs
using System;
namespace Outer.Inner
{
    class Foo
    {
        static void Bar()
        {
            double d = Math.PI;
        }
    }
}

Now imagine that someone adds another file (File2.cs) to the project that looks like this:

// File2.cs
namespace Outer
{
    class Math
    {
    }
}

The compiler searches Outer before looking at those using directives outside the namespace, so it finds Outer.Math instead of System.Math. Unfortunately (or perhaps fortunately?), Outer.Math has no PI member, so File1 is now broken.

This changes if you put the using inside your namespace declaration, as follows:

// File1b.cs
namespace Outer.Inner
{
    using System;
    class Foo
    {
        static void Bar()
        {
            double d = Math.PI;
        }
    }
}

Now the compiler searches System before searching Outer, finds System.Math, and all is well.

Some would argue that Math might be a bad name for a user-defined class, since there's already one in System; the point here is just that there is a difference, and it affects the maintainability of your code.

It's also interesting to note what happens if Foo is in namespace Outer, rather than Outer.Inner. In that case, adding Outer.Math in File2 breaks File1 regardless of where the using goes. This implies that the compiler searches the innermost enclosing namespace before it looks at any using directive.

Best Answer

Related Solutions

C# – How to get the index of the current iteration of a foreach loop

C# – Should ‘using’ directives be inside or outside the namespace

Related Topic