Reply to thread

I migrate MySQL MP 2 server DB to SQL Server using migration tool.

Then i rewrite coding for plugin using System.Data.SqlClient.

my coding for SQLServerDatabase

--------------------------------------------------------------

#region Copyright (C) 2007-2014 Team MediaPortal

/*

    Copyright (C) 2007-2014 Team MediaPortal

    https://www.team-mediaportal.com

    This file is part of MediaPortal 2

    MediaPortal 2 is free software: you can redistribute it and/or modify

    it under the terms of the GNU General Public License as published by

    the Free Software Foundation, either version 3 of the License, or

    (at your option) any later version.

    MediaPortal 2 is distributed in the hope that it will be useful,

    but WITHOUT ANY WARRANTY; without even the implied warranty of

    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License

    along with MediaPortal 2. If not, see <http://www.gnu.org/licenses/>.

*/

#endregion

using System;

using System.Data;

using System.Data.SqlTypes;

using MediaPortal.Backend.Database;

using System.Data.SqlServerCe;

using System.IO;

using MediaPortal.Backend.Services.Database;

using MediaPortal.Common.PathManager;

using MediaPortal.Common;

using MediaPortal.Common.Logging;

using System.Data.SqlClient;

namespace MediaPortal.Database.SQLCE

{

  public class SQLCEDatabase : ISQLDatabase

  {

    public const string SQLCE_DATABASE_TYPE = "SQL SERVER";

    public const string DATABASE_VERSION = "11.0.2100.60";

    public const int MAX_NUM_CHARS_CHAR_VARCHAR = 4000;

    public const int LOCK_TIMEOUT = 30000; // Time in ms the database will wait for a lock

    public const int MAX_BUFFER_SIZE = 2048;

    /// <summary>

    /// Maximum size of the shared memory region in MB which SQL CE uses for shared database connections.

    /// The maximum database size defaults to 256 MB, which might be too small for big databases.

    /// </summary>

    public const int INITIAL_MAX_DATABASE_SIZE = 1024;

    /// <summary>

    /// Buffer, the "Max Database Size" parameter must be bigger than the actual database file size, in MB.

    /// </summary>

    public const int DATABASE_SIZE_BUFFER = 256;

    public const string DEFAULT_DATABASE_FILE = "Datastore.sdf";

    protected string _connectionString;

    public SQLCEDatabase()

    {

        //--START My coding

        SqlConnection conn;

        _connectionString = @"Data Source=CHINTHAKA-PC\SQLEXPRESS;Database=mp2server;Integrated Security=True;User ID=sa;Password=;connection timeout=30";

        conn = new SqlConnection(_connectionString);

        try

        {

            conn.Open();

            conn.Close();

        }

        catch (Exception ex)

        {

            ServiceRegistration.Get<ILogger>().Critical("SQL server Error establishing database connection", ex);

            throw;

        }

        //--END

    #region ISQLDatabase implementation

    public string DatabaseType

    {

      get { return SQLCE_DATABASE_TYPE; }

    }

    public string DatabaseVersion

    {

      get { return DATABASE_VERSION; }

    }

    public uint MaxObjectNameLength

    {

      get { return 30; }

    }

    public string GetSQLType(Type dotNetType)

    {

      if (dotNetType == typeof(DateTime))

        return "DATETIME";

      if (dotNetType == typeof(Char))

        return "NCHAR(1)";

      if (dotNetType == typeof(Boolean))

        return "BIT";

      if (dotNetType == typeof(Single))

        return "REAL";

      if (dotNetType == typeof(Double))

        return "FLOAT";

      if (dotNetType == typeof(Byte) || dotNetType == typeof(SByte))

        return "TINYINT";

      if (dotNetType == typeof(UInt16) || dotNetType == typeof(Int16))

        return "SMALLINT";

      if (dotNetType == typeof(UInt32) || dotNetType == typeof(Int32))

        return "INTEGER";

      if (dotNetType == typeof(UInt64) || dotNetType == typeof(Int64))

        return "BIGINT";

      if (dotNetType == typeof(Guid))

        return "UNIQUEIDENTIFIER";

      if (dotNetType == typeof(byte[]))

        return "IMAGE";

      return null;

    }

    public string GetSQLVarLengthStringType(uint maxNumChars)

    {

      if (maxNumChars > MAX_NUM_CHARS_CHAR_VARCHAR)

        return "NTEXT";

      return "NVARCHAR(" + maxNumChars + ")";

    }

    public string GetSQLFixedLengthStringType(uint maxNumChars)

    {

      if (maxNumChars > MAX_NUM_CHARS_CHAR_VARCHAR)

        return "NTEXT";

      return "NCHAR(" + maxNumChars + ")";

    }

    public bool IsCLOB(uint maxNumChars)

    {

      return maxNumChars > MAX_NUM_CHARS_CHAR_VARCHAR;

    }

    public IDbDataParameter AddParameter(IDbCommand command, string name, object value, Type type)

    {

      if (type == typeof(byte[]))

      {

        SqlParameter result = (SqlParameter) command.CreateParameter();

        result.ParameterName = name;

        result.Value = value ?? DBNull.Value;

        result.SqlDbType = SqlDbType.Image;

        command.Parameters.Add(result);

        return result;

      }

      // We need to use NText as parameter type, if the value is of "IsCLOB" type.

      if (type == typeof(string) && value != null && IsCLOB((uint) value.ToString().Length))

      {

        SqlParameter result = (SqlParameter) command.CreateParameter();

        result.ParameterName = name;

        result.Value = value;

        result.SqlDbType = SqlDbType.NText;

        command.Parameters.Add(result);

        return result;

      }

      return DBUtils.AddSimpleParameter(command, name, value, type);

    }

    public object ReadDBValue(Type type, IDataReader reader, int colIndex)

    {

      if (reader.IsDBNull(colIndex))

        return null;

      if (type == typeof(byte[]))

      {

        SqlBinary result = ((SqlDataReader) reader).GetSqlBinary(colIndex);

        return result.Value;

      }

      return DBUtils.ReadSimpleDBValue(type, reader, colIndex);

    }

    public ITransaction BeginTransaction(IsolationLevel level)

    {

      return SQLCETransaction.BeginTransaction(this, _connectionString, level);

    }

    public ITransaction BeginTransaction()

    {

      return BeginTransaction(IsolationLevel.ReadCommitted);

    }

    public bool TableExists(string tableName)

    {

      using (SqlConnection conn = new SqlConnection(_connectionString))

      {

        conn.Open();

        using (IDbCommand cmd = conn.CreateCommand())

        {

          cmd.CommandText = @"SELECT COUNT(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='" + tableName + "'";

          int cnt = (int) cmd.ExecuteScalar();

          return (cnt == 1);

        }

      }

    }

    public string CreateStringConcatenationExpression(string str1, string str2)

    {

      return str1 + "+" + str2;

    }

    public string CreateSubstringExpression(string str1, string posExpr)

    {

      return "SUBSTRING(" + str1 + "," + posExpr + "," + Int32.MaxValue + ")"; // Int32.MaxValue seems to be the biggest supported value

    }

    public string CreateSubstringExpression(string str1, string posExpr, string lenExpr)

    {

      return "SUBSTRING(" + str1 + "," + posExpr + "," + lenExpr + ")";

    }

    public string CreateDateToYearProjectionExpression(string selectExpression)

    {

      return "DATEPART(YEAR, " + selectExpression + ")";

    }

    #endregion

  }

}

---------------------------------------------

my coding for SQLServer Transaction

-----------------------------------------------

#region Copyright (C) 2007-2014 Team MediaPortal

/*

    Copyright (C) 2007-2014 Team MediaPortal

    https://www.team-mediaportal.com

    This file is part of MediaPortal 2

    MediaPortal 2 is free software: you can redistribute it and/or modify

    it under the terms of the GNU General Public License as published by

    the Free Software Foundation, either version 3 of the License, or

    (at your option) any later version.

    MediaPortal 2 is distributed in the hope that it will be useful,

    but WITHOUT ANY WARRANTY; without even the implied warranty of

    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License

    along with MediaPortal 2. If not, see <http://www.gnu.org/licenses/>.

*/

#endregion

using System.Data;

using MediaPortal.Backend.Database;

//using System.Data.SqlServerCe;

using MediaPortal.Backend.Services.Database;

using System.Data.SqlClient;

namespace MediaPortal.Database.SQLCE

{

    public class SQLCETransaction : ITransaction

    {

      #region Protected fields

      protected SqlTransaction _transaction;

      protected ISQLDatabase _database;

      protected IDbConnection _connection;

      #endregion

      #region ITransaction Member

      public ISQLDatabase Database

      {

        get { return _database; }

      }

      public IDbConnection Connection

      {

        get { return _connection; }

      }

      public void Commit()

      {

        _transaction.Commit();

        Dispose();

      }

      public void Rollback()

      {

        _transaction.Rollback();

        Dispose();

      }

      public IDbCommand CreateCommand()

      {

        IDbCommand result = _connection.CreateCommand();

#if DEBUG

        // Return a LoggingDbCommandWrapper to log all CommandText to logfile in DEBUG mode.

        result = new LoggingDbCommandWrapper(result);

#endif

        result.Transaction = _transaction;

        return result;

      }

      #endregion

      #region IDisposable Member

      public void Dispose()

      {

        if (_connection != null)

        {

          _connection.Close();

          _connection = null;

        }

      }

      #endregion

      public static ITransaction BeginTransaction(SQLCEDatabase database, string connectionString, IsolationLevel level)

      {

        SqlConnection connection = new SqlConnection(connectionString);

        connection.Open();

        return new SQLCETransaction(database, connection, connection.BeginTransaction(level));

      }

      public SQLCETransaction(ISQLDatabase database, IDbConnection connection, SqlTransaction transaction)

      {

        _database = database;

        _connection = connection;

        _transaction = transaction;

      }

    }

}

--------------------------------------------------------------------

I build the pluging using VS 2013 and copy the .DLL fils to 'C:\Program Files (x86)\Team MediaPortal\MP2-Server\Plugins\SQLCEDatabase'

folder then start the 'MediaPortal 2 server service' using services window but the service is not starting.

Please, can you give me some ideas to solve this.

Thanx.