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.

88 comments:

  1. THanks for this article

    ReplyDelete
  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. Thanks for sharing informative article… Know about How to Change BSNL WiFi Password from techfizy.

    ReplyDelete

  9. 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
  10. I believe there are many more pleasurable opportunities ahead for individuals that looked at your site.
    google-cloud-platform-training-in-chennai

    ReplyDelete
  11. 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
  12. 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
  13. 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
  14. 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
  15. 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
  16. 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
  17. I love the information you provide here and can’t wait to take a look when I get home.
    nebosh course in chennai

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

    appvn app

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

    ReplyDelete
  20. 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
  21. 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
  22. 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
  23. Thanks for sharing the information.

    Please check out Data Science Certification in Pune

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

    data science course in pune

    ReplyDelete
  25. 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
  26. 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

  27. 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
  28. 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
  29. 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
  30. 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
  31. 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
  32. 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
  33. 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
  34. 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
  35. 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
  36. 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
  37. 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
  38. 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

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

    ReplyDelete
  40. This post is very simple to read and appreciate without leaving any details out. Great work!
    data scientist course

    ReplyDelete

  41. This is a really very nice post you shared, I like the post, thanks for sharing...

    Best Institute for Data Science in Hyderabad

    ReplyDelete
  42. Thank you quite much for discussing this type of helpful informative article. Will certainly stored and reevaluate your Website.

    AWS Training in Hyderabad

    ReplyDelete
  43. Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting. Thanks for sharing.
    data science training in malaysia


    ReplyDelete
  44. I was actually browsing the internet for certain information, accidentally came across your blog found it to be very impressive. I am elated to go with the information you have provided on this blog, eventually, it helps the readers whoever goes through this blog. Hoping you continue the spirit to inspire the readers and amaze them with your fabulous content.
    th
    Data Science Course in Faridabad

    ReplyDelete
  45. Thanks a lot. You have done an excellent job. I enjoyed your blog . Nice efforts.
    Visit us: Dot Net Training Online India
    Visit us: .Net Online Training Hyderabad

    ReplyDelete
  46. The article posted was very informative and useful. You people are doing a great job. Keep going.
    data science training

    ReplyDelete

  47. Thanks for sharing this information. I really like your blog post very much. You have really shared a informative and interesting blog post .
    data scientist training in hyderabad

    ReplyDelete
  48. this is really nice to read..informative post is very good to read..thanks a lot!
    data scientist training in malaysia

    ReplyDelete
  49. It’s good to check this kind of website. I think I would so much from you. data scientist course in noida

    ReplyDelete
  50. Extremely overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one. Continue posting. A debt of gratitude is in order for sharing.business analytics course in warangal

    ReplyDelete
  51. Nice blog and informative content. I am impressed a lot with your blog. Keep up your work in further blogs.
    Data Science Online Training in Hyderabad

    ReplyDelete
  52. I’m excited to uncover this page. I need to to thank you for ones time for this particularly fantastic read!! I definitely really liked every part of it and i also have you saved to fav to look at new information in your site. data scientist course in kanpur

    ReplyDelete
  53. It is imperative that we read blog post very carefully. I am already done it and find that this post is really amazing. data science course in surat

    ReplyDelete
  54. Hello! I just wish to give an enormous thumbs up for the nice info you've got right here on this post. I will probably be coming back to your weblog for more soon! data scientist course in surat

    ReplyDelete
  55. Best AWS Training provided by Vepsun in Bangalore for the last 12 years. Our Trainer has more than 20+ Years
    of IT Experience in teaching Virtualization and Cloud topics.. we are very delighted to say that Vepsun is
    the Top AWS cloud training Provider in Bangalore. We provide the best atmosphere for our students to learn.
    Our Trainers have great experience and are highly skilled in IT Professionals. AWS is an evolving cloud
    computing platform provided by Amazon with a combination of IT services. It includes a mixture of
    infrastructure as service and packaged software as service offerings and also automation. We have trained
    more than 10000 students in AWS cloud and our trainer Sameer has been awarded as the best Citrix and Cloud
    trainer in india.

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

    ReplyDelete
  57. this is great article, how ever i am having issue with this
    Could not load file or assembly 'Microsoft.Identity.Client, Version=4.22.0.0, Culture=neutral, PublicKeyToken=0a613f4dd989e8ae'

    ReplyDelete
  58. I searched in google about sap course but i found many sites, finally i got good information in your site thanks for sharing.
    If you need any construction supervisor course related service please check our website Diploma Engineering Classes in Ambernath

    ReplyDelete
  59. Thank you for your information.Lab Furniture Manufacturers in Chennai is a manufacturer of laboratory furniture and laboratory workstations in Chennai. They offer a range of products including laboratory furniture, laboratory workstations, and laboratory benches.Lab Furniture Manufacturers in Chennai is a leading lab furniture manufacturer in Chennai. They specialize in custom-designing quality pharmaceutical laboratory furniture.They follow principles such as integrity, quality control, ergonomics, customization, and a complete solutions approach.Lab furniture manufacturers offer a wide range of laboratory furniture solutions for various industries, including healthcare, biotech, food and beverage, engineering, and more. They design, build, and install laboratory furniture for any application, ensuring that the lab is held to the highest industry standards.

    ReplyDelete

Comments to this post

LinkWithin

Related Posts with Thumbnails