home
products
contribute
download
documentation
forum
Home
Forums
New posts
Search forums
What's new
New posts
All posts
Latest activity
Members
Registered members
Current visitors
Donate
Log in
Register
What's new
Search
Search
Search titles only
By:
New posts
Search forums
Search titles only
By:
Menu
Log in
Register
Navigation
Install the app
Install
More options
Contact us
Close Menu
Forums
MediaPortal 2
Plugin Development
MediaPortal 2 MS SQL Server database connecting
Contact us
RSS
JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser
.
Reply to thread
Message
<blockquote data-quote="chinthaka" data-source="post: 1100502" data-attributes="member: 149837"><p>I migrate MySQL MP 2 server DB to SQL Server using migration tool.</p><p>Then i rewrite coding for plugin using System.Data.SqlClient.</p><p></p><p>my coding for SQLServerDatabase</p><p>--------------------------------------------------------------</p><p>#region Copyright (C) 2007-2014 Team MediaPortal</p><p></p><p>/*</p><p> Copyright (C) 2007-2014 Team MediaPortal</p><p> <a href="https://www.team-mediaportal.com" target="_blank">https://www.team-mediaportal.com</a></p><p></p><p> This file is part of MediaPortal 2</p><p></p><p> MediaPortal 2 is free software: you can redistribute it and/or modify</p><p> it under the terms of the GNU General Public License as published by</p><p> the Free Software Foundation, either version 3 of the License, or</p><p> (at your option) any later version.</p><p></p><p> MediaPortal 2 is distributed in the hope that it will be useful,</p><p> but WITHOUT ANY WARRANTY; without even the implied warranty of</p><p> MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the</p><p> GNU General Public License for more details.</p><p></p><p> You should have received a copy of the GNU General Public License</p><p> along with MediaPortal 2. If not, see <<a href="http://www.gnu.org/licenses/>" target="_blank">http://www.gnu.org/licenses/></a>.</p><p>*/</p><p></p><p>#endregion</p><p></p><p>using System;</p><p>using System.Data;</p><p>using System.Data.SqlTypes;</p><p>using MediaPortal.Backend.Database;</p><p>using System.Data.SqlServerCe;</p><p>using System.IO;</p><p>using MediaPortal.Backend.Services.Database;</p><p>using MediaPortal.Common.PathManager;</p><p>using MediaPortal.Common;</p><p>using MediaPortal.Common.Logging;</p><p></p><p>using System.Data.SqlClient;</p><p></p><p>namespace MediaPortal.Database.SQLCE</p><p>{</p><p> public class SQLCEDatabase : ISQLDatabase</p><p> {</p><p> public const string SQLCE_DATABASE_TYPE = "SQL SERVER";</p><p> public const string DATABASE_VERSION = "11.0.2100.60";</p><p> public const int MAX_NUM_CHARS_CHAR_VARCHAR = 4000;</p><p> public const int LOCK_TIMEOUT = 30000; // Time in ms the database will wait for a lock</p><p> public const int MAX_BUFFER_SIZE = 2048;</p><p></p><p> /// <summary></p><p> /// Maximum size of the shared memory region in MB which SQL CE uses for shared database connections.</p><p> /// The maximum database size defaults to 256 MB, which might be too small for big databases.</p><p> /// </summary></p><p> public const int INITIAL_MAX_DATABASE_SIZE = 1024;</p><p></p><p> /// <summary></p><p> /// Buffer, the "Max Database Size" parameter must be bigger than the actual database file size, in MB.</p><p> /// </summary></p><p> public const int DATABASE_SIZE_BUFFER = 256;</p><p></p><p> public const string DEFAULT_DATABASE_FILE = "Datastore.sdf";</p><p></p><p> protected string _connectionString;</p><p></p><p></p><p> public SQLCEDatabase()</p><p> {</p><p> //--START My coding</p><p></p><p> SqlConnection conn;</p><p> _connectionString = @"Data Source=CHINTHAKA-PC\SQLEXPRESS;Database=mp2server;Integrated Security=True;User ID=sa;Password=;connection timeout=30";</p><p> conn = new SqlConnection(_connectionString);</p><p> try</p><p> {</p><p> conn.Open();</p><p> conn.Close();</p><p> }</p><p> catch (Exception ex)</p><p> {</p><p> ServiceRegistration.Get<ILogger>().Critical("SQL server Error establishing database connection", ex);</p><p> throw;</p><p> }</p><p></p><p></p><p> //--END</p><p></p><p></p><p> #region ISQLDatabase implementation</p><p></p><p> public string DatabaseType</p><p> {</p><p> get { return SQLCE_DATABASE_TYPE; }</p><p> }</p><p></p><p> public string DatabaseVersion</p><p> {</p><p> get { return DATABASE_VERSION; }</p><p> }</p><p></p><p> public uint MaxObjectNameLength</p><p> {</p><p> get { return 30; }</p><p> }</p><p></p><p> public string GetSQLType(Type dotNetType)</p><p> {</p><p> if (dotNetType == typeof(DateTime))</p><p> return "DATETIME";</p><p> if (dotNetType == typeof(Char))</p><p> return "NCHAR(1)";</p><p> if (dotNetType == typeof(Boolean))</p><p> return "BIT";</p><p> if (dotNetType == typeof(Single))</p><p> return "REAL";</p><p> if (dotNetType == typeof(Double))</p><p> return "FLOAT";</p><p> if (dotNetType == typeof(Byte) || dotNetType == typeof(SByte))</p><p> return "TINYINT";</p><p> if (dotNetType == typeof(UInt16) || dotNetType == typeof(Int16))</p><p> return "SMALLINT";</p><p> if (dotNetType == typeof(UInt32) || dotNetType == typeof(Int32))</p><p> return "INTEGER";</p><p> if (dotNetType == typeof(UInt64) || dotNetType == typeof(Int64))</p><p> return "BIGINT";</p><p> if (dotNetType == typeof(Guid))</p><p> return "UNIQUEIDENTIFIER";</p><p> if (dotNetType == typeof(byte[]))</p><p> return "IMAGE";</p><p> return null;</p><p> }</p><p></p><p> public string GetSQLVarLengthStringType(uint maxNumChars)</p><p> {</p><p> if (maxNumChars > MAX_NUM_CHARS_CHAR_VARCHAR)</p><p> return "NTEXT";</p><p> return "NVARCHAR(" + maxNumChars + ")";</p><p> }</p><p></p><p> public string GetSQLFixedLengthStringType(uint maxNumChars)</p><p> {</p><p> if (maxNumChars > MAX_NUM_CHARS_CHAR_VARCHAR)</p><p> return "NTEXT";</p><p> return "NCHAR(" + maxNumChars + ")";</p><p> }</p><p></p><p> public bool IsCLOB(uint maxNumChars)</p><p> {</p><p> return maxNumChars > MAX_NUM_CHARS_CHAR_VARCHAR;</p><p> }</p><p></p><p> public IDbDataParameter AddParameter(IDbCommand command, string name, object value, Type type)</p><p> {</p><p> if (type == typeof(byte[]))</p><p> {</p><p> SqlParameter result = (SqlParameter) command.CreateParameter();</p><p> result.ParameterName = name;</p><p> result.Value = value ?? DBNull.Value;</p><p> result.SqlDbType = SqlDbType.Image;</p><p> command.Parameters.Add(result);</p><p> return result;</p><p> }</p><p> // We need to use NText as parameter type, if the value is of "IsCLOB" type.</p><p> if (type == typeof(string) && value != null && IsCLOB((uint) value.ToString().Length))</p><p> {</p><p> SqlParameter result = (SqlParameter) command.CreateParameter();</p><p> result.ParameterName = name;</p><p> result.Value = value;</p><p> result.SqlDbType = SqlDbType.NText;</p><p> command.Parameters.Add(result);</p><p> return result;</p><p> }</p><p> return DBUtils.AddSimpleParameter(command, name, value, type);</p><p> }</p><p></p><p> public object ReadDBValue(Type type, IDataReader reader, int colIndex)</p><p> {</p><p> if (reader.IsDBNull(colIndex))</p><p> return null;</p><p> if (type == typeof(byte[]))</p><p> {</p><p> SqlBinary result = ((SqlDataReader) reader).GetSqlBinary(colIndex);</p><p> return result.Value;</p><p> }</p><p> return DBUtils.ReadSimpleDBValue(type, reader, colIndex);</p><p> }</p><p></p><p> public ITransaction BeginTransaction(IsolationLevel level)</p><p> {</p><p> return SQLCETransaction.BeginTransaction(this, _connectionString, level);</p><p> }</p><p></p><p> public ITransaction BeginTransaction()</p><p> {</p><p> return BeginTransaction(IsolationLevel.ReadCommitted);</p><p> }</p><p></p><p> public bool TableExists(string tableName)</p><p> {</p><p> using (SqlConnection conn = new SqlConnection(_connectionString))</p><p> {</p><p> conn.Open();</p><p> using (IDbCommand cmd = conn.CreateCommand())</p><p> {</p><p> cmd.CommandText = @"SELECT COUNT(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='" + tableName + "'";</p><p> int cnt = (int) cmd.ExecuteScalar();</p><p> return (cnt == 1);</p><p> }</p><p> }</p><p> }</p><p></p><p> public string CreateStringConcatenationExpression(string str1, string str2)</p><p> {</p><p> return str1 + "+" + str2;</p><p> }</p><p></p><p> public string CreateSubstringExpression(string str1, string posExpr)</p><p> {</p><p> return "SUBSTRING(" + str1 + "," + posExpr + "," + Int32.MaxValue + ")"; // Int32.MaxValue seems to be the biggest supported value</p><p> }</p><p></p><p> public string CreateSubstringExpression(string str1, string posExpr, string lenExpr)</p><p> {</p><p> return "SUBSTRING(" + str1 + "," + posExpr + "," + lenExpr + ")";</p><p> }</p><p></p><p> public string CreateDateToYearProjectionExpression(string selectExpression)</p><p> {</p><p> return "DATEPART(YEAR, " + selectExpression + ")";</p><p> }</p><p></p><p> #endregion</p><p> }</p><p>}</p><p></p><p></p><p>---------------------------------------------</p><p></p><p>my coding for SQLServer Transaction</p><p>-----------------------------------------------</p><p>#region Copyright (C) 2007-2014 Team MediaPortal</p><p></p><p>/*</p><p> Copyright (C) 2007-2014 Team MediaPortal</p><p> <a href="https://www.team-mediaportal.com" target="_blank">https://www.team-mediaportal.com</a></p><p></p><p> This file is part of MediaPortal 2</p><p></p><p> MediaPortal 2 is free software: you can redistribute it and/or modify</p><p> it under the terms of the GNU General Public License as published by</p><p> the Free Software Foundation, either version 3 of the License, or</p><p> (at your option) any later version.</p><p></p><p> MediaPortal 2 is distributed in the hope that it will be useful,</p><p> but WITHOUT ANY WARRANTY; without even the implied warranty of</p><p> MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the</p><p> GNU General Public License for more details.</p><p></p><p> You should have received a copy of the GNU General Public License</p><p> along with MediaPortal 2. If not, see <<a href="http://www.gnu.org/licenses/>" target="_blank">http://www.gnu.org/licenses/></a>.</p><p>*/</p><p></p><p>#endregion</p><p></p><p>using System.Data;</p><p>using MediaPortal.Backend.Database;</p><p>//using System.Data.SqlServerCe;</p><p>using MediaPortal.Backend.Services.Database;</p><p></p><p>using System.Data.SqlClient;</p><p></p><p>namespace MediaPortal.Database.SQLCE</p><p>{</p><p> public class SQLCETransaction : ITransaction</p><p> {</p><p> #region Protected fields</p><p></p><p> protected SqlTransaction _transaction;</p><p> protected ISQLDatabase _database;</p><p> protected IDbConnection _connection;</p><p></p><p> #endregion</p><p></p><p> #region ITransaction Member</p><p></p><p> public ISQLDatabase Database</p><p> {</p><p> get { return _database; }</p><p> }</p><p></p><p> public IDbConnection Connection</p><p> {</p><p> get { return _connection; }</p><p> }</p><p></p><p> public void Commit()</p><p> {</p><p> _transaction.Commit();</p><p> Dispose();</p><p> }</p><p></p><p> public void Rollback()</p><p> {</p><p> _transaction.Rollback();</p><p> Dispose();</p><p> }</p><p></p><p> public IDbCommand CreateCommand()</p><p> {</p><p> IDbCommand result = _connection.CreateCommand();</p><p>#if DEBUG</p><p> // Return a LoggingDbCommandWrapper to log all CommandText to logfile in DEBUG mode.</p><p> result = new LoggingDbCommandWrapper(result);</p><p>#endif</p><p> result.Transaction = _transaction;</p><p> return result;</p><p> }</p><p></p><p> #endregion</p><p></p><p> #region IDisposable Member</p><p></p><p> public void Dispose()</p><p> {</p><p> if (_connection != null)</p><p> {</p><p> _connection.Close();</p><p> _connection = null;</p><p> }</p><p> }</p><p></p><p> #endregion</p><p></p><p> public static ITransaction BeginTransaction(SQLCEDatabase database, string connectionString, IsolationLevel level)</p><p> {</p><p> SqlConnection connection = new SqlConnection(connectionString);</p><p> connection.Open();</p><p> return new SQLCETransaction(database, connection, connection.BeginTransaction(level));</p><p> }</p><p></p><p> public SQLCETransaction(ISQLDatabase database, IDbConnection connection, SqlTransaction transaction)</p><p> {</p><p> _database = database;</p><p> _connection = connection;</p><p> _transaction = transaction;</p><p> }</p><p> }</p><p>}</p><p>--------------------------------------------------------------------</p><p></p><p>I build the pluging using VS 2013 and copy the .DLL fils to 'C:\Program Files (x86)\Team MediaPortal\MP2-Server\Plugins\SQLCEDatabase'</p><p>folder then start the 'MediaPortal 2 server service' using services window but the service is not starting.</p><p></p><p>Please, can you give me some ideas to solve this.</p><p></p><p>Thanx.</p></blockquote><p></p>
[QUOTE="chinthaka, post: 1100502, member: 149837"] 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 [url]https://www.team-mediaportal.com[/url] 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 <[url]http://www.gnu.org/licenses/>[/url]. */ #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 [url]https://www.team-mediaportal.com[/url] 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 <[url]http://www.gnu.org/licenses/>[/url]. */ #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. [/QUOTE]
Insert quotes…
Verification
Post reply
Forums
MediaPortal 2
Plugin Development
MediaPortal 2 MS SQL Server database connecting
Contact us
RSS
Top
Bottom