none
Application Roles in Entity Framework RRS feed

  • Question

  • How do I call sp_setapprole stored procedure with the entity framework?   using AD groups for readonly access and application role enables insert and update permissions to users.  Need to activate app role. 
    Sunday, February 21, 2010 2:05 AM

Answers

  • Hello,

     

    Welcome to ADO.NET Entity Framework and LINQ to Entities forum!

     

    Since sp_setapprole is a system stored procedure, we cannot add it into our Entity Data Model.  However, we can directly call this stored procedures via EntityConnection and EntityCommand.  For detail, please see the following sample codes:

    ========================================================================

                MyObjectContext c = new MyObjectContext();

                var entityConnection = (System.Data.EntityClient.EntityConnection)c.Connection;

                DbConnection conn = entityConnection.StoreConnection;

                ConnectionState initialState = conn.State;

                try

                {

                    if (initialState != ConnectionState.Open)

                        conn.Open();

                    using (DbCommand cmd = conn.CreateCommand())

                    {

                        cmd.CommandText = "sp_setapprole";

                        cmd.Parameters.Add(new SqlParameter("@rolename", "MyAppRole"));

                        cmd.Parameters.Add(new SqlParameter("@password", "MyPassword"));

                        cmd.CommandType = CommandType.StoredProcedure;

                        int i = cmd.ExecuteNonQuery();

                    }

                }

                catch (Exception ex)

                {

                    Console.WriteLine(ex);

                }

                finally

                {

                    if (initialState != ConnectionState.Open)

                        conn.Close();

                }

    ========================================================================

     

    If you have any questions, please feel free to let me know. 

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by tbradner Monday, February 22, 2010 9:49 PM
    Monday, February 22, 2010 5:18 AM
    Moderator

All replies

  • Hello,

     

    Welcome to ADO.NET Entity Framework and LINQ to Entities forum!

     

    Since sp_setapprole is a system stored procedure, we cannot add it into our Entity Data Model.  However, we can directly call this stored procedures via EntityConnection and EntityCommand.  For detail, please see the following sample codes:

    ========================================================================

                MyObjectContext c = new MyObjectContext();

                var entityConnection = (System.Data.EntityClient.EntityConnection)c.Connection;

                DbConnection conn = entityConnection.StoreConnection;

                ConnectionState initialState = conn.State;

                try

                {

                    if (initialState != ConnectionState.Open)

                        conn.Open();

                    using (DbCommand cmd = conn.CreateCommand())

                    {

                        cmd.CommandText = "sp_setapprole";

                        cmd.Parameters.Add(new SqlParameter("@rolename", "MyAppRole"));

                        cmd.Parameters.Add(new SqlParameter("@password", "MyPassword"));

                        cmd.CommandType = CommandType.StoredProcedure;

                        int i = cmd.ExecuteNonQuery();

                    }

                }

                catch (Exception ex)

                {

                    Console.WriteLine(ex);

                }

                finally

                {

                    if (initialState != ConnectionState.Open)

                        conn.Close();

                }

    ========================================================================

     

    If you have any questions, please feel free to let me know. 

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by tbradner Monday, February 22, 2010 9:49 PM
    Monday, February 22, 2010 5:18 AM
    Moderator
  • What is MyObjectContext?  Is it a reference to a library? 
    Monday, February 22, 2010 4:17 PM
  • Hello,

     

    Thank you very much for marking my reply as the answer.  It is your ObjectContext class for certain Entity Data Model, not a library. 

     

    If you have any questions, please feel free to let me know. 

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, February 23, 2010 8:25 AM
    Moderator
  • Application Role is fixed. However, now I get when I go to query the database I get an error on the query.tolist() function.  I do not know why that would be.  I query it once for foreign key records once but the subsequent query for other records throws and exception.  Any idea why this would be?

    program flow

    Initialize Entities  - no problem
    SetAppRole - no problem
    LoadARecords() - no problem
    LoadBRecords() - exception

    both functions do the same thing.  They query the db for foreign keys.
    Friday, February 26, 2010 4:58 PM
  • Imports

     

    System.Data.Objects

    Imports

     

    System.Data

    Imports

     

    System.Data.Common

    Imports

     

    System.Data.EntityClient

    Imports

     

    System.Data.SqlClient

    Imports

     

    System.Linq

     

    Public

     

    Class Form1

     

    Private _entities As AdventureWorksEntities

     

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    _entities =

    New AdventureWorksEntities

    _entities.SaveChanges()

    SetAppRole()

     

    Dim lst As New List(Of Contact)

     

    Dim query As IQueryable(Of Contact) = From t In _entities.Contact _

     

    Where t.ContactID = 5 _

     

    Select t

    lst = query.ToList

    _entities.SaveChanges()

     

    Dim cont As Contact = lst.First

    _entities.DeleteObject(cont)

    _entities.SaveChanges()

    _entities.AddToContact(cont)

    _entities.SaveChanges()

     

    End Sub

     

    Private Sub SetAppRole()

     

    Dim c As ObjectContext = New ObjectContext(_entities.Connection)

     

    Dim entityConnection = CType(c.Connection, System.Data.EntityClient.EntityConnection)

     

    Dim conn As DbConnection = entityConnection.StoreConnection

     

    Dim initialState As ConnectionState = conn.State

     

    Try

     

     

     

    If (initialState <> ConnectionState.Open) Then

    conn.Open()

     

    End If

     

    Using cmd As DbCommand = conn.CreateCommand()

     

     

    cmd.CommandText =

    "sp_setapprole"

    cmd.Parameters.Add(

    New SqlParameter("@rolename", "AwAppRole"))

    cmd.Parameters.Add(

    New SqlParameter("@password", "password"))

    cmd.CommandType = CommandType.StoredProcedure

     

    Dim i As Integer = cmd.ExecuteScalar()

     

    End Using

     

     

     

     

    Catch ex As Exception

     

     

    Console.WriteLine(ex)

     

     

     

    Finally

     

     

     

    If (initialState <> ConnectionState.Open) Then

    conn.Close()

     

    End If

     

     

     

    ' cmd.CommandText = "EXEC dbo.sp_setapprole '','Letmein2'"

     

    End Try

     

    End Sub

     

    ' AddHandler thisConnection.StateChange, AddressOf StateIsChanged

    End

     

    Class













    The exception comes in the query.ToList() but if you wait a minute it will not throw an exception.  Can somebody help with this?
    Saturday, February 27, 2010 4:05 AM
  • Hello,

     

    Since the new question is not related to the original one, could you please open a new thread to discuss it to benefit more community members?   Meanwhile, more community members can see your new threads and provide their ideas.    Please tell me the new thread link so that we can do some further discussion.   Thanks a lot! 

     

    Have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Saturday, February 27, 2010 4:39 AM
    Moderator
  • If I used the instance of a entity framework I have already created and passed the connection from it will it work the same as the Object Context?
    Monday, March 1, 2010 1:37 PM
  • Hi, I'm trying to use application roles with an Entity Famework project - where exactly do I activate the approle?

    ie sample code:

    Public

    ClassForm

    Dimdb AsNewMyEntities

    PrivateSubButton1_Click(sender AsSystem.Object, e AsSystem.EventArgs) HandlesButton1.Clic

        

    ' setup approle


            db.ExecuteStoreCommand(

    "sp_setapprole @rolename={0}, @password={1}", "mytest", "pass1234")

           

    Dimt AsNewmyData


            t.field1=

    "test"


            t.field2=

    "test2"


            db.tblTest.AddObject(t)

            db.SaveChanges()

    Thursday, March 10, 2016 8:45 PM
  • Please don't reply to old threads.  Put your question in a new thread.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, March 10, 2016 9:22 PM
  • Also learn how to use the insert code block the 2nd icon on the reply tool bar from far right. It is also there when you create a new post.
    Friday, March 11, 2016 12:24 AM