Use SQLite and Enterprise Library in applications

System. Data. SQLite:
Http://sqlite.phxsoftware.com/

Enterprise Library 3.1 (May 2007 ):
Http://www.microsoft.com/downloads/details.aspx? Familyid = 4c557c63-708f-4280-8f0c-637481c31718 & displaylang = en

Entlibcontrib project at codeplex:
Http://www.codeplex.com/entlibcontrib

Recently, SQLite and Enterprise Library have been used in a small application, and some notes are recorded as follows.

For the combination of SQLite and Enterprise Library, see Peter Bromberg's article ASP. NET: Using SQLite with Enterprise Library 3.1.

Effect of APP. config Configuration:

<configuration>  <configSections>    <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />  </configSections>  <dataConfiguration defaultDatabase="ConnectionString">    <providerMappings>      <add databaseType="EntLibContrib.Data.SQLite.SQLiteDatabase, EntLibContrib.Data.SqLite, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"        name="System.Data.SQLite" />    </providerMappings>  </dataConfiguration>  <connectionStrings>    <add name="ConnectionString" connectionString="Data Source=./Fintronx.db3;Version=3;"      providerName="System.Data.SQLite" />  </connectionStrings></configuration>

Note that entlibcontrib requires the corresponding system. Data. SQLite version 1.0.43.0.

Initialization example:

db = DatabaseFactory.CreateDatabase("ConnectionString");

SQL operation example:

string strSql = "INSERT INTO tp_PlayList (ListID, ListName, CreateTime) VALUES (NULL, @ListName, @CreateTime)";DbCommand cmd = Global.Instance.CommonDatabase.GetSqlStringCommand(strSql);Global.Instance.CommonDatabase.AddInParameter(cmd, "@ListName", DbType.String, ListName);Global.Instance.CommonDatabase.AddInParameter(cmd, "@CreateTime", DbType.DateTime, DateTime.Now);Global.Instance.CommonDatabase.ExecuteNonQuery(cmd);

The insert operation shown above was moved from a web project. The table structure and SQL statements only adjust the auto-increment fields (how to create auto-increment fields in SQLite ), everything is normal.

 

But there are still new problems in the development process. In some SQL operations, the "no such table:..." exception will be reported, and the location is not fixed, making it very depressing.

After a long time of entanglement, I finally found that it was because folderbrowserdialog or openfiledialog was used in the program. After the operation, the default directory changes will occur and the SQLite database file cannot be found!

In fact, the methods in Peter Bromberg's article can solve this problem. His code constructs the complete physical path of the database file, and I didn't pay attention to it.

I know the problem, and the rest is to solve it. I adopted another method that is not good enough. The Code is as follows:

Public Database commondatabase {get {If (DB = NULL) {string connectionname = "connectionstring"; # When region is running, it updates the database connection string information. // when SQLite database is used, if folderbrowserdialog or openfiledialog is used in the program, the default directory changes, and the SQL operation reports "No such table :... "exception string connectionstring =" Data Source = "+ application. startuppath + @ "\ fintronx. db3; version = 3; "; // new configuration connectionstringsettings connectionsettings = new connectionstringsettings (connectionname, connectionstring," system. data. SQLite "); // Save the configuration information configuration Config = configurationmanager. openexeconfiguration (configurationuserlevel. none); config. connectionstrings. connectionstrings. remove (connectionname); config. connectionstrings. connectionstrings. add (connectionsettings); config. save (configurationsavemode. modified); // force the connectionstrings configuration section of the configuration file to be reloaded. refreshsection ("connectionstrings"); # endregion // DB = databasefactory. createdatabase (connectionname); databaseproviderfactory factory = new databaseproviderfactory (New fileconfigurationsource (appdomain. currentdomain. setupinformation. configurationfile); DB = factory. create (connectionname) ;}return dB ;}}