DataSource Controls :: Deleting Data From The Multiple SQL Tables With LINQ (C#)?
Apr 22, 2010
I'm looking for ideas on deleting data from the multiple SQL tables with LINQ (C#).
My existing solution does the following:
1. DeleteAllOnSubmit( Subset of the data from the child table)
2. DeleteAllOnSubmit( Subset of the data from another child table)
3. DeleteAllOnSubmit( Data from the parent table)
4. CommitChanges()
All this resides within a single method, which makes it ugly and not re-usable.
I have considered the following alternatives, but I decided not to use them:
1. Cascade delete on the tables.
2. Do a join on all tables from which I will remove the data, but I'm not sure yet whether I could pass a join query to DeleteAllOnSubmit().
3. Write stored procedure(s) to delete data from each table. I don't have the time to implement this due to a large number of tables and the time constraints.
View 1 Replies
Similar Messages:
Feb 27, 2011
I have two tables with a foreign key constraint how can I delete rows from both of them in one transaction? linq to SQL seems to call my deletes in the wrong order. Is there somewhere I can check and make sure the constraint is recognized properly by linq to SQL??
DataContext.OtherImages.DeleteOnSubmit(myOtherImage);
DataContext.Images.DeleteOnSubmit(myImage);
DataContext.SubmitChanges();
The Foreign key constraint is on OtherImages.
View 2 Replies
May 19, 2010
I'm using ASP.NET MVC to create a small web apllication.
Here is my database And here is my class
[Code]....
View 2 Replies
Apr 28, 2010
How to get data from two tables through linq .1 ) productsDetails 2) productsImages to bind further listview.
View 1 Replies
May 7, 2015
I have to display records from two tables where foreign key table contains many values for a primary key column how to get the data using linq query???
View 1 Replies
Aug 17, 2010
I have hard time trying to find the solution to retrieve data from multiple tables in one database.
table Kategorije has fields: idKategorija, naziv, opis table Clanak has fields: idClanak, idKategorija, naslov, autor,...
Kategorije.idKategorijais joined with Clanak.idKategorija, but that isn't the solution because I want my web site to show the Kategorije.naziv instead of Kategorije.idKategorija. This is the code I'm currently using to get idKategorije shown on my web site:
[Code]....
[Code]....
View 3 Replies
May 6, 2010
I have to write data to multiple (two) tables.
tbl1 has a column "ORDERID" with auto increment. I have to get this "ORDERID" whenever I write to tbl1 (Insert new record)
tbl2 has a coumn "ORDERID" as foreign key, I have to write "ORDERID" in tbl2 received from the tbl1.
How to do it using SQL ( I am not using StoredProcedure)
View 5 Replies
Mar 16, 2010
I am writting a directory website that advertises fitness classes. These classes are listed by Class Type, and Geographical Area. Each Class Type can satisfy one or more of four Fitness Goals such as Loose Weight etc...
I have a table for Classes (classid, classname, classdesc, classtypeid, geoareaid) I have a table for geographic areas (geoareaid, geoarea) And I have a table for ClassTypes (classtypeid, classtypename, classtypedesc)
Which obviously links geographic area via the classes.geoareaid as a forgien key and classtypes by classes.classtypeid as a forgien key.
I can list all classes by type and also by geo area no problem, the problem arrises when I then also want to filter by goal.
The Goals Table (goalid, goal, goaldesc) is linked to the classtypes table via another table classtypes_goals (classtypeid, goalid) as each classtype can satisfy any of the 4 goals.
I am using a DataSet to fetch the data from a Stored Procedure in the SQL Database but because the classtypes can be linked to multiple goals I am getting duplication in my list depending on how many goals a classtype satisfies.
All I need in my data list is class name, class type and area I don't even need to display the goal I just want a drop down above the table that will filter the list and show only classes of classtype that satisfy the goal chosen.
View 18 Replies
Jan 26, 2010
I'm using the following query as an SqlCommand:
[Code]....
It obviously doesn't work...what can I do? I cannot use the "Merge" method, because I am working with several tables in a single SELECT statment.
View 2 Replies
Jun 26, 2010
After creating a dataset, which has CRUD statements generated two or more tables, which Data Control should I use that will allow me to insert data into fields into 2 or more tables from a single web form. I'm trying to create a web form that will allow the user to input student information, such as general information, medical information, etc.
View 3 Replies
Apr 23, 2010
As the title sugests, I am missing a (basic?) concept as I teach myself asp.net, linq, and C#. It has been awhile since I've done anything object oriented, so I don't know how much that is impacting this gap in my understanding. Anyhow, here is what I am trying to do. I have three SQL Server tables: PERSON, POSITION, AREA. There is a 1 to many relationship between PERSON and POSITION, and a 1 to 1 relationship between POSITION and AREA. Given the following code I verify who the person is who has logged into the browser:
[Code]....
I want to be able to return all of the AREAs the person is able to access, based on the position they currently hold. I am able to easily do this in a SQL Server query, and I am sure I can do this with a SQL stored procedure once I pass the EMPLOYEE_ID to the procedure. However, I would rather do this in the page_load event, and load the returned results into gridview1 for the viewer. The issue I am running into is in building the LINQ query. When I try to build it, I can only get access to two of the three tables, depending on which table I select with db. Is it possible to access all three tables in the same query?
View 10 Replies
May 20, 2010
can get two tables in only one query with LINQ to Entities
Code for example:
[Code]....
View 4 Replies
Jan 22, 2010
I have two tables namely "CategoryMapping" and "PartnerCategory"...The partnercategory table has foreign key with categorymapping on CategoryID...
let us say categorymapping should be filed first then partnercategory should be filed based on the categories defined in categorymapping. For example my two tables have the following data:
[code]....
View 3 Replies
Aug 17, 2010
I have a partitioned db where several tables have the exact same structure. I'm able to query the tables using linq, but only by quering the individual tables 1 at a time and by using the explicit table name. For example at the moment, I have 8 tables so I have something similar to the following:
[Code]....
Is there any way, that based on a certain value (stringValue), I could determine which table to query and then code the linq query just once?
View 2 Replies
Aug 12, 2010
I have a couple tables that are kind of unrelated - id like to search through both of them and create a type that i can sift through later
something like this doesnt work
[Code]....
I basically want to create a list of "AnimalSearchResults" that contains all dogs and all cats that have that name Whats the best way to do something like this?
View 6 Replies
Aug 8, 2010
Using Linq to Sql how do i group the following 2 tables.
Orders Table:
CustomerID | Name |Date
1 | order1 | 2010-01-01
2 | order2 | 2010-01-01
2 | order3 | 2010-04-01
Calls Table:
CustomerID | Name |Date
1 | call1 | 2010-01-01
3 | call2 | 2010-06-01
2 | call3 | 2010-05-01
I want to group the two tables by date , Result:
Date | Orders | Calls
2010-01-01 | 2 | 1
2010-04-01 | 1 | 0
2010-05-01 | 0 | 1
2010-06-01 | 0 | 1
i know how to group a single table ,from o in Orders group o by o.Date.Date into og select new {Date = og.Key,Orders= og.Count()};
View 2 Replies
Jan 11, 2010
I have a sproc that will insert a new record into Table1. Next I need to Insert into another table the scope_identity of the inserted record. How do I incorporate the scope_identity value as the insert parameter in the second insert statement?
View 3 Replies
Jul 12, 2010
I've got 3 tables.
Table A: UID, Name
Table B: CID, Category
Table C: ID, UID, CID
I've got a TextBox to capture the value of "Name" in Table A.
Ive got a CheckBoxList that displays all the pre-determined values of Table B.
When the user selects an item from the CheckBoxList i want to populate Table C with the value of the selected item from the CheckBoxList and the cooresponding ID associated with the user name for that person's entry.
How do I write the INSERT statement?
View 2 Replies
Apr 27, 2010
How can i Drop Multiple table through an SQL Statement rather than using a procedure or a function.
View 7 Replies
Jan 29, 2010
I have a dataset with 3 tables that are inner joined. Thsi dataset is connected to an object datasource which is connected to a gridview. So at runtime it displays data from the database. The problem is when I click on a save button to add information to the database it throughs me an error saying Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints. But if i have two tables in my dataset it works 100%. But the problem i need that 3 tables in order to pull the required information. And if i populate the gridview using code to select the information from the database and insert information it also works 100%.
View 3 Replies
Apr 10, 2010
How to return multiple tables to dataset. My code is
string q = "select job_title,primary_skill,description from T12_Company_AddRequirement where job_code='JB1';select count(p_status) from T12_SentDetails where job_code='JB1'and p_status='Technical 1';select count(p_status) from T12_SentDetails where job_code='JB1'and
p_status='Technical 2';select count(p_status) from T12_SentDetails where job_code='JB1'and p_status='Technical 3';select count(p_status) from T12_SentDetails where job_code='JB1'and p_status='HR'";
SqlConnection con=new SqlConnection(s);
SqlDataAdapter da = new SqlDataAdapter(q, con);
DataSet ds = new DataSet();
da.Fill(ds);
Grid_description.DataSource = ds.Tables[0];
Grid_description.DataSource = ds.Tables[1];
Grid_description.DataSource = ds.Tables[2];
Grid_description.DataSource = ds.Tables[3];
Grid_description.DataSource = ds.Tables[4];
Grid_description.DataBind();
This code is returning error.
View 9 Replies
Feb 4, 2010
I am using ASP.NET 2.0, C# and Sql Server 2005.I have 2 tables retruned by the stored procedure to the application. I am returning an Array from my datalayer to my bussiness layer and then to my UI layer.I have added the first table into the array and passing it to the UI layer. This is my current code:
[Code]....
How to pass the second table details to the same array, NameArrayKeyEntity .
View 3 Replies
Aug 19, 2010
Does anyone have some really great tutorials on how to use link with datasets that contain multiple tables that utilise primary key and foreign key relations? For eample
Table1
- id (PK)
- language
Table2
-id (PK)
-programmingword
Table3
-id (PK)
-languages_ID (FK)
-pgrammingword_ID (FK)
-translatedword
Linq search for: Table3.translatedword where Table1.Language ='french' and Table2.programmingword = "yes" does this make sense? im looking for exampes/ tutorials for how to do this? or can anyone recommend any good books on linq?
View 3 Replies
Apr 29, 2010
I have a dataset that comprises of multiple tables (about 20) linked together with constraints to reflect the relational database. My question(s):
1) Will having multiple tables affect performance? I'm asking this as there are often times I would not need to get data for all the tables, but only for specific tables.
2) If it does affect performance, would breaking up the tables into their own datasets be the best solution. It seems to me that by doing that, you would lose the ability to apply constraints among tables and tables that might exist in multiple datasets will be subjected to replication.
I would like to have everything in one dataset as I get a view of the related tables and their relationships and also because the Fill method allow me to fill tables, maintaining the hierarchy of the data, but performance is still a key factor.
View 5 Replies
Jan 24, 2011
I'm a newbie to this so bear with me. From what I've read online LINQ to Entity should see the relationships without having to specify the joins between multiple tables. I have a relationship that goes across 4 tables Employee, EmployeeDepartmentLink, EmployeeProjectLink, and Project. The idea is an employee can be in multiple departments and work on multiple projects with it being specified which department the employee is in for that project.
This is a C# MVC3 program connecting to a SQL database using VS 2010 Express.
I have no problem with running this query to find employees on a project:
[Code]....
Tring to do the reverse, finding projects that an employee worked on results in an error:
[Code]....
Error message: The specified type member 'EmployeeDepartmentLinks' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported. So I have 2 questions,
1) Why would it work in one direction and not the other? I've played around with it for a couple days with the same results.
2) Does it matter that my primary key and foreign keys are not named the same since all the relationships are connected in SQL? Ex: in Employee (EmployeePK)--(EmployeeFK) in EmployeeDepartmentLink Trying different stuff in LINQPad I was able to get the 2nd query to work if I specified all the joins which I thought was not neccesary with LINQ to Entity. I converted an SQL query that I made on the server into a LINQ query below which works.
View 5 Replies
