sqlite EF6

2021/5/21 19:27:47

本文主要是介绍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的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程