使用C＃在SQL Server表中插入 [英] Inserting in SQL Server Table using C#
本文介绍了使用C＃在SQL Server表中插入的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我的数据库中有这个简单的表格
------------------------- ---
table_Items
----------------------------
Item1 |项目2 | Item3 | Item4
A | B | C | D
E | F | G |空
H |我|空|空
J |空|空|空
------------------
table_Item_Shelves
-------------- ----
Item_Name | Item_ID
A |空
B |空
C |空
D |空
E | Null
这是我需要做的。
对于table_items中的每一行，首先检查是否行在所有（项目1,2,3,4）或三个字段中具有值，
比在table_Item_Shelves中为Item_ID字段中的每个项目插入'1'。对于下一行进行相同的检查
并从Item_ID字段获取最大值并增加1.我还需要检查最多四个Item_ID是否相同。
任何帮助在C＃和SQL中会很棒。谢谢。
请不要数据库设计建议。
编辑：如果table_Item_Shelves列Item_ID已经有值我不应该插入新的ID
table_Item_Shelves已经包含记录，我需要更新这些记录。
解决方案
- 空目标表
DELETE TABLE_ITEM_SHELVES
GO
- 使用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
< span class =code-keyword> SELECT ITEM2， 0 FROM TABLE_ITEMS WHERE LEN（ISNULL（ITEM1，' '）+ ISNULL（ITEM2，' '）+ ISNULL（ITEM3， ' '）+ ISNULL（ITEM4，' '））> 2 AND NOT ITEM2 IS NULL
UNION
< span class =code-keyword> SELECT ITEM3， 0 FROM TABLE_ITEMS WHERE LEN（ISNULL（ITEM1，' '）+ ISNULL（ITEM2，' '）+ ISNULL（ITEM3， ' '）+ ISNULL（ITEM4，' '））> 2 AND NOT ITEM3 IS NULL
UNION
< span class =code-keyword> SELECT ITEM4， 0 FROM TABLE_ITEMS WHERE LEN（ISNULL（ITEM1，' '）+ ISNULL（ITEM2，' '）+ ISNULL（ITEM3， ' '）+ ISNULL（ITEM4，' '））> 2 AND NOT ITEM4 IS NULL
GO
< span class =code-comment> - 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
- 更新item2
更新 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
- 更新item3
更新 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
- 更新item4
更新 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
- 获得结果
SELECT * FROM TABLE_ITEM_SHELVES
< span class =code-keyword> GO
嘿那里，
试试这个并告诉我这是你要找的：
C＃：
SqlConnection connection = new SqlConnection（YourConnectionString）;
SqlCommand com = new SqlCommand（ UpdateItemIDs，connection）;
com.CommandType = CommandType.StoredProcedure;
connection.Open（）;
com.ExecuteNonQuery（）;
connection.Close（）;
存储过程：
CREATE PROCEDURE UpdateItemIDs
AS
BEGIN
SELECT ROW_NUMBER（） OVER （ ORDER BY Item1） AS RowIndex，
IT。*，
0 AS 已处理
INTO #TempTable
FROM dbo.table_items IT
< span class =code-keyword> 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
更新 #TempTable
SET processed = 1
WHERE RowIndex = @ RowIndex
SET @ NewItemID =（ SELECT ISNULL（MAX（Item_ID）， 0 ）+ 1
FROM dbo.table_items_shelves
）;
更新 dbo.table_items_shelves
SET Item_ID = @ NewItemID
WHERE Item_Name IN （< span class =code-sdkkeyword> @ ITEM1 ， @ ITEM2 ， @ ITEM3 ， @ ITEM4 ）
AND Item_ID IS NULL
END
结束
希望它有所帮助
Azee ..
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.
解决方案
-- 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
Hey there,
Here try this and let me know if this is what you were looking for:
[EDIT]
C#:
SqlConnection connection = new SqlConnection("YourConnectionString"); SqlCommand com = new SqlCommand("UpdateItemIDs", connection); com.CommandType = CommandType.StoredProcedure; connection.Open(); com.ExecuteNonQuery(); connection.Close();
Stored Procedure:
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...
这篇关于使用C＃在SQL Server表中插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
