PostgreSQL sql script:
-- Database: geovindu
-- DROP DATABASE geovindu;
CREATE DATABASE geovindu
WITH OWNER = postgres
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'Chinese_People''s Republic of China.936'
LC_CTYPE = 'Chinese_People''s Republic of China.936'
CONNECTION LIMIT = -1;
--创建表
create table BookKindList
(
BookKindID SERIAL PRIMARY KEY,
BookKindName varchar(500) not null,
BookKindCode varchar(100) null,
BookKindParent int null
);
--查询
select * from BookKindList;
--添加
insert into BookKindList(BookKindName,BookKindParent) values('六福书目录',0);
insert into BookKindList(BookKindName,BookKindParent) values('文学',1);
insert into BookKindList(BookKindName,BookKindParent) values('设计艺术',1);
insert into BookKindList(BookKindName,BookKindParent) values('自然科学',1);
insert into BookKindList(BookKindName,BookKindParent) values('小说',2);
insert into BookKindList(BookKindName,BookKindParent) values('诗词散曲',2);
select * from BookKindList where BookKindID=1;
--返回增加的ID
select lastval();
csharp:
/// <summary>
/// 涂聚文 20150212
/// PostgreSql添加返回值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 NpgsqlParameter[] cmdParms)
{
string en = "";
using (NpgsqlConnection connection = new NpgsqlConnection(connectionString))
{
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
int rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.CommandText = "select lastval();";
en = cmd.ExecuteScalar().ToString();
identity = int.Parse(en);
cmd.Parameters.Clear();
return rows;
}
catch (NpgsqlException E)
{
throw new Exception(E.Message);
}
}
}
}
/// <summary>
/// 追回返回值
/// PostgreSql涂聚文
/// </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);");
NpgsqlParameter[] par = new NpgsqlParameter[]{
new NpgsqlParameter("@BookKindName",DbType.String,1000),
new NpgsqlParameter("@BookKindCode",DbType.String,1000),
new NpgsqlParameter("@BookKindParent",DbType.Int32,4),
//new NpgsqlParameter("@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 = PostgreSqlHelper.ExecuteSql(str.ToString(), out tid, par);
if (ret > 0)
{
//tid = ret;// (int)par[3].Value;
}
}
catch (NpgsqlException ex)
{
throw ex;
}
id = tid;
return ret;
}