sql:PostgreSQL

PostgreSQL sql script:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

-- 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:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75

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