c# - removing data from database using linq to sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
// -----------------------------------------------------------
// Program.cs file:
// -----------------------------------------------------------

using System.Linq;
using System.Data.Linq;

public class Program
{
    public static void Main()
    {
        string connectionString = @"Data Source=localhost\SQLEXPRESS;Initial Catalog=projectdb;Integrated Security=True";

        using (DataContext dataContext = new DataContext(connectionString))
        {
            Table<UserEntity> users = dataContext.GetTable<UserEntity>();

            UserEntity user = users.SingleOrDefault(u => u.Id == 1);  // tries to find user with id == 1

            if (user != null)
            {
                users.DeleteOnSubmit(user);   // sets user that will be removed from database

                dataContext.SubmitChanges();  // submits delete operation
            }
        }
    }
}


// -----------------------------------------------------------
// UserEntity.cs file:
// -----------------------------------------------------------

using System;
using System.Data.Linq.Mapping;

[Table(Name = "Users")]
public class UserEntity
{
    [Column(IsPrimaryKey = true, Name = "Id", IsDbGenerated = true)]
    public long Id { get; set; }

    [Column(Name = "Name")]
    public string Name { get; set; }

    [Column(Name = "Role")]
    public string Role { get; set; } // ADMIN, MODERATOR, TEACHER, STUDENT

    [Column(Name = "CreatedAt")]
    public DateTime? CreatedAt { get; set; }

    [Column(Name = "RemovedAt")]
    public DateTime? RemovedAt { get; set; }
}


// -----------------------------------------------------------
// Data base preparation:
// -----------------------------------------------------------

/*

1. Create `projectdb` database

2. Create `Users` table using:

    USE [projectdb]
    GO

    CREATE TABLE [dbo].[Users] (
        [Id] BIGINT IDENTITY(1,1) NOT NULL,
        [Name] NCHAR(50) NOT NULL,
        [Role] NCHAR(20) NOT NULL,
        [CreatedAt] DATETIME NOT NULL,
        [RemovedAt] DATETIME,
        PRIMARY KEY ([Id])
    );

    GO

    INSERT INTO [dbo].[Users]
               ([Id], [Name], [Role], [CreatedAt], [RemovedAt])
         VALUES
               (1, 'Jan',    'ADMIN',     CURRENT_TIMESTAMP, null),
               (2, 'Tomasz', 'MODERATOR', CURRENT_TIMESTAMP, null),
               (3, 'Marek',  'STUDENT',   CURRENT_TIMESTAMP, null);

    GO

*/