提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
文章目录
4.编写 Controllers 内容 同上,简单的进行测试
3.接着我们在UserController中 添加增删改查接口
前言
这里简单记录一下 WebAPI 项目搭建,发布,调用以及实现 增删改查
一、WebAPI 大致部署方式分为3种
1.IIS
2.控制台(SelfHost)
3.Windows 服务(SelfHost )
二、开始搭建
1.IIS 承载WebAPI 项目搭建
1.创建ASP.NET WebAPI 项目:
2.添加NuGet 包引用,跨域配置 什么是跨域?
https://www.jianshu.com/p/8fa2acd103ea
nuget 包搜索 Microsoft.aspnet.webapi.cors
添加跨域配置
App_Start --> WebApiConfig.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.Http;
using System.Web.Http.Cors;
namespace WebApiHost
{
public static class WebApiConfig
{
public static void Register(HttpConfiguration config)
{
// Web API 配置和服务
config.EnableCors(new EnableCorsAttribute("*", "*", "*"));//跨域配置
// Web API 路由
config.MapHttpAttributeRoutes();
config.Routes.MapHttpRoute(
name: "DefaultApi",
routeTemplate: "api/{controller}/{action}",
defaults: new { id = RouteParameter.Optional }
);
}
}
}
3.WebAPI接口开发
webapi 常用类型有 post get 等类型,下面我们简单的写一个Post LoginCheck 接口验证用户名密码。
Controllers目录下,新增一个控制器 UserController.cs
内容如下
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using WebApiHost.BusinessObjects.Requests;
using WebApiHost.BusinessObjects.Responses;
namespace WebApiHost.Controllers
{
public class UserController : ApiController
{
[HttpPost]
public LoginCheckResponse LoginCheck([FromBody] LoginCheckRequest request)
{
LoginCheckResponse response = new LoginCheckResponse();
try
{
if(request !=null)
{
if(request.UserAccount=="Admin" && request.Password=="abcF123")
{
response.IsSuccess = true;
response.ResultMessage = "登录成功.";
}
else
{
response.IsSuccess = false;
response.ResultMessage = "账号或密码错误!";
}
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.ResultMessage = $"LoginCheck Failed . Detail: {ex.Message}" ;
}
return response;
}
}
}
以上接口完成,使用IIS Express 本地运行
使用Postman 接口测试工具 调用接口测试
返回结果 接口调用成功.
2.SelfHost 承载WebAPI 控制台项目搭建
1.创建控制台项目
2.新建SelfHost 类库
3.添加 nuget 包 引用
搜索 Microsoft.AspNet.WebApi.SelfHost 安装
4.编写 Controllers 内容 同上,简单的进行测试
using Bct.WebAPI.SelfHost.Common.Helper;
using Bct.WebAPI.SelfHost.Controllers.Requests;
using Bct.WebAPI.SelfHost.Controllers.Responses;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web.Http;
namespace Bct.WebAPI.SelfHost.Controllers
{
public class UserController : ApiController
{
[HttpPost]
public LoginCheckResponse LoginCheck([FromBody] LoginCheckRequest request)
{
LoginCheckResponse response = new LoginCheckResponse();
try
{
if (request != null)
{
if (request.UserAccount == "Admin" && request.Password == "abcF123")
{
response.IsSuccess = true;
response.ResultMessage = "登录成功.";
ConsoleHelper.PrintMessage(MsgLevel.Information, response.ResultMessage);
}
else
{
response.IsSuccess = false;
response.ResultMessage = "账号或密码错误!";
ConsoleHelper.PrintMessage(MsgLevel.Error, response.ResultMessage);
}
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.ResultMessage = $"LoginCheck Failed . Detail: {ex.Message}";
ConsoleHelper.PrintMessage(MsgLevel.Error, response.ResultMessage);
}
return response;
}
}
}
5.宿主程序控制台 启动服务编码 代码如下
using Bct.WebAPI.SelfHost.Common.Helper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Web.Http;
using System.Web.Http.SelfHost;
namespace WebAPI.SelfHostConsole
{
internal class Program
{
static void Main(string[] args)
{
//加载外部程序集
Assembly.Load("Bct.WebAPI.SelfHost");
var config = new HttpSelfHostConfiguration("http://localhost:8083");
config.Routes.MapHttpRoute(
"API Default", "api/{controller}/{action}",
new { id = RouteParameter.Optional });
using (var server = new HttpSelfHostServer(config))
{
server.OpenAsync().Wait();
ConsoleHelper.PrintMessage(MsgLevel.Information, $"Listening to: {config.BaseAddress.ToString()}.");
ConsoleHelper.PrintMessage(MsgLevel.Information, "Press Enter to quit.");
Console.ReadLine();
}
}
}
}
使用Postman 测试结果 成功.
3.接口中简单的增删改查
1.首先app.config 中添加数据库连接字符串
<connectionStrings>
<add name="ServerConnectionString" providerName="System.Data.SqlClient" connectionString="data source=127.0.0.1;user id=sa;password=xxxxxx;Initial Catalog=WebAPI_DEV;Integrated Security=false" />
</connectionStrings>
添加 System.Configuration 引用,读取连接字符串
2.新建一个帮助类实现数据库增删改查操作 ,代码如下
using Bct.WebAPI.SelfHost.Common.Database;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
namespace Bct.WebAPI.SelfHost.Common.DataBase
{
public sealed class GenerateSQL
{
private const string AppSettingDatabaseConnectionTimeout = "DatabaseConnectionTimeout";
/// <summary>
/// 查询
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataTable Get(string sql)
{
DataTable dataTable = new DataTable();
using (var dbContext = GenerateSQL.GetConnection())
{
using (DbCommand dbCommand = GenerateSQL.CreateCommand(string.Empty, CommandType.Text, dbContext))
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(sql);
dbCommand.CommandText = stringBuilder.ToString();
using (DataTableReader dataTableReader = GenerateSQL.ExecuteReader(dbCommand))
{
if (dataTableReader != null && dataTableReader.HasRows)
{
dataTable = ConvertDataReaderToDataTable(dataTableReader);
}
}
}
}
return dataTable;
}
/// <summary>
/// 增删改
/// </summary>
/// <param name="sql"></param>
/// <returns>影响行数</returns>
public static int NonQuery(string sql)
{
int result = 0;
using (var dbContext = GenerateSQL.GetConnection())
{
using (DbCommand dbCommand = GenerateSQL.CreateCommand(string.Empty, CommandType.Text, dbContext))
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(sql);
dbCommand.CommandText = stringBuilder.ToString();
result = ExecuteNonQuery(dbCommand);
}
}
return result;
}
public GenerateSQL()
{
}
private static DbConnection GetConnection()
{
string strConnection = "";
try
{
strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["ServerConnectionString"].ConnectionString;
if (string.IsNullOrEmpty(strConnection))
throw new Exception("ConnectionString is Null or Empty!");
else
return new SqlConnection(strConnection);
}
catch (Exception e)
{
throw new Exception("Error in GetConnectionString!!! Source:" + e.Source.ToString() + " Message:" + e.Message.ToString());
}
}
/// <summary>
/// Create a DbCommand
/// </summary>
/// <param name="commandText">The SQL or store procedure name</param>
/// <param name="commandType">Command type</param>
/// <param name="dbContext">The database connection</param>
/// <returns>Returns the command created</returns>
public static DbCommand CreateCommand(string commandText, CommandType commandType, DbConnection dbContext)
{
DbCommand dbCommand = dbContext.CreateCommand();
dbCommand.CommandText = commandText;
dbCommand.CommandType = commandType;
dbCommand.CommandTimeout = 600;
return dbCommand;
}
/// <summary>
/// Executes a SQL statement
/// </summary>
/// <param name="command">The command to execute</param>
/// <returns>Returns the number of rows affected</returns>
public static int ExecuteNonQuery(DbCommand command)
{
if (command.Connection.State == ConnectionState.Closed)
command.Connection.Open();
int num = command.ExecuteNonQuery();
if (command.Connection.State == ConnectionState.Open)
command.Connection.Close();
return num;
}
/// <summary>
/// Execute the command
/// </summary>
/// <param name="command">The command to be executed</param>
/// <returns>Returns a data reader</returns>
public static DataTableReader ExecuteReader(DbCommand command)
{
return ExecuteReader(command, CommandBehavior.Default);
}
/// <summary>
/// Executes the System.Data.Common.DbCommand.CommandText against the System.Data.Common.DbCommand.Connection,
/// and returns a DataTableReader using one of the System.Data.CommandBehavior values.
/// </summary>
/// <param name="command">The command to be executed.</param>
/// <param name="behavior"> One of the System.Data.CommandBehavior values.</param>
/// <returns>A DataTableReader object.</returns>
public static DataTableReader ExecuteReader(DbCommand command, CommandBehavior behavior)
{
bool flag = false;
if (command.Connection.State == ConnectionState.Closed)
{
flag = true;
command.Connection.Open();
}
DbDataReader dbDataReaders = command.ExecuteReader(behavior);
var dataTables = new List<DataTable>();
var dataTable = new DataTable();
dataTable.Load(dbDataReaders, LoadOption.OverwriteChanges);
dataTables.Add(dataTable);
while (!dbDataReaders.IsClosed)
{
dataTable = new DataTable();
dataTable.Load(dbDataReaders);
dataTables.Add(dataTable);
}
if (flag && command.Connection.State == ConnectionState.Open)
command.Connection.Close();
return new DataTableReader(dataTables.ToArray());
}
/// <summary>
/// Executes a SQL statement
/// </summary>
/// <param name="command">The command to execute</param>
/// <returns>Returns the number of rows affected</returns>
public static object ExecuteScalar(DbCommand command)
{
if (command.Connection.State == ConnectionState.Closed)
command.Connection.Open();
object obj = command.ExecuteScalar();
if (command.Connection.State == ConnectionState.Open)
command.Connection.Close();
return obj;
}
/// <summary>
/// Inserts the or update parameter.
/// </summary>
/// <param name="command">The command.</param>
/// <param name="parameterName">Name of the parameter.</param>
/// <param name="value">The value.</param>
/// <param name="direction">The direction.</param>
/// <param name="sqlType">Type of the SQL.</param>
/// <param name="size">The size.</param>
internal static void InsertOrUpdateParameterWithPerformancePenalty(DbCommand command, string parameterName, object value, ParameterDirection direction, SqlDbType? sqlType, int? size)
{
if (value == null)
{
value = DBNull.Value;
}
else if (value is string && string.IsNullOrWhiteSpace(value.ToString()))
{
value = DBNull.Value;
}
if (command.Parameters.Contains(parameterName))
{
command.Parameters[parameterName].Value = value;
return;
}
SqlParameter sqlParameter = null;
if (!sqlType.HasValue || !size.HasValue)
{
sqlParameter = new SqlParameter(parameterName, value);
}
else
{
sqlParameter = new SqlParameter(parameterName, sqlType.Value, size.Value);
sqlParameter.Value = value;
}
sqlParameter.Direction = direction;
command.Parameters.Add(sqlParameter);
}
/// <summary>
/// Inserts the paramater.
/// </summary>
/// <param name="command">The command.</param>
/// <param name="parameterName">Name of the parameter.</param>
/// <param name="value">The value.</param>
/// <param name="direction">The direction.</param>
/// <param name="type">The type.</param>
/// <returns>Created SQL Parameter</returns>
public static SqlParameter InsertParameter(DbCommand command, string parameterName, DataTable value, ParameterDirection direction, TableType type)
{
int? nullable = null;
SqlParameter parameter = InsertParameter(command, parameterName, value, direction, new SqlDbType?(SqlDbType.Structured), nullable);
return parameter;
}
/// <summary>
/// Inserts the parameter.
/// </summary>
/// <param name="command">The command.</param>
/// <param name="parameterName">Name of the parameter.</param>
/// <param name="value">The value.</param>
/// <param name="direction">The direction.</param>
/// <param name="sqlType">Type of the SQL.</param>
/// <param name="size">The size.</param>
/// <returns>Created SQL Parameter</returns>
public static SqlParameter InsertParameter(DbCommand command, string parameterName, object value, ParameterDirection direction, SqlDbType? sqlType, int? size=null)
{
if (value == null)
value = DBNull.Value;
else if (value is string && string.IsNullOrWhiteSpace(value.ToString()))
value = DBNull.Value;
SqlParameter sqlParameter = null;
if (!sqlType.HasValue || !size.HasValue)
{
sqlParameter = new SqlParameter(parameterName, value);
}
else
{
sqlParameter = new SqlParameter(parameterName, sqlType.Value, size.Value);
sqlParameter.Value = value;
}
sqlParameter.Direction = direction;
command.Parameters.Add(sqlParameter);
return sqlParameter;
}
/// <summary>
/// Creates a SqlParameter and adds it to the command
/// </summary>
/// <param name="command">The sql command</param>
/// <param name="parameterName">Parameter name</param>
/// <param name="value">Parameter value</param>
/// <param name="direction">Parameter direction (input, output, etc)</param>
/// <returns>Created SQL Parameter</returns>
public static SqlParameter InsertParameter(DbCommand command, string parameterName, object value, ParameterDirection direction= ParameterDirection.Input)
{
SqlDbType? nullableSqlDbType = null;
int? nullableSize = null;
return InsertParameter(command, parameterName, value, direction, nullableSqlDbType, nullableSize);
}
public static DataTable ConvertDataReaderToDataTable(DataTableReader reader)
{
try
{
DataTable objDataTable = new DataTable();
int intFieldCount = reader.FieldCount;
for (int intCounter = 0; intCounter < intFieldCount; ++intCounter)
{
objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter));
}
objDataTable.BeginLoadData();
object[] objValues = new object[intFieldCount];
while (reader.Read())
{
reader.GetValues(objValues);
objDataTable.LoadDataRow(objValues, true);
}
reader.Close();
objDataTable.EndLoadData();
return objDataTable;
}
catch (Exception ex)
{
throw new Exception("转换出错!", ex);
}
}
}
}
3.接着我们在UserController中 添加增删改查接口
using Bct.WebAPI.SelfHost.Common.DataBase;
using Bct.WebAPI.SelfHost.Common.Extensions;
using Bct.WebAPI.SelfHost.Common.Helper;
using Bct.WebAPI.SelfHost.Common.Models;
using Bct.WebAPI.SelfHost.Controllers.Requests;
using Bct.WebAPI.SelfHost.Controllers.Responses;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web.Http;
namespace Bct.WebAPI.SelfHost.Controllers
{
public class UserController : ApiController
{
[HttpPost]
public LoginCheckResponse LoginCheck([FromBody] LoginCheckRequest request)
{
LoginCheckResponse response = new LoginCheckResponse();
try
{
if (request != null)
{
if (string.IsNullOrWhiteSpace(request.UserAccount) && string.IsNullOrWhiteSpace(request.Password))
{
var user = GenerateSQL.Get(SQLStatements.ST_LoginCheck(request.UserAccount,request.Password)).TableToModel<User>();
if (user!=null)
{
response.User = user;
response.IsSuccess = true;
response.ResultMessage = $"[{response.User.Name}]登录成功.";
ConsoleHelper.PrintMessage(MsgLevel.Information, response.ResultMessage);
}
else
{
response.IsSuccess = false;
response.ResultMessage = "账号或密码错误!";
ConsoleHelper.PrintMessage(MsgLevel.Error, response.ResultMessage);
}
}
else
{
response.IsSuccess = false;
response.ResultMessage = "账号或密码不可为空!";
ConsoleHelper.PrintMessage(MsgLevel.Error, response.ResultMessage);
}
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.ResultMessage = $"LoginCheck Failed . Detail: {ex.Message}";
ConsoleHelper.PrintMessage(MsgLevel.Error, response.ResultMessage);
}
return response;
}
[HttpPost]
public CreateUserResponse CreateUser([FromBody] CreateUserRequest request)
{
CreateUserResponse response = new CreateUserResponse();
try
{
if (request != null && request.User != null)
{
var validation = request.User.DataValidation(Common.Enum.ValidationType.Insert);
if (string.IsNullOrEmpty(validation))
{
var count = GenerateSQL.NonQuery(SQLStatements.ST_CreateUser(request.User));
if (count>0)
{
response.IsSuccess = true;
response.ResultMessage = $"用户[{request.User.Name}]\r\n账户[{request.User.UserAccount}] \r\n创建成功.";
ConsoleHelper.PrintMessage(MsgLevel.Information, response.ResultMessage);
}
}
else
{
response.IsSuccess = false;
response.ResultMessage = $"CreateUser Failed . Detail:\r\n {validation}";
ConsoleHelper.PrintMessage(MsgLevel.Error, response.ResultMessage);
}
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.ResultMessage = $"CreateUser Failed . Detail: {ex.Message}";
ConsoleHelper.PrintMessage(MsgLevel.Error, response.ResultMessage);
}
return response;
}
[HttpPost]
public DeleteUserResponse DeleteUser([FromBody] DeleteUserRequest request)
{
DeleteUserResponse response = new DeleteUserResponse();
try
{
if (request != null)
{
if(request.UserId<=0)
{
throw new Exception("[UserId] 不能为空!");
}
var count = GenerateSQL.NonQuery(SQLStatements.ST_DeleteUser(request.UserId));
if (count > 0)
{
response.IsSuccess = true;
response.ResultMessage = $"用户删除成功.";
ConsoleHelper.PrintMessage(MsgLevel.Information, response.ResultMessage);
}
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.ResultMessage = $"DeleteUser Failed . Detail: {ex.Message}";
ConsoleHelper.PrintMessage(MsgLevel.Error, response.ResultMessage);
}
return response;
}
[HttpPost]
public UpdateUserResponse UpdateUser([FromBody] UpdateUserRequest request)
{
UpdateUserResponse response = new UpdateUserResponse();
try
{
if (request != null && request.User != null)
{
var validation = request.User.DataValidation(Common.Enum.ValidationType.Update);
if (string.IsNullOrEmpty(validation))
{
var count = GenerateSQL.NonQuery(SQLStatements.ST_UpdateUser(request.User));
if (count > 0)
{
response.IsSuccess = true;
response.ResultMessage = $"用户[{request.User.Name}]\r\n账户[{request.User.UserAccount}] \r\n修改成功.";
ConsoleHelper.PrintMessage(MsgLevel.Information, response.ResultMessage);
}
}
else
{
response.IsSuccess = false;
response.ResultMessage = $"UpdateUser Failed . Detail:\r\n {validation}";
ConsoleHelper.PrintMessage(MsgLevel.Error, response.ResultMessage);
}
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.ResultMessage = $"UpdateUser Failed . Detail: {ex.Message}";
ConsoleHelper.PrintMessage(MsgLevel.Error, response.ResultMessage);
}
return response;
}
[HttpPost]
public GetUsersResponse GetUsers([FromBody] GetUsersRequest request)
{
GetUsersResponse response = new GetUsersResponse();
try
{
var users = GenerateSQL.Get(SQLStatements.ST_GetUsersByFilter(request.Name, request.MailAddress,request.CreatedStartTime,request.CreatedEndTime)).TableToList<User>();
if (users != null && users.Count>0)
{
response.Users = users;
}
ConsoleHelper.PrintMessage(MsgLevel.Information, $"获取 Users 成功,返回行数[{users.Count}] !");
}
catch (Exception ex)
{
ConsoleHelper.PrintMessage(MsgLevel.Error, $"GetUsers Failed . Detail: {ex.Message}");
}
return response;
}
}
}
三、c# 调用WebAPI
1.使用httpClient 进行调用 ,调用方式十分简单 就不进行过多的叙述了
using (var client = new HttpClient())
{
var request = new TestRequest();
request.User = new User
{
Name = "Sean",
UserAccount = "Sean94116",
Password = "123qqq",
MailAddress = "sean@bct-best3.com"
};
string postData = JsonConvert.SerializeObject(request);
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
var url = "http://localhost:8083/api/User/CreateUser";
var content = new StringContent(postData, Encoding.UTF8);
content.Headers.ContentType = new MediaTypeHeaderValue("application/json");
var tmpResult = client.PostAsync(url, content).Result;
tmpResult.EnsureSuccessStatusCode();
var result = tmpResult.Content.ReadAsStringAsync().Result;
Console.Write(result);
Console.ReadKey();
return;
}
总结
以上就是今天要讲的内容,本文仅仅简单介绍了WebAPI 的三种搭建方式,以及接口中对数据库进行增删改查,其中二、三种搭建方式为同一种方式,
宿主程序不同,宿主程序可以是 控制台 Windos服务 ,WPF 客户端
还有一种 OwinSelfHost 这里就不过多的介绍,可以自行了解.