-2

I have a many-to-many relationship table in SQL Server.

Table: Application

Application

Table: AppCategory

AppCategory

Table: AppPerCategory

AppPerCategory

Primary key in [AppPerCategory] is CategoryID + AppID.

The primary keys in [Application] and [AppPerCategory] are NOT in sequence/order.

So, how do I add a new row to [Application], using C#, ASP.NET WebForms?

I am building a small C# ASP.NET Web form to allow user to add new row to the [Application] table.

WhileLoop
  • 7
  • 6
  • You [asked an identical question](https://stackoverflow.com/questions/49743887/how-to-add-new-row-to-sql-table-with-multiple-columns-primary-key) literally an hour ago. The question also had an answer before you deleted it. Your question isn't very well formed either. Can you define "effectively" for us? Are you asking for performance reasons? Are you asking because you simply have no idea how to do it? You need to define this for us and show us some code you already have. If I asked you "How do I do X with Y technology" and provided you no starting reference... how would you answer it? – Simon Whitehead Apr 10 '18 at 03:10
  • I have no idea how to do it. I thought of using (max) function when insert a new row to the [Application] table but it does not work because the primary keys are not in sequence. (Example: 001, 020,027, etc.) – WhileLoop Apr 10 '18 at 03:21
  • Are you working with a legacy code base? How do those rows get in there currently? You could hack something together in SQL where you cast the values to numbers and then zero-pad them ... but if possible I would think about re-designing the database. – Simon Whitehead Apr 10 '18 at 03:22
  • Previously I will manually insert a new row to [Application] by looking at the CategoryID, AppID and SeqIndex at [AppPerCategory]. Example, if I need to insert a new row for (Category 00), I need to manually check the [AppPerCategory] table; example for CategoryID "00", the latest row for "00" is CategoryID["00"], AppID["094"], and SeqIndex["13"], then I will manually add "00", "095" and "14"; then with the "095", I need to check if "095" exists at table [Application] or not, otherwise I would not be able to input "095" because PK cannot be duplicated. I am sorry if my explanation is not clear – WhileLoop Apr 10 '18 at 03:33

1 Answers1

0

The table design is a bit odd. Ideally, ApplicationID should have been an identity column so that SQL server will auto-increment it for you. But, hey, we all deal with legacy systems where some portions are out of our control.

See below SQL fiddle for one way of doing this. Obviously, it will break down when you have more than 999 apps, so you'd have to deal with that:

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE Application (Id varchar(3), ApplicationName varchar(max))
CREATE TABLE AppCategory (Id varchar(2), CategoryName nvarchar(max))
CREATE TABLE AppPerCategory (CategoryId varchar(2), ApplicationId varchar(3), SeqIndex int)


INSERT Application VALUES ('091', 'foo')
INSERT AppCategory VALUES ('00', 'test category')
INSERT AppPerCategory VALUES ('00', '091', 1)

Query 1:

-- Assume below two are inputs for creating new application
DECLARE @newAppName varchar(max) = 'new application'
DECLARE @category varchar(max) = 'test category'

-- Below will set newAppId to 092. Not you have to adjust for when # apps > 999
DECLARE @newAppId varchar(3) = (SELECT REPLACE(STR(CAST(MAX(Id) AS int) + 1, 3), ' ', 0) FROM Application)
DECLARE @categoryId varchar(2) = (SELECT Id from AppCategory WHERE CategoryName = @category)

DECLARE @newCategorySeqIndex int = (SELECT MAX(SeqIndex) + 1 FROM AppPerCategory WHERE CategoryId = @categoryId)

INSERT Application (Id, ApplicationName)
  VALUES (@newAppId, @newAppName)

INSERT AppPerCategory (CategoryId, ApplicationId, SeqIndex)
  VALUES (@categoryId, @newAppId, @newCategorySeqIndex)
RnP
  • 390
  • 1
  • 8
  • Thank you so much for taking your time to answer my question. :) However, I could not re-create/re-design the database because the elements are used in many (literally many) other tables, database and c# webform. (I'm a newbie working in a big company). In fact, I also wonder how and why the developer built this odd design database table... – WhileLoop Apr 10 '18 at 09:05