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