Archiv der Kategorie: SQL

Entity Framwork 6 provider wechsel – MSSQL und MySQL


Oft muss eine Software sehr flexibel gestaltet werden, damit diese so vielen Kundenwünschen wie möglich entspricht. Hier muss z.B. auch die DB Schnittstelle eine entsprechende Flexibilität aufweisen, damit man nicht nur MS SQL Server unterstützt sondern z.B. auch MySQL.

Wenn man also eine Software erstellen möchte die auf unterschiedlichen Datenbanken laufen soll, sucht man sich am Besten ein passendes Framework wie das Entity Framework. Jetzt muss man noch darauf achten, das man in seinen Modeldaten nur Datentypen verwendet, die sowohl MS SQL und MySQL unterstützten. MySQL unterstützt z.B. kein “varchar(max)”, hier muss dann entsprechend abgewogen werden welcher Datentyp bzw. welche Länge wirklich benötigt wird.

Der Providerwechsel bzw. die Umsetzung, kann mit dem Entity Framework sehr einfach durchgeführt werden. Hier muss aber darauf hingewiesen werden, das der Release von EF6 erst vor kurzem war und es z.B. mit Visual Studio 2013 zu diversen Problemen kommen kann, wenn man ein passendes MySQL Script generieren möchte.

1. Installieren der Voraussetzungen

Als erstes benötigen wir einen aktuellen Connector für MySQL in der Version 6.8.1.0 oder höher, zu finden unter: http://dev.mysql.com/downloads/connector/net/

2. Erstellen einer Beispiel Konsolenanwendung

Legen Sie eine Konsolenanwendung für .NET 4 an und fügen Sie der die folgenden Verweise hinzu:

  • MySql.Data.dll (Version 6.8.1.0 oder höher)
  • MySql.Data.Entity.EF6.dll (Achtung aufpassen für .NET 4.0 und nicht 4.5 – für unser Beispiel)
  • EntityFramework.dll (Version 6+)
  • EntityFramework.SqlServer
  • System.Data.Entity
  • System.Configuration

Die MySql DLLs wurden vom Connector für MySql installiert und sollte man unter den Standard Assemblies finden.

3. Anlegen der passenden Datenbanken in MySQL und MS SQL

Der einfachste Weg um zwei “identische” Datenbanken für MySQL und MS SQL zu bekommen, ist es ein Model zu erstellen und dann entsprechend die SQL Skripte vom Visual Studio erstellen zu lassen.

Das Erstellen eines MySQL Skriptes über das Visual Studio ist aktuell aber noch sehr “fehleranfällig” und ist mir nur in einer bestimmten Konstellation gelungen. Dazu habe ich auf einer Virtuellen Maschine nur Visual Studio 2012 und dotConnect für MySQL installiert. Dann habe ich das entsprechende Datenmodell erstellt und darauf geachtet, das ich nur Datentypen verwende die auch von beiden DB Systemen unterstützt werden. Da ich Visual Studio 2012 verwendet habe, handelte es sich hier auch um ein EF5 Model! – dies sollte aber nur zum erstellen der SQL Skripte verwendet werden.

Wenn man dann in den Eigenschaften der EDMX Datei die DDL Genration Template auf “Devart SSDLToMySql.tt” stellt und dann im Model auf “Generate Database from Model” klickt wird einem das passende MySQL Script erstellt.image

Mit dem Originaltemplate “SSDLToSQL10.tt” erhält man natürlich das passende MS SQL Script.

Ich denke wenn die einzelnen Anbieter noch etwas Zeit bekommen, ist es bestimmt bald möglich alles direkt mit EF6 in V2013 auszuführen. Dies war mir aber mit den aktuellen Treiberversionenversionen nicht möglich.

4. Passende MySql SSDL extrahieren

Damit wir später auch entsprechende Abfragen auf der MySql Datenbank ausführen können, benötigen wir noch die passende SSDL von der MySql Datenbank. Denn wenn wir später zwischen MySql und MS Sql Datenbank wechseln wollen, benötigen wir eine SSDL für die MySql Datenbank und eine SSDL für die MS SQL Datenbank. In meinem Beispiel extrahieren wir die SSDL für die MySql Datenbank und verwenden die Standard SSDL aus dem EDMX Model für die MS SQL Datenbank.

Die SSDL ist in der EDMX Model Datei “verpackt”. Als erstes benötigen wir eine EDMX Datei bei der wir das Model aus der MySql Datenbank erstellt haben. Dann öffnet man die EDMX Datei mit einem einfachen SQL Editor über das Visual Studio

image

Und wir extrahieren den Abschnitt nach “<!– SSDL content –>” von “<Schema …>” bis zum “</Schema>” Ende und erstellen eine neue Datei z.B. EFModel.MySql.ssdl, hier fügen wir als erste Zeile folgendes ein

<?xml version="1.0" encoding="utf-8"?>

Der Rest wird mit unseren XML Daten aus dem EDMX gefüllt, das ganze könnte dann z.B. folgendermaßen aussehen

<?xml version="1.0" encoding="utf-8"?>
<Schema Namespace="testDbModel.Store" 
        Alias="Self" 
        Provider="MySql.Data.MySqlClient" 
        ProviderManifestToken="5.6" 
        xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" 
        xmlns="http://schemas.microsoft.com/ado/2009/11/edm/ssdl">
  <EntityContainer Name="testDbModelStoreContainer">
    <EntitySet Name="person" EntityType="testDbModel.Store.person" store:Type="Tables" Schema="testdb" />
  </EntityContainer>
  <EntityType Name="person">
    <Key>
      <PropertyRef Name="Name" />
    </Key>
    <Property Name="Name" Type="char" Nullable="false" MaxLength="10" />
    <Property Name="Vorname" Type="char" MaxLength="10" />
    <Property Name="Age" Type="int" />
    <Property Name="Geburtsdatum" Type="datetime" />
    <Property Name="IsMale" Type="bit" />
  </EntityType>
</Schema>

Hier ist wichtig, das als Provider “MySql.Data.MySqlClient” eingetragen ist. Sollte dies nicht der Fall sein, dann wurde das Model aus der falschen DB erstellt. Die Datei legen wir mit im EF Projekt im Selben Pfad wie die EDMX ab und stellen unter den Eigenschaften der Datei ein, das diese mit ins Ausgabeverzeichnis kopiert werden soll.

image

Auf die SSDL selbst greifen wir erst wieder im Connectionstring zu.

5. Anpassen der App.config (Connectionstring erstellen und Provider hinzufügen)

Als nächstes müssen wir die App.config anpassen, denn für EF6 gibt es hier weitere Einstellungen die vorgenommen werden müssen. Dazu gehört es die passenden Provider für MS SQL und MySQL in der App.config einzutragen, die Angabe des Providers wird ab EF6 benötigt.

<entityFramework>
  <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
  <providers>
    <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, mysql.data.entity.EF6" />
  </providers>
</entityFramework>

Auch die jeweils passenden Connectionstrings für die MySQL und MS SQL Datenbank fügen wir der App.config hinzu. Hier ist der entsprechende “provider” angepasst, der zugehörige “provider connection string” und die SSDL für MySql angepasst.

  <connectionStrings>
    <add name="MsSQLConn" connectionString="metadata=res://*/EFModel.csdl|res://*/EFModel.ssdl|res://*/EFModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=172.168.0.1\SQL2008R2;initial catalog=testDb;persist security info=True;user id=sa;password=meinPasswort;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
    <add name="MySQLConn" connectionString="metadata=EFModel.MySql.ssdl|res://*/EFModel.csdl|res://*/EFModel.msl;provider=MySql.Data.MySqlClient;provider connection string=&quot;server=localhost;user id=root;password=meinPasswort;persistsecurityinfo=True;database=testDb&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>

Im Connectionstring für MySql muss die passende SSDL für “EFModel.MySql.ssdl” eingetragen werden, hier ist es wichtig das diese ohne “res:/*//…” eingetragen wird. Die Datei wird nur vom EF gefunden, wenn diese auch auf “Copy to Output Directory” gestellt wurde, denn sonst kommt es beim Ausführen von MySql Anweisungen z.B. zu folgendem Fehler:

“System.Data.MetadataException: The specified metadata path is not valid. A valid path must be either an existing directory, an existing file with extension ‚.csdl‘, ‚.ssdl‘, or ‚.msl‘, or a URI that identifies an embedded resource. …”

Wenn man keine extra SSDL z.B. für MySql einbindet, dann kommt es bei SQL Abfragen zu folgendem Fehler:

„Unable to cast object of type ‚MySql.Data.MySqlClient.MySqlConnection‘ to type ‚System.Data.SqlClient.SqlConnection‘.“

UPDATE: Es ist auch möglich die MySql.ssdl Datei mit als Resource in die DDL einzubinden. Wichtig ist, dass dann unter Metadata der Namespace in dem sich die ssdl befindet mit vor den Namen der ssdl geschrieben wird. D.h. wenn die DLL den Namespace “efdata” hat und die ssdl den Namen “EFModel.MySql.ssdl” dann sieht der Metadata Eintrag für die MySql.ssdl folgendermaßen aus: “res://*/efdata.EFModel.MySql.ssdl|res://*/edoModel.csdl…”. Sollte dies nicht funktionieren, dann muss die DDL in der die ssdl als Resource eingebettet wurde mit z.B. ILSpy angeschaut werden und dort sieht man dann auch den genauen Namen der eingebetteten ssdl Resource.

6. Verwenden der Connectionstrings

Ich habe mein EF Model entsprechend erweitert, das ich den Connectionstring direkt übergeben kann. Daher kann ich den passenden Connectionstring direkt aus der App.config verwenden um z.B: eine neue Person der MS SQL Datenbank hinzuzufügen.

string connectionString = ConfigurationManager.ConnectionStrings["MsSQLConn"].ConnectionString;
testDbEntities entities = new testDbEntities(connectionString);
entities.Person.Add(new Person() { Age = 23, Geburtsdatum = DateTime.Now, IsMale = true, Name = "Test", Vorname = "Blubb"});
entities.SaveChanges();

Das einzige was angepasst werden muss, wenn man die Daten der MySQL Datenbank hinzufügen möchte ist, das “MsSQLConn” in ein “MySQLConn” zu ändern, um den MySQL Connectionstring zu ermitteln.

Advertisements

SQL Transaktion inkl. Rollback (Skript)


Transkationen bieten die Möglichkeit eine Datenbank wieder in den Zustand vor dem Ausführen eines z.B. Fehlerhaften SQL Skriptes zurückzusetzten.

Wenn man z.B. eine Abfolge von SQL Befehlen auf einer DB ausführt an der man an unterschiedlichen Tabellen Änderungen vornimmt und beim Update ein Fehler auftritt, dann hat man die Möglichkeit mit Hilfe von Transkationen wieder den Ausgangszustand der DB vor dem Ausführen des Skriptes wieder herzustellen.

Ich verwende dazu das folgende Script, das Script führt bei einem Fehler automatisch ein Rollback der Transaktion durch, funktioniert auch noch für den SQL Server 2005.

 BEGIN TRY
  BEGIN TRAN

 /* SQL CODE der ausgeführt werden soll HIER einfügen */
  
  COMMIT;
 END TRY
 BEGIN CATCH
  /* Fehlerauswertung und der Rollback bei einem Fehler */
  if @@TRANCOUNT > 0
  BEGIN
	ROLLBACK TRAN
  END	

  DECLARE @ErrorMessage VARCHAR(4000)
  SELECT @ErrorMessage = 'Message: '+ ERROR_MESSAGE(); 
  raiserror (@ErrorMessage,16,1)
 END CATCH

MSSQL letztes Änderungsdatum für eine Tabelle ermitteln


Wenn man Daten aus einer Datenbank in seiner Anwendung nicht immer wieder neu abrufen möchte und diese z.B. in statischen Listen vorhält, will man von Zeit zu Zeit doch ermitteln ob es sich bei den vorgehaltenen Daten noch um die aktuellen Daten handelt. Da es hier sehr Aufwendig sein kann jeden einzelnen Datensatz zu überprüfen, wäre es von Vorteil wenn man erst herausbekommen würde, wann die letzte Datensatzänderung in dieser Tabelle stattgefunden hat. Dann kann man dieses Datum mit dem Datum vergleichen, als man die Datensätze das erste/letzte mal abgerufen hat.

Dafür kann das Folgende SQL Schnipsel verwendet werden:

SELECT TOP 1 last_user_update FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'DBName') AND OBJECT_ID=OBJECT_ID('TabellenName')
Order By last_user_update ASC

Hier wird als Einzelner Wert die Letzte Datensatzänderung in der Übergebenen Datenbank und Tabelle zurück gegeben.

ACHTUNG: Man muss aufpassen, das hier auch “Null” bzw. kein Wert zurückkommen kann, wenn der DB Server z.B. neu gestartet wurde, da es sich nur um Temporäre Tabellen handelt, die Ihre Daten nicht dauerhaft im Dateisystem ablegen und sich der Wert erst wieder ändert wenn ein Datensatz geändert wurde.

Quelle:

http://blog.sqlauthority.com/2009/05/09/sql-server-find-last-date-time-updated-for-any-table/

Eigenes C# “Plugin” (BusinessLogicModule) für SQL Server Replikation erstellen


Ich hatte das “Vergnügen” eine SQL Server Replikation einzurichten und in meinem Fall auch noch die Möglichkeit ein passendes Plugin zu erstellen, für z.B. Replikationsfehler die der SQL Server nicht von selbst lösen kann. Im folgenden Beitrag gehe ich daher nicht auf das Anlegen und Erstellen einer Replikation ein, sondern wie erstelle ich ein Plugin für die SQL Server Replikation.

Problemstellung war bei mir eine Tabelle mit einem zusammengesetztem Primärschlüssel der sich aus zwei Spalten zusammensetzt. Hier war es möglich das der Replication Server und der Replication Client Datensätze erzeugten die den gleichen zusammengesetzten Primärschlüssel haben. Wie und warum soll hier an dieser Stelle keine Rolle spielen.

0. Vorgehensweise

Um ein eigenes Replikationsmodul erstellen zu können werden die Folgenden Schritte durchgeführt, die ich dann noch näher erläutere

  • Es existiert eine eingerichtete Replikation (Mergereplikation) und man hat Zugriff auf den Replikationsserver (SQL Server 2008R2)
  • Erstellen einer einfachen Klassenbibliothek in C# und .NET 2.0, die die Logik enthält was z.B. bei einem Replikationsfehler zwischen Publisher und Subscriber unternommen werden soll
  • Einbinden der erstellten DLL über ein SQL Script was auf dem SQL Server ausgeführt werden muss
  • Unser Replikationsmodul in der Replikation als Konfliktlöser einstellen
  • Testen unseres Replikationsmoduls

1. Voraussetzungen

Eine installierte SQL Server 2008R2 Standard Installation inkl. einer eingerichteten Merge Replikation und die Daten werden vom Server an die Clients gepusht. Eine Client Installation mit z.B. einer SQL Server 2008R2 Express Instanz als Replication Client. (habe ich bisher nur unter diesen Bedingungen testen können)

Name der Replikation auf dem Replication Server: testReplication

2. Visual Studio Projekt erstellen

Für ein SQL Server Replication Plugin, wird als Projekt nur eine einfache Klassenbibliothek für .NET 2.0 benötigt. Unserer Klassenbibliothek muss noch ein Verweis hinzugefügt werden auf “Microsoft.SqlServer.Replication.BusinessLogicSupport” damit wir auf die Replikation Einfluss nehmen können. Dafür wird unsere Klasse von “BusinessLogicModule” abgeleitet. Die passende DLL findet man unter

“C:\Program Files (x86)\Microsoft SQL Server\100\COM\Microsoft.SqlServer.Replication.BusinessLogicSupport.dll”

Das Projekt steht auch wie immer unter Codeplex zur Verfügung und kann dort heruntergeladen werden unter:

https://squadwuschel.codeplex.com/

Dann unter “Source Code” –> “Browse” –> “Testprojekte” –> “SqlMyReplicationModule”

3. Implementieren der passenden Anwendungslogik

In unserer Hauptklasse leiten wir von “BusinessLogicModule” ab und binden alle Abhängigkeiten ein.

   public class MyReplicationModule : BusinessLogicModule
    {
        #region Properties
        /// <summary>
        /// Gibt an welche Änderungen von unserem Plugin verarbeitet werden sollen.
        /// In unserem Falle nur Fehler die beim Einfügen des Subscribers oder Publishers auftreten.
        /// </summary>
        public override ChangeStates HandledChangeStates
        {
            get { return ChangeStates.SubscriberInsertErrors
                | ChangeStates.PublisherInsertErrors; }
        }
...

Die Funktion “HandledChangeStates” gibt hier an für welche Fälle unser Modul überhaupt benutzt werden soll.

/// <summary>
/// Initialisieren, damit wir alle wichtigen Informationen erhalten um z.B. eine DB Verbindung öffnen zu können und das Problem zu lösen.
/// </summary>
public override void Initialize(string publisher, string subscriber, string distributor, string publisherDb, string subscriberDb, string articleName)
{
    SqlReplicationConnection = new SqlReplicationConnection(publisher, subscriber, distributor, publisherDb, subscriberDb, articleName);
}

Wenn man die Replikationsdaten ändern/löschen möchte ist es wichtig die “Initialize” Funktion zu überschreiben, denn diese Funktion stellt alle wichtigen Strings zur Verfügung, damit wir später eine Verbindung zur Datenbank aufbauen können. Die Klasse “SqlReplicationConnection” ist von mir und kapselt einfach nur die SQL Verbindung und stellt einfache Rückgabewerte wie eine DataTable für SQL Abfragen zur Verfügung.

/// <summary>
/// Da wir in den HandleChangeStates nur auf InsertError "lauschen" wird nur diese Funktion von uns überschrieben.
/// </summary>
/// <returns></returns>
public override ActionOnDataError InsertErrorHandler(SourceIdentifier insertSource, DataSet insertedDataSet, ref ErrorLogType errorLogType, ref string customErrorMessage, int errorCode, string errorMessage, ref int historyLogLevel, ref string historyLogMessage)
{
     //Wenn es sich um die jeweils passende Tabelle handelt, die jeweiligen Aktionen ausführen. Hier wird überprüft ob die Tabelle
     //bei der es zu einem InsertError gekommen ist "Kategorie" heißt, wenn ja kann für diese Tabelle dann der passende Code ausgeführt werden.
     if (SqlReplicationConnection.ArticleName.ToLower() == "Kategorie")
     {
          //Hier die Replikation manipulieren und wenn alles erfolgreich abgelaufen ist, das passende Return liefern.
          string kategorieId = insertedDataSet.Tables[0].Rows[0]["id"].ToString();
          string secondId = insertedDataSet.Tables[0].Rows[0]["secondId"].ToString();
           //Die Abfrage erstellen die auf beiden Systemen durchgeführt werden soll Publisher und Subscriber
           string sqlString = string.Format("Select * from {0} where id = {1} and secondId = {2}", SqlReplicationConnection.ArticleName, kategorieId, secondId);

           //Prüfen ob hier ein Datensatz gefunden werden kann.
           DataTable tablePublisher = SqlReplicationConnection.GetDataTable(sqlString, SqlReplicationConnection.RepType.Publisher);
            //... Weitere Logik z.B. Löschen von Datensätzen oder Manipulieren

            return ActionOnDataError.AcceptCustomErrorBehavior;
       }

      return base.InsertErrorHandler(insertSource, insertedDataSet, ref errorLogType, ref customErrorMessage, errorCode, errorMessage, ref historyLogLevel, ref historyLogMessage);
 }

Dann überschreiben wir den passenden Handler der für uns am besten geeignet ist. In unserem Fall handelt es sich hier um den “InsertErrorHandler”. Das übergebene Dataset “insertedDataSet” ist nur zum überprüfen, bei welchem Datensatz der Fehler aufgetreten ist. Dieses Dataset kann man nicht manipulieren. Wenn man die Daten in der Datenbank anpassen möchte, dann muss man die SQL Verbindung selbst aufbauen und die passenden Abfragen selbst ausführen (beliebig komplex, da man ja auf die gesamte Datenbank zugriff hat). In meinem Beispiel führe ich nur ein einfaches Select aus, anhand der ids die ich im Dataset finden konnte. Die Verbindung wird an Hand der Daten aufgebaut die wir in der “Initialize” Funktion übergeben bekommen haben.

string.Format("Data Source='{0}';Initial Catalog='{1}';Integrated Security=SSPI", Publisher, PublisherDb)

Für eine Verbindung benötigen wir im Connectionstring nur den Namen der DataSource und den Datenbanknamen. Als Security wird hier einfach “SSPI” übergeben, denn unser Replikationsdienst besitzt ja bereits alle Rechte um auf die DB zugreifen zu können. Daher ist hier kein Username und kein Passwort notwendig.

4. Unser Modul auf dem SQL Replikationsserver registrieren

Das Script wird nicht auf der Replikationsdatenbank ausgeführt, sondern muss auf der „distribution“ Datenbank unter „System Databases“ ausgeführt werden. Außerdem muss das Script für jede Tabelle ausgeführt werden, die unser Modul benutzten soll. Denn wir können in einer DLL die Probleme mehrerer Tabellen implementieren.

DECLARE @publication AS sysname;
DECLARE @article AS sysname;
DECLARE @friendlyname AS sysname;
DECLARE @assembly AS nvarchar(500);
DECLARE @class AS sysname;
SET @publication = N'testReplication';
SET @article = N'Kategorie';
SET @friendlyname = N'MyReplicationModuleForKategorie';
SET @assembly = N'C:\ReplicationModule\SqlMyReplicationModule.dll';
SET @class = N'SqlMyReplicationModule.MyReplicationModule';

exec sp_unregistercustomresolver @article_resolver = @friendlyname
exec sp_registercustomresolver @article_resolver = @friendlyname
    , @resolver_clsid = NULL
    , @is_dotnet_assembly = 'true'
    , @dotnet_assembly_name =  @assembly
    , @dotnet_class_name =  @class

5. Unser Modul auf dem Replikationssystem als Konfliktlöser einstellen

Nach dem wir unser Modul erfolgreich auf dem SQL Server registriert haben, müssen wir noch unser Modul als Konfliktlöser einbinden. Dafür müssen wir die Eigenschaften der eingerichteten Replikation öffnen.

image

Und die “Eigenschaften dieses Tabellenartikels festlegen”.

image

Wenn wir dann unter “Konfliktlöser” die “Benutzerdefinierten Konfliktlöser” auswählen, sehen wir auch unser Modul mit dem im SQL Script festgelegten Namen aufgelistet “MyReplicationModuleForKategorie”, das Modul einfach auswählen. Jetzt löst der SQL Server bestimmte Konflikte die er nicht selbst lösen kann für die Tabelle “Kategorie” über unseren selbst geschriebenen Konfliktlöser.

image

Zum Testen würde ich empfehlen wenn die Replikation so eingerichtet wurde, das diese nicht automatisch alle XX Sekunden ausgeführt wird, sondern das diese manuell ausgelöst wird, dann hat man eine bessere Kontrolle über die Datensätze mit denen man das ganze testen möchte.

6. Allgemeine Informationen

  • Als ich das ganze das erste mal eingerichtet habe, musste ich aufpassen, das ich auch ein .NET 2.0 Projekt erstellt habe da es sonst Probleme mit dem SQL Server 2008R2 gab
  • Ich hatte Verständnisprobleme wie man denn die Daten manipulieren sollte, ich dachte erst das man dies mit dem übergebenen Dataset erledigen kann und habe nicht an eine eigene Verbindung gedacht, da ich nicht wusste wo ich den Usernamen und das Passwort herbekomme, was aber letztendlich gar nicht benötigt wird.
  • Sollte ich hier eine falsche Herangehensweise an den Tag gelegt haben, dann bin ich natürlich für jeden Tipp dankbar. Denn ich fand es schwer hier an wirklich gute Informationen zu kommen. Da ich in der MSDN nur ein Beispiel finden konnte in dem man Daten aus dem Dataset in ein Logfile schreibt und nicht wie man die Daten manipulieren kann.
  • Ich hoffe aus meinem Beispiel wird klar wie man die Daten manipulieren kann, wenn nicht würde ich mich über Kommentare freuen und werde versuchen das ganze anzupassen.

7. Quellen

http://blogs.msdn.com/b/sql_pfe_blog/archive/2011/04/08/merge-replication-conflict-detection-vs-conflict-resolution-part-2-custom-business-logic-handler.aspx

http://technet.microsoft.com/en-us/library/microsoft.sqlserver.replication.businesslogicsupport.businesslogicmodule%28SQL.100%29.aspx

EF5 – “model first” Datenbank Updates mit VS 2012


Nach einem guten Abend an Recherchearbeit, um herauszufinden ob es möglich ist, dass man evtl. direkt im EF5 Framework oder über EF Tools die Möglichkeit hat für den “model first” Ansatz seine Datenbank nach einer Modeländerung zu aktualisieren. Habe ich leider keine Integrierte Lösung finden können. Wenn hier jemand andere Erfahrungen gemacht hat, dann freue ich mich hier sehr über einen passenden Lösungsvorschlag oder Ansatz.

Außerdem habe ich einen Feature Request auf der EF Seite dafür gestartet und ich freue mich über jeden Vote, den ich bekommen kann für den Request, Voten könnt Ihr ohne euch anzumelden auf der folgenden Seite:

http://data.uservoice.com/forums/72025-entity-framework-feature-suggestions/suggestions/3065564-ef5-model-first-genrate-db-update-script

Visual Studio – SQL Schemavergleich zum Aktualisieren der Datenbank

Die einzige Lösung die ich hier aktuell vorschlagen kann, ist es den Integrierten Schemavergleich im Visual Studio für Datenbankupdates zu nutzen.

Nachteile dieser Lösung:

  • Nicht direkt in den EF Modellierungsprozess eingebunden
  • Man benötigt zwei Datenbanken – einmal die Originaldatenbank die aktualisiert werden soll und dann die neue Datenbank mit allen Updates, damit ein Vergleich erstellt werden kann mit der Originaldatenbank.

Wichtige Hinweise beim Durchführen von Modelländerungen im edmx Model

Außerdem muss darauf geachtet werden, wenn man in einem edmx Modell einer Klasse neue Member hinzufügt. Hier muss “Null” angegeben werden oder ein Default Wert hinterlegt werden. Denn sonst kann später kein Updatescript vom Schemavergleich erstellt werden. Im edmx Model können zwar Standardwerte eingestellt werden, diese werden jedoch nicht im generierten SQL eingebunden. Daher muss dies von Hand angepasst werden.

Im Folgenden lege ich für den Namen den Standardwert “Maus” fest, wenn man dann aber über

image

“Datenbank aus Modell generieren” das passende SQL Script generieren lässt,

image

dann sieht der Teil in dem die Tabelle angelegt wird folgendermaßen aus:

-- Creating table 'TiereSatz'
CREATE TABLE [dbo].[TiereSatz] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Art] nvarchar(max)  NOT NULL
);

Hier wird aber das Folgende Script benötigt, damit später auch ein Updatescript erstellt werden kann, denn wenn in der jeweiligen Tabelle der späteren Zieldatenbank bereits Werte enthalten sind, kann kein Update durchgeführt werden. Da z.B. schon die Tabelle existierte mit dem Member “Art” und hier bereits Werte eingetragen sind muss bei einem Update entweder der default Wert gesetzt werden oder null zugelassen sein für die neue Spalte “Name”.

-- Creating table 'TiereSatz'
CREATE TABLE [dbo].[TiereSatz] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL default 'Maus',
    [Art] nvarchar(max)  NOT NULL
);

Der “Name” kann auch ”Null” sein, dann gibt es keine Probleme beim Hinzufügen der Spalte, wenn man im Designer “Null-Werte zulassen” auf “True” stellt, dann wird das folgende Script generiert welches auch funktioniert.

-- Creating table 'TiereSatz'
CREATE TABLE [dbo].[TiereSatz] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NULL,
    [Art] nvarchar(max)  NOT NULL
);

Erstellen des Schema Vergleiches mit Visual Studio und Updaten der DB

Sobald beide Datenbanken eingerichtet sind, zum einen die “alte” Datenbank die aktualisiert werden soll und die “neue” Datenbank in der alle Änderungen enthalten sind, dann können wir im Visual Studio im Menü einen neuen Schemavergleich erstellen.

image

Hier kann man jetzt das Ziel und die Quelldatenbank auswählen

image

Die Quelle ist bei uns die “neue” Datenbank die alle Updates enthält und das Ziel ist bei und die “alte” Datenbank in der die Änderungen durchgeführt werden sollen.

Die Auswahl der passenden DB findet über ein einfaches Menü statt.

image

und sobald beide Datenbanken ausgewählt wurden, steht die Funktion “Vergleichen” zur Verfügung

image

Wenn der Vergleich ausgeführt wurde, steht einem die Aktualisieren Funktion zur Verfügung oder das Erstellen eines SQL Scripts, welches man sich anschauen kann und dann meist schon in den ersten Zeilen erkennt ob es ein erfolgreiches update wird oder nicht.

image

Wenn man auf aktualisieren klickt, sieht man im VS dann auch ob alles geklappt hat

image

Jetzt sind beide Datenbanken auf dem „gleichen” Stand, was zumindest die Tabellen und Verknüpfungen betrifft.

Beispiel für einen Fehlschlag des Updates:

Wenn eine Aktualisierung fehlschlägt, empfehle ich das man sich das Update Script ausgeben lässt, damit man sieht wo der Fehler aufgetreten ist.

Das folgende generierte SQL Update Script kann nicht erfolgreich ausgeführt werden, da ich in der Tiertabelle die Spalte “Name” auf “Not Null” gesetzt habe. Hier kann man sehr gut erkennen wo der Fehler liegt und man kann dann im edmx Model oder dem Script für das Erstellen der neuen Datenbank die passenden Änderungen vornehmen und hier wieder testen ob ein Update möglich ist.

/*
Die Spalte "[dbo].[TiereSatz].[Vorname]" in der Tabelle "[dbo].[TiereSatz]" muss hinzugefügt werden,
besitzt jedoch keinen Standardwert und unterstützt keine NULL-Werte. Wenn die Tabelle Daten enthält,
funktioniert das ALTER-Skript nicht. Um dieses Problem zu vermeiden, müssen Sie der Spalte einen
 Standardwert hinzufügen, sie so kennzeichnen, dass NULL-Werte zulässig sind, oder die Generierung
 von intelligenten Standardwerten als Bereitstellungsoption aktivieren.
*/

IF EXISTS (select top 1 1 from [dbo].[TiereSatz])
    RAISERROR (N'Zeilen wurden erkannt. Das Schemaupdate wird beendet, da es möglicherweise zu einem Datenverlust kommt.', 16, 127) WITH NOWAIT
GO