Create entry to a table with C#

[patbucks]

Hi,
I'm currently trying to create a program that will update a Microsoft SQL express database.

In the database I have multiple table that are all link with primary key and foreign keys.

Let say I have...
Table A:
ID_TableA (Auto increment)
Name (char 25)

Table b:
ID_TableB (Auto increment)
description (char 45)
FK_IDTAbleA (Foreign key from table a)

Now let say I want to create a Name with a description.
So I will need to first populate table A with the name, then I will need to get the ID of that name from the tableA and use it to create the description in table B.

I don't know what are the steps to create entry in a relational database.
Do I have to first create entry in table A with the name.
Then because the ID in tableA is autoIncrement I won't be able to know what is the ID of the new name I just created, so does that mean I have to query the database to obtain the ID of the new name I have just added?
And then using that ID I can create my entry in table B since that ID will be use to populate the foreigh key of table B.

Is there a better way of doing this without having to query tableA to obtain the foreign key that need to go in tableB?

 

[KDavie]

This would be most easily and efficiently accomplished with a stored procedure (in SQL Express). If you need help with the T-SQL syntax, try posting a thread in the SQL forum.

 

[GoTerps88]

It seems to me that name and description are usually elements that fit in the same entity. Why use separate tables to store them? You need separate tables only if you have multiple descriptions for one name, or multiple names for one description.

 

[patbucks]

I know but it is just an example.
Just go with Client and order if you prefer.
I don't want to do it in a stored procedure.

 

[ca8msm]

I don't want to do it in a stored procedure.

Why? It's more efficient and much easier...


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[patbucks]

I'm building a database with an apps that I'm going to sell.

I want to put the least amout of coding on the database side to avoid the people that buy my apps to be able to figure out how I'm working with my database structure.

I want to hide the most code I can from the people that I'm selling the apps to.

I'm currently using MSsqlExpress and I currently have no idea about how I'm going to hide the database struture from the people how buys the apps.

Since it doesn't seem like I can hide data structure in a MSSql database, my other option is to avoid procedure, and avoid creating the relation in the database it self.
So my database will only have table with no relation, and I'm thinking about renaming the database table to wierd name so it will be event harder to figure it out.

 

[ca8msm]

Have you considered using encryption? e.g.

CREATE PROCEDURE usp_Test
WITH ENCRYPTION
AS
Select ID from Table1

____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[misterstick]

I want to hide the most code I can from the people that I'm selling the apps to.

then don't use .net.

mr s. <

 

[patbucks]

Don't use .net?

What do you mean?

Is it easy reverse engeneer a .net apps?

 

[misterstick]

easy? positively trivial.

there are things you can do, depending on how much money you want to spend, but, basically, if it's .net it's completely reverse-engineerable.

read thread732-1352117.

or better still start with

http://www.ericsink.com/bos/Transparency.html.

good luck.

mr s. <

 

[ca8msm]

Is it easy reverse engeneer a .net apps?

Have a read for yourself: [google].NET obfuscation[/google]


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[patbucks]

ok thanks guys, I got the idea.
)

 

[GoTerps88]

If you are still looking for a SQL Statement or Stored Procedure to answer your original question. In this case I have provided a stored procedure that will first insert an order into tblOrders. Then it will insert an associated item in tblOrderDetails. OrderID serves as the primary key associating these two tables. I insert the SCOPE_IDENTITY(), which represents the last incremental value inserted into the tblOrders, into tblOrderDetails.

This works for SQL Server 2005 or Express. If you have SQL Server 2000 or below, you'd have to use @@IDENTITY instead, which isn't as reliable.

CREATE PROCEDURE proc_ins_Orders
AS
BEGIN

   INSERT INTO dbo.tblOrders
   (CustomerID)
   VALUES
   (328)

   INSERT INTO dbo.tblOrderDetails
   (OrderID, ProductID, Qty)
   VALUES
   (SCOPE_IDENTITY(),332,32)

END 
GO

 

[AlexCuse]

GoTerps88 - SCOPE_IDENTITY() is available in SQL Server 2000. I believe it was added after version 7, but thats' going too far back for me to know much about ;-)

@@IDENTITY is

Ignorance of certain subjects is a great part of wisdom