C# - adding new data to 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
// -----------------------------------------------------------
// Program.cs file:
// -----------------------------------------------------------

using System;
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>();

            users.InsertOnSubmit(new UserEntity  // adding a new user to the future saving
            {
                Name = "Piotr",
                Role = "MODERATOR",
                CreatedAt = DateTime.Now
            });

            dataContext.SubmitChanges();         // saves changes in the database (saves added user)
        }
    }
}


// -----------------------------------------------------------
// 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

*/