Database help

hii have a DB scenario, it may be basic to u, but i am new to this so plz explain in details or if you can provided links to related articles that will be great.

i have sql server 2000 and i am using dataset on my asp.net application. now as Dataset is connenctionless, what will happen if i have filled my dataset with some records from database and updated those records in dataset. in that time some other user deleted some of those records from database. now when i will try to batch update database using my dataset (with records that has been deleted from DB) what will happen? will i get an exception or what. plz explain in detail . any links to articles will be great.

my second question is same, what are the methods to lock database if one user is accessing (updating) one record, so that other user dont get to change it at the same time? do i have to manage it in Database or what.

thank you in advance

regards

Hi,

I think this article will explain a lot

http://www.asp.net/learn/data-access/tutorial-21-vb.aspx

|||

Hi zeeshannasir,

now as Dataset is connenctionless, what will happen if i have filled my dataset with some records from database and updated those records in dataset. in that time some other user deleted some of those records from database.

My question is: what do you mean by "dataset is connectionless"? Do you mean that your database has been detached in your database engine? Well,in that case your database won't be accessible at all so you(and all the other users) cannot modify your database.

As to your second quesion, actually you don't have to worrry about it.All the transactions in SQL has been designed to comply with theACID (Atomicity ,Consistency, Isolation Durability) compliance.For example, if user A is currently opening up and reading the info. of a particular database, and meanwhile, user B is trying to open up the same database. Since there is no modification being made to the database, user B is allowed to open up. However, if user A is writting new data to the database, all the operations user B makes will be denied.

Hope my suggestion helps

|||

hi Bo Chen

what i meant by "dataset is connectionless" is that, i filled my dataset with required data using a stored procedure. now i am not connected to database, plz correct me if i am wrong. any changes i made to dataset is local until i dont batch update manually, is it correct. now what will be the scenario if this is the case, i have filled my dataset and made some changes and while i was doing that someone else also access the same database and changed some of the records.

plz correct me if i am wrong

thanks

Database Help

Hi,

I have a question about setting up a couple of database tables.

I'll try to explain using an example:

We have a customer (stored in a customer table)

CustomerCode: JDOE (Primary Key)

CustomerName: John Doe

Address: 123 Cherry Lane

City: Dubbo

State: NSW

PostalCode: 2830

Who was sent an invoice for an order he placed. That invoice was sent to his current address at the time (123 Cherry Lane).

Let’s say he moved to a new address. We would update his customer record accordingly.

CustomerCode: JDOE (Primary Key)

CustomerName: John Doe

Address: 987 Apple Road

City: Dubbo

State: NSW

PostalCode: 2830

Now, let’s say that we want to be able to look back at JDOE’s invoices and see where they were shipped too. This means we have to store that address somewhere.

Now if we said just store it on the invoice table like:

InvoiceNo: 0001 (Primary Key)

CustomerCode: JDOE (Foreign Key)

Address: 123 Cherry Lane

City: Dubbo

State: NSW

PostalCode: 2830

Amount: $300.00

That would break 3^rd normal form as the address doesn’t depend on the primary key (InvoiceNo [0001]).

We want to be able to see the address from the invoice table via a relationship/lookup/computational column/whatever.

How would we go about doing this in SQL Server version 8?

Cheers,

Pete

I think it is pretty common to have a set up like that. So you might have have a table(s) that lists default values for customers and an actual invoice table that includes actual ship address. You can divide it up for storage and performance reasons as necessary but most companies need to have that historical info available.

My two cents :-)

|||

Hi,

You can have an additional column say address2 for the current address. I know this sounds naive, but sometimes this is the best way to get things done. Now you may ask, what if I have more than 2 addresses, then boy ur going to have a tough time coming up with a schema, which stores historical information.

Good Luck and let me know, how u went about it.

PP

|||

G'day,

Not be blunt, but the above suggested example is very poor design. One particular (and there are several) solutions to this is to first separate the address details from the customer details:

Customer Table

CustomerCode, CustomerName,

Address Table

CustomerCode, AddressID, Addresstype, line1, Line2, PostCode,State

This will then allow a customer to first of all have more then 1 address recorded (physical, postal, work, home etc) as well as almost conforming to 3NF (the State column would in fact need to become a "StateID" column lookup into a "State' table...).

Or, if you really didn't want to store multiple addresses, just keep your same design and create a trigger on the customer table to record any address details that change into a seperate table (example only):

CREATE TRIGGER CustomerAddress_upd

on CustomerTable AFTER UPDATE

AS

BEGIN

IF ( UPDATE(Line1) OR UPDATE(PostCode) ) --list all required cols here

BEGIN

INSERT INTO AddressAudit (CustomerCode, Line1, PostCode, DateChanged)

SELECT CustomerCode, Line1, PostCode, getDate()

FROM CustomerTable c

JOIN Inserted i ON c.CustomerCode = i.CustomerCode

END

END

Then, if required, just query the audit table.

Cheers,

Rob

sql