none
Save Generated SQL Statement to a Database RRS feed

  • Question

  • I am new to LINQ. I'd like to save the generated SQL statement from a LINQ to SQL statement and save it to a database. Can someone show me how to do this?
    Saturday, September 26, 2009 2:56 PM

Answers

  • Hi Randy,

    If you want to analyze the generated statements for debugging purposes, then you can have a look at How to: Display Generated SQL (LINQ to SQL) and LINQ to SQL Debug Visualizer. Another option would be to use LINQPad.

    If you want to use the generated SQL for something else, e..g saving it to database, you can use the DataContext.GetCommand() method as
    LinqDataClasses context = new LinqDataClasses();
    var query = from t in context.MyTable 
                      ......
    string generatedSQL = context.GetCommand(query).CommandText;


    Hope that helps,

    Regards,

    Syed Mehroz Alam
    My Blog | My Articles
    • Marked as answer by Yichun_Feng Tuesday, October 13, 2009 2:56 AM
    Monday, September 28, 2009 4:51 AM
  • Hi Randy,

     

    Welcome to MSDN forums!

     

    I am not exactly sure about your issue. Do you mean to get the generated SQL statements while executing the LINQ to SQL statement?

     

    If it is the case, you have two options:

    1) Get the SQL statements by coding.

    You can refer to this blog for detail steps:

    http://mattberseth.com/blog/2008/01/profiling_linq_to_sql_using_th.html

     

    2) Get the SQL statements manually by SQL Server Profiler.

    Before running the project, open SQL Server Profiler under Microsoft SQL Server 200X.Performance Tools.

    In the main menu, choose File -> New Trace -> Connect the SQL Server -> Run.

    Then, you can run your project and you’ll get the SQL statement in the Profiler.

     

    And you also mentioned that you want to save the SQL statements to database. For example, it is a query statement. I don’t think you can save it to database. However, you can save it as SQL file or other forms files.

     

     

    Does this works for you? If you have any questions or concerns, please clarify your issue and we will have a further discussion.

     

     

    Have a nice day!

     

     

    Best Regards

    Yichun Feng


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Yichun_Feng Tuesday, October 13, 2009 2:57 AM
    Monday, September 28, 2009 5:51 AM

All replies

  • Hi Randy,

    If you want to analyze the generated statements for debugging purposes, then you can have a look at How to: Display Generated SQL (LINQ to SQL) and LINQ to SQL Debug Visualizer. Another option would be to use LINQPad.

    If you want to use the generated SQL for something else, e..g saving it to database, you can use the DataContext.GetCommand() method as
    LinqDataClasses context = new LinqDataClasses();
    var query = from t in context.MyTable 
                      ......
    string generatedSQL = context.GetCommand(query).CommandText;


    Hope that helps,

    Regards,

    Syed Mehroz Alam
    My Blog | My Articles
    • Marked as answer by Yichun_Feng Tuesday, October 13, 2009 2:56 AM
    Monday, September 28, 2009 4:51 AM
  • Hi Randy,

     

    Welcome to MSDN forums!

     

    I am not exactly sure about your issue. Do you mean to get the generated SQL statements while executing the LINQ to SQL statement?

     

    If it is the case, you have two options:

    1) Get the SQL statements by coding.

    You can refer to this blog for detail steps:

    http://mattberseth.com/blog/2008/01/profiling_linq_to_sql_using_th.html

     

    2) Get the SQL statements manually by SQL Server Profiler.

    Before running the project, open SQL Server Profiler under Microsoft SQL Server 200X.Performance Tools.

    In the main menu, choose File -> New Trace -> Connect the SQL Server -> Run.

    Then, you can run your project and you’ll get the SQL statement in the Profiler.

     

    And you also mentioned that you want to save the SQL statements to database. For example, it is a query statement. I don’t think you can save it to database. However, you can save it as SQL file or other forms files.

     

     

    Does this works for you? If you have any questions or concerns, please clarify your issue and we will have a further discussion.

     

     

    Have a nice day!

     

     

    Best Regards

    Yichun Feng


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Yichun_Feng Tuesday, October 13, 2009 2:57 AM
    Monday, September 28, 2009 5:51 AM
  • Hi Randy,

     

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards

    Yichun Feng


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, October 2, 2009 2:50 AM
  • Syed,

    Your second option is what I am looking to do. Are you aware of any way to automatically saved the generated SQL to a database? For example, what I would really like to be able to do is to somehow hook onto an event that fires each time Context.Log sends output to its stream, capture that output and send it to a database. Is this possible?

    Randy
    Friday, October 2, 2009 12:26 PM
  • Yichun,

    Thank you very much. What I would like to be able to do is to somehow automatically send Context.Log output to my database. Ideally, I would like to be able to hook into some event that fires each time Context.Log sends data to its stream, capture the data and send it to the database. If I could do this, it would save me a lot of coding.

    Randy
    Friday, October 2, 2009 12:29 PM
  • Randy,

    Since the dataContext.Log property expects a TextReader, so you can write derive from TextReader and provide appropriate implementation of Write methods to write to a database.




    Syed Mehroz Alam
    My Blog | My Articles
    Friday, October 2, 2009 4:40 PM
  • Hi Randy,

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards

    Yichun Feng


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, October 5, 2009 2:39 AM
  • I am new to LINQ. I'd like to save the generated SQL statement from a LINQ to SQL statement and save it to a database. Can someone show me how to do this?

    If you want to do that for query performance / profiling reasons - take a look at http://huagati.blogspot.com/2009/06/profiling-linq-to-sql-applications.html and http://www.huagati.com/L2SProfiler/


    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    Monday, October 5, 2009 5:23 AM
    Answerer