项目地址
github: https://github.com/zichenlbl/Learn-Dot-Net-Core 的文件夹《Clone连接PostgreSQL》->《Demo - try语句中使用》
gitee:https://gitee.com/hujinya_admin/learn-dot-net-core 的文件夹《Clone连接PostgreSQL》->《Demo - try语句中使用》
新建项目
环境:Win10,Vs2017,PostgreSQL10
项目:ASP.NET Core Web应用程序 -> Web应用程序(模型视图控制器) -> 取消勾选为HTTP配置 -> End.
安装NeGet包
<PackageReference Include="Chloe.PostgreSQL" Version="5.1.1" />
<PackageReference Include="Npgsql" Version="6.0.5" />
创建DbContext
Chloe框架文档:https://github.com/shuxinqin/Chloe/wiki/PostgreSQL-DbContext
public class PostgreSQLConnectionFactory : IDbConnectionFactory
{
string _connString = null;
public PostgreSQLConnectionFactory(string connString)
{
this._connString = connString;
}
public IDbConnection CreateConnection()
{
NpgsqlConnection conn = new NpgsqlConnection(this._connString);
return conn;
}
}
创建连接字符串DBConnectionString
本地的postgreSQL数据库,默认的postgres用户,密码以及我的数据库名称mydb
public class DBConnectionString
{
/// <summary>
/// PostgreSQL数据库连接字符串
/// </summary>
public static string POSTGRE_SQL = "Host=127.0.0.1;Username=postgres;Password=123456;Database=mydb";
}
创建实体用户类User
public class User
{
public int id { get; set; }
public string name { get; set; }
public int age { get; set; }
}
创建返回结果类Result
/// <summary>
/// 前台Ajax请求的统一返回结果类
/// </summary>
public class Result
{
private Result()
{
}
private ErrCode mErrCode = ErrCode.Success;
/// <summary>
/// 返回编码
/// </summary>
public ErrCode S_ErrCode { get { return mErrCode; } set { mErrCode = value; } }
/// <summary>
/// 错误信息,或者成功信息
/// </summary>
public string Message { get; set; }
/// <summary>
/// 成功可能时返回的数据
/// </summary>
public object Data { get; set; }
#region Error
public static Result Error()
{
return new Result()
{
S_ErrCode = ErrCode.Err
};
}
public static Result Error(string message)
{
return new Result()
{
S_ErrCode = ErrCode.Err,
Message = message
};
}
public static Result Error(string message, ErrCode pErrCode)
{
return new Result()
{
S_ErrCode = pErrCode,
Message = message
};
}
#endregion
#region Success
public static Result Success()
{
return new Result()
{
S_ErrCode = ErrCode.Success
};
}
public static Result Success(string message)
{
return new Result()
{
S_ErrCode = ErrCode.Success,
Message = message
};
}
public static Result Success(object data)
{
return new Result()
{
S_ErrCode = ErrCode.Success,
Data = data
};
}
public static Result Success(object data, string message)
{
return new Result()
{
S_ErrCode = ErrCode.Success,
Data = data,
Message = message
};
}
#endregion
/// <summary>
/// 错误类型
/// </summary>
public enum ErrCode
{
/// <summary>
/// 正常
/// </summary>
Success = 0,
/// <summary>
/// 服务器内部错误
/// </summary>
ServerErr = -100,
}
}
创建业务接口IUserService
public interface IUserService
{
/// <summary>
/// 查询所有的用户
/// </summary>
/// <returns></returns>
Result GetAllUser();
}
实现业务接口UserServiceImpl
public class UserServiceImpl : IUserService
{
public UserServiceImpl()
{
}
/// <summary>
/// 查询所有的用户
/// </summary>
/// <returns></returns>
public Result GetAllUser()
{
//获取PostgreSQLContext实例,表示与数据库的会话,可用于查询和保存实体。
PostgreSQLContext context = new Chloe.PostgreSQL.PostgreSQLContext(new PostgreSQLConnectionFactory(DBConnectionString.POSTGRE_SQL));
try
{//查询用户表
IQuery<User> m_q_user = context.Query<User>();
var mGetUser = m_q_user.OrderBy(a => a.id).ToList();
return Result.Success(mGetUser, "查询成功"); //返回用户信息
}
catch (Exception ex)
{
return Result.Error(ex.Message, Result.ErrCode.ServerErr);
}
finally
{
context.Dispose();
}
}
}
创建控制器UserController
创建UserController控制器,修改Get()方法的返回类型为返回结果类Result,调用业务接口GetAllUser();方法获取所有用户的信息。
// GET: api/User
[HttpGet]
public Result Get()
{
IUserService userService = new UserServiceImpl();
return userService.GetAllUser();
}
查询结果
接口查询结果
使用PgAdmin查看数据库中的结果
新增数据,再进行查询