Monday, June 7, 2010

Running sql script file from .Net code - C#.net and VB.net

Many a times we get into situations where we have to execute a .sql script file from .NET code on Sql Server for eg. either to create a new database, or creating a new table, or backing up or restoring database etc. But for doing so the SqlCommand class is not useful. We can't execute the batch commands in the .sql script file using the SqlCommand class because the .sql script  file contains 'GO' (batch finalizer command) in it. Since SQL Server 2005 there is another option which is much better and preferred. That option is using SMO library which comes with SQL Server and can be used for managing everything on SQL Server 2005. You can backup, restore databases, configure permissions, replication, etc.

The following example demonstrates how to do it. Here I have my .sql script file as myscript.sql and it is in C:\ directory.

The code for it in C# is as follows:

01using System.IO;
02using Microsoft.SqlServer.Management.Common;
03using Microsoft.SqlServer.Management.Smo;
04
05namespace ConsoleApplication1
06{
07    class Program
08    {
09        private static void Main(string[] args)
10        {
11            string sqlConnectionString = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True";
12            FileInfo file = new FileInfo("C:\\myscript.sql");
13            string script = file.OpenText().ReadToEnd();
14            SqlConnection conn = new SqlConnection(sqlConnectionString);
15            Server server = new Server(new ServerConnection(conn));
16            server.ConnectionContext.ExecuteNonQuery(script);
17        }
18    }
19}


The code for it in VB.Net is as follows:


01Imports System.Data.SqlClient
02Imports System.IO
03Imports Microsoft.SqlServer.Management.Common
04Imports Microsoft.SqlServer.Management.Smo
05
06Namespace ConsoleApplication1
07    Class Program
08        Private Shared Sub Main(args As String())
09            Dim sqlConnectionString As String "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True"
10            Dim file As New FileInfo("C:\myscript.sql")
11            Dim script As String = file.OpenText().ReadToEnd()
12            Dim conn As New SqlConnection(sqlConnectionString)
13            Dim server As New Server(New ServerConnection(conn))
14            server.ConnectionContext.ExecuteNonQuery(script)
15        End Sub
16    End Class
17End Namespace


Thus this will execute the .sql script file from the .Net code.

106 comments:

  1. THanks for this article

    ReplyDelete
    Replies
    1. IEEE Final Year Project centers make amazing deep learning final year projects ideas for final year students Final Year Projects for CSE to training and develop their deep learning experience and talents.

      IEEE Final Year projects Project Centers in India are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation.

      corporate training in chennai corporate training in chennai

      corporate training companies in india corporate training companies in india

      corporate training companies in chennai corporate training companies in chennai

      I have read your blog its very attractive and impressive. I like it your blog. Digital Marketing Company in Chennai

      Delete
  2. Hi. Thanks much.

    Can you please answer this question for me

    http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/ff46a43f-47cd-4783-8f16-07fa1e32e7f0

    ReplyDelete
  3. SQL Server version (7.0) is not supported.

    ReplyDelete
  4. will this handle if the script contains GO commands?
    using VS2008/SQL2008

    ReplyDelete
  5. Go VS 2010-> Project->Add Refrences
    browse : C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies
    Add
    1.Microsoft.SqlServer.Smo.dll
    2.Microsoft.SqlServer.Management.Sdk.Sfc.dll
    3.Microsoft.SqlServer.ConnectionInfo.dll

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. hy, did all assemblys but when run the code gives error : mixed mode assembly is built against version 'v2.0.50727' of the runtime and cant be loaded in the 4.0 runtine without adicional configuration information.
      So i added in app.config
      startup useLegacyV2RuntimeActivationPolicy="true"
      supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0,Profile=Client"
      startup
      runtime
      NetFx40_LegacySecurityPolicy enabled="true"
      runtime

      But i get the folowing error :
      An exception occured while executing Transact- Sql statment or batch.

      What can i do ?

      Delete
  6. how to create .sql File in Asp.ne C# coding

    ReplyDelete
  7. when I run the vb.net code above in console application, I got the following error:
    'Sub Main' was not found in 'ConsoleApplication1.Module1'

    Please help.

    ReplyDelete
  8. Hi Admin,
    Excellent blog and its totally loaded with valid posts on Java and .Net technology. Consider including RSS feed in your blog, so aspirants like me can follow your blog easily. .Net Training in Chennai

    ReplyDelete
  9. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    Android Training in Chennai
    Ios Training in Chennai

    ReplyDelete
  10. Thanks for sharing informative article… Know about How to Change BSNL WiFi Password from techfizy.

    ReplyDelete

  11. Dot Net is the best programming language ever. your blog have explained the excellent concept which help me to gain more info on C# programming language.

    dot net training institute in velachery |
    DOT NET Course Chennai

    ReplyDelete
  12. I believe there are many more pleasurable opportunities ahead for individuals that looked at your site.
    google-cloud-platform-training-in-chennai

    ReplyDelete
  13. Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like this.
    Devops Training in Chennai

    Devops Training in Bangalore

    Devops Training in pune

    ReplyDelete
  14. Were a gaggle of volunteers as well as starting off a brand new gumption within a community. Your blog furnished us precious details to be effective on. You've got completed any amazing work!
    python training institute in chennai
    python training in Bangalore
    python training in pune
    python online training

    ReplyDelete
  15. Really great post, I simply unearthed your site and needed to say that I have truly appreciated perusing your blog entries.
    python training in Bangalore
    python training in pune
    python online training

    ReplyDelete
  16. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...

    Blueprism training in tambaram

    Blueprism training in annanagar

    Blueprism training in velachery

    ReplyDelete
  17. Thank you so much for a well written, easy to understand article on this. It can get really confusing when trying to explain it – but you did a great job. Thank you!

    Data Science course in rajaji nagar | Data Science with Python course in chenni
    Data Science course in electronic city | Data Science course in USA
    Data science course in pune | Data science course in kalyan nagar

    ReplyDelete
  18. Great Article… I love to read your articles because your writing style is too good, its is very very helpful for all of us and I never get bored while reading your article because, they are becomes a more and more interesting from the starting lines until the end.

    java training in omr | oracle training in chennai

    java training in annanagar | java training in chennai

    ReplyDelete
  19. Good Explanation, I was really impressed by reading this .NET Online Course

    ReplyDelete
  20. This is such a good post. One of the best posts that I\'ve read in my whole life. I am so happy that you chose this day to give me this. Please, continue to give me such valuable posts. Cheers!
    angularjs Training in btm

    angularjs Training in electronic-city

    angularjs online Training

    angularjs Training in marathahalli

    angularjs interview questions and answers

    ReplyDelete
  21. Whoa! I’m enjoying the template/theme of this website. It’s simple, yet effective. A lot of times it’s very hard to get that “perfect balance” between superb usability and visual appeal. I must say you’ve done a very good job with this.

    Selenium Interview Questions and Answers

    Best Selenium Training in Chennai | Selenium Training Institute in Chennai | Besant Technologies

    Selenium Training in Bangalore | Best Selenium Training in Bangalore

    Free Selenium Tutorial |Selenium Webdriver Tutorial |For Beginners




    ReplyDelete
  22. Do you have a spam issue on this website; I also am a blogger, and I wanted to know your situation;
    nebosh course in chennai

    ReplyDelete
  23. I love the information you provide here and can’t wait to take a look when I get home.
    nebosh course in chennai

    ReplyDelete
  24. Your post is really awesome. Your blog is really helpful for me to develop my skills in a right way. Thanks for sharing this unique information with us.
    - Learn Digital Academy

    ReplyDelete
  25. Just found your post by searching on the Google, I am Impressed and Learned Lot of new thing from your post.

    appvn app

    ReplyDelete
  26. Great Article… I love to read your articles because your writing style is too good,
    its is very very helpful for all of us and I never get bored while reading your article because,
    they are becomes a more and more interesting from the starting lines until the end.
    Java training in Annanagar
    Java training in Chennai
    Java training in Chennai
    Java training in Electronic city
    Java training in Marathahalli

    ReplyDelete
  27. This comment has been removed by the author.

    ReplyDelete
  28. From your discussion I have understood that which will be better for me and which is easy to use. Really, I have liked your brilliant discussion. I will comThis is great helping material for every one visitor. You have done a great responsible person. i want to say thanks owner of this blog.

    Microsoft Azure online training
    Selenium online training
    Java online training
    Java Script online training
    Share Point online training


    ReplyDelete
  29. Great post! I am actually getting ready to across this information, It’s very helpful for this blog.Also great with all of the valuable information you have Keep up the good work you are doing well.

    devops online training

    aws online training

    data science with python online training

    data science online training

    rpa online training

    ReplyDelete
  30. Nice blog..! I really loved reading through this article. Thanks for sharing such a amazing post with us and keep blogging...Also Checkout: cryptocurrency training in chennai | blockchain coaching in chennai | blockchain certification training in chennai | blockchain certification course in chennai

    ReplyDelete
  31. We are talking about funfair today it’s a project for casino gaming and online gambling. When coming to Binance in funfair project. Funfair is related to casino gaming.We can play funfair by using Bitcoin through our Binance wallet.The main issue that we face regarding the funfair i.e. how it become fast enough to play for fun. I have also a question regarding it that etherium is also very fast enough. We are also more concern about security also we know that by spending money in casino game is also a very risky so if you have any question regarding it please call us on our Binance customer care number or Binance Support number 800-861-8259.

    ReplyDelete
  32. Very nice post here thanks for it .I always like and such a super contents of these post.Excellent and very cool idea and great content of different kinds of the valuable information's.

    Check out : big data hadoop training in chennai
    big data training in chennai chennai tamilnadu
    spark training in chennai

    ReplyDelete
  33. Really happy to say your post is very interesting. Keep sharing your information regularly for my future reference. Thanks Again.

    Check Out:
    reactjs training in chennai
    react training chennai
    react native course in chennai

    ReplyDelete
  34. Sql is also very import part of Data Science and during my Data Science Training in Pune

    i have studied this very important tool.

    ReplyDelete
  35. Thanks for sharing the information.

    Please check out Data Science Certification in Pune

    ReplyDelete
  36. thank you for such a informative blog. For more

    data science course in pune

    ReplyDelete
  37. Attend The Python training in bangalore From ExcelR. Practical Python training in bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Python training in bangalore.
    python training in bangalore

    ReplyDelete



  38. Get the most advanced AWS Course by Professional expert. Just attend a FREE Demo session.
    For further details call us @ 9884412301 | 9600112302
    AWS training in chennai | AWS training in velachery

    ReplyDelete
  39. I am impressed by the information
    aws course in Bangalore that you have on this blog. It shows how well you understand this subject.

    ReplyDelete
  40. Just saying thanks will not just be sufficient, for the fantasti c lucidity in your writing. I will instantly grab Python training in pune your rss feed to stay informed of any updates.

    ReplyDelete
  41. i have read about this article, it was very useful. i have gain more knowledge about this. if you want to learn more about data science then follow this given link:

    https://www.excelr.com/data-science-course-training-in-pune/

    ReplyDelete

  42. Thank you. A Great knowledge shared by you Data Science certification is designed to teach specific computer programming. Kindly keep on posting these types of tutorials.Here I also want to tell them who are seeking their career in Data science certification, can check this link.
    https://www.excelr.com/data-science-course-training-in-pune/

    ReplyDelete
  43. thanks for your information really good and very nice web design company in velachery

    ReplyDelete
  44. Attend The Data Analytics Course From ExcelR. Practical Data Analytics Course Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Analytics Course.
    ExcelR Data Analytics Course

    ReplyDelete
  45. I am looking for and I love to post a comment that "The content of your post is awesome" Great work!
    data analytics course

    ReplyDelete
  46. I gathered a lot of information through this article.Every example is easy to undestandable and explaining the logic easily.google cloud platform training in bangalore

    ReplyDelete
  47. Post is very useful. Thank you, this useful information.

    Looking for Best Training Institute in Bangalore , India. Softgen Infotech is the best one to offers 85+ computer training courses including IT Software Course in Bangalore , India. Also, it provides placement assistance service in Bangalore for IT.

    ReplyDelete
  48. Thank you for sharing such a nice post!

    Start your journey with In Software Training in Bangalore and get hands-on Experience with 100% Placement assistance from experts Trainers @eTechno Soft Solutions Located in BTM Layout Bangalore.

    ReplyDelete
  49. Great post!I am actually getting ready to across this information,i am very happy to this commands.Also great blog here with all of the valuable information you have.Well done,its a great knowledge. Real Time Experts training center bangalore

    ReplyDelete
  50. Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site.Real Time Experts Training in Bangalore

    ReplyDelete
  51. Great post!I am actually getting ready to across this information,i am very happy to this commands.Also great blog here with all of the valuable information you have.Well done,its a great knowledge.student review for Realtime Experts marathahalli bangalore

    ReplyDelete
  52. Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site. Real Time Experts Training in Bangalore center address bangalore

    ReplyDelete
  53. I just loved your article on the beginners guide to starting a blog.If somebody take this blog article seriously in their life, he/she can earn his living by doing blogging.thank you for thizs article. top blockchain online training

    ReplyDelete
  54. Thanks for giving me the time to share such nice information. Thanks for sharing.
    Data Science Course
    Data Science Course in Marathahalli

    ReplyDelete
  55. wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries.
    Data science Interview Questions

    ReplyDelete
  56. Attend The Data Analytics Courses From ExcelR. Practical Data Analytics Courses Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Analytics Courses.
    Data Analytics Courses
    Data Science Interview Questions

    ReplyDelete
  57. Somebody necessarily help to make severely posts I might state. This is the first time I frequented your website page and to this point? I surprised with the research you made to create this particular post extraordinary. Well done admin..




    Dot Net Training in Chennai | Dot Net Training in anna nagar | Dot Net Training in omr | Dot Net Training in porur | Dot Net Training in tambaram | Dot Net Training in velachery


    ReplyDelete
  58. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspried me to read more. keep it up.
    Correlation vs Covariance

    ReplyDelete
  59. Excellent Blog! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well. I wanted to thank you for this websites! Thanks for sharing. Great websites!
    data science certification

    ReplyDelete
  60. Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.
    data science certification

    ReplyDelete
  61. Nice information, valuable and excellent design, as share good stuff with good ideas and concepts, lots of great information and inspiration, both of which I need, thanks to offer such a helpful information here.
    data science course

    ReplyDelete
  62. Awesome blog. I enjoyed reading your articles. This is truly a great read for me. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work!
    data science certification

    ReplyDelete
  63. I like viewing web sites which comprehend the price of delivering the excellent useful resource free of charge. I truly adored reading your posting. Thank you!
    data science certification

    ReplyDelete
  64. Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.

    Simple Linear Regression

    Correlation vs Covariance

    ReplyDelete
  65. This post is much helpful for us. This is really very massive value to all the readers and it will be the only reason for the post to get popular with great authority.
    AWS training in chennai | AWS training in annanagar | AWS training in omr | AWS training in porur | AWS training in tambaram | AWS training in velachery

    ReplyDelete
  66. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple linear regression
    data science interview questions

    ReplyDelete
  67. This Was An Amazing ! I Haven't Seen This Type of Blog Ever ! Thankyou For Sharing, data scientist courses

    ReplyDelete
  68. This post is really helpful for us. I certainly love this website, keep on it.
    Data Science Course in Hyderabad

    ReplyDelete
  69. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple linear regression
    data science interview questions

    ReplyDelete
  70. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple linear regression
    data science interview questions

    ReplyDelete
  71. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me. oracle training in chennai

    ReplyDelete
  72. Amazing Article ! I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Simple Linear Regression
    Correlation vs covariance
    data science interview questions
    KNN Algorithm

    ReplyDelete
  73. This is my first time visit here. From the tremendous measures of comments on your articles.I deduce I am not only one having all the fulfillment legitimately here!
    data scientist course in noida

    ReplyDelete
  74. Thanks for Sharing This Article.It is very so much valuable content. I hope these Commenting lists will help to my website
    blockchain online training
    best blockchain online training
    top blockchain online training

    ReplyDelete
  75. Attend The Course in Data Analytics From ExcelR. Practical Course in Data Analytics Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Course in Data Analytics.
    Course in Data Analytics

    ReplyDelete
  76. Good Post! , it was so good to read and useful to improve my knowledge as an updated one, keep blogging.After seeing your article I want to say that also a well-written article with some very good information which is very useful for the readers....thanks for sharing it and do share more posts likethis. https://www.3ritechnologies.com/course/angular-js-training-institute-in-pune/

    ReplyDelete
  77. very well explained. I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Logistic Regression explained
    Correlation vs Covariance
    Simple Linear Regression
    data science interview questions
    KNN Algorithm

    ReplyDelete
  78. Great Article
    Cloud Computing Projects




    Networking Projects



    Final Year

    Projects for CSE





    JavaScript Training in Chennai



    JavaScript

    Training in Chennai




    The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals,

    Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer.

    Angular Training

    ReplyDelete
  79. Excellent blog thanks for sharing the valuable information..it becomes easy to read and easily understand the information.
    Useful article which was very helpful. also interesting and contains good information.
    to know about python training course , use the below link.

    Python Training in chennai

    Python Course in chennai

    ReplyDelete

  80. Impressive. Your story always brings hope and new energy. Keep up the good work.
    Best Digital Marketing Institute in Hyderabad

    ReplyDelete

Comments to this post

LinkWithin

Related Posts with Thumbnails