none
Please help RRS feed

  • Question

  • I am using VS 2008.

    All I want to do(and this is about the 2nd time I have ever used asp.net so a complete novice) is run a .SQl file from within my c# code.  i have tried the following code:

    using System.Data.SqlClient;

    using System.IO;

    using Microsoft.SqlServer.Management.Common;

    using Microsoft.SqlServer.Management.Smo;

     

    namespace ConsoleApplication1

    {

        class Program

        {

            static void Main(string[] args)

            {

                string sqlConnectionString = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True";

                FileInfo file = new FileInfo("C:\\myscript.sql");

                string script = file.OpenText().ReadToEnd();

                SqlConnection conn = new SqlConnection(sqlConnectionString);

                Server server = new Server(new ServerConnection(conn));

                server.ConnectionContext.ExecuteNonQuery(script);

            }

        }

    }


    but am getting errors not recognising the "Server" word.  Also

    using Microsoft.SqlServer.Management.Common;

    using Microsoft.SqlServer.Management.Smo; are not understood by VS


    Please help!


    Friday, February 6, 2009 1:01 PM

All replies

  • There should be no need to involve the:

      using Microsoft.SqlServer.Management.Common;

      using Microsoft.SqlServer.Management.Smo

    namespaces here. 

    Just use the SqlCommand class from the System.Data.SqlClient namespace.

            static void Main(string[] args)

            {

                string sqlConnectionString = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True";

                FileInfo file = new FileInfo("C:\\myscript.sql");

                string script = file.OpenText().ReadToEnd();

                SqlConnection conn = new SqlConnection(sqlConnectionString);

                
                
    conn.Open();
                   SqlCommand cmd = new SqlCommand
    (script, conn);
                   cmd.ExecuteNonQuery();
                   conn.Close();

                //Server server = new Server(new ServerConnection(conn));
                
    //server.ConnectionContext.ExecuteNonQuery(script);
           
    }

    HTH
    //Michael
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.

    Friday, February 6, 2009 1:48 PM
  • Dear Micheal,

    Many thanks for that.  I have tried(unsuccesfully) to implement the above.  What I should have done is provide the actual code I am using rather than an example from the Internet.  Here it is:

    protected void Page_Load(object sender, EventArgs e)
            {
                if (!this.IsPostBack)
                {
                    //Connect to SQL database
                  SqlConnection myConnection = new SqlConnection("Some connection details entered here");
                    myConnection.Open();
                   
                    string[] emailAL = new string[1000];
                    string emailSearch;
                        int i = 0;
                       SqlDataReader myReader = null;
                       SqlCommand myCommand = new SqlCommand("select * from tablename", myConnection);

                        myReader = myCommand.ExecuteReader();
                                      while (myReader.Read())
                                {
                                    emailAL[i] = myReader["Email_address"].ToString();
                                    i++;
                                }
                         myReader.Close();
                         SmtpClient smtp = new SmtpClient("127.0.0.1");


    The code is working fine and returning what i need.  What I wanted to do is replace the "select * from tablename" with a .sql file listing the full SQl statement.  At the moment we are manually running a script using SQL Query Analyser to update the table before running the code. 

    Sanjeev
    Friday, February 6, 2009 2:45 PM
  • If you want to do this first of all you need to be sure that all SQL statements inside of your file are semicolon-delimited. .NET Managed provider for SQL Server allows executing multiple SQL statements in single batch, but they must be delimited with semicolon (;). Second, you also need to be sure that source file does not contain any batch commands, like GO. Go is not part of Transact SQL syntax and only works within tools, like SQL Server Management Studio, Query Analyzer etc, but will be recognized by provider as an invalid statement and execution will fail.

    I am not really sure if your idea is good in terms of security. Since you are executing script from the Web application your code potentially is open to execute any type of script in database and it is dangerous. Why are you trying to do this from your code? What is your goal?

     


    Val Mazur (MVP) http://www.xporttools.net
    Monday, February 9, 2009 10:54 AM
    Moderator