Relacja jeden do wielu w C# i SQL Server

Autor: Artur Protasewicz

Wstęp

Specjalnie nie ma sensu mówić o wiązaniu danych, jeśli nie pracuje się na prawdziwej bazie danych np. SQL Server. Omawiam tu, jak wykorzystać SQL Server (używam SQL Server Management Studio Express 2005) do stworzenia bazy danych zawierającej relację master-detail (jeden do wielu). Następnie pokazuję, jak włączyć ją do projektu w C#. Robię to na dwa sposoby - tylko za pomocą klikania i wiązania kontrolek oraz tylko za pomocą kodu C# i SQL i pustych niepowiązanych gridów, choć wiązanie faktycznie istnieje na poziomie zapytania SQL. Zrzuty ekranu komentują się same, dlatego jest tylko trochę moich komentarzy. Pokazuję też, jak przygotować dane do własnych eksperymentów i je wyświetlić w programie. Natomiast nie pokazuje jak dane zmieniać i aktualizować. Przy aktualizacji danych powiązanych relacją master-detail należy pamiętać, że najpierw aktualizuje się tabelę master (tutaj Companies - Firmy), a dopiero potem detail (tutaj Contacts - Kontakty).

Tworzenie bazy danych SQL Server

Zakładam, że masz zainstalowany SQL Server z możliwością wizualnego tworzenia tabel i relacji. Jak wspomniałem, może to być SQL Server Management Studio Express 2005. Utworzę lokalną bazę danych. Taka baza danych powstanie domyślnie w katalogu C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data. Można ją później udostępnić przez sieć, ale to wykracza poza ramy tego tematu.

001 New Database.jpg

002 New Database Companies And Contacts.jpg

003 New Table.jpg

Kiedy utworzysz kolumny tabeli, kliknij prawym przyciskiem myszy pierwszy z pokazanych wierszy CompanyId i wybierz z menu podręcznego Set Primary Key. Domyślnie Identity będzie numerować wiersze tabeli od 1 zwiększając się zawsze o 1. Identity to odpowiednik Autoincrement.

004 Identity Companies.jpg

Zapisz tabelę nadając jej nazwę.

005 Name table Companies.jpg

To samo z drugą tabelą.

006 Identity ContactId.jpg

Klucz główny i klucz obcy

Pole (kolumna) CompanyId w tabeli Companies (Firmy) jest kluczem głównym, a w tabeli Contacts (Kontakty) jest kluczem obcym. Powiązanie ich relacją na poziomie SQL Servera dużo ułatwia i wiele rzeczy dzieje się potem automatycznie w twoim programie.

007 Relationships.jpg

008 Foreign Key Relationships.jpg

009 FK_Contacts_Companies.jpg

Kaskada

Kiedy usuwa się rekord z tabeli master, wszystkie związane z nim za pomocą klucza obcego rekordy w tabeli detail powinny zostać usunięte. SQL Server zrobi to za ciebie, jeśli ustawisz regułę usuwania na Cascade. Zwykle można to zrobić, choć nie zawsze. Można to robić również dla wielokrotnych relacji master-detail.

010 Cascade Delete Rule.jpg

Wpisanie przykładowych danych na poziomie SQL Server

011 Open Table.jpg

Nie wpisuj danych do pola identity.

012 Entering Data Companies.jpg

013 Entering Data Contacts.jpg

Tabele połączone - zapytanie SQL

Nie ma znaczenia dla zapytania SQL, czy utworzyłeś relację. Możesz ją utworzyć w zapytaniu używając join. Pokazuję tylko jak sprawdzić, jakie masz dane do testów.

014 New Query.jpg

015 Execute.jpg

Wiązanie w projekcie C# - bez pisania kodu

Skopiuj dwa pliki bazy danych (.mdf)(.ldf) ze ścieżki domyślnej SQL Servera do katalogu twojego projektu. Jeżeli się nie daje, to poszukaj informacji na temat Attach i Detach – dołączania i wyłączania baz ze zbioru baz SQL Servera.

016 Add Existing Item.jpg

![017 Add Companies And Contact.jpg](//static.4programmers.net/uploads/attachment/017 Add Companies And Contact.jpg)

018 Data Source Configuration Wizard.jpg

Przeciągnij i upuść tabele z Data Sources na formę. C# stworzy za ciebie gridy danych.

019 Drag And Drop Companies.jpg

020 Drag And Drop Contacts.jpg

021 Form1 Two Grids.jpg

Zmień własności drugiego grida. To jest właśnie podstawa utworzenia wiązania na poziomie projektu, ale warunkiem jest wcześniejsze utworzenie relacji w SQL Server.

022 Data Member.jpg

023 Grid Binding Source.jpg

024 Grids Ready.jpg

Uruchom program.

025 Form 1 Master-Detail Ready 1.jpg

026 Form1 Master-Detail Ready 2.jpg

Połączenie kodu C# i SQL

To samo co wyżej można zrobić używając pustych gridów niepowiązanych z bazą danych. Najpierw trzeba położyć na formę dwa gridy i utworzyć odpowiednią ilość kolumn (Add column) w każdym gridzie (tylko ilość kolumn jest istotna).

using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
 
namespace MasterDetail
{
    public partial class Form1 : Form
    {
        String ConnStr = "Data Source=.\\SQLEXPRESS;Integrated Security=True;Initial Catalog=CompaniesAndContacts;";
        public Form1()
        {
            InitializeComponent();
            btnOpenContacts.Click += new EventHandler(btnOpenContacts_Click);
            gridCompanies.Click += new EventHandler(gridCompanies_Click);
        }
        void btnOpenContacts_Click(object sender, EventArgs e)
        {
            FillCompanies();
        }
        void gridCompanies_Click(object sender, EventArgs e)
        {
            FillContactsBy((int)gridCompanies[0, gridCompanies.SelectedCells[0].RowIndex].Value);
        }
        void FillCompanies()
        {
            SqlConnection Conn = new SqlConnection(ConnStr);
            SqlCommand CmdSelect = new SqlCommand("select CompanyId, CompanyName from Companies");
            CmdSelect.Connection = Conn;
            Conn.Open();
            SqlDataReader reader = CmdSelect.ExecuteReader();
            gridCompanies.Rows.Clear();
            int i = 0;
            while (reader.Read())
            {
                gridCompanies.Rows.Add();
                gridCompanies[0, i].Value = reader[0];
                gridCompanies[1, i].Value = reader[1];
                i++;
            }
            reader.Close();
            Conn.Close();
        }
        void FillContactsBy(int CompanyId)
        {
            SqlConnection Conn = new SqlConnection(ConnStr);
            SqlCommand CmdSelect = new SqlCommand( "select CompanyId, ContactName, Phone from Contacts where CompanyId = @CompanyId");
            CmdSelect.Parameters.Add("@CompanyId", SqlDbType.Int);
            CmdSelect.Parameters[0].Value = CompanyId;
            CmdSelect.Connection = Conn;
            Conn.Open();
            SqlDataReader reader = CmdSelect.ExecuteReader();
            gridContacts.Rows.Clear();
            int i = 0;
            while (reader.Read())
            {
                gridContacts.Rows.Add();
                gridContacts[0, i].Value = reader[0];
                gridContacts[1, i].Value = reader[1];
                gridContacts[2, i].Value = reader[2];
                i++;
            }
            reader.Close();
            Conn.Close();
        }
    }
}


Rezultaty są te same.

027 Same Results 1.jpg

028 Same Results 2.jpg

Źródła

Moi przyjaciele z jednej z firm, w której pracowałem, którzy dali mi podstawy do własnych eksperymentów. Myślę, że nie mieliby nic przeciw tej publikacji, a jednocześnie nie życzyliby sobie umieszczania ich nazwisk.

1 komentarz

Kod C# jest bez komentarzy. Jak będę miał czas to uzupełnię.