| re: HELP! Best practice for lookup data and foreign key values?
Hi James,
There are several answers to your situation - I use SQL Server stored
procedures in almost all instances like this.
I break down my sp's into common actions like inserting and updating (1 sp),
deleting, etc. For instance, if I wanted to insert a communication record I
would create an sp named "spInsertCommunication" with 3 parameters.
CommunicationType
Sender
Recipient
Within the sp I would lookup the CommunicationTypeID (and probably the
SenderID and RecipientID for that matter) like this.
Declare @CommunicationTypeID as tinyint
SELECT @CommunicationTypeID = CommunicationTypeID FROM CommunicationTypes
WHERE CommunicationType = @CommunicationType
....and while not always needed...
IF ISNULL(@CommunicationTypeID,0) < 1
BEGIN
INSERT INTO CommunicationTypes (CommunicationType) VALUES
(@CommunicationType)
SET @CommunicationTypeID = SCOPE_IDENTITY()
END
--Do your insert to the Communication table here.
There are a lot of examples so I won't bore you with the details, but check
out the System.Data.SQLClient.Command and Parameter objects. These objects
are the best way to communicate to sp's.
Hope this helps,
Mike
"James E" wrote:
[color=blue]
> I have a question about best practices of how to deal with lookup data from
> my C# apps. On a couple of occasions I have come across a problem where I
> have to automate inserting a record into a table that has a foreign key
> constraint that is linked to a lookup table. E.g. Take the following
> database structure:
>
>
> SQL-Server Database:
>
> Table 1:
> Name: CommunicationTypes
> Columns: CommunicationID (Primary Key), Description
>
>
> Table 2:
> Name: Communications
> Columns: CommunicationID (Primary Key), CommunicationTypeID (Foreign Key),
> Sender, Recipient, etc.
>
>
>
> ...In this example, there is data such as the following in the
> CommunicationTypes table:
>
> CommunicationTypeID Description
> ------------------ ------------
> 1 Email
> 2 Letter
> 3 Phonecall
> etc.....
>
>
> My app needs to log details of communications, but in some cases,
> auto-generates emails and letters. When this occurs I need to auto-insert a
> record into the communications table. This means my app needs to know about
> what the CommunicationTypeID is for the communication being 'auto-recorded'.
> Should my app retrieve the CommunicationTypeID from the database by passing
> the description? If the list is short, could I store the ID's in the config
> file? Should I have a strongly typed class that has static methods to return
> the ID from the lookup table? Other suggestions please!!
>
> My question is, what is the best practice for situations like this, where:
> a) You have a lookup table that will be added to over time
> b) You need to insert a record into a table that has a foreign key related
> to the lookup table, but the app needs to know the foreign key value without
> the user selecting anything from a list.
>
> Many thanks
>
> JamesE
>
>
>[/color]
| 
