Database

2-Apr-06 21:02

Hi all!!

I need some advise please. I have a stored procedure that adds a member to tblMember, and in the same stored procedure I add the member ID and role ID to the tblMemberInRoles table. Now what happens if the member, for example, has more than one role? How do I pass this "array of roles" to the stored procedure?

How would the C# code look like when passing these parameters? I am using framework 2.0 and SQL Server 2005.

Is this the correct waty to do it? Or is there a different way that you guys would do it? Please let me know!!

Any help or advise is appreciated!!

Cheers.
ma se

Colin Angus Mackay2-Apr-06 23:18

2-Apr-06 23:18

Two ways I can think of... [1] Call the stored procedure multiple times for each role (or create a separate SP for adding the roles) or [2] pass the role names in as a comma separated string and parse the string in the stored procedure.

There may be a third possibility but I've not had a chance to really look at it and that is passing an XML fragment into the stored procedure. SQL Server 2005 has better XML processing capabilities than SQL Server 2000 and that may be a possibility. However, I've not really looked at the XML capabilities of SQL Server 2005 yet, so I cannot say for certain.

ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?

Brendan Vogt3-Apr-06 0:57

3-Apr-06 0:57

Thanks for the reply..

What I would've liked to have done was to add the member, return the new member ID, and loop through the roles array and just add the role ID and member ID to the MemberInRoles tables (by calling another stored procedure to add the roles), but I don't think you can loop in a stored procedure.

Can you maybe help to write some code to parse the comma-separated string, loop through the contents and call the sp_MemberInRoles_InsertMemberRole stored. But there can be 1 role per member, 5 roles, etc, you can't have a fixed loop, it differs.

Regards,
ma se

Colin Angus Mackay3-Apr-06 2:11

3-Apr-06 2:11

ma se wrote:
but I don't think you can loop in a stored procedure

Of course you can loop in a stored procedure. You can use WHILE[^]

DECLARE @remainingRoles NVARCHAR(4000);
DECLARE @commaIndex INT;
DECLARE @currentRole SYSNAME;
SET @remainingRoles = @roles; -- @roles == the value sent into the stored proc
SET @commaIndex = CHARINDEX(',',@remainingRoles);
WHILE(@commaIndex <> 0)
BEGIN
    SET @currentRole = LEFT(@remainingRoles, @commaIndex);
    -- Do stuff with the current role here!
    SET @remainingRoles = RIGHT(@remainingRoles, @commaIndex + 1);
END
IF LEN(@remainingRoles) <> 0
BEGIN
    -- Do stuff with the remaining role - Same code as above, but substituting @currentRole
    -- with @remainingRoles
END

DISCLAIMER: The above has not been tested or optimised. E&:OE

ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?

Brendan Vogt4-Apr-06 4:14

4-Apr-06 4:14

Hi Colin,

I managed to work through your code, and struggled a bit. Your RIGHT statement is incorrect. The input @Role value is "AnotherTestAdmin,Member,SystemAdmin,TestAdminn".

My changed code is (PLEASE FEEL FREE TO COMMENT AND CHANGE AS PLEASED), not sure if I'm using the variables in the right places??

ALTER PROCEDURE [dbo].[sp_Member_InsertMember]
(
@Roles nvarchar(200)
)

AS

BEGIN

DECLARE @RemainingRoles nvarchar(4000);
SET @RemainingRoles = @Roles;

DECLARE @CommaIndex int;
SET @CommaIndex = CHARINDEX(',', @RemainingRoles);

DECLARE @CurrentRole sysname;

WHILE(@CommaIndex <> 0)
BEGIN
SET @CurrentRole = LEFT(@RemainingRoles, @CommaIndex - 1);
PRINT @CurrentRole;
SET @RemainingRoles = RIGHT(@RemainingRoles, LEN(@RemainingRoles) - @CommaIndex);
SET @CommaIndex = CHARINDEX(',', @RemainingRoles);
END

IF LEN(@RemainingRoles) <> 0
PRINT @RemainingRoles

END

Not sure what you mean by:
-- Do stuff with the remaining role - Same code as above, but substituting @currentRole
-- with @remainingRoles

And why do you have the following declaration?
DECLARE @currentRole SYSNAME;

Hope you can shead some more light??

Thanks
ma se

Colin Angus Mackay4-Apr-06 5:21

4-Apr-06 5:21

ma se wrote:
Not sure what you mean by:
-- Do stuff with the remaining role - Same code as above, but substituting @currentRole
-- with @remainingRoles

Where you have put the PRINT statements is where you need to put your code that deals with the roles - e.g. insert them into your table. In the loop the variable that contains the role name is @CurrentRole, after the loop the variable that contains the last role is @RemainingRoles.

ma se wrote:
And why do you have the following declaration?
DECLARE @currentRole SYSNAME;

Force of habit. SQL Server defines a role name as sysname when it is stored internally: sysusers table[^]

SYSNAME is a synonym for NVARCHAR(128)

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog

Brendan Vogt10-Apr-06 3:56

10-Apr-06 3:56

Just getting back to the this stored procedure. Just a little confused.

The stored procedure is called sp_InsertMember.

What it does is to add the member details, like first and last name, DOB, username and password to the tblMember table. If it @@ERROR is equal to 0, then it loops through the roles string passed as a parameter. The stored procedure used to add the member role is called sp_MemberInRoles_InsertMemberRole, and this I call using the EXECUTE function.

Now my questions are:

In sp_InsertMember I start off with a BEGIN TRANSACTION. If the member was added successfully, where do I add the COMMIT TRANSACTION? Before or after looping through the string of roles?

In the sp_MemberInRoles_InsertMemberRole I also have a BEGIN TRANSACTION and a COMMIT TRANSACTION. Lets say the member was added succesully, then it starts to loop through the string of roles. And lets say the member ahs 3 different roles. After the inserting the first role, it encounters an error, so in sp_MemberInRoles_InsertMemberRole it does a ROLLBACK TRANSACTION. Hoe far does it do this rollback? Will it remove the first role it added? What I want it to do is if the member was inserted successfully, and there is an error with inserting roles, that it removes all roles added, plus the member details.

Here is a modified copy of sp_Member_InsertMember:

ALTER PROCEDURE [dbo].[sp_Member_InsertMember]
(
-- input parameters here
)

AS

BEGIN

DECLARE @ErrorCode int
SET @ErrorCode = 0

DECLARE @TranStarted bit
SET @TranStarted = 0

DECLARE @RemainingRoles nvarchar(4000);
SET @RemainingRoles = @Roles;

DECLARE @CommaIndex int;
SET @CommaIndex = CHARINDEX(',', @RemainingRoles);

DECLARE @CurrentRole sysname

IF (@@TRANCOUNT = 0)
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END

IF (EXISTS(SELECT MemberUsername
FROM dbo.Members
WHERE MemberUsername = @Username))
BEGIN
SET @ErrorCode = -2 -- Username already exists
GOTO CleanUp
END

INSERT INTO Members
(
-- column names
)
VALUES
(
-- input parameters
)

IF @@ERROR <> 0
BEGIN
SET @ErrorCode = -1
GOTO CleanUp
END

-- Loop thru roles string
WHILE(@CommaIndex <> 0)
BEGIN
SET @CurrentRole = LEFT(@RemainingRoles, @CommaIndex - 1);
EXECUTE dbo.sp_MemberInRoles_InsertMemberRole @@IDENTITY, @CurrentRole
SET @RemainingRoles = RIGHT(@RemainingRoles, LEN(@RemainingRoles) - @CommaIndex);
SET @CommaIndex = CHARINDEX(',', @RemainingRoles);
END

-- If all is cool
IF (@TranStarted = 1)
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END

RETURN 0

CleanUp:
IF (@TranStarted = 1)
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode

END

My sp_MemberInRoles_InsertMemberRole has the exact same structure, but just different input values and column names. Also, please comment on the wat that I have doe things here.

Regards,
ma se
South Africa

HimaBindu Vejella3-Apr-06 2:16

HimaBindu Vejella

3-Apr-06 2:16

We can't directly pass array to stored procedure. But the trickish thing is to make the selected ids as a string seperated by commas(,) as "id1,id2,id3 etc. And pass this value to the stored procedure as a varchar in the stored procedure

Code can be written as

CREATE proc UspTestArray
@ids Varchar(100)
As
DECLARE @sql as varchar(500)
Set @sql = 'SELECT * FROM tablename WHERE id in (' + @ids + ')'
Exec(@sql)
go

http://www.sqlservercentral.com/columnists/aGrinberg/thearrayinsqlserver2000.asp[^]

"Aim to go where U have never been B4 and Strive to achieve it"
http://groups.yahoo.com/subscribe/dotnetforfreshers
http://himabinduvejella.blogspot.com

PaulMcKenzie4-Apr-06 11:07

PaulMcKenzie

4-Apr-06 11:07

I use would use this technique:

First create a user function (one of my many tools):

--==========================================
CREATE FUNCTION tools_Splitter
(
@IDs VARCHAR(1000)
)

RETURNS @TableIDs TABLE(ID INT)

AS

BEGIN
-- Append comma
SET @IDs = @IDs + ','

-- Indexes to keep the position of searching
DECLARE @Pos1 INT, @pos2 INT

-- Start from first character
SET @Pos1=1
SET @Pos2=1

WHILE @Pos1 < Len(@IDs)
BEGIN
SET @Pos1 = CharIndex(',' , @IDs, @Pos1)

INSERT INTO @TableIDs SELECT Cast(Substring(@IDs,
@Pos2, @Pos1-@Pos2) AS BIGINT)

-- Go to next non comma character
SET @Pos2 = @Pos1+1

-- Search from the next charcater
SET @Pos1 = @Pos1 + 1
END
RETURN
END
--==================================================

Then your sproc could look like this:

declare @UserRoles varchar(1000)
declare @UserID int
SET @UserRoles=''
--Build a comma sep list of roles to insert
select @UserRoles=@UserRoles+Convert(varchar(20),OrderID)+',' from tblRoles WHERE 1=1
--Then insert the roles calling the tool you created earlier...
INSERT INTO tblRoles (@UserID,tools_Splitter(@UserRoles))

Re: How to uPload a photo?:)

Igor Sukhov4-Apr-06 15:38

Igor Sukhov

4-Apr-06 15:38

Try one of these aproaches:

Pass the comma, semicolon (whatever character that never can be a part of role name) separated string of the roles.

Alternatively you may call the same sproc several times within one transaction (to ensure data integrity).

Second approach requires several client - Sql Server roundtrips, but it a bit easier to implement.

Best regards,

-----------
Igor Sukhovhttp://sukhov.net

How to uPload a photo?:)

coolshad2-Apr-06 11:06

coolshad

2-Apr-06 11:06

hi..

HOw can i store User's photo in sql db?
how can i upload photo by ASP.net page?

computer science..Ainshams UNV..
IS group. Smile | :)

Dinuj Nath2-Apr-06 19:51

Dinuj Nath

2-Apr-06 19:51

See reply in ASP.Net forum.

OleDbException:"no value given for one or more required parameters"

MohammadAmiry2-Apr-06 10:06

MohammadAmiry

2-Apr-06 10:06

Hi I have a database containing several Tables. In a winfoarm, when I want to fill the dataset using data adapter, one of them generates this error (on the Fill statement)...
The strange part is taht when I delete the oleDataAdapter control from the form and create a new one, and then generate dataset from that, this error won't raise...
But the next time I open the solution, again the error continues to raise, unless I recreate the adapter!!!

Could any one tell me what is wrong (while I have recreated the data Table in the database and that didn't help...)
My data Table has two unicode right-to-left Text filds and two Memo fields...

Thanks

Newbie ? User Defined SQL Types and C#

verniy_muzhschina1-Apr-06 19:05

verniy_muzhschina

1-Apr-06 19:05

Sorry if this is a dumb question. I have a database that uses a number of user defined types, for example varchar(10) is aliased as "T_TableKey".

I would like to work with same aliases in C# for consistency, but there seems to be no equivalent to the C++ typedef statement.

Any suggestions?

TIA

Viewing large databases

Pius__X1-Apr-06 10:14

Pius__X

1-Apr-06 10:14

I am using database which content are more than 100 000 rows. OMG | :OMG:

In VB 6, with open data connection, I can view my data after 2 sec. Smile | :)

but in VB .NET I am using DataSet but while data are loading to dataset it's take me 30 sec. and set DataGridView.DataSource = MyDataSet takes me next 10 .sec and waiting 40 sec. is too much. Mad | :mad:

Is posible using open conection in .Net and read only rows what I just need to view alike in VB 6? Confused | :confused:

DataSet is not just the best solution if you using large Database....

Can any body help me with this problem... thx Confused | :confused:

Colin Angus Mackay1-Apr-06 11:11

1-Apr-06 11:11

Pius__X wrote:
I am using database which content are more than 100 000 rows.

That's not large. The database I'm working on at the moment grows at a rate of around a million rows per week.

Pius__X wrote:
Is posible using open conection in .Net and read only rows what I just need to view alike in VB 6?

Since I've never used VB6 I can't answer that exact question. However, If you want to get data out and processing as it arrives rather than wait for the whole set to arrive first then you need a Data Reader. It provides a fast, forward only view of the data.

ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?

Pius__X1-Apr-06 12:10

Pius__X

1-Apr-06 12:10

DataReader is fast, but how to bind DataReader with DataGrid?

Because bind DataReader with DataGrid manually is too compliacetd.. may be not possible..... Cry | :((

manually - I thing, read rows which should be currently view in DataGrid.... not all rows which content database and when you scroll DataGrid then read next rows..... OH MY WEAK ENGLISH Smile | :)

Colin Angus Mackay <-- "million rows per week" are you using DataGrid for viewing these to many rows? Confused | :confused:

thx

Colin Angus Mackay1-Apr-06 12:26

1-Apr-06 12:26

Pius__X wrote:
"million rows per week" are you using DataGrid for viewing these to many rows?

Good grief, No! Big Grin | :-D

I was just suggesting a new perspective on what you think of as a large database. In fact, SQL Server 2005 Enterprise Edition can handle a database that is up to a billion terrabytes in size. (Remember that SQL Server can handle many databases also)

Pius__X wrote:
DataReader is fast, but how to bind DataReader with DataGrid?

If you must use a data grid then you have some other solutions. For example: Populate the data grid with less data. This is common, but requires extra work if you don't want the user to notice. However, normally, there would be page forward/backward buttons for the user to scroll through the data.

ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?

how to connect VB.Net webform with mysql

Pius__X2-Apr-06 1:16

Pius__X

2-Apr-06 1:16

Is it posible access to database whitout DataSet and DateReader and use open connectivity or there are only thease two ability?

Or how to set DataGrid to view data which sould be currently view in DataGrid, here is example.....

I have DataGrid where are show 10 rows, without using scroll bars, when I scroll down next 10 rows then some object read next 10 rows from databaze and show them to DataGrid... How to do this things in .NET?????

thx Roll eyes | :rolleyes:

Amit Agarrwal1-Apr-06 0:28

Amit Agarrwal

1-Apr-06 0:28

can anybody tell me how i can connect vb.net with mysql.
byeeeee

Aavesh

Paul Conrad1-Apr-06 10:48

Paul Conrad

1-Apr-06 10:48

Have you looked at mysql's website?

Amit Agarrwal2-Apr-06 20:41

Amit Agarrwal

2-Apr-06 20:41

hi i went on the mysql website.but i'm very novice in dotnet.so didn't get anything please send me the code or steps to connect.

Aavesh

nguyenvhn2-Apr-06 21:42

nguyenvhn

2-Apr-06 21:42

Access MySQL from NET application can be done as same as MS SQL Server by using MySQL Connector for Net. Just download that connector from MySql website and see their samples enclosed.