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; --返回添加自增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; }
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。