sql:PostgreSQL

-- 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();

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