csharp:SQLite and Access using C# code read data

来源：互联网发布：sql decode 编辑：程序博客网时间：2024/05/21 06:52

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

0 0