//===============================================================================
// Copyright © Microsoft Corporation and Mark Seemann.  All rights reserved.
// THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
// OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
// LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
// FITNESS FOR A PARTICULAR PURPOSE.
//===============================================================================

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Security.Permissions;

namespace Ploeh.Common.DBTest
{
    /// <summary>
    /// Enables integration testing against SQL Server.
    /// </summary>
    /// <remarks>
    /// <para>
    /// <b>SqlServerIntegrationTester</b> contains helper methods to create and delete SQL server
    /// databases for testing purposes. To create a transient database for testing purposes, use
    /// the <see cref="CreateTestDatabase"/>. To delete it again, use the
    /// <see cref="DeleteTestDatabase"/> method.
    /// </para>
    /// <para>
    /// The <see cref="InitializationScripts"/> and <see cref="CleanUpScripts"/> collections are
    /// used to define T-SQL scripts for defining the database schema, etc.
    /// </para>
    /// </remarks>
	public class SqlServerIntegrationTester
	{
        private string server_;
        private string testDatabaseName_;
        private List<string> initializationScripts_;
        private List<string> cleanUpScripts_;

        /// <summary>
        /// Initializes a new instance of the <see cref="SqlServerIntegrationTester"/> class.
        /// </summary>
        /// <remarks>
        /// <para>
        /// Before a new instance of the <see cref="SqlServerIntegrationTester"/> class is used,
        /// the <see cref="Server"/> and <see cref="TestDatabaseName"/> properties must be set.
        /// </para>
        /// </remarks>
        public SqlServerIntegrationTester()
        {
            this.initializationScripts_ = new List<string>();
            this.cleanUpScripts_ = new List<string>();
        }

        /// <summary>
        /// Gets or sets the name of the database server.
        /// </summary>
        public string Server
        {
            get { return this.server_; }
            set { this.server_ = value; }
        }

        /// <summary>
        /// Gets or sets the name of the test database that will be created by
        /// <see cref="CreateTestDatabase"/>.
        /// </summary>
        public string TestDatabaseName
        {
            get { return this.testDatabaseName_; }
            set { this.testDatabaseName_ = value; }
        }

        /// <summary>
        /// Gets the connection string for the test database.
        /// </summary>
        public string TestDatabaseConnectionString
        {
            get 
            {
                SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
                csb.DataSource = this.Server;
                csb.InitialCatalog = this.TestDatabaseName;
                csb.IntegratedSecurity = true;
                csb.Pooling = false;
                return csb.ConnectionString;
            }
        }

        /// <summary>
        /// Gets the connection string for the master database.
        /// </summary>
        public string MasterDatabaseConnectionString
        {
            get 
            {
                SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
                csb.DataSource = this.Server;
                csb.InitialCatalog = "master";
                csb.IntegratedSecurity = true;
                csb.Pooling = false;
                return csb.ConnectionString;
            }
        }

        /// <summary>
        /// Gets the list of initialization scripts.
        /// </summary>
        public IList<string> InitializationScripts
        {
            get { return this.initializationScripts_; }
        }

        /// <summary>
        /// Gets the list of clean-up scripts.
        /// </summary>
        public IList<string> CleanUpScripts
        {
            get { return this.cleanUpScripts_; }
        }

		/// <summary>
		/// Creates the test database and database objects.
		/// </summary>
        [PermissionSet(SecurityAction.LinkDemand, Name = "FullTrust")]
        public void CreateTestDatabase()
		{
            this.DeleteTestDatabase();

			// Create the database.
            SqlServerIntegrationTester.ExecuteNonQuery(this.MasterDatabaseConnectionString,
                "CREATE DATABASE {0}", this.TestDatabaseName);

			// Create the objects in the database.
			foreach(string scriptFile in this.InitializationScripts)
			{
                this.RunSqlScript(scriptFile);
			}
		}

		/// <summary>
		/// Deletes the test database.
		/// </summary>
		public void DeleteTestDatabase()
		{
            SqlServerIntegrationTester.ExecuteNonQuery(this.MasterDatabaseConnectionString,
                "IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = '{0}') BEGIN DROP DATABASE {0} END",
                this.TestDatabaseName);
		}

		/// <summary>
		/// Executes a SQL statement with no return values.
		/// </summary>
		/// <param name="sqlStatement">The SQL statement to execute.</param>
		/// <param name="args">Arguments for string formatting, if any.</param>
        public void ExecuteNonQuery(string sqlStatement, params object[] args)
		{
            SqlServerIntegrationTester.ExecuteNonQuery(this.TestDatabaseConnectionString,
                sqlStatement, args);
		}

        /// <summary>
        /// Executes a SQL statement and returns a <see cref="SqlDataReader"/>.
        /// </summary>
        /// <param name="sqlStatement">The T-SQL statement to execute.</param>
        /// <param name="args">Arguments for string formatting, if any.</param>
        /// <returns>
        /// A <see cref="SqlDataReader"/> over the result set for <paramref name="sqlStatement"/>.
        /// </returns>
        /// <remarks>
        /// <para>
        /// The client is responsible for closing the returned <see cref="SqlDataReader"/> after
        /// use. Closing the reader will automatically close the underlying connection as well.
        /// </para>
        /// </remarks>
        public SqlDataReader ExecuteReader(string sqlStatement, params object[] args)
        {
            SqlConnection con = new SqlConnection(this.TestDatabaseConnectionString);
            SqlCommand cmd = new SqlCommand(string.Format(sqlStatement, args), con);
            con.Open();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }


		/// <summary>
		/// Executes a SQL statement and returns a single return value.
		/// </summary>
		/// <param name="sqlStatement">The SQL statement to execute.</param>
		/// <param name="args">Arguments for string formatting, if any.</param>
		/// <returns>The return value from the query.</returns>
        public object ExecuteScalar(string sqlStatement, params object[] args)
		{
            using (SqlConnection conn = new SqlConnection(this.TestDatabaseConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(string.Format(CultureInfo.CurrentCulture, sqlStatement, args), conn))
                {
                    conn.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }

        /// <summary>
        /// Resets all tables after each test.
        /// </summary>
        [PermissionSet(SecurityAction.LinkDemand, Name = "FullTrust")]
        public void ResetTables()
        {
            foreach (string scriptFile in this.CleanUpScripts)
            {
                this.RunSqlScript(scriptFile);
            }
        }

        /// <summary>
        /// Executes a non-query
        /// </summary>
        /// <param name="connectionString">The connection string used for the query.</param>
        /// <param name="sqlStatement">The T-SQL statement to execute.</param>
        /// <param name="args">Any parameters that will be applied to the statement.</param>
        private static void ExecuteNonQuery(string connectionString, string sqlStatement, params object[] args)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(string.Format(CultureInfo.CurrentCulture, sqlStatement, args), con))
                {
                    con.Open();
                    cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// Runs a SQL script file.
        /// </summary>
        /// <param name="scriptFile">The file name of the script file.</param>
        [PermissionSet(SecurityAction.LinkDemand, Name = "FullTrust")]
        private void RunSqlScript(string scriptFile)
        {
            using (Process p = new Process())
            {
                p.StartInfo.FileName = "sqlcmd.exe";
                p.StartInfo.Arguments =
                    string.Format(CultureInfo.CurrentCulture,
                    "-b -S {0} -d {1} -i \"{2}\"",
                    this.Server,
                    this.TestDatabaseName, Path.GetFullPath(scriptFile));
                p.StartInfo.UseShellExecute = false;
                p.StartInfo.CreateNoWindow = true;
                p.StartInfo.RedirectStandardOutput = true;
                p.StartInfo.RedirectStandardError = true;
                p.Start();
                string output = p.StandardOutput.ReadToEnd();
                string error = p.StandardError.ReadToEnd();
                p.WaitForExit();
                if (p.ExitCode != 0)
                {
                    throw new InvalidOperationException(string.Format(CultureInfo.CurrentCulture, "An error occurred while executing a SQL script file. \nScript output: {0}\nScript error: {1}", output, error));
                }
            }
        }
	}
}