Database

Tunisiano32 wrote:

How can you add transaction functionality to your stored procedure without
locking anything ( I know there is different isolation level, but I'm not sure
which one to use )

Tunisiano32 wrote:

I need to use transaction just for the sake of rolling back the changes if there
was an exception in the middle of the procedure. so I really don't care about
locking tables. which isolation level should I use.

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]

static class Extensions
{
    public static IEnumerable<T> TakeWindow<T>(this IEnumerable<T> source, Func<T, bool> pivot, int numberBefore, int numberAfter)
    {
        if (source == null) throw new ArgumentNullException("source");
        if (pivot == null) throw new ArgumentNullException("pivot");
        if (numberBefore < 0) throw new ArgumentOutOfRangeException("numberBefore");
        if (numberAfter < 0) throw new ArgumentOutOfRangeException("numberAfter");

        if (numberBefore == 0)
        {
            return source.SkipWhile(x => !pivot(x)).Take(1 + numberAfter);
        }

        return TakeWindowIterator(source, pivot, numberBefore, numberAfter);
    }

    private static IEnumerable<T> TakeWindowIterator<T>(IEnumerable<T> source, Func<T, bool> pivot, int numberBefore, int numberAfter)
    {
        var queue = new Queue<T>(numberBefore);

        foreach (T item in source)
        {
            if (queue != null && pivot(item))
            {
                foreach (T previous in queue)
                {
                    yield return previous;
                }

                queue = null;

                yield return item;
            }
            else if (queue == null)
            {
                if (numberAfter == 0)
                {
                    yield break;
                }

                yield return item;
                numberAfter--;
            }
            else
            {
                if (queue.Count == numberBefore)
                {
                    queue.Dequeue();
                }

                queue.Enqueue(item);
            }
        }
    }
}

Module Extensions
    <System.Runtime.CompilerServices.Extension>
    Public Function TakeWindow(Of T)(ByVal source As IEnumerable(Of T), ByVal pivot As Func(Of T, Boolean), ByVal numberBefore As Integer, ByVal numberAfter As Integer) As IEnumerable(Of T)
        If source Is Nothing Then Throw New ArgumentNullException("source")
        If pivot Is Nothing Then Throw New ArgumentNullException("pivot")
        If numberBefore < 0 Then Throw New ArgumentOutOfRangeException("numberBefore")
        If numberAfter < 0 Then Throw New ArgumentOutOfRangeException("numberAfter")
        
        If numberBefore = 0 Then
            return source.SkipWhile(Function (x) Not pivot(x)).Take(1 + numberAfter)
        End If
        
        Return TakeWindowIterator(source, pivot, numberBefore, numberAfter)
    End Function
    
    Private Iterator Function TakeWindowIterator(Of T)(ByVal source As IEnumerable(Of T), ByVal pivot As Func(Of T, Boolean), ByVal numberBefore As Integer, ByVal numberAfter As Integer) As IEnumerable(Of T)
        Dim queue As New Queue(Of T)(numberBefore)
        
        For Each item As T In source
            If queue IsNot Nothing AndAlso pivot(item) Then
                For Each previous As T in queue
                    Yield previous
                Next
                
                queue = Nothing
                
                Yield item
            
            ElseIf queue Is Nothing Then
                If numberAfter = 0 Then
                    Return
                End If
                
                Yield item
                numberAfter -= 1
                
            Else
                If queue.Count = numberBefore Then
                    queue.Dequeue()
                End If
                
                queue.Enqueue(item)
            End If
        Next
    End Function
End Module

Dim p_partNumber As String = "06-SM4" 

Using context As New DBcontext
    Dim orders = context.ProductInfo
        .OrderBy(Function(p) p.PartNumber)
        .TakeWindow(Function(p) p.PartNumber = p_partNumber, 4, 3)

    orders.Dump()
End Using

jkirkerx wrote:

Did you write that?

jkirkerx wrote:

it's pretty elegant and wild in how it works.

' Old:
' If pivot(item) Then

' New:
If queue IsNot Nothing AndAlso pivot(item) Then

Update TableName set field1 = ValueFromExcelCell, field2 = ... where PrimareyKeyField = PrimaryKeyValueFromExcel