J'utilise un exemple très simple, une base de données ContactDB sur SQL Server 2005, et une seule table de la base : la table « Contact »
1. Ajouter une référence à System.Data.Linq.dll
2. Ajouter les attributs [System.Data.Linq.Table()] and [System.Data.Linq.Column()]
[System.Data.Linq.Table(Name = "dbo.Contact")]
[System.Data.Linq.Column(Storage="_ContactID", Name="ContactID", DBType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDBGenerated=true, CanBeNull=false)]
[System.Data.Linq.Column(Storage = "_ContactName", Name = "ContactName", DBType = "Char(250)")]
3. Faire hérité la classe de
Implémenter les 2 interfaces de classe > 2 événements sont générés
#region INotifyPropertyChanged Members public event System.ComponentModel.PropertyChangedEventHandler PropertyChanged; #endregion #region INotifyPropertyChanging Members public event System.ComponentModel.PropertyChangedEventHandler PropertyChanging; #endregion
4. Ajouter 2 méthodes
protected void OnPropertyChanging(string propertyName) { if ((this.PropertyChanging != null)) { this.PropertyChanging(this, new System.ComponentModel.PropertyChangedEventArgs(propertyName)); } } protected void OnPropertyChanged(string propertyName) { if ((this.PropertyChanged != null)) { this.PropertyChanged(this, new System.ComponentModel.PropertyChangedEventArgs(propertyName)); } }
5. Modifier les properties de la classe (Set)
[System.Data.Linq.Column(Storage="_ContactID", Name="ContactID", DBType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDBGenerated=true, CanBeNull=false)] public Int32 ContactID { get { return _ContactID; } set { _ContactID = value; } }
[System.Data.Linq.Column(Storage = "_ContactAge", Name = "ContactAge", DBType = "Int")] public Nullable<Int32> ContactAge { get { return _ContactAge; } set { if ((this._ContactAge != value)) { this.OnPropertyChanging("ContactAge"); this._ContactAge = value; this.OnPropertyChanged("ContactAge"); } } }
[System.Data.Linq.Column(Storage = "_ContactName", Name = "ContactName", DBType = "Char(250)")] public String ContactName { get { return _ContactName; } set { if ((this._ContactName != value)) { this.OnPropertyChanging("ContactName"); this._ContactName = value; this.OnPropertyChanged("ContactName"); } } }
Finalement le code complet de la classe contact donne :
using System; using System.Linq; using System.Collections.Generic; using System.Text; namespace NET35CsDatacontext { [System.Data.Linq.Table(Name = "dbo.Contact")] public class Contact : System.Data.Linq.INotifyPropertyChanging, System.ComponentModel.INotifyPropertyChanged { // Columns private Int32 _ContactID; private String _ContactName; private String _ContactFirstName; private String _ContactEmail; private Int32 _ContactTypeID; private Nullable<Int32> _ContactAge; // Constructors public Contact() { } // Properties [System.Data.Linq.Column(Storage="_ContactID", Name="ContactID", DBType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDBGenerated=true, CanBeNull=false)] public Int32 ContactID { get { return _ContactID; } set { _ContactID = value; } } [System.Data.Linq.Column(Storage = "_ContactName", Name = "ContactName", DBType = "Char(250)")] public String ContactName { get { return _ContactName; } set { if ((this._ContactName != value)) { this.OnPropertyChanging("ContactName"); this._ContactName = value; this.OnPropertyChanged("ContactName"); } } } [System.Data.Linq.Column(Storage = "_ContactFirstName", Name = "ContactFirstName", DBType = "Char(250)")] public String ContactFirstName { get { return _ContactFirstName; } set { if ((this._ContactFirstName!= value)) { this.OnPropertyChanging("ContactFirstName"); this._ContactFirstName = value; this.OnPropertyChanged("ContactFirstName"); } } } [System.Data.Linq.Column(Storage = "_ContactEmail", Name = "ContactEmail", DBType = "Char(250)")] public String ContactEmail { get { return _ContactEmail; } set { if ((this._ContactEmail != value)) { this.OnPropertyChanging("ContactEmail"); this._ContactEmail = value; this.OnPropertyChanged("ContactEmail"); } } } [System.Data.Linq.Column(Storage = "_ContactTypeID", Name = "ContactTypeID", DBType = "Int")] public Int32 ContactTypeID { get { return _ContactTypeID; } set { if ((this._ContactTypeID != value)) { this.OnPropertyChanging("ContactTypeID"); this._ContactTypeID = value; this.OnPropertyChanged("ContactTypeID"); } } } [System.Data.Linq.Column(Storage = "_ContactAge", Name = "ContactAge", DBType = "Int")] public Nullable<Int32> ContactAge { get { return _ContactAge; } set { if ((this._ContactAge != value)) { this.OnPropertyChanging("ContactAge"); this._ContactAge = value; this.OnPropertyChanged("ContactAge"); } } } #region INotifyPropertyChanged Members public event System.ComponentModel.PropertyChangedEventHandler PropertyChanged; #endregion #region INotifyPropertyChanging Members public event System.ComponentModel.PropertyChangedEventHandler PropertyChanging; #endregion protected void OnPropertyChanging(string propertyName) { if ((this.PropertyChanging != null)) { this.PropertyChanging(this, new System.ComponentModel.PropertyChangedEventArgs(propertyName)); } } protected void OnPropertyChanged(string propertyName) { if ((this.PropertyChanged != null)) { this.PropertyChanged(this, new System.ComponentModel.PropertyChangedEventArgs(propertyName)); } } } }
6. Les 4 opérations de base consultation,ajout,modification,suppression
Ne pas oublier oDataContext.SubmitChanges() pour valider les changements.
Code de la Form
using System.Text; using System.Windows.Forms; namespace NET35CsDatacontext { public partial class Form1 : Form { public Form1() { InitializeComponent(); } // Declaration public System.Data.Linq.DataContext oDataContext; public System.Data.Linq.Table<Contact> oContacts; private void Form1_Load(object sender, EventArgs e) { // Initialisation oDataContext = new System.Data.Linq.DataContext(@"Data Source=.;Initial Catalog=ContactDB;Integrated Security=SSPI;");// chaine de connexion oContacts = oDataContext.GetTable<Contact>(); } private void button1_Click(object sender, EventArgs e) { // 1 Read consultation var Query = from oContact in oContacts orderby oContact.ContactName select oContact; // Affichage dataGridView1.DataSource = Query; } private void button2_Click(object sender, EventArgs e) { // 2 Add Ajout Contact oContact = new Contact(); oContact.ContactFirstName = "Martin"; oContact.ContactName = "Louis"; oContacts.Add(oContact); oDataContext.SubmitChanges(); } private void button3_Click(object sender, EventArgs e) { // 3 Update Modification var Query = (from oContact in oContacts where oContact.ContactID == 7 select oContact).First<Contact>(); // automatiquement le type déduit est Contact donc on a accés aux propriétés de Contact Query.ContactName = "Wang"; oDataContext.SubmitChanges(); } private void button4_Click(object sender, EventArgs e) { // 4 Remove suppression var Query = (from oContact in oContacts where oContact.ContactID == 7 select oContact).First(); oContacts.Remove(Query); oDataContext.SubmitChanges(); } } }
=Avec un fichier Linq To SQL et le designer de Visual Studio Orcas==
1. Ajouter une référence à System.Data.Linq.dll
2. Explorateur de serveurs: Ajouter une connexion à la base de données SQL Server 2005
Note : le designer des versions Express de Visual studio Orcas ne prennent en charge que les fichiers de bases de données SQL Server 2005 Express (pas sur server). Les designers des versions de Visual Studio Orcas quand à elles prennent en charge également les bases de données sur serveur SQL Server 2005, actuellement Access (Jet) n'est pas pris en charge mais devrait l'être dans la version finale dOrcas
3. Glisser les tables depuis l'explorateur de serveurs sur le designer de Orcas
Le code est automatiquement généré. On peut modifier dans le designer (ajouter tables,supprimer colonnes,etc.), le code sera mis à jour. C'est l'utilitaire Sqlmetal.exe (que l'on peut donc utiliser en ligne de commande) qui est utilisé pour généré le code depuis le designer de Orcas. Il se trouve dans C:\Program Files\Microsoft Visual Studio 9.0\SDK\v3.5\Bin (mais je ne suis pas sur que selon la version Express, beta, ctp, extension mai 2006, il ne soit pas dans un autre répertoire)
1 classe héritant de Datacontext est générée
1 classe par table glissée est générée (+ les relations), celles ci héritent de
global::System.Data.Linq.INotifyPropertyChanging, global::System.ComponentModel.INotifyPropertyChanged
Le code en fait est relativement simple, on retrouve tout ce qui a été défini dans la première partie du tutoriel
// // <auto generated> // This code was generated by a tool. // Runtime Version:2.0.50727.1302 // // Changes to this file may cause incorrect behavior and will be lost if // the code is regenerated. // </auto generated> // namespace NET35CsDatacontextSqlMetal { public partial class ContactDataContext : global::System.Data.Linq.DataContext { [global::System.Diagnostics.DebuggerNonUserCodeAttribute()] public ContactDataContext(string connection) : base(connection) { } [global::System.Diagnostics.DebuggerNonUserCodeAttribute()] public ContactDataContext(global::System.Data.IDbConnection connection) : base(connection) { } [global::System.Diagnostics.DebuggerNonUserCodeAttribute()] public ContactDataContext() : base(global::NET35CsDatacontextSqlMetal.Properties.Settings.Default.ContactDBConnectionString) { } public global::System.Data.Linq.Table<Contact> Contacts { get { return this.GetTable<Contact>(); } } } [global::System.Data.Linq.Table(Name="dbo.Contact")] public partial class Contact : global::System.Data.Linq.INotifyPropertyChanging, global::System.ComponentModel.INotifyPropertyChanged { private int _ContactID; private string _ContactName; private string _ContactFirstName; private string _ContactEmail; private int _ContactTypeID; private global::System.Nullable<int> _ContactAge; [global::System.Diagnostics.DebuggerNonUserCodeAttribute()] public Contact() { this._ContactID = default(int); } [global::System.Data.Linq.Column(Storage="_ContactID", Name="ContactID", DBType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDBGenerated=true, CanBeNull=false)] public int ContactID { get { return this._ContactID; } } [global::System.Data.Linq.Column(Storage="_ContactName", Name="ContactName", DBType="Char(100) NOT NULL", CanBeNull=false)] public string ContactName { get { return this._ContactName; } set { if ((this._ContactName != value)) { this.OnPropertyChanging("ContactName"); this._ContactName = value; this.OnPropertyChanged("ContactName"); } } } [global::System.Data.Linq.Column(Storage="_ContactFirstName", Name="ContactFirstName", DBType="Char(100) NOT NULL", CanBeNull=false)] public string ContactFirstName { get { return this._ContactFirstName; } set { if ((this._ContactFirstName != value)) { this.OnPropertyChanging("ContactFirstName"); this._ContactFirstName = value; this.OnPropertyChanged("ContactFirstName"); } } } [global::System.Data.Linq.Column(Storage="_ContactEmail", Name="ContactEmail", DBType="Char(250)")] public string ContactEmail { get { return this._ContactEmail; } set { if ((this._ContactEmail != value)) { this.OnPropertyChanging("ContactEmail"); this._ContactEmail = value; this.OnPropertyChanged("ContactEmail"); } } } [global::System.Data.Linq.Column(Storage="_ContactTypeID", Name="ContactTypeID", DBType="Int NOT NULL", CanBeNull=false)] public int ContactTypeID { get { return this._ContactTypeID; } set { if ((this._ContactTypeID != value)) { this.OnPropertyChanging("ContactTypeID"); this._ContactTypeID = value; this.OnPropertyChanged("ContactTypeID"); } } } [global::System.Data.Linq.Column(Storage="_ContactAge", Name="ContactAge", DBType="Int")] public global::System.Nullable<int> ContactAge { get { return this._ContactAge; } set { if ((this._ContactAge != value)) { this.OnPropertyChanging("ContactAge"); this._ContactAge = value; this.OnPropertyChanged("ContactAge"); } } } public event global::System.ComponentModel.PropertyChangedEventHandler PropertyChanging; public event global::System.ComponentModel.PropertyChangedEventHandler PropertyChanged; [global::System.Diagnostics.DebuggerNonUserCodeAttribute()] protected void OnPropertyChanging(string propertyName) { if ((this.PropertyChanging != null)) { this.PropertyChanging(this, new global::System.ComponentModel.PropertyChangedEventArgs(propertyName)); } } [global::System.Diagnostics.DebuggerNonUserCodeAttribute()] protected void OnPropertyChanged(string propertyName) { if ((this.PropertyChanged != null)) { this.PropertyChanged(this, new global::System.ComponentModel.PropertyChangedEventArgs(propertyName)); } } } }
4. Utilisation les 4 opérations de base (consultation, ajout, modification, suppression)
On fait appel au Datacontext généré (c'est la seule différence avec la première partie)
Code de la Form:
using System; using System.Linq; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; namespace NET35CsDatacontextSqlMetal { public partial class Form1 : Form { public Form1() { InitializeComponent(); } public ContactDataContext oContactDataContext; public System.Data.Linq.Table<Contact> oContacts; private void Form1_Load(object sender, EventArgs e) { oContactDataContext = new ContactDataContext(); // ici inutile dajouter la chaine de connexion oContacts = oContactDataContext.GetTable<Contact>(); } private void button1_Click(object sender, EventArgs e) { // 1 Read consultation var Query = from oContact in oContacts orderby oContact.ContactName select oContact; // Affichage dataGridView1.DataSource = Query; } private void button2_Click(object sender, EventArgs e) { // 2 Add Ajout Contact oContact = new Contact(); oContact.ContactFirstName = "Petitsuisse"; oContact.ContactName = "Henri"; oContacts.Add(oContact); oContactDataContext.SubmitChanges(); } private void button3_Click(object sender, EventArgs e) { // 3 Update Modification var Query = (from oContact in oContacts where oContact.ContactFirstName == "Petitsuisse" select oContact).First<Contact>(); // automatiquement le type déduit est Contact donc on a accés aux propriétés de Contact Query.ContactName = "GrandYoghourt"; oContactDataContext.SubmitChanges(); } private void button4_Click(object sender, EventArgs e) { // 4 Remove suppression var Query = (from oContact in oContacts where oContact.ContactName == "GrandYoghourt" select oContact).First(); oContacts.Remove(Query); oContactDataContext.SubmitChanges(); } } }
DataContext: http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext_members(vs.90).aspx
101 queries: http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b
Linq project: http://msdn.microsoft.com/fr-fr/vstudio/aa904594.aspx