used2could
asked on
Combining LINQ to SQL with Inline SQL
I am converting a project that i am in the process of developing to use LINQ to SQL. My problem is a scheduled process that syncs data with a 3rd party via several XML files. I am building one huge sql script to run at the end of the xml parsing process to then execute in one trip to the database. A big part of this is a series of SQL "if" statements to see if something exists with some conditional logic and either update or insert a new record. From what i know LINQ doesn't handle conditional update or inserting without multiple database trips. I want to be able to get the generated SQL (select/insert/update/dele
Thanks
~B
Thanks
~B
ASKER
Thanks for the reply nasp.
Using the debugger to get the SQL hits the database each time you view it. Itested this with SQL Profiler.
Here is an example in SQL of what i need to in LINQ:
Declare @PersonId int,
Set @PersonId = 0;
Select @PersonId = PersonId from Persons where BadgeNumber = 'XXXXXXXXXXX';
IF @PersonId > 0
Begin
Update Persons Set Name = 'John', BadgeNumber = 'XXXXXXXXXXX'' Where PersonId = @PersonId
End
Else
Begin
Insert into Persons (Name, BadgedNumber) values('John', 'XXXXXXXXXXX'');
Select @PersonId = Scope_Identity();
End
I would loop and parse through lots of raw information to construct several of these types of statements into one SQL script and submit it to the database.
Using the debugger to get the SQL hits the database each time you view it. Itested this with SQL Profiler.
Here is an example in SQL of what i need to in LINQ:
Declare @PersonId int,
Set @PersonId = 0;
Select @PersonId = PersonId from Persons where BadgeNumber = 'XXXXXXXXXXX';
IF @PersonId > 0
Begin
Update Persons Set Name = 'John', BadgeNumber = 'XXXXXXXXXXX'' Where PersonId = @PersonId
End
Else
Begin
Insert into Persons (Name, BadgedNumber) values('John', 'XXXXXXXXXXX'');
Select @PersonId = Scope_Identity();
End
I would loop and parse through lots of raw information to construct several of these types of statements into one SQL script and submit it to the database.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Using a stream for the Log won't help because it has to hit the database before it outputs the SQL. I like the idea of running updates first then inserts but that wouldn't work because there are extra steps that involve child items of Person. LINQ does multiple calls when inserting Parent > Child objects to get the newly created ID for of the parent.
Its starting to look like i will have to write an extension to build simple selects/updates/inserts using reflection on the attached object :(
The new concept of querying collections via LINQ if awesome but i think LINQ to SQL differently doesn't live up to the hype.
Its starting to look like i will have to write an extension to build simple selects/updates/inserts using reflection on the attached object :(
The new concept of querying collections via LINQ if awesome but i think LINQ to SQL differently doesn't live up to the hype.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
http://weblogs.asp.net/scottgu/archive/2007/01/28/video-using-linq-with-asp-net-in-vs-orcas-part-1.aspx
But I think you might be able to get all the info you need in one trip, then run through it to sort, then insert it through linq if you want. I am not sure if I completely understand.