csharp:SQLite and Access using C# code read data

SQLite sql script:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

CREATE TABLE BookKindList (     BookKindID INTEGER PRIMARY KEY AUTOINCREMENT,     BookKindName varchar(500) not null,            BookKindCode varchar(100) null,     BookKindParent int null )   --添加 insert into BookKindList(BookKindName,BookKindCode,BookKindParent) values('目录','0003',1); --查询 select * from BookKindList;   select * from BookKindList where BookKindList.BookKindID=2; --删除 delete from BookKindList where BookKindList.BookKindID=3; --更新 update  BookKindList set BookKindName='文学',BookKindCode='0002',BookKindParent=1 where BookKindID=2;   --返回添加自增ID select last_insert_rowid();

　　SQLite 数据库连接字符

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

<?xml version="1.0" encoding="utf-8" ?> <configuration>   <appSettings>     <add key="ConnectionAccess2003String" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|geovindu.mdb;Persist Security Info=True"/>     <add key="ConnectionAccess2007String" value="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|geovindu.accdb;Persist Security Info=True"/>     <add key="ConnectionSQLString" value="Data Source=LF-WEN\GEOVINDU;Initial Catalog=geovindu;User ID=sa;Password=geovindu"/>     <add key="ConnectionSQLiteString" value="Data Source=geovindu.db3;Version=3;Password=geovindu;Pooling=true;FailIfMissing=false;"/>     <add key="ConnectionMySQLString" value="Database='geovindu';Data Source='127.0.0.1';User Id='root';Password='geovindu';charset='utf8';pooling=true;Port=3306;Allow Zero Datetime=true;"/>     <add key="WebDAL" value="AccessDAL"/>     <!--<add key="WebDAL" value="SqlServerDAL"/>-->     <!--<add key="WebDAL" value="SqlSQLiteDAL"/>-->     <!--<add key="WebDAL" value="SqlMySQLDAL"/>-->     <!--<add key="WebDAL" value="SqlPostgreSQLDAL"/>-->   </appSettings>     </configuration>

　　

Csharp操作SQLite 添加返回值

?

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

/// <summary>        /// 涂聚文 20150212        /// SQLite 添加返回值ID        /// </summary>        /// <param name="SQLString"></param>        /// <param name="identity"></param>        /// <param name="cmdParms"></param>        /// <returns></returns>        public static int ExecuteSql(string SQLString, out int identity, params SQLiteParameter[] cmdParms)        {            string en = "";            using (SQLiteConnection connection = new SQLiteConnection(connectionString))            {                using (SQLiteCommand cmd = new SQLiteCommand())                {                    try                    {                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);                        int rows = cmd.ExecuteNonQuery();                        cmd.Parameters.Clear();                        cmd.CommandText = "select last_insert_rowid();";                        en = cmd.ExecuteScalar().ToString();                        identity = int.Parse(en);                        cmd.Parameters.Clear();                        return rows;                    }                    catch (SQLiteException E)                    {                        throw new Exception(E.Message);                    }                }            }        }

　　

?

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

/// <summary>        /// 追回返回值        /// SQLite涂聚文        /// </summary>        /// <param name="bookKindList"></param>        /// <param name="id"></param>        /// <returns></returns>        public int InsertBookKindOut(BookKindListInfo bookKindList, out int id)        {            int ret = 0;            int tid = 0;            try            {                StringBuilder str = new StringBuilder();                str.Append("insert into BookKindList(BookKindName,BookKindCode,BookKindParent) values(");                str.Append("@BookKindName,@BookKindCode,@BookKindParent);");                               SQLiteParameter[] par = new SQLiteParameter[]{                new SQLiteParameter("@BookKindName",DbType.String,1000),                new SQLiteParameter("@BookKindCode",DbType.String,1000),                new SQLiteParameter("@BookKindParent",DbType.Int32,4),                //new SQLiteParameter("@BookKindID",DbType.Int32,4),                };                par[0].Value = bookKindList.BookKindName;                par[1].Value = bookKindList.BookKindCode;                par[2].Value = bookKindList.BookKindParent;                //par[3].Direction = ParameterDirection.Output;//无效  不支持                ret = SQLiteHelper.ExecuteSql(str.ToString(), out tid, par);                if (ret > 0)                {                    //tid = ret;// (int)par[3].Value;                }            }            catch (SQLiteException ex)            {                throw ex;            }            id = tid;              return ret;        }

　　Csharp操作Access添加返回值

?

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

/// <summary> ///  Access 添加返迴ID值 ///  涂聚文 2014-12-29 ///  Geovin Du /// 參考：  http://www.mikesdotnetting.com/article/54/getting-the-identity-of-the-most-recently-added-record /// http://stackoverflow.com/questions/186544/identity-after-insert-statement-always-returns-0 /// </summary> /// <param name="SQLString"></param> /// <param name="identity"></param> /// <param name="cmdParms"></param> /// <returns></returns> public static int ExecuteSql(string SQLString, out int identity, params OleDbParameter[] cmdParms) {          using (OleDbConnection connection = new OleDbConnection(connectionString))     {         using (OleDbCommand cmd = new OleDbCommand())         {             try             {                 PrepareCommand(cmd, connection, null, SQLString, cmdParms);                 int rows = cmd.ExecuteNonQuery();                 cmd.CommandText = "Select @@Identity";                 identity = (int)cmd.ExecuteScalar();                 cmd.Parameters.Clear();                 return rows;             }             catch (System.Data.OleDb.OleDbException E)             {                 throw new Exception(E.Message);             }         }     } }

　　

?

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

/// <summary> /// Access 追加返回值 /// 涂聚文 /// 20141205 /// </summary> /// <param name="bookKindList"></param> /// <param name="id"></param> /// <returns></returns> public int InsertBookKindOut(BookKindListInfo bookKindList, out int id) {     int ret = 0;     int tid = 0;     try     {         StringBuilder str = new StringBuilder();         str.Append("insert into BookKindList(BookKindName,BookKindCode,BookKindParent) values(");         str.Append("@BookKindName,@BookKindCode,@BookKindParent);");         OleDbParameter[] par = new OleDbParameter[]{         new OleDbParameter("@BookKindName",OleDbType.VarChar,1000),         new OleDbParameter("@BookKindCode",OleDbType.VarChar,1000),         new OleDbParameter("@BookKindParent",OleDbType.Integer,4),         //new OleDbParameter("@BookKindID",OleDbType.Integer,4),         };         par[0].Value = bookKindList.BookKindName;         par[1].Value = bookKindList.BookKindCode;         par[2].Value = bookKindList.BookKindParent;         //par[3].Direction = ParameterDirection.Output;//无效  不支持         ret = DbHelperOleDb.ExecuteSql(str.ToString(), out tid, par);         if (ret > 0)         {             //tid = ret;// (int)par[3].Value;         }     }     catch (OleDbException ex)     {         throw ex;     }     id = tid;       return ret; }

　https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

https://github.com/praeclarum/sqlite-net

https://github.com/sqlitebrowser/sqlitebrowser

https://sourceforge.net/projects/sqlitebrowser/files/

http://sqlitebrowser.org/