csharp:SQLite and Access using C# code read data

最新推荐文章于 2024-10-09 11:23:02 发布

geovi

最新推荐文章于 2024-10-09 11:23:02 发布

阅读量1.1k

点赞数

分类专栏： SQL 文章标签： SQLite access csharp

本文链接：https://blog.csdn.net/geovi/article/details/43734383

版权

SQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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;
 
--返回添加自增ID
select 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;
}