1 配置路由规则
找到网站文件目录,选择“App_Start”文件夹中的WebApiConfig.cs文件并打开。如下图所示。
打开WebApiConfig.cs文件后,按照如下代码进行修改。修改后可以在Controller中写不同的Post方法和Get方法。
public static class WebApiConfig
{
public static void Register(HttpConfiguration config)
{
// Web API 配置和服务
// Web API 路由
config.MapHttpAttributeRoutes();
// 修改前的路由配置
//config.Routes.MapHttpRoute(
// name: "DefaultApi",
// routeTemplate: "api/{controller}/{id}",
// defaults: new { id = RouteParameter.Optional }
//);
// 修改后的路由配置
config.Routes.MapHttpRoute(
name: "DefaultApi",
routeTemplate: "api/{controller}/{action}/{id}",
defaults: new { id = RouteParameter.Optional }
);
}
}
2 配置网页文档中的注释
在启动项目之后要查看接口的中文注释,需要按照如下操作进行一些配置。
右击WebAPI项目,选择属性。如下图所示。
选中“生成”,到最下方选中XML文档文件,复制文档路径。如下图所示。
到目录“Areas\HelpPage\App_Start”中,找到HelpPageConfig.cs文件并打开。如下图所示。
打开HelpPageConfig.cs文件后,找到Register方法,在此方法中找到如下代码进行修改。
config.SetDocumentationProvider(new XmlDocumentationProvider(HttpContext.Current.Server.MapPath("~/bin/PTL.BackEnd.xml")));
在最后的MapPath("")中将刚才复制的XML文件的路径添加进去。
3 配置并使用数据库
3.1 数据库库连接字段
在这个项目中,我使用了SQL Server作为数据库,因此这里只放SQL Server的配置方法。
找到项目的Web.config文件。在<configuration></configuration>
标签中添加如下代码。
<configuration>
<connectionStrings>
<add name="PTL_DBConnection" connectionString="Data Source=DESKTOP-HRE7PQN\SQLEXPRESS;Initial Catalog=PalletBarcode1;User ID=root;Password=root;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False" providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
配置完数据库连接字段后,在.cs文件中引用时使用如下代码。
string strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["PTL_DBConnection"].ConnectionString
3.2 数据库工具类
在项目目录下新建一个Tools文件夹,在文件中新建一个SqlHelper.cs文件,将如下代码复制进去。
/// <summary>
/// 无参同步执行语句,返回受影响的行数
/// </summary>
/// <param name="Sql"></param>
/// <returns></returns>
public int ExecuteNonQuery(string Sql)
{
SqlConnection sc = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PTL_DBConnection"].ConnectionString);
try
{
sc.Open();
SqlCommand sqlCommand = new SqlCommand(Sql, sc);
return sqlCommand.ExecuteNonQuery();
}
catch (Exception error)
{
throw error;
}
finally
{
sc.Close();
}
}
/// <summary>
/// 带参同步执行语句,返回受影响的行数
/// </summary>
/// <param name="Sql"></param>
/// <param name="sqlParameters"></param>
/// <returns></returns>
public int ExecuteNonQuery(string Sql, SqlParameter[] sqlParameters)
{
SqlConnection sc = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PTL_DBConnection"].ConnectionString);
try
{
sc.Open();
SqlCommand sqlCommand = new SqlCommand(Sql, sc);
sqlCommand.Parameters.AddRange(sqlParameters);
return sqlCommand.ExecuteNonQuery();
}
catch (Exception error)
{
throw error;
}
finally
{
sc.Close();
}
}
/// <summary>
/// 无参异步执行语句,返回受影响的行数
/// </summary>
/// <param name="Sql"></param>
/// <returns></returns>
public int ExecuteNonQueryAsync(string Sql)
{
SqlConnection sc = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PTL_DBConnection"].ConnectionString);
try
{
sc.Open();
SqlCommand sqlCommand = new SqlCommand(Sql, sc);
return sqlCommand.ExecuteNonQuery();
}
catch (Exception error)
{
throw error;
}
finally
{
sc.Close();
}
}
/// <summary>
/// 带参异步执行语句,返回受影响的行数
/// </summary>
/// <param name="Sql"></param>
/// <param name="sqlParameters"></param>
/// <returns></returns>
public async Task<int> ExecuteNonQueryAsync(string Sql, SqlParameter[] sqlParameters)
{
SqlConnection sc = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PTL_DBConnection"].ConnectionString);
try
{
sc.Open();
SqlCommand sqlCommand = new SqlCommand(Sql, sc);
sqlCommand.Parameters.AddRange(sqlParameters);
return await sqlCommand.ExecuteNonQueryAsync();
}
catch (Exception error)
{
throw error;
}
finally
{
sc.Close();
}
}
/// <summary>
/// 无参同步执行语句,返回DataTable
/// </summary>
/// <param name="Sql"></param>
/// <returns></returns>
public DataTable Query(string Sql)
{
SqlConnection sc = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PTL_DBConnection"].ConnectionString);
try
{
sc.Open();
SqlCommand sqlCommand = new SqlCommand(Sql, sc);
SqlDataReader sdr = sqlCommand.ExecuteReader();
DataTable result = ConvertDataReaderToDataTable(sdr);
return result;
}
catch (Exception error)
{
throw error;
}
finally
{
sc.Close();
}
}
/// <summary>
/// 带参同步执行语句,返回DataTable
/// </summary>
/// <param name="Sql"></param>
/// <param name="sqlParameters"></param>
/// <returns></returns>
public DataTable Query(string Sql, SqlParameter[] sqlParameters)
{
SqlConnection sc = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PTL_DBConnection"].ConnectionString);
try
{
sc.Open();
SqlCommand sqlCommand = new SqlCommand(Sql, sc);
sqlCommand.Parameters.AddRange(sqlParameters);
SqlDataReader sdr = sqlCommand.ExecuteReader();
DataTable result = ConvertDataReaderToDataTable(sdr);
return result;
}
catch (Exception error)
{
throw error;
}
finally
{
sc.Close();
}
}
/// <summary>
/// 无参异步执行语句,返回DataTable
/// </summary>
/// <param name="Sql"></param>
/// <returns></returns>
public async Task<DataTable> QueryAsync(string Sql)
{
SqlConnection sc = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PTL_DBConnection"].ConnectionString);
try
{
sc.Open();
SqlCommand sqlCommand = new SqlCommand(Sql, sc);
SqlDataReader sdr = await sqlCommand.ExecuteReaderAsync();
DataTable result = ConvertDataReaderToDataTable(sdr);
return result;
}
catch (Exception error)
{
throw error;
}
finally
{
sc.Close();
}
}
/// <summary>
/// 带参异步执行语句,返回DataTable
/// </summary>
/// <param name="Sql"></param>
/// <param name="sqlParameters"></param>
/// <returns></returns>
public async Task<DataTable> QueryAsync(string Sql, SqlParameter[] sqlParameters)
{
SqlConnection sc = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PTL_DBConnection"].ConnectionString);
try
{
sc.Open();
SqlCommand sqlCommand = new SqlCommand(Sql, sc);
sqlCommand.Parameters.AddRange(sqlParameters);
SqlDataReader sdr = await sqlCommand.ExecuteReaderAsync();
DataTable result = ConvertDataReaderToDataTable(sdr);
return result;
}
catch (Exception error)
{
throw error;
}
finally
{
sc.Close();
}
}
/// <summary>
/// 无参同步执行语句,返回首行首列
/// </summary>
/// <param name="Sql"></param>
/// <param name="sqlParameters"></param>
/// <returns></returns>
public object ExecuteScalar(string Sql)
{
SqlConnection sc = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PTL_DBConnection"].ConnectionString);
try
{
sc.Open();
SqlCommand sqlCommand = new SqlCommand(Sql, sc);
return sqlCommand.ExecuteScalar();
}
catch (Exception error)
{
throw error;
}
finally
{
sc.Close();
}
}
/// <summary>
/// 带参同步执行语句,返回首行首列
/// </summary>
/// <param name="Sql"></param>
/// <param name="sqlParameters"></param>
/// <returns></returns>
public object ExecuteScalar(string Sql, SqlParameter[] sqlParameters)
{
SqlConnection sc = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PTL_DBConnection"].ConnectionString);
try
{
sc.Open();
SqlCommand sqlCommand = new SqlCommand(Sql, sc);
sqlCommand.Parameters.AddRange(sqlParameters);
return sqlCommand.ExecuteScalar();
}
catch (Exception error)
{
throw error;
}
finally
{
sc.Close();
}
}
/// <summary>
/// 无参异步执行语句,返回首行首列
/// </summary>
/// <param name="Sql"></param>
/// <param name="sqlParameters"></param>
/// <returns></returns>
public object ExecuteScalarAsync(string Sql)
{
SqlConnection sc = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PTL_DBConnection"].ConnectionString);
try
{
sc.Open();
SqlCommand sqlCommand = new SqlCommand(Sql, sc);
return sqlCommand.ExecuteScalarAsync();
}
catch (Exception error)
{
throw error;
}
finally
{
sc.Close();
}
}
/// <summary>
/// 带参异步执行语句,返回首行首列
/// </summary>
/// <param name="Sql"></param>
/// <param name="sqlParameters"></param>
/// <returns></returns>
public async Task<object> ExecuteScalarAsync(string Sql, SqlParameter[] sqlParameters)
{
SqlConnection sc = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PTL_DBConnection"].ConnectionString);
sc.Open();
SqlTransaction st = sc.BeginTransaction();
try
{
SqlCommand sqlCommand = new SqlCommand(Sql, sc);
sqlCommand.Parameters.AddRange(sqlParameters);
sqlCommand.Transaction = st;
object result = await sqlCommand.ExecuteScalarAsync();
st.Commit();
return result;
}
catch (Exception error)
{
st.Rollback();
throw error;
}
finally
{
sc.Close();
}
}
/// <summary>
/// 执行存储过程,返回DataSet
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</param>
/// <param name="Times">超时时间</param>
/// <returns>DataSet</returns>
public static DataSet RunProcedure(string procName, IDataParameter[] parameters, string tableName, int Times = 30)
{
using (SqlConnection sc = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PTL_DBConnection"].ConnectionString))
{
DataSet dataSet = new DataSet();
sc.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(sc, procName, parameters);
sqlDA.SelectCommand.CommandTimeout = Times;
sqlDA.Fill(dataSet, tableName);
sc.Close();
return dataSet;
}
}
/// <summary>
/// 构建SqlCommand对象
/// </summary>
/// <param name="connection">数据库连接字符</param>
/// <param name="procName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlCommand</returns>
private static SqlCommand BuildQueryCommand(SqlConnection connection, string procName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand(procName, connection);
command.CommandType = CommandType.StoredProcedure;
if (parameters != null && parameters.Length > 0)
{
foreach (SqlParameter parameter in parameters)
{
if (parameter != null)
{
// 未赋值的参数,赋值为DBNull.Value
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input)
&& (parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
command.Parameters.Add(parameter);
}
}
}
return command;
}
protected static DataTable ConvertDataReaderToDataTable(SqlDataReader 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);
}
}
protected static DataTable DataReaderToDataTable(SqlDataReader sdr)
{
try
{
DataTable result = new DataTable();
for (int i = 0; i < sdr.FieldCount; i++)
{
DataColumn dc = new DataColumn(sdr.GetName(i), sdr.GetFieldType(i));
result.Columns.Add(dc);
}
while (sdr.Read())
{
DataRow dr = result.NewRow();
for (int i = 0; i < sdr.FieldCount; i++)
{
dr[i] = sdr[i];
}
}
return result;
}
catch (Exception ex)
{
throw new Exception("转换出错!", ex);
}
}
在项目中,需要读取数据库时,使用这个SqlHelper中的读取方法就能获取到数据库的信息。
4 请求返回Model
这里新建了一个Model来存放请求返回的数据和一些信息。
在Models文件夹中,新建一个ResultModel.cs类。内容为如下代码:
/// <summary>
/// 返回到前端的Model
/// </summary>
/// <typeparam name="T"></typeparam>
public class ResultModel<T>
{
/// <summary>
/// 请求成功
/// </summary>
public Boolean Success { get; set; }
/// <summary>
/// 请求成功的数据
/// </summary>
public T ResultData { get; set; }
/// <summary>
/// 请求成功返回的数据数量
/// </summary>
public int ResultCount { get; set; }
/// <summary>
/// 请求失败时的错误信息
/// </summary>
public string ErrorMessage { get; set; }
}
5 WebApi跨域请求
这是我在开发Vue的时候碰到的一个跨域的问题。网上搜下来发现这种方法是可用的,具体原理我暂时还不清楚。
在Web.config文件中找到<system.webServer></system.webServer>
标签下,添加如下代码。
<httpProtocol>
<customHeaders>
<add name="Access-Control-Allow-Origin" value="*"/>
<add name="Access-Control-Allow-Headers" value="*"/>
<add name="Access-Control-Allow-Methods" value="GET, POST, PUT, DELETE"/>
</customHeaders>
</httpProtocol>
按照如上方法进行配置后,WebApi项目基本能够正常使用了。