Getting Data from the database but without ID column?

sajadshafi

Member
Joined
Oct 17, 2020
Messages
5
Programming Experience
Beginner
I am creating a Database to database sync application. now i want to get data from one database and then send to another database but i dont want to send the id field to the second database.
 

sajadshafi

Member
Joined
Oct 17, 2020
Messages
5
Programming Experience
Beginner
i am retrieving data as:

var products = productContext.Products.ToList();
this will select all data from product table along with all fields including id...

and then i send it to webapi as:

Client client = new Client();
client.PostRequest(new PostData<object>() {
ApiEndPoint = "save-product",
Data = products
});

It will send all the columns?
So, what c# Code i have to write to exclude id.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
1,959
Location
Chesapeake, VA
Programming Experience
10+
It really depends on how that PostData<T> object works in relation to the that Client.PostRequest() method. If the method and object work in conjunction to iterate over every public field of an List<Product> and its elements, then of course the IDs will be sent. I'm only speculating because you have not told us how those things work. Can you link to the documentation for those classes so that we can be clued in?
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
1,959
Location
Chesapeake, VA
Programming Experience
10+
I'll ask a silly question though... how do you plan to correlate rows from database A with rows in database B without sending the row IDs so that you know that you are updating the correct row?
 

sajadshafi

Member
Joined
Oct 17, 2020
Messages
5
Programming Experience
Beginner
I'll ask a silly question though... how do you plan to correlate rows from database A with rows in database B without sending the row IDs so that you know that you are updating the correct row?
I didn't explained it perfectly as i am new here asking questions....

I have two databases on two different servers now when i take data from one database and send it to another database i get the error.
the error is simple, ProductId is auto-increment so it doesn't accept the id from the data we send to it.

I want a Linq query with which i can exclude id from the first database..

I found a way to do it but i dont think it is good idea:

var ProductItems = context.Products.Where(x => x new{
x.name,
x.price,
x.brand
}).ToList();

This works fine for me...
but the more columns I will have the diificult this one is going to be.

i want something like

context.Products.exclude(x => x.productId).ToList();
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
1,959
Location
Chesapeake, VA
Programming Experience
10+
Off the top off my head, I would say use reflection, but it's not clear to me how you determine whether a field is an ID field or not. Or if a set of fields comprise a composite key and is so effectively is also an ID.

If would suggest that having to list each field (albeit tedious and error prone), is not really an issue. The reason I say this is because you would have to know all the fields anyway to create the second database.

Are you saying that it won't be tedious and error prone to clone the initial schema, and that is a perfectly okay problem to have, but it's not okay to have that same problem in the code?

Or did you just throw the schema problem over the wall to your DBAs (or whomever owns the second database and the web API)? And so now you are left with having to same problem that you just threw over the wall and are asking here.

I would suggest that whatever automation/scripting is used to solve the schema problem be also dual purposed to solve the problem of listing the fields that need to be copied. At best, you'll be writing some T4 templates that takes the database schema and generates helper classes. (search for "T4 text template transformation toolkit". It's built right into Visual Studio, as well as available at runtime.)

Another approach would be to look into AutoMapper. It excels in doing that kind of tedious field mappings. I would not be surprised if also supports "do not copy" field mapping rules.
 

sajadshafi

Member
Joined
Oct 17, 2020
Messages
5
Programming Experience
Beginner
Off the top off my head, I would say use reflection, but it's not clear to me how you determine whether a field is an ID field or not. Or if a set of fields comprise a composite key and is so effectively is also an ID.

If would suggest that having to list each field (albeit tedious and error prone), is not really an issue. The reason I say this is because you would have to know all the fields anyway to create the second database.

Are you saying that it won't be tedious and error prone to clone the initial schema, and that is a perfectly okay problem to have, but it's not okay to have that same problem in the code?

Or did you just throw the schema problem over the wall to your DBAs (or whomever owns the second database and the web API)? And so now you are left with having to same problem that you just threw over the wall and are asking here.

I would suggest that whatever automation/scripting is used to solve the schema problem be also dual purposed to solve the problem of listing the fields that need to be copied. At best, you'll be writing some T4 templates that takes the database schema and generates helper classes. (search for "T4 text template transformation toolkit". It's built right into Visual Studio, as well as available at runtime.)

Another approach would be to look into AutoMapper. It excels in doing that kind of tedious field mappings. I would not be surprised if also supports "do not copy" field mapping rules.
Sorry to Make it complex...
i will go like this:

I have already created two databases, both the databases have product table, Now which means i have two product tables, from database one and from database two. Both product tables have same columns.. the ProductId of both of the tables is auto-incremented... so i don't have to replicate the whole schema... i am only retrieving from one database and sending it to another. The database to which i send the data doesn't accept product id as it is auto-incremented
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
1,959
Location
Chesapeake, VA
Programming Experience
10+
In your shoes, I would just re-create your second database without AutoIncrement.
 

sajadshafi

Member
Joined
Oct 17, 2020
Messages
5
Programming Experience
Beginner
In your shoes, I would just re-create your second database without AutoIncrement.
then it will assign same value to multiple product items...
while inserting data into database the id's my data will have may already be present in second product table

e.g:
data in table one:
1 iphone 20k
2 Samsung 15k


data in table second:
1 Nokia 10k
2 Mac 50k



both tables have id 1 and id 2 so there will be conflict
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
1,959
Location
Chesapeake, VA
Programming Experience
10+
How would you have data is the second database that didn't come from the first database?

You said that the second database is synchronized with the first database. So if the data is valid in the first database, then the data in the second database should also be valid because the only way to get data into the second database is if it came from the first database. Or is there yet another thing you are not telling us?
 
Top Bottom