ADO.NET操作PostgreSQL：数据库操作类(未封装)

1.添加

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="newEntity"></param>
        /// <returns></returns>
        public static int Insert(Person newEntity)
        {
            string sql = @"insert into ""Person"" (""PersonId"",""IdCard"",""RealName"",""Gender"",""Address"") values (@PersonId,@IdCard,@RealName,@Gender,@Address)";
            using (NpgsqlConnection conn = new NpgsqlConnection(postgresqlconn.connectionString))
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
                {
                    #region MyRegion
                    cmd.Parameters.Add("@PersonId", NpgsqlDbType.Integer).Value = newEntity.PersonId;
                    cmd.Parameters.Add("@IdCard", NpgsqlDbType.Varchar).Value = newEntity.IdCard ?? (object)DBNull.Value;
                    cmd.Parameters.Add("@RealName", NpgsqlDbType.Varchar).Value = newEntity.RealName ?? (object)DBNull.Value;
                    cmd.Parameters.Add("@Gender", NpgsqlDbType.Bit).Value = newEntity.Gender;
                    cmd.Parameters.Add("@Address", NpgsqlDbType.Varchar).Value = newEntity.Address ?? (object)DBNull.Value;
                    #endregion

                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }

 

2.修改

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="newEntity"></param>
        /// <returns></returns>
        public static int Update(Person newEntity)
        {
            string sql = @"update ""Person"" set ""IdCard""=@IdCard,""RealName""=@RealName,""Gender""=@Gender,""Address""=@Address where ""PersonId""=@PersonId";
            using (NpgsqlConnection conn = new NpgsqlConnection(postgresqlconn.connectionString))
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
                {
                    #region MyRegion
                    cmd.Parameters.Add("@PersonId", NpgsqlDbType.Integer).Value = newEntity.PersonId;
                    cmd.Parameters.Add("@IdCard", NpgsqlDbType.Varchar).Value = newEntity.IdCard ?? (object)DBNull.Value;
                    cmd.Parameters.Add("@RealName", NpgsqlDbType.Varchar).Value = newEntity.RealName ?? (object)DBNull.Value;
                    cmd.Parameters.Add("@Gender", NpgsqlDbType.Bit).Value = newEntity.Gender;
                    cmd.Parameters.Add("@Address", NpgsqlDbType.Varchar).Value = newEntity.Address ?? (object)DBNull.Value;
                    #endregion

                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }

 

3.删除

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="personId"></param>
        /// <returns></returns>
        public static int Delete(int personId)
        {
            string sql = @"delete from ""Person"" where  ""PersonId""=@PersonId";
            using (NpgsqlConnection conn = new NpgsqlConnection(postgresqlconn.connectionString))
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
                {
                    cmd.Parameters.Add("@PersonId", NpgsqlDbType.Integer).Value = personId;
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }

 

4.读取

        /// <summary>
        /// 读取
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        private static Person GetByReader(NpgsqlDataReader reader)
        {
            Person newEntity = new Person();
            if (reader != null && !reader.IsClosed)
            {
                if (reader["PersonId"] != DBNull.Value) newEntity.PersonId = (int)reader["PersonId"];
                if (reader["IdCard"] != DBNull.Value) newEntity.IdCard = (string)reader["IdCard"];
                if (reader["RealName"] != DBNull.Value) newEntity.RealName = (string)reader["RealName"];
                if (reader["Gender"] != DBNull.Value) newEntity.Gender = (bool)reader["Gender"];
                if (reader["Address"] != DBNull.Value) newEntity.Address = (string)reader["Address"];
            }
            return newEntity;
        }

 

5.查询1行

        /// <summary>
        /// 查询1行
        /// </summary>
        /// <param name="personId"></param>
        /// <returns></returns>
        public static Person GetEntity(int personId)
        {
            Person entity = null;
            string sql = @"select * from ""Person"" where ""PersonId""=@PersonId";
            using (NpgsqlConnection conn = new NpgsqlConnection(postgresqlconn.connectionString))
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand(sql, conn))
                {
                    cmd.Parameters.Add("@PersonId", NpgsqlDbType.Integer).Value = personId;
                    conn.Open();
                    using (NpgsqlDataReader sr = cmd.ExecuteReader())
                    {
                        if (sr != null)
                        {
                            while (sr.Read())
                            {
                                entity = GetByReader(sr);
                            }
                        }
                    }
                }
            }
            return entity;
        }