SqLite教程
sqlite EF6
本文主要是介绍sqlite EF6，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
1.nuget包添加System.Data.SQLite
2.dbcontext
1 public class AirportBridContext : DbContext
2 {
3 public AirportBridContext() : base(new SQLiteConnection()
4 {
5 ConnectionString = new SQLiteConnectionStringBuilder()
6 {
7 DataSource = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "C:/Users/Guo/Desktop/发票/sqlitestudio-3.3.3/SQLiteStudio/bird".Replace("/", "\\")),
8 ForeignKeys = true
9 }.ConnectionString
10 }, true)
11 { }
12 protected override void OnModelCreating(DbModelBuilder modelBuilder)
13 {
14 //modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
15 base.OnModelCreating(modelBuilder);
16 }
17 protected override DbEntityValidationResult ValidateEntity(DbEntityEntry entityEntry, IDictionary<object, object> items)
18 {
19 return base.ValidateEntity(entityEntry, items);
20 }
21 public override int SaveChanges()
22 {
23 return base.SaveChanges();
24 }
25
26 protected override bool ShouldValidateEntity(DbEntityEntry entityEntry)
27 {
28 return base.ShouldValidateEntity(entityEntry);
29 }
30 public DbSet<WatchingBirdTasksModel> WatchingBirdTasks { get; set; }
View Code
3. 转换器 MigrationSqLiteGenerator
1 public class MigrationSqLiteGenerator : MigrationSqlGenerator
2 {
3 #region Constantes
4
5 private const string pstrDefaultDateTime = "yyyy-MM-dd hh:mm:ss";
6 private const int pintDefaultStringMaxLength = 255;
7 private const int pintDefaultPrecisaoNumerica = 10;
8 private const byte pbytDefaultPrecisaoTempo = 7;
9 private const byte pintDefaultEscala = 0;
10 //private const string pstrNomeTabelaMigration = "__MigrationHistory";
11
12 #endregion
13
14 #region Instancias
15
16 private DbProviderManifest pprovProviderManifest;
17 private List<MigrationStatement> plstComandos;
18 private bool pblnGerouPrimaryKey;
19
20 #endregion
21
22 #region Método de Geração sobrescrito de MigratioSqlGenerator
23
24 public override IEnumerable<MigrationStatement> Generate(
25 IEnumerable<MigrationOperation> lstOperacoesMigrations, string strManifestoProvider)
26 {
27 plstComandos = new List<MigrationStatement>();
28
29 InicializaServicosProvider(strManifestoProvider);
30 GeraComandos(lstOperacoesMigrations);
31
32 return plstComandos;
33 }
34
35 #endregion
36
37 #region Métodos de geração dos comandos
38
39 protected virtual void Generate(CreateTableOperation opeCriacaoTabela)
40 {
41 // Preferencialmente não iremos gerar a tabela de dados do Migration
42 if (opeCriacaoTabela.Name.Contains("MigrationHistory"))
43 return;
44
45 using (var ltextWriter = TextWriter())
46 {
47 GeraComandoCriacaoTabela(opeCriacaoTabela, ltextWriter);
48
49 ComandoSQL(ltextWriter);
50 }
51 }
52
53 protected virtual void Generate(AddForeignKeyOperation opeChaveEstrangeira)
54 {
55 // Inicialmente não havera a criação de chave estrangeira
56 }
57
58 protected virtual void Generate(DropForeignKeyOperation dropForeignKeyOperation)
59 {
60 // Inicalmente não haverá a criação de chave estrangeira
61 }
62
63 protected virtual void Generate(CreateIndexOperation opeCriacaoIndex)
64 {
65 using (var ltextWriter = TextWriter())
66 {
67 ltextWriter.Write("CREATE ");
68
69 if (opeCriacaoIndex.IsUnique)
70 ltextWriter.Write(" UNIQUE ");
71
72 ltextWriter.Write("INDEX ");
73 ltextWriter.Write(opeCriacaoIndex.Name);
74 ltextWriter.Write(" ON ");
75 ltextWriter.Write(RemoveDBO(opeCriacaoIndex.Table));
76 ltextWriter.Write("(");
77
78 for (int lintCount = 0; lintCount < opeCriacaoIndex.Columns.Count; lintCount++)
79 {
80 var lstrDadosColuna = opeCriacaoIndex.Columns[lintCount];
81
82 ltextWriter.Write(lstrDadosColuna);
83
84 if (lintCount < opeCriacaoIndex.Columns.Count - 1)
85 ltextWriter.WriteLine(",");
86 }
87
88 ltextWriter.Write(")");
89
90 ComandoSQL(ltextWriter);
91 }
92 }
93
94 protected virtual void Generate(DropIndexOperation opeDropIndex)
95 {
96 using (var ltextWriter = TextWriter())
97 {
98 ltextWriter.Write("DROP INDEX ");
99 ltextWriter.Write(opeDropIndex.Name);
100
101 ComandoSQL(ltextWriter);
102 }
103 }
104
105 protected virtual void Generate(AddPrimaryKeyOperation opeAdicionaPrimaryKey)
106 {
107 using (var ltextWriter = TextWriter())
108 {
109 ltextWriter.Write("ALTER TABLE ");
110 ltextWriter.Write(RemoveDBO(opeAdicionaPrimaryKey.Table));
111 ltextWriter.Write(" ADD CONSTRAINT ");
112 ltextWriter.Write(opeAdicionaPrimaryKey.Name);
113 ltextWriter.Write(" PRIMARY KEY ");
114 ltextWriter.Write("(");
115
116 for (int li = 0; li < opeAdicionaPrimaryKey.Columns.Count; li++)
117 {
118 var lstrDadosColuna = opeAdicionaPrimaryKey.Columns[li];
119
120 ltextWriter.Write(lstrDadosColuna);
121
122 if (li < opeAdicionaPrimaryKey.Columns.Count - 1)
123 ltextWriter.WriteLine(",");
124 }
125
126 ltextWriter.Write(")");
127
128 ComandoSQL(ltextWriter);
129 }
130 }
131
132 protected virtual void Generate(DropPrimaryKeyOperation opeDropPrimaryKey)
133 {
134 using (var ltextWriter = TextWriter())
135 {
136 ltextWriter.Write("ALTER TABLE ");
137 ltextWriter.Write(RemoveDBO(opeDropPrimaryKey.Table));
138 ltextWriter.Write(" DROP CONSTRAINT ");
139 ltextWriter.Write(opeDropPrimaryKey.Name);
140
141 ComandoSQL(ltextWriter);
142 }
143 }
144
145 protected virtual void Generate(AddColumnOperation opeAdicionaColuna)
146 {
147 using (var ltextWriter = TextWriter())
148 {
149 ltextWriter.Write("ALTER TABLE ");
150 ltextWriter.Write(RemoveDBO(opeAdicionaColuna.Table));
151 ltextWriter.Write(" ADD ");
152
153 var lcmColuna = opeAdicionaColuna.Column;
154
155 Generate(lcmColuna, ltextWriter, null);
156
157 if ((lcmColuna.IsNullable != null)
158 && !lcmColuna.IsNullable.Value
159 && (lcmColuna.DefaultValue == null)
160 && (string.IsNullOrWhiteSpace(lcmColuna.DefaultValueSql))
161 && !lcmColuna.IsIdentity
162 && !lcmColuna.IsTimestamp
163 && !lcmColuna.StoreType.Equals("rowversion", StringComparison.InvariantCultureIgnoreCase)
164 && !lcmColuna.StoreType.Equals("timestamp", StringComparison.InvariantCultureIgnoreCase))
165 {
166 ltextWriter.Write(" DEFAULT ");
167
168 if (lcmColuna.Type == PrimitiveTypeKind.DateTime)
169 {
170 ltextWriter.Write(Generate(DateTime.Parse("1900-01-01 00:00:00", CultureInfo.InvariantCulture)));
171 }
172 else
173 {
174 ltextWriter.Write(Generate((dynamic)lcmColuna.ClrDefaultValue));
175 }
176 }
177
178 ComandoSQL(ltextWriter);
179 }
180 }
181
182 protected virtual void Generate(DropColumnOperation opeRemoveColuna)
183 {
184 using (var ltextWriter = TextWriter())
185 {
186 ltextWriter.Write("ALTER TABLE ");
187 ltextWriter.Write(RemoveDBO(opeRemoveColuna.Table));
188 ltextWriter.Write(" DROP COLUMN ");
189 ltextWriter.Write(opeRemoveColuna.Name);
190
191 ComandoSQL(ltextWriter);
192 }
193 }
194
195 protected virtual void Generate(AlterColumnOperation opeAlteraColuna)
196 {
197 var lcmColuna = opeAlteraColuna.Column;
198
199 using (var ltextWriter = TextWriter())
200 {
201 ltextWriter.Write("ALTER TABLE ");
202 ltextWriter.Write(RemoveDBO(opeAlteraColuna.Table));
203 ltextWriter.Write(" ALTER COLUMN ");
204 ltextWriter.Write(lcmColuna.Name);
205 ltextWriter.Write(" ");
206 ltextWriter.Write(ConstruirTipoColuna(lcmColuna));
207
208 if ((lcmColuna.IsNullable != null)
209 && !lcmColuna.IsNullable.Value)
210 {
211 ltextWriter.Write(" NOT NULL");
212 }
213
214 ComandoSQL(ltextWriter);
215 }
216
217 if ((lcmColuna.DefaultValue == null) && string.IsNullOrWhiteSpace(lcmColuna.DefaultValueSql))
218 return;
219
220 using (var ltextWriter = TextWriter())
221 {
222 ltextWriter.Write("ALTER TABLE ");
223 ltextWriter.Write(RemoveDBO(opeAlteraColuna.Table));
224 ltextWriter.Write(" ALTER COLUMN ");
225 ltextWriter.Write(lcmColuna.Name);
226 ltextWriter.Write(" DROP DEFAULT");
227
228 ComandoSQL(ltextWriter);
229 }
230
231 using (var ltextWriter = TextWriter())
232 {
233 ltextWriter.Write("ALTER TABLE ");
234 ltextWriter.Write(RemoveDBO(opeAlteraColuna.Table));
235 ltextWriter.Write(" ALTER COLUMN ");
236 ltextWriter.Write(lcmColuna.Name);
237 ltextWriter.Write(" SET DEFAULT ");
238 ltextWriter.Write(
239 (lcmColuna.DefaultValue != null)
240 ? Generate((dynamic)lcmColuna.DefaultValue)
241 : lcmColuna.DefaultValueSql
242 );
243
244 ComandoSQL(ltextWriter);
245 }
246 }
247
248 protected virtual void Generate(DropTableOperation opeDropTable)
249 {
250 using (var ltextWriter = TextWriter())
251 {
252 ltextWriter.Write("DROP TABLE ");
253 ltextWriter.Write(RemoveDBO(opeDropTable.Name));
254
255 ComandoSQL(ltextWriter);
256 }
257 }
258
259 protected virtual void Generate(SqlOperation opeSQL)
260 {
261 ComandoSQL(opeSQL.Sql, opeSQL.SuppressTransaction);
262 }
263
264 protected virtual void Generate(RenameColumnOperation opeRenomearColuna)
265 {
266 // Inicialmente não suportada
267 }
268
269 protected virtual void Generate(RenameTableOperation opeRenameTable)
270 {
271 }
272
273 protected virtual void Generate(MoveTableOperation opeMoveTable)
274 {
275 }
276
277 private void Generate(ColumnModel cmDadosColuna, IndentedTextWriter textWriter, PrimaryKeyOperation opePrimaryKey)
278 {
279 textWriter.Write(cmDadosColuna.Name);
280 textWriter.Write(" ");
281 bool lblnEhPrimaryKey = false;
282
283 if (opePrimaryKey != null)
284 lblnEhPrimaryKey = opePrimaryKey.Columns.Contains(cmDadosColuna.Name);
285
286
287 if (lblnEhPrimaryKey)
288 {
289 if ((cmDadosColuna.Type == PrimitiveTypeKind.Int16) ||
290 (cmDadosColuna.Type == PrimitiveTypeKind.Int32))
291 textWriter.Write(" INTEGER ");
292 else
293 textWriter.Write(ConstruirTipoColuna(cmDadosColuna));
294
295 if (cmDadosColuna.IsIdentity)
296 {
297 textWriter.Write(" PRIMARY KEY AUTOINCREMENT ");
298 pblnGerouPrimaryKey = true;
299 }
300 }
301 else
302 {
303 textWriter.Write(ConstruirTipoColuna(cmDadosColuna));
304
305 if ((cmDadosColuna.IsNullable != null)
306 && !cmDadosColuna.IsNullable.Value)
307 {
308 textWriter.Write(" NOT NULL");
309 }
310
311 if (cmDadosColuna.DefaultValue != null)
312 {
313 textWriter.Write(" DEFAULT ");
314 textWriter.Write(Generate((dynamic)cmDadosColuna.DefaultValue));
315 }
316 else if (!string.IsNullOrWhiteSpace(cmDadosColuna.DefaultValueSql))
317 {
318 textWriter.Write(" DEFAULT ");
319 textWriter.Write(cmDadosColuna.DefaultValueSql);
320 }
321 }
322 }
323
324 protected virtual void Generate(HistoryOperation opeHistorico)
325 {
326 // Foi removido, pois atualmente não usaremos o Migration
327 //using (var ltextWriter = TextWriter())
328 //{
329 // if (opeHistorico is InsertHistoryOperation)
330 // {
331 // InsertHistoryOperation lhisOperacaoInsert = opeHistorico as InsertHistoryOperation;
332 // ltextWriter.Write(" INSERT INTO ");
333 // ltextWriter.Write(pstrNomeTabelaMigration);
334 // ltextWriter.Write(" ( MigrationId, Model, ProductVersion) VALUES ");
335 // ltextWriter.Write(string.Format(" ('{0}', {1}, '{2}')",
336 // lhisOperacaoInsert.MigrationId,
337 // Generate(lhisOperacaoInsert.Model), lhisOperacaoInsert.ProductVersion));
338 // }
339 // else if (opeHistorico is DeleteHistoryOperation)
340 // {
341 // DeleteHistoryOperation lhisOperacaoInsert = opeHistorico as DeleteHistoryOperation;
342 // ltextWriter.Write(" DELETE FROM ");
343 // ltextWriter.Write(pstrNomeTabelaMigration);
344 // ltextWriter.Write(string.Format(" WHERE MigrationId = '{0}'",
345 // lhisOperacaoInsert.MigrationId));
346 // }
347
348 // ComandoSQL(ltextWriter);
349 //}
350 }
351
352 protected virtual string Generate(byte[] bytDefaultValue)
353 {
354 var lstrbHexString = new StringBuilder();
355
356 foreach (var lbtByte in bytDefaultValue)
357 lstrbHexString.Append(lbtByte.ToString("X2", CultureInfo.InvariantCulture));
358
359 return "x'" + lstrbHexString + "'";
360 }
361
362 protected virtual string Generate(bool blnDefaultValue)
363 {
364 return blnDefaultValue ? "1" : "0";
365 }
366
367 protected virtual string Generate(DateTime dtmDefaultValue)
368 {
369 return "'" + dtmDefaultValue.ToString(pstrDefaultDateTime, CultureInfo.InvariantCulture) + "'";
370 }
371
372 protected virtual string Generate(DateTimeOffset dtfDefaultValue)
373 {
374 return "'" + dtfDefaultValue.ToString(pstrDefaultDateTime, CultureInfo.InvariantCulture) + "'";
375 }
376
377 protected virtual string Generate(Guid guidDefaultValue)
378 {
379 return "'" + guidDefaultValue + "'";
380 }
381
382 protected virtual string Generate(string strDefaultValue)
383 {
384 return "'" + strDefaultValue + "'";
385 }
386
387 protected virtual string Generate(TimeSpan tsDefaultValue)
388 {
389 return "'" + tsDefaultValue + "'";
390 }
391
392 protected virtual string Generate(object objDefaultValue)
393 {
394 return string.Format(CultureInfo.InvariantCulture, "{0}", objDefaultValue);
395 }
396
397 #endregion
398
399 #region Métodos auxiliares
400
401 protected virtual string ConstruirTipoColuna(ColumnModel cmModeloColuna)
402 {
403 return cmModeloColuna.IsTimestamp ? "rowversion" : ConstruirTipoPropriedade(cmModeloColuna);
404 }
405
406 private string ConstruirTipoPropriedade(ColumnModel propModeloPropriedade)
407 {
408 var lstrOriginalStoreTypeName = propModeloPropriedade.StoreType;
409
410 if (string.IsNullOrWhiteSpace(lstrOriginalStoreTypeName))
411 {
412 var ltypeUsage = pprovProviderManifest.GetStoreType(propModeloPropriedade.TypeUsage).EdmType;
413 lstrOriginalStoreTypeName = ltypeUsage.Name;
414 }
415
416 var lstrStoreTypeName = lstrOriginalStoreTypeName;
417
418 const string lstrSufixoMax = "(max)";
419
420 if (lstrStoreTypeName.EndsWith(lstrSufixoMax, StringComparison.Ordinal))
421 lstrStoreTypeName = lstrStoreTypeName.Substring(0, lstrStoreTypeName.Length - lstrSufixoMax.Length) + lstrSufixoMax;
422
423 switch (lstrOriginalStoreTypeName.ToLowerInvariant())
424 {
425 case "decimal":
426 case "numeric":
427 lstrStoreTypeName += "(" + (propModeloPropriedade.Precision ?? pintDefaultPrecisaoNumerica)
428 + ", " + (propModeloPropriedade.Scale ?? pintDefaultEscala) + ")";
429 break;
430 case "datetime":
431 case "time":
432 lstrStoreTypeName += "(" + (propModeloPropriedade.Precision ?? pbytDefaultPrecisaoTempo) + ")";
433 break;
434 case "blob":
435 case "varchar2":
436 case "varchar":
437 case "char":
438 case "nvarchar":
439 case "nvarchar2":
440 lstrStoreTypeName += "(" + (propModeloPropriedade.MaxLength ?? pintDefaultStringMaxLength) + ")";
441 break;
442 }
443
444 return lstrStoreTypeName;
445 }
446
447 protected void ComandoSQL(string strInstrucaoSQL, bool blnSuprimeTransacao = false)
448 {
449 plstComandos.Add(new MigrationStatement
450 {
451 Sql = strInstrucaoSQL,
452 SuppressTransaction = blnSuprimeTransacao
453 });
454 }
455
456 protected void ComandoSQL(IndentedTextWriter writer)
457 {
458 ComandoSQL(writer.InnerWriter.ToString());
459 }
460
461
462 protected static IndentedTextWriter TextWriter()
463 {
464 return new IndentedTextWriter(new StringWriter(CultureInfo.InvariantCulture));
465 }
466
467 private static string RemoveDBO(string strTexto)
468 {
469 return strTexto.Replace("dbo.", string.Empty);
470 }
471
472 private void GeraComandos(IEnumerable<MigrationOperation> lstOperacoesMigrations)
473 {
474 foreach (dynamic ldynOperacao in lstOperacoesMigrations)
475 Generate(ldynOperacao);
476 }
477
478
479 private void InicializaServicosProvider(string strManifestoProvider)
480 {
481 using (var lconConexao = CreateConnection())
482 {
483 pprovProviderManifest = DbProviderServices
484 .GetProviderServices(lconConexao)
485 .GetProviderManifest(strManifestoProvider);
486 }
487 }
488
489 protected virtual DbConnection CreateConnection()
490 {
491 return new SQLiteConnection();
492 }
493
494 private void GeraComandoCriacaoTabela(CreateTableOperation opeCriacaoTabela, IndentedTextWriter textWriter)
495 {
496
497 textWriter.WriteLine("CREATE TABLE " + RemoveDBO(opeCriacaoTabela.Name) + " (");
498 textWriter.Indent++;
499
500 for (int i = 0; i < opeCriacaoTabela.Columns.Count; i++)
501 {
502 ColumnModel lcmDadosColuna = opeCriacaoTabela.Columns.ToList()[i];
503 Generate(lcmDadosColuna, textWriter, opeCriacaoTabela.PrimaryKey);
504
505 if (i < opeCriacaoTabela.Columns.Count - 1)
506 textWriter.WriteLine(",");
507 }
508
509 if ((opeCriacaoTabela.PrimaryKey != null) && !pblnGerouPrimaryKey)
510 {
511 textWriter.WriteLine(",");
512 textWriter.Write("CONSTRAINT ");
513 textWriter.Write(RemoveDBO(opeCriacaoTabela.PrimaryKey.Name));
514 textWriter.Write(" PRIMARY KEY ");
515 textWriter.Write("(");
516
517 for (int li = 0; li < opeCriacaoTabela.PrimaryKey.Columns.Count; li++)
518 {
519 var lstrNomeColuna = opeCriacaoTabela.PrimaryKey.Columns[li];
520
521 textWriter.Write(lstrNomeColuna);
522
523 if (li < opeCriacaoTabela.PrimaryKey.Columns.Count - 1)
524 textWriter.WriteLine(",");
525 }
526
527 textWriter.WriteLine(")");
528 }
529 else
530 {
531 textWriter.WriteLine();
532 }
533
534 textWriter.Indent--;
535 textWriter.Write(")");
536 }
537
538 #endregion
539
540 }
View Code
4.dbcontext配置 SQLiteConfiguration
1 public class SQLiteConfiguration : DbConfiguration
2 {
3 public SQLiteConfiguration()
4 {
5 SetProviderFactory("System.Data.SQLite", SQLiteFactory.Instance);
6 SetProviderFactory("System.Data.SQLite.EF6", SQLiteProviderFactory.Instance);
7 SetProviderServices("System.Data.SQLite", (DbProviderServices)SQLiteProviderFactory.Instance.GetService(typeof(DbProviderServices)));
8 }
9 }
View Code
这篇关于sqlite EF6的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
-
"SQLite注入获取表名技巧"12-29
-
SQLite数据库注入方式及安全性12-27
-
Sqlite 并发读写的演进之路09-16
-
修改ASQLite3D2010 支持中文显示09-07
-
02-mORMot框架样例学习-02 - Embedded SQLite3 ORM(SQLite3 数据库）09-05
-
基础复习——数据库SQLite——SQL的基本语法——数据库管理器SQLiteDatabase——数据库帮助器SQLiteOpenHelper08-22
-
【数据库】SQLite数据库 工具08-21
-
SQLite 错误：database disk image is malformed - 数据库磁盘映像格式不正确08-06
-
学生信息管理系统（QT+SQLITE实现）07-30
-
SQLite 建表、增删改查 简单操作07-22
-
sqlite 内存模式存盘07-21
-
Unity using Sqlite07-10
-
Uubuntu搭建sqlite07-09
-
使用手游模拟器无法在adb shell 使用sqlite3命令的问题06-30
-
未能加载文件或程序集“System.Data.SQLite.DLL”或它的某一个依赖项。找不到指定 问题的处理06-29
栏目导航
