Dynamic query in Linq to sql RSS

• • Reply
  

  Ravi_Rt

  Member

  53 Points

  48 Posts

  Dynamic query in Linq to sql

  Feb 28, 2012 05:01 PM|LINK

  I am sending query in a string from linq to sql

  string _ObjCocktail = "declare @max_id int Insert into dbo.Cocktails(name,cocktail_category_id,instrustions) values ('Mojto',1,'Instruction of the cocktail') select @max_id = MAX(dbo.Cocktails.id) from dbo.Cocktails Insert into RecipeDetails(cocktail_id,mixers_id,liquor_id,quantity,unit_id,other_ingredients_id)  values  ( @max_id, null,2,null,1,null) Insert into RecipeDetails(cocktail_id,mixers_id,liquor_id,quantity,unit_id,other_ingredients_id)  values  ( @max_id, null,7,null,1,null)";

  public void InsertCocktail()
          {
              try
              {
                   _DataContext.InsertCocktails(_ObjCocktail);
              }
              catch (Exception ex)
              {
                  
              }

  and 

  i am handling that string in sql like this.

  ALTER procedure [dbo].[InsertCocktails]
  (
  @query varchar(max)
  )
  as
  begin
  exec @query
  end

  It is going to Exception with this error.

  The name 'declare @max_id int Insert into dbo.Cocktails(name,cocktail_category_id,instrustions) values ('Mojto',1,'Instruction of the cocktail') select @max_id = MAX(dbo.Cocktails.id) from dbo.Cocktails Insert into RecipeDetails(cocktail_id,mixers_id,liquor_id,quantity,unit_id,other_ingredients_id)  values  ( @max_id, null,2,null,1,null) Insert into RecipeDetails(cocktail_id,mixers_id,liquor_id,quantity,unit_id,other_ingredients_id)  values  ( @max_id, null,7,null,1,null)' is not a valid identifier.

  When i copy paste this query string in sql it is running fine.

  Harry
  • Edited by ravi_rt on Feb 28, 2012 05:01 PM
• • Reply
  

  kuber.manral

  Contributor

  3051 Points

  714 Posts

  Re: Dynamic query in Linq to sql

  Feb 28, 2012 05:14 PM|LINK

  Hi Ravi,

              Please review following :

  http://blog.sqlauthority.com/2007/09/13/sql-server-difference-between-exec-and-execute-vs-exec-use-execexecute-for-sp-always/

  Hope it helps..

  Please "Mark As Answer;", if this Post helps you.
  Visit My Blog
• • Reply
  

  kuber.manral

  Contributor

  3051 Points

  714 Posts

  Re: Dynamic query in Linq to sql

  Feb 28, 2012 05:18 PM|LINK

  Hi Ravi,

            Try using EXEC() Method like below code :

  CREATE PROCEDURE runquery
  AS
  BEGIN
      DECLARE @tempquery varchar(255)
      select @tempquery = "select empl_id from empl" 
      exec (@tempquery)
  END

  Hope it helps...

  Please "Mark As Answer;", if this Post helps you.
  Visit My Blog
  • Marked as answer by ravi_rt on Feb 28, 2012 05:58 PM
• • Reply
  

  Ravi_Rt

  Member

  53 Points

  48 Posts

  Re: Dynamic query in Linq to sql

  Feb 28, 2012 05:40 PM|LINK

  kuber.manral sir thank you for replying

  Sir i am new to this field. Need some more favour from you.

  Sir i am generating query in form and then i am sending it to storeprocedure from code.

  I want to write a storeprocedure which take one parameter(Which has query inside it) and then execute it.

  Regards

  Harry
• • Reply
  

  kuber.manral

  Contributor

  3051 Points

  714 Posts

  Re: Dynamic query in Linq to sql

  Feb 28, 2012 05:51 PM|LINK

  Hi Ravi,

               Try below code, However this is not tested. So make atry and let us know the result..

  CREATE procedure [dbo].[InsertCocktails]
  (
  @query varchar(max)
  )
  as
  begin
  exec(@query)
  end

  
  

  Hope it helps..

  Please "Mark As Answer;", if this Post helps you.
  Visit My Blog
  • Marked as answer by ravi_rt on Feb 28, 2012 05:58 PM
• • Reply
  

  Ravi_Rt

  Member

  53 Points

  48 Posts

  Re: Dynamic query in Linq to sql

  Feb 28, 2012 05:58 PM|LINK

  Thankyou  Thankyou  Thankyou  Thankyou  Soooo much sir.. It worked..

  Harry
• • Reply
  

  kuber.manral

  Contributor

  3051 Points

  714 Posts

  Re: Dynamic query in Linq to sql

  Feb 28, 2012 06:23 PM|LINK

  Hi Ravi,

              Happy to help... ;)

  Please "Mark As Answer;", if this Post helps you.
  Visit My Blog