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

  • Thread starter Thread starter Harry Leboeuf
  • Start date Start date
H

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 ....
 
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.
 
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 ??
 
Hello Harry,
Thanks for your reply.

In DataSet Designer interface, please double-click the design surface in an
empty area. VS 2005 IDE will open the dataset's partial class file in the
code editor. You can add code into this dataset's partial class file

Partial class is a new feature in C# 2.0. This feature allows code for a
specific class to be divided among multiple physical files. If you are
interested in Partial class, you may refer to
http://msdn2.microsoft.com/en-us/library/wa80x488(VS.80).aspx
[Partial Class Definitions (C# Programming Guide)]

Regarding how to extend the functionality of Typed Dataset, you may refer
to
http://msdn2.microsoft.com/en-us/library/ms171896(VS.80).aspx
[How to: Extend the Functionality of a Dataset ]

Please note: We can only define new data/code in this part. If the code has
been defined in dataset.designer.cs(which is generated by VS IDE), it's
impossible for us to modify it in this part. Thanks.

Hope this helps. Please feel free to update here again, if there is
anything unclear. It's my pleasure 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.
 
Back
Top