Usman ur Rehman Ahmed's blog

Just a use of looping rocrds rather than using a cursor.

Increase Display Priority

This stored procedure will increase display priority field of provided 'project' plus all projects associated with this TabId and having display priority equivelent or greater than provided project.

create procedure {databaseOwner}{objectQualifier}IncreaseProjectDisplayPriority
    @TabId int,
    @ProjectFK int
as
    begin
            Declare @ToUpdate bit
            set @ToUpdate = 0
        -- Step 1: Retrieve display priority of this project and store that since that will be the last one to be updated

            declare @CurrentProjectDisplayPriority int
            select @CurrentProjectDisplayPriority = DisplayPriority from {objectQualifier}ProjectsPerTab where TabId = @TabId and ProjectFK = @ProjectFK

        -- Step 2: Loop through all records which have a display priority greater than this project's display priority
        -- and increment by one

            declare @pk int
            declare @DisplayPriority int

            -- Select minimum primary key for this tab with display priority greater than that of current project
            select @pk = min(pk) from {objectQualifier}ProjectsPerTab where TabId = @TabId and DisplayPriority > @CurrentProjectDisplayPriority
            select @DisplayPriority = DisplayPriority from {objectQualifier}ProjectsPerTab where pk = @pk

            while @pk is not null
            begin
                set @ToUpdate = 1
                -- if display priority is null, set it equivelent to zero (0)
                if @DisplayPriority is null
                    begin
                        set @DisplayPriority = 0
                    end

                -- Increment display priority by one
                set @DisplayPriority = @DisplayPriority + 1

                -- Update display priority
                update {objectQualifier}ProjectsPerTab set DisplayPriority = @DisplayPriority where pk = @pk

                -- Select next records for looping
                select @pk = min(pk) from {objectQualifier}ProjectsPerTab where pk > @pk AND TabId = @TabId
                select @DisplayPriority = DisplayPriority from {objectQualifier}tb_DNM_8_ProjectsPerTab where pk = @pk
            end

        -- Step 3: Increment display priority of this project by one (1)
        if @ToUpdate = 1
            begin
                set @CurrentProjectDisplayPriority = @CurrentProjectDisplayPriority + 1
                update {objectQualifier}ProjectsPerTab set DisplayPriority = @CurrentProjectDisplayPriority where TabId = @TabId and ProjectFK = @ProjectFK
            end
    end

GO

Decrease Display Priority

This stored procedure will decrease display priority field of provided 'project' plus all projects associated with this TabId and having display priority equivelent or less than provided project.

create procedure {databaseOwner}{objectQualifier}DecreaseProjectDisplayPriority
    @TabId int,
    @ProjectFK int
as
    begin
            Declare @ToUpdate bit
            set @ToUpdate = 0
        -- Step 1: Retrieve display priority of this project and store that since that will be the last one to be updated

            declare @CurrentProjectDisplayPriority int
            select @CurrentProjectDisplayPriority = DisplayPriority from {objectQualifier}ProjectsPerTab where TabId = @TabId and ProjectFK = @ProjectFK

        -- Step 2: Loop through all records which have a display priority less than this project's display priority
        -- and decrement by one

            declare @pk int
            declare @DisplayPriority int

            -- Select maximum primary key for this tab with display priority less than that of current project
            select @pk = max(pk) from {objectQualifier}ProjectsPerTab where TabId = @TabId and DisplayPriority < @CurrentProjectDisplayPriority
            select @DisplayPriority = DisplayPriority from {objectQualifier}ProjectsPerTab where pk = @pk

            while @pk is not null
            begin
                set @ToUpdate = 1
                -- if display priority is null, set it equivelent to zero (0)
                if @DisplayPriority is null
                    begin
                        set @DisplayPriority = 0
                    end

                -- Increment display priority by one
                set @DisplayPriority = @DisplayPriority - 1

                -- Update display priority
                update {objectQualifier}ProjectsPerTab set DisplayPriority = @DisplayPriority where pk = @pk

                -- Select next records for looping
                select @pk = max(pk) from {objectQualifier}ProjectsPerTab where pk < @pk AND TabId = @TabId
                select @DisplayPriority = DisplayPriority from {objectQualifier}ProjectsPerTab where pk = @pk
            end

        -- Step 3: Increment display priority of this project by one (1)
        if @ToUpdate = 1
            begin
                set @CurrentProjectDisplayPriority = @CurrentProjectDisplayPriority - 1
                update {objectQualifier}ProjectsPerTab set DisplayPriority = @CurrentProjectDisplayPriority where TabId = @TabId and ProjectFK = @ProjectFK
            end
    end

GO

I encountered this exception today while trying to read a data set from MySql database from C# .NET using MySql connector and enterprise library. Complete stack trace of this exception is below,

{MySql.Data.MySqlClient.MySqlException: The user specified as a definer ('root'@'%') does not exist

   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()

   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int32& insertedId)

   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int32& insertedId)

   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId)

   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()

   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)

   at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

   at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoLoadDataSet(IDbCommand command, DataSet dataSet, String[] tableNames)

   at Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String[] tableNames)

   at Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String tableName)

   at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteDataSet(DbCommand command)

 This happens when you try to make connection with MySql database from within .NET and is a privileges issues. For resolution, please follow these steps,

1- Run > Command Prompt

                Move to C:\Program Files\MySql\bin folder. Path for me appears as following,

       C:\Program Files\MySQL\MySQL Server 5.5\bin> 

2- Log into My Sql by exeucting the following command,

       mysql -u root -p

                You will be prompted for password.

       Enter password:

                On successfull password input you will log into mySql console,

                mysql> _

3- Grant All Privileges

                Execute following command under my sql console,

       grant all privileges on *.* to `root`@`%` identified by 'password';

                Upon successfull completion of command you will recieve confirmation simialr to this,

                Query OK, 0 rows affected (0.05 sec)

Note: Pay special heed to position of apostrophe vs. commas. Usernameis enclosed within apostrophy (`) where as password within commas (').

4- Flush Privileges

                Execute following command to ensure the privileges take affect,

       flush privileges;

                which will result in notification similar to this,

                Query OK, 0 rows affected (0.11 sec)

                You are done. Switch back to Visual Studio to test your settings are properly taking affect. To exit mysql console type exit and press enter. Above steps are summarized in figure below,

One of the good things I learnt while I was working over Hibernate in my early days with Java was to extensively use wrappers once you have fetched the required custom business objects from database. However on .NET platform, when I was trying to fetch a rather composite (with data from two tables) type from a stored procedure, I came across a problem, and intitutively, the solution that I came up with was far more elegent (atleast for myself) than other recomended on the web.You see, I had this table structure, and the output that I needed from the stored procedure can be well understood from,

ALTER procedure [dbo].[GetAllUserAnswersAgainstCategory]
    @CategoryId  int
    as
    BEGIN
        SET NOCOUNT ON;
        select a.AnswerId,a.FUID,a.PostedDate,c.Answer
        from dbo.UserAnswers as a, dbo.tblCategoryAnswers as c
            where  a.CategoryId = @CategoryId and a.AnswerId = c.AnswerId
                order by a.PostedDate descEND

Now when I compiled my class library containing the dbml file, the output function yielded by Visual Studio was returning an Integer value.

That was quite an expected behavior from Visual Studio.

    <FunctionAttribute(Name:="dbo.GetAllUserAnswersAgainstCategory")>
    _GetAllUserAnswersAgainstCategory(<Parameter(Name:="CategoryId", DbType:="Int")> 
        ByVal categoryId As System.Nullable(Of Integer)) As Integer
        Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod, MethodInfo), categoryId)
        Return CType(result.ReturnValue, Integer)
    End Function

So what I did was, I changed the return type of function to this,

    <FunctionAttribute(Name:="dbo.GetAllUserAnswersAgainstCategory")>
    _Public Function GetAllUserAnswersAgainstCategory(<Parameter(Name:="CategoryId", DbType:="Int&amp;amp")>
        ByVal categoryId As System.Nullable(Of Integer)) As ISingleResult(Of GetAllUserAnswersAgainstCategoryResult)
        Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod, MethodInfo), categoryId)
        Return CType(result.ReturnValue, ISingleResult(Of GetAllUserAnswersAgainstCategoryResult))
    End Function

and declared a class manually inside the .dbml designer file that exhibits the values returned from my stored procedure.

Partial Public Class GetAllUserAnswersAgainstCategoryResult   

    Private _AnswerId As Integer   
    Private _Answer As String   
    Private _FUID As Integer   
    Private _PostedDate As System.Nullable(Of Date)   

    Public Sub New()       
        MyBase.New()   
    End Sub   

    <Column(Storage:="_AnswerId", DbType:="Int NOT NULL")> _   
    Public Property AnswerId() As Integer       
        Get           
            Return Me._AnswerId
            End Get
            Set(ByVal value As Integer)           
            If ((Me._AnswerId = value) _ = False) Then
                        Me._AnswerId = value
                End If
           End Set   
    End Property   

    <Column(Storage:="_Answer", DbType:="VarChar(1024) NOT NULL", CanBeNull:=False)> _   
    Public Property Answer() As String
        Get           
            Return Me._Answer       
        End Get       

        Set(ByVal value As String)           
            If (String.Equals(Me._Answer, value) = False) Then               
                Me._Answer = value           
            End If       
        End Set   
    End Property   

    <Column(Storage:="_FUID", DbType:="Int NOT NULL")> _   
    Public Property FUID() As Integer       
        Get           
            Return Me._FUID       
        End Get       
        Set(ByVal value As Integer)           
            If ((Me._FUID = value) _ = False) Then
                        Me._FUID = value           
            End If           End Set   
    End Property   

    <Column(Storage:="_PostedDate", DbType:="DateTime")> _   
    Public Property PostedDate() As System.Nullable(Of Date)       
        Get
            Return Me._PostedDate       
        End Get

        Set(ByVal value As System.Nullable(Of Date)) 
            If (Me._PostedDate.Equals(value) = False) Then
                Me._PostedDate = value
            End If       
        End Set   
    End Property
End Class

Compiled and I was getting the required values from my composite type or wrapper class upon each iteration.