C# 使用EntityFramework CodeFirst 创建PostgreSQL数据库

1.先创建一个ASP.Net Web应用程序，选择Web API

 2、创建EntityLib、EF、AppService三个类库。EntityLib用于存放数据库表所对应的实体，AppService用于编写用户对实体的一些操作方法，如增删改查等操作。

 创建好所有类库之后，需要添加引用库EntityFramework6.Npgsql，右击项目中的引用———》管理NuGet程序包———》搜索EntityFramework6.Npgsql添加到项目中，这个库会自动添加EntityFramework6和Npgsql的引用。需要给EF、AppService和API都添加这个引用。添加完成之后就开始写各个类库相应的代码了。
 
EFContext类

1. public class EFContext : DbContext
2.     {
3.         public EFContext() : base("Postgresql")//连接字符串名称
4.         {
6.         }
7.         protected override void OnModelCreating(DbModelBuilder modelBuilder)
8.         {
9. <connectionStrings>
10.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
11.     </connectionStrings>modelBuilder.HasDefaultSchema("public");//EF默认创建到dbo架构中，而PostgreSQL默认为public架构
12. <connectionStrings>
13.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
14.     </connectionStrings>modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
15. <connectionStrings>
16.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
17.     </connectionStrings>base.OnModelCreating(modelBuilder);
18.         }
19.         public DbSet<User> Users { get; set; }
20.     }

复制代码

 
 User类

1. [Table("User",Schema ="public")]
2.     public class User
3.     {
4.         [Key]
5.         [Required]
6.         [Column("ID")]
7.         public int ID { get; set; }
9.         public string Name { get; set; }
11.         public string Account { get; set; }
13.         public string Password { get; set; }
14.     }

复制代码

 
AppService类

1. public class UserService
2.     {
3.         public void AddUser(User user)
4.         {
5. <connectionStrings>
6.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
7.     </connectionStrings>try
8. <connectionStrings>
9.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
10.     </connectionStrings>{
11. <connectionStrings>
12.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
13.     </connectionStrings>    using (var db = new EFContext())
14. <connectionStrings>
15.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
16.     </connectionStrings>    {
18. <connectionStrings>
19.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
20.     </connectionStrings>        db.Users.Add(user);
21. <connectionStrings>
22.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
23.     </connectionStrings>        db.SaveChanges();
24. <connectionStrings>
25.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
26.     </connectionStrings>    }
27. <connectionStrings>
28.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
29.     </connectionStrings>}
30. <connectionStrings>
31.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
32.     </connectionStrings>catch (Exception e)
33. <connectionStrings>
34.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
35.     </connectionStrings>{
36. <connectionStrings>
37.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
38.     </connectionStrings>    string msg = e.Message;
39. <connectionStrings>
40.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
41.     </connectionStrings>}
42.         }
44.         public void DeleteUserByID(int id)
45.         {
46. <connectionStrings>
47.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
48.     </connectionStrings>try
49. <connectionStrings>
50.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
51.     </connectionStrings>{
52. <connectionStrings>
53.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
54.     </connectionStrings>    using (var db = new EFContext())
55. <connectionStrings>
56.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
57.     </connectionStrings>    {
59. <connectionStrings>
60.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
61.     </connectionStrings>        db.Users.Remove(db.Users.Find(id));
62. <connectionStrings>
63.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
64.     </connectionStrings>        db.SaveChanges();
65. <connectionStrings>
66.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
67.     </connectionStrings>    }
68. <connectionStrings>
69.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
70.     </connectionStrings>}
71. <connectionStrings>
72.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
73.     </connectionStrings>catch (Exception e)
74. <connectionStrings>
75.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
76.     </connectionStrings>{
77. <connectionStrings>
78.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
79.     </connectionStrings>    string msg = e.Message;
80. <connectionStrings>
81.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
82.     </connectionStrings>}
83.         }
85.         public void EditUser(User user)
86.         {
87. <connectionStrings>
88.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
89.     </connectionStrings>try
90. <connectionStrings>
91.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
92.     </connectionStrings>{
93. <connectionStrings>
94.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
95.     </connectionStrings>    using (var db = new EFContext())
96. <connectionStrings>
97.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
98.     </connectionStrings>    {
99. <connectionStrings>
100.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
101.     </connectionStrings>        
102. <connectionStrings>
103.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
104.     </connectionStrings>        User u = db.Users.Find(user.ID);
105. <connectionStrings>
106.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
107.     </connectionStrings>        u.Name = user.Name;
108. <connectionStrings>
109.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
110.     </connectionStrings>        u.Account = user.Account;
111. <connectionStrings>
112.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
113.     </connectionStrings>        u.Password = user.Password;
114. <connectionStrings>
115.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
116.     </connectionStrings>        db.SaveChanges();
117. <connectionStrings>
118.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
119.     </connectionStrings>    }
120. <connectionStrings>
121.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
122.     </connectionStrings>}
123. <connectionStrings>
124.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
125.     </connectionStrings>catch (Exception e)
126. <connectionStrings>
127.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
128.     </connectionStrings>{
129. <connectionStrings>
130.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
131.     </connectionStrings>    string msg = e.Message;
132. <connectionStrings>
133.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
134.     </connectionStrings>}
135.         }
137.         public User FindUserByID(int id)
138.         {
139. <connectionStrings>
140.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
141.     </connectionStrings>try
142. <connectionStrings>
143.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
144.     </connectionStrings>{
145. <connectionStrings>
146.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
147.     </connectionStrings>    using (var db = new EFContext())
148. <connectionStrings>
149.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
150.     </connectionStrings>    {
152. <connectionStrings>
153.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
154.     </connectionStrings>        User user = db.Users.Find(id);
155. <connectionStrings>
156.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
157.     </connectionStrings>        return user;
158. <connectionStrings>
159.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
160.     </connectionStrings>    }
161. <connectionStrings>
162.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
163.     </connectionStrings>}
164. <connectionStrings>
165.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
166.     </connectionStrings>catch (Exception e)
167. <connectionStrings>
168.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
169.     </connectionStrings>{
170. <connectionStrings>
171.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
172.     </connectionStrings>    string msg = e.Message;
173. <connectionStrings>
174.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
175.     </connectionStrings>    return null;
176. <connectionStrings>
177.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
178.     </connectionStrings>}
179.         }
181.         public List<User> FindAll()
182.         {
183. <connectionStrings>
184.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
185.     </connectionStrings>try
186. <connectionStrings>
187.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
188.     </connectionStrings>{
189. <connectionStrings>
190.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
191.     </connectionStrings>    using (var db = new EFContext())
192. <connectionStrings>
193.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
194.     </connectionStrings>    {
196. <connectionStrings>
197.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
198.     </connectionStrings>        List<User> users = db.Users.ToList();
199. <connectionStrings>
200.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
201.     </connectionStrings>        return users;
202. <connectionStrings>
203.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
204.     </connectionStrings>    }
205. <connectionStrings>
206.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
207.     </connectionStrings>}
208. <connectionStrings>
209.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
210.     </connectionStrings>catch (Exception e)
211. <connectionStrings>
212.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
213.     </connectionStrings>{
214. <connectionStrings>
215.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
216.     </connectionStrings>    string msg = e.Message;
217. <connectionStrings>
218.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
219.     </connectionStrings>    return null;
220. <connectionStrings>
221.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
222.     </connectionStrings>}
223.         }
224.     }

复制代码

完成类的编写之后，需要添加数据库连接字符串，打开API的web.config文件添加：

1. <connectionStrings>
2.         <add name="Postgresql" connectionString="PORT=502;DATABASE=postgres;HOST=10.60.140.1;PASSWORD=007;USER ID=postgres" providerName="Npgsql" />
3.     </connectionStrings>

复制代码

 3.进行数据迁移，通过命令将实体类导入数据库生成相应的数据库表。先打开VS的工具，然后点击NuGet包管理器 ，选择程序包管理器控制台，默认项目改为EF,输入以下三行命令

1-》enable-migrations  
2-》add-migration 
3-》update-database
 
去PostgreSQL数据库去查看发现已经生成好了数据库和对应的表了，这里因为我postgres数据库已经有User表了，所以修改了一下数据库连接字符串

 

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！