csharp:SQLite and Access using C# code read data

標籤：

SQLite sql script:

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;--返回添加自增IDselect last_insert_rowid();

　　SQLite 資料庫連接字元

<?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 添加傳回值

 /// <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 (System.Data.OleDb.OleDbException E)                    {                        throw new Exception(E.Message);                    }                }            }        }

　　

 /// <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添加傳回值

        /// <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);                    }                }            }        }

　　

        /// <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;        }

　　

csharp:SQLite and Access using C# code read data