Inserting in SQL Server Table using C#

Question

1.20/5 (2 votes)

See more:

I have this simple table in my database

----------------------------
table_Items
----------------------------
Item1 | Item2| Item3 | Item4
A     | B    | C     | D
E     | F    | G     | Null
H     | I    | Null  | Null
J     | Null | Null  | Null

------------------
table_Item_Shelves
------------------
Item_Name | Item_ID
A	  |  Null
B	  |  Null
C	  |  Null
D	  |  Null
E	  |  Null

This is what i need to do.
For each row in table_items, first check if row has values in all(item 1,2,3,4) or three of the fields,
than in table_Item_Shelves insert '1' for each item in Item_ID field. For next row conduct the same check
and get maximum value from Item_ID field and increment by 1. I also need to check that maximum four Item_ID's can be same.
Any help in C# and SQL will be great. Thanks.

Kindly no database design suggestions.

Edit: If table_Item_Shelves column Item_ID already has a value than i should not insert new ID
table_Item_Shelves already contain records, and i need to update against those records.

Posted 8-Oct-13 23:49pm

Mubshir

Updated 9-Oct-13 6:00am

v8

Add a Solution

Comments

Torakami 9-Oct-13 5:56am

This is bad programming idea that you want to go for such design without using any database.Atlest go for my sql

Mubshir 9-Oct-13 6:01am

Hey man, i understand that and i don't have options to go for mysql or change database design. Thanks.

Azee 9-Oct-13 6:18am

Does table_Items_Shelves already contains records? and you want to update against those records?
Or you want to insert new records from table_items?

Mubshir 9-Oct-13 6:19am

Yes, sorry i forgot to mention that. Thanks

Azee 9-Oct-13 6:22am

which one is it? :)

Mubshir 9-Oct-13 6:23am

table_Items_Shelves already contains records i need to update against those records

Mubshir 9-Oct-13 6:42am

Do you have any solution?

Thanks7872 9-Oct-13 6:24am

So what is the question? The above all is just what you want. Not a question.

Mubshir 9-Oct-13 6:27am

I have no idea how to do this, so i was just wondering if some ideas on what to use etc, pseudocode

Kornfeld Eliyahu Peter 9-Oct-13 6:30am

I didn't understand your description, can you add the values you expecting to table_Item_Shelves. It can help - me at least - to understand the process...

Mubshir 9-Oct-13 6:36am

Item_Name has the same items as in table_items. Item_ID i have to generate, starting from 1. So, what i need to is each row in table_items are similar items from table_Item_Shelves. So if row has 3 or 4 items i have to assign them a same Item_ID. Hope it makes sense now.

Kornfeld Eliyahu Peter 9-Oct-13 7:25am

No. I can't see table_Items.Item_ID column you talk about.
I'm not mean to be rude - but may someone can help you with the English part of this question?

Mubshir 9-Oct-13 7:42am

Not my first Language man. Ok let me try again.
Consider table_Item_Shelves as the main table and the one in which i have to insert values in Item_ID field.
Item_name in table_Item_Shelves contains all the Items.
I have to create groups of 4 items which i think are similar from Item_name in table_Item_Shelves, so i insert 4 similar items in table_Items contains 4 columns item1, item2, item3 etc. Now i have to insert a number(as ID most probably starting from 1) in table_Item_Shelves(Item_ID field).
So, if a row in table_Items has 3 or more(4) values than in table_Item_Shelves(Item_ID field) i will insert one for these 3 or 4 items, for the next row same checks but Item_ID = Item_ID +1. Maximum 4 Item_name has same Item_ID

Kornfeld Eliyahu Peter 9-Oct-13 8:08am

Not mine either - not even the second...
But cheer-up I think I understood it...
To be continued

Mubshir 9-Oct-13 8:29am

I am glad you understand it now. At least i have learned how to explain.

2 solutions

Solution 2

SQL

-- empty target table
DELETE TABLE_ITEM_SHELVES
GO
-- create entry for every item with item_id 0
INSERT INTO TABLE_ITEM_SHELVES
	SELECT ITEM1, 0 FROM TABLE_ITEMS WHERE LEN(ISNULL(ITEM1,'') + ISNULL(ITEM2,'') + ISNULL(ITEM3,'') + ISNULL(ITEM4,'')) > 2 AND NOT ITEM1 IS NULL
	UNION
	SELECT ITEM2, 0 FROM TABLE_ITEMS WHERE LEN(ISNULL(ITEM1,'') + ISNULL(ITEM2,'') + ISNULL(ITEM3,'') + ISNULL(ITEM4,'')) > 2 AND NOT ITEM2 IS NULL
	UNION
	SELECT ITEM3, 0 FROM TABLE_ITEMS WHERE LEN(ISNULL(ITEM1,'') + ISNULL(ITEM2,'') + ISNULL(ITEM3,'') + ISNULL(ITEM4,'')) > 2 AND NOT ITEM3 IS NULL
	UNION
	SELECT ITEM4, 0 FROM TABLE_ITEMS WHERE LEN(ISNULL(ITEM1,'') + ISNULL(ITEM2,'') + ISNULL(ITEM3,'') + ISNULL(ITEM4,'')) > 2 AND NOT ITEM4 IS NULL
GO
-- update for item1
UPDATE TABLE_ITEM_SHELVES SET ITEM_ID = ITEM_ID + ITMS.CNT
FROM 
(SELECT ITEM1, COUNT(*) AS CNT FROM TABLE_ITEMS WHERE LEN(ISNULL(ITEM1,'') + ISNULL(ITEM2,'') + ISNULL(ITEM3,'') + ISNULL(ITEM4,'')) > 2 AND NOT ITEM1 IS NULL
GROUP BY ITEM1) AS ITMS
WHERE TABLE_ITEM_SHELVES.ITEM_NAME = ITMS.ITEM1
GO
-- update for item2
UPDATE TABLE_ITEM_SHELVES SET ITEM_ID = ITEM_ID + ITMS.CNT
FROM 
(SELECT ITEM2, COUNT(*) AS CNT FROM TABLE_ITEMS WHERE LEN(ISNULL(ITEM1,'') + ISNULL(ITEM2,'') + ISNULL(ITEM3,'') + ISNULL(ITEM4,'')) > 2 AND NOT ITEM2 IS NULL
GROUP BY ITEM2) AS ITMS
WHERE TABLE_ITEM_SHELVES.ITEM_NAME = ITMS.ITEM2
GO
-- update for item3
UPDATE TABLE_ITEM_SHELVES SET ITEM_ID = ITEM_ID + ITMS.CNT
FROM 
(SELECT ITEM3, COUNT(*) AS CNT FROM TABLE_ITEMS WHERE LEN(ISNULL(ITEM1,'') + ISNULL(ITEM2,'') + ISNULL(ITEM3,'') + ISNULL(ITEM4,'')) > 2 AND NOT ITEM3 IS NULL
GROUP BY ITEM3) AS ITMS
WHERE TABLE_ITEM_SHELVES.ITEM_NAME = ITMS.ITEM3
GO
-- update for item4
UPDATE TABLE_ITEM_SHELVES SET ITEM_ID = ITEM_ID + ITMS.CNT
FROM 
(SELECT ITEM4, COUNT(*) AS CNT FROM TABLE_ITEMS WHERE LEN(ISNULL(ITEM1,'') + ISNULL(ITEM2,'') + ISNULL(ITEM3,'') + ISNULL(ITEM4,'')) > 2 AND NOT ITEM4 IS NULL
GROUP BY ITEM4) AS ITMS
WHERE TABLE_ITEM_SHELVES.ITEM_NAME = ITMS.ITEM4
GO
-- get results
SELECT * FROM TABLE_ITEM_SHELVES
GO

Posted 9-Oct-13 2:52am

Kornfeld Eliyahu Peter

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Azee** · Accepted Answer · 2013-10-09T01:50:00

Hey there,

Here try this and let me know if this is what you were looking for:
[EDIT]
C#:

C#

SqlConnection connection = new SqlConnection("YourConnectionString");

                SqlCommand com = new SqlCommand("UpdateItemIDs", connection);
                com.CommandType = CommandType.StoredProcedure;
                connection.Open();
                com.ExecuteNonQuery();
                connection.Close();

Stored Procedure:

SQL

	CREATE PROCEDURE UpdateItemIDs
AS 
    BEGIN
        SELECT  ROW_NUMBER() OVER ( ORDER BY Item1 ) AS RowIndex ,
                IT.* ,
                0 AS processed
        INTO    #TempTable
        FROM    dbo.table_items IT
        WHERE   ( Item1 IS NOT NULL
                  AND Item2 IS NOT NULL
                  AND item3 IS NOT NULL
                )
                OR ( Item1 IS NOT NULL
                     AND Item2 IS NOT NULL
                     AND item4 IS NOT NULL
                   )
                OR ( Item1 IS NOT NULL
                     AND Item3 IS NOT NULL
                     AND item4 IS NOT NULL
                   )
                OR ( Item2 IS NOT NULL
                     AND Item3 IS NOT NULL
                     AND item4 IS NOT NULL
                   )
	
	
        DECLARE @ITEM1 VARCHAR(50)
        DECLARE @ITEM2 VARCHAR(50)
        DECLARE @ITEM3 VARCHAR(50)
        DECLARE @ITEM4 VARCHAR(50)
        DECLARE @RowIndex INT
        DECLARE @NewItemID INT
	
        WHILE ( SELECT  COUNT(*)
                FROM    #TempTable
                WHERE   processed = 0
              ) > 0 
            BEGIN
                SELECT TOP 1
                        @ITEM1 = Item1 ,
                        @ITEM2 = Item2 ,
                        @ITEM3 = item3 ,
                        @ITEM4 = Item4 ,
                        @RowIndex = RowIndex
                FROM    #TempTable
                WHERE   processed = 0
	
                UPDATE  #TempTable
                SET     processed = 1
                WHERE   RowIndex = @RowIndex
	
                SET @NewItemID = ( SELECT   ISNULL(MAX(Item_ID), 0) + 1
                                   FROM     dbo.table_items_shelves
                                 ) ;
	
                UPDATE  dbo.table_items_shelves
                SET     Item_ID = @NewItemID
                WHERE   Item_Name IN ( @ITEM1, @ITEM2, @ITEM3, @ITEM4 )
                        AND Item_ID IS NULL
            END
    END

Hope it helps

Azee...