Named DataSets with Grids, How to enable a SqlServer Application role

22 Nov 2007 11:11 AM

Harry Leboeuf

Hello,

I'm a novice in .Net development (switched after 10 years Delphi).
I've created a small test app with a Dataset in it.
The Dataset contains a TableAdapter.
In my Form i'm using a grid that uses that named dataset to display an
update the table.

Now i would like to enable a application role so that regular users in the
real environment could access the data.
We always work by Application roles.

I've found some examples on how to use the 'sp_setapprole' but in this kind
of development i don't have a connection created by myself.
It is all in the generated code van VisStud.

What is the way or where is the event i could hook into to enable the role
....

Kind Regards ....

23 Nov 2007 7:19 AM

WenYuan Wang [MSFT]

Hello Harry,

It sounds like you'd like get table from SQLDatabase with the permissions
associated with an application role , correct? I'm not sure what you mean
by "Named DataSet". (I assume it is "Strong Typed DataSet" which is
generated by VS 2005.) If I misunderstood anything here, please correct me.

If this is the case, my suggestion is to check Connection.StateChange
event.We can call setapprole function after connection openning.
For example: If the connection state is changed from "Close" to "Open", I
actives application role.

#1--------------------------------------------------------------------------
--------------
DataSet1TableAdapters.Table_1TableAdapter tta = new
ConsoleApplication61.DataSet1TableAdapters.Table_1TableAdapter();
tta.Connection.StateChange += new
System.Data.StateChangeEventHandler(Connection_StateChange);
DataSet1 ds = new DataSet1();
tta.Fill(ds.Table_1);

void Connection_StateChange(object sender, System.Data.StateChangeEventArgs
e)
{
            if (e.OriginalState == System.Data.ConnectionState.Closed &&
e.CurrentState == System.Data.ConnectionState.Open)
            {
                System.Data.SqlClient.SqlCommand sc = new
System.Data.SqlClient.SqlCommand();
                sc.Connection = (System.Data.SqlClient.SqlConnection)sender;
                sc.CommandText = @"EXEC sp_setapprole 'weekly_receipts',
'987G^bv876sPY)Y5m23'";
                sc.ExecuteNonQuery();
            }
}
----------------------------------------------------------------------------
------------

In addition, we can add this method in partical class. Thereby, we needn't
add this event each time.
For example:

#2--------------------------------------------------------------------------
--------------
DataSet1TableAdapters.Table_1TableAdapter tta = new
ConsoleApplication61.DataSet1TableAdapters.Table_1TableAdapter();
tta.setapprole();
DataSet1 ds = new DataSet1();
tta.Fill(ds.Table_1);

namespace ConsoleApplication61.DataSet1TableAdapters
{
    partial class Table_1TableAdapter
    {
        public void setapprole()
        {
            Connection.StateChange += new
System.Data.StateChangeEventHandler(Connection_StateChange);
        }

        void Connection_StateChange(object sender,
System.Data.StateChangeEventArgs e)
        {
            if (e.OriginalState == System.Data.ConnectionState.Closed &&
e.CurrentState == System.Data.ConnectionState.Open)
            {
                System.Data.SqlClient.SqlCommand sc = new
System.Data.SqlClient.SqlCommand();
                sc.Connection = (System.Data.SqlClient.SqlConnection)sender;
                sc.CommandText = @"EXEC sp_setapprole 'weekly_receipts',
'987G^bv876sPY)Y5m23'";
                sc.ExecuteNonQuery();
            }
        }
    }
}
----------------------------------------------------------------------------
------------

Hope this helps. Please feel free to update here again, if you have any
more concern.We are glad to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

28 Nov 2007 3:36 PM

Harry Leboeuf

Thx,

But, where should I add this code, the only place where i can find this code
is in the designer and then in de 'Windows Form Designer generated code'.

May I change data/code in that part ??

Show quote

"WenYuan Wang [MSFT]" <v-wyw***@online.microsoft.com> wrote in message
news:7gvxCFaLIHA.5204@TK2MSFTNGHUB02.phx.gbl...
> Hello Harry,
>
> It sounds like you'd like get table from SQLDatabase with the permissions
> associated with an application role , correct? I'm not sure what you mean
> by "Named DataSet". (I assume it is "Strong Typed DataSet" which is
> generated by VS 2005.) If I misunderstood anything here, please correct
> me.
>
> If this is the case, my suggestion is to check Connection.StateChange
> event.We can call setapprole function after connection openning.
> For example: If the connection state is changed from "Close" to "Open", I
> actives application role.
>
> #1--------------------------------------------------------------------------
> --------------
> DataSet1TableAdapters.Table_1TableAdapter tta = new
> ConsoleApplication61.DataSet1TableAdapters.Table_1TableAdapter();
> tta.Connection.StateChange += new
> System.Data.StateChangeEventHandler(Connection_StateChange);
> DataSet1 ds = new DataSet1();
> tta.Fill(ds.Table_1);
>
> void Connection_StateChange(object sender,
> System.Data.StateChangeEventArgs
> e)
> {
>            if (e.OriginalState == System.Data.ConnectionState.Closed &&
> e.CurrentState == System.Data.ConnectionState.Open)
>            {
>                System.Data.SqlClient.SqlCommand sc = new
> System.Data.SqlClient.SqlCommand();
>                sc.Connection =
> (System.Data.SqlClient.SqlConnection)sender;
>                sc.CommandText = @"EXEC sp_setapprole 'weekly_receipts',
> '987G^bv876sPY)Y5m23'";
>                sc.ExecuteNonQuery();
>            }
> }
> ----------------------------------------------------------------------------
> ------------
>
> In addition, we can add this method in partical class. Thereby, we needn't
> add this event each time.
> For example:
>
> #2--------------------------------------------------------------------------
> --------------
> DataSet1TableAdapters.Table_1TableAdapter tta = new
> ConsoleApplication61.DataSet1TableAdapters.Table_1TableAdapter();
> tta.setapprole();
> DataSet1 ds = new DataSet1();
> tta.Fill(ds.Table_1);
>
> namespace ConsoleApplication61.DataSet1TableAdapters
> {
>    partial class Table_1TableAdapter
>    {
>        public void setapprole()
>        {
>            Connection.StateChange += new
> System.Data.StateChangeEventHandler(Connection_StateChange);
>        }
>
>        void Connection_StateChange(object sender,
> System.Data.StateChangeEventArgs e)
>        {
>            if (e.OriginalState == System.Data.ConnectionState.Closed &&
> e.CurrentState == System.Data.ConnectionState.Open)
>            {
>                System.Data.SqlClient.SqlCommand sc = new
> System.Data.SqlClient.SqlCommand();
>                sc.Connection =
> (System.Data.SqlClient.SqlConnection)sender;
>                sc.CommandText = @"EXEC sp_setapprole 'weekly_receipts',
> '987G^bv876sPY)Y5m23'";
>                sc.ExecuteNonQuery();
>            }
>        }
>    }
> }
> ----------------------------------------------------------------------------
> ------------
>
> Hope this helps. Please feel free to update here again, if you have any
> more concern.We are glad to assist you.
>
> Have a great day,
> Best regards,
>
> Wen Yuan
> Microsoft Online Community Support
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>