这个项目由前端页面index.html,MVC控制器HomeController.cs,MVC路由配置RouteConfig.cs,提前准备好的DBhelper.cs,以及临时的一个含有多个数据成员的类nameClass.cs。
数据库那边就自己提前写好,这里我用的是sqlserver,用其他的MySQL,Oracle啥的也差不多,控制器里面的sql语句基本是一样的。
jquery脚本我是直接添加的在线脚本压缩包,可以自行去网上下载完整的离线包也是可以的。
目录
index.html
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>我的.NET</title>
<script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script type="text/javascript">
$(function () {
//alert("欢迎使用.NET");
$("#text1").keyup(function () {
var name = this.value;
if(name=="")
{
return;
}
//无刷新ajax
$.ajax({
//url: "/home/get?name="+name, 以问号的方式去查询 适用于单个数据列的查询
url: "/home/get",//必填 遵守{controller}/{action}字段格式 这里需要访问的就是在控制器里创建的get这个json字符串生成函数
type: "post",//选填 (数据的请求方式) 还有get请求方式,但对于已经登录的用户进行其他页面访问不安全 [https://shiyousan.com/post/635428880708292949]
data: { "name": name },//选填 多参数查询时建议使用这种键值对的方式 如 data: {"name":name, "id":id}
dataType: "json",//(返回值类型)写上之后不用在遍历里面再去写类型转换
success: function (data) {//消息回调 data的名字可以任意命名
//使用each迭代器进行 循环遍历
$.each(data,function(i,row){//第i行数据,row是这行数据的内容集体
$("#u1").append("<li>" + row.id + "-" + row.name + "</li>");
})
}
})
});
});
</script>
</head>
<body>
<div>
<image src="http://image.baidu.com/search/detail?ct=503316480&z=undefined&tn=baiduimagedetail&ipn=d&word=%E5%89%91%E4%B8%89&step_word=&ie=utf-8&in=&cl=2&lm=-1&st=undefined&hd=undefined&latest=undefined©right=undefined&cs=398850736,2753287081&os=3078190967,748316120&simid=4226655618,707939447&pn=9&rn=1&di=100508769790&ln=1919&fr=&fmq=1557843727393_R&fm=&ic=undefined&s=undefined&se=&sme=&tab=0&width=undefined&height=undefined&face=undefined&is=0,0&istype=0&ist=&jit=&bdtype=0&spn=0&pi=0&gsm=0&objurl=http%3A%2F%2Fi1.hdslb.com%2Fbfs%2Farchive%2Fda980e570cbce34c6122f300a1e383ecb1c07395.jpg&rpstart=0&rpnum=0&adpicid=0&force=undefined"></image>
</div>
<div>
<input type="text" id="text1"/>
<ul id="u1"></ul>
</div>
</body>
</html>
HomeController.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using test1.Models;
using System.Data.SqlClient;
namespace test1.Controllers
{
public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
return View();
}
/// <summary>
/// 生成json字符串
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public JsonResult get(string name)//创建一个get函数用于将getName函数返回的数据转换成json格式的字符串
{
//两种获取到网页上相应控件value的方法
//Request["name"];
//RouteData.Values["name"];
JsonResult jr = new JsonResult();
//使用type:"get"时使用JsonRequestBehavior.AllowGet来允许客户端使用http get请求
//jr = Json(getName(name), JsonRequestBehavior.AllowGet);
//使用type:"post"时则直接将字符串赋值即可
jr.Data = getName(name);
return jr;
}
/// <summary>
/// 数据库访问
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
private List<nameClass> getName(string name)
{
List<nameClass> list = new List<nameClass>();//list集合用来接收查询到的数据
string sql = "select id,name from SN_DB where name like @name";//sql查询语句
SqlParameter[] pa = { new SqlParameter ("@name",$"{name}%") };//sql语句中的变量
using (SqlDataReader reader = DBhelper1.ExecuteReader(sql,pa))//查询多行多列的ExecuteReader函数
{
while (reader.Read())//Read函数即是数据库的游标
{
nameClass na = new nameClass();//可以理解这里的nameClass类是一个结构体,用来存放带有多类型数据的数据表中的数据
na.id = (int)reader["id"];
na.name = reader["name"].ToString();
list.Add(na);
}
}
return list;//将最终的查询结果保存好返回到函数
}
}
}
RouteConfig.cs
按照系统自动生成的基本没怎么修改
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Routing;
namespace test1
{
public class RouteConfig
{
public static void RegisterRoutes(RouteCollection routes)
{
routes.IgnoreRoute("{resource}.axd/{*pathInfo}");
routes.MapRoute(
name: "Default",
url: "{controller}/{action}/{id}",
defaults: new { controller = "Home", action = "Index", id = UrlParameter.Optional }
);
}
}
}
nameClass.cs
这个具体根据自己数据库里的数据列去定义
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace test1.Models
{
public class nameClass
{
public int id { get; set; }
public string name { get; set; }
}
}
DBhelper.cs
忘记是找的哪位大佬封装好的
using System;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace test1.Models
{
public class DBhelper1
{
private static string strConn = @"server = DESKTOP-LC5MIAI;uid=sa;pwd=1234;database=Database1";
//private static string strConn = ConfigurationManager.ConnectionStrings["strConn"].ConnectionString;
private static SqlConnection conn = new SqlConnection(strConn);
/// <summary>
/// 获取数据库连接对象
/// </summary>
public static SqlConnection Conn
{
get
{
return conn;
}
}
/// <summary>
/// 打开连接
/// </summary>
public static void ConnOpen()
{
if (conn.State == System.Data.ConnectionState.Closed)
conn.Open();
}
/// <summary>
/// 关闭连接
/// </summary>
public static void ConnClose()
{
if (conn.State == System.Data.ConnectionState.Open)
conn.Close();
}
/// <summary>
/// 对数据进行增删查改
/// </summary>
/// <param name="strsql">sql 语句</param>
/// <returns></returns>
public static int ExecuteNonQuery(string strsql)
{
int i = 0;
try
{
SqlCommand comm = new SqlCommand();
comm.CommandText = strsql;//command需要执行的sql 语句
comm.Connection = conn;//连接对象
ConnOpen();//打开连接
i = comm.ExecuteNonQuery();//command方法,它的返回值类型为int型。多用于执行增加,删除,修改数据。返回受影响的行数。当select操作时,返回-1
}
catch (Exception e)
{
}
finally
{
ConnClose();//执行完command之后 进行关闭
}
return i;
}
/// <summary>
/// 带参数进行增删查改
/// </summary>
/// <param name="strsql"></param>
/// <param name="param"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string strsql, SqlParameter[] param)
{
int i = 0;
try
{
SqlCommand cmd = new SqlCommand(strsql, conn);
cmd.Parameters.AddRange(param);
ConnOpen();
i = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
}
finally
{
ConnClose();
}
return i;
}
//带参数的查询
public static SqlDataReader ExecuteReader(string strSql, params SqlParameter[] param)
{
SqlConnection connn = new SqlConnection(strConn);
connn.Open();
SqlCommand comm = new SqlCommand(strSql, connn);
comm.Parameters.AddRange(param);
return comm.ExecuteReader(CommandBehavior.CloseConnection);
}
/// <summary>
/// 查询是否有该数据
/// </summary>
/// <param name="strSql">数据操作的Sql语句</param>
/// <returns></returns>
public static bool Exists(string strSql, SqlParameter[] param)
{
bool bReturn = false;
try
{
SqlCommand comm = new SqlCommand(strSql, conn);
comm.Parameters.AddRange(param);
ConnOpen();
bReturn = (int)comm.ExecuteScalar() > 0;
}
catch (Exception e)
{
}
finally
{
ConnClose();
}
return bReturn;
}
/// <summary>
/// 查询是否有该数据
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public static int ExecuteScalar(string sql, params SqlParameter[] param)
{
int i = 0;
SqlCommand comm = new SqlCommand(sql, conn);
comm.Parameters.AddRange(param);
try
{
ConnOpen();
i = Convert.ToInt32(comm.ExecuteScalar().ToString());
}
catch (Exception)
{
}
finally
{
comm.Dispose();
}
return i;
}
public static DataSet GetAll(string sql)
{
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
public static DataSet GetAll(string sql, SqlParameter[] param)
{
SqlCommand comm = new SqlCommand(sql, conn);
comm.Parameters.AddRange(param);
SqlDataAdapter da = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
/// <summary>
/// dataset 存储过程进行数据修改
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public static DataSet ExcuteDatasetProc(string sql, params SqlParameter[] param)
{
SqlCommand comm = new SqlCommand(sql, conn);
comm.Parameters.AddRange(param);
comm.CommandType = System.Data.CommandType.StoredProcedure;
ConnOpen();
using (SqlDataAdapter da = new SqlDataAdapter(comm))
{
DataSet ds = new DataSet();
da.Fill(ds);
comm.Parameters.Clear();
ConnClose();
return ds;
}
}
/// <summary>
/// 操作存储过程
/// </summary>
/// <param name="strSql">数据操作的Sql语句</param>
/// <returns></returns>
public static int ExecuteProcedure(string strSql, SqlParameter[] param)
{
int i = 0;
try
{
SqlCommand comm = new SqlCommand(strSql, conn);
comm.Parameters.AddRange(param);
comm.CommandType = System.Data.CommandType.StoredProcedure;
ConnOpen();
i = comm.ExecuteNonQuery();
}
catch (Exception e)
{
}
finally
{
ConnClose();
}
return i;
}
/// <summary>
/// 执行存储过程查询数据
/// </summary>
/// <param name="strSql">存储过程名称</param>
/// <param name="param">存储过程参数</param>
/// <returns></returns>
public static SqlDataReader ExecuteDataReaderProcedure(string strSql, SqlParameter[] param)
{
SqlDataReader dr = null;
try
{
SqlCommand comm = new SqlCommand(strSql, conn);
comm.Parameters.AddRange(param);
comm.CommandType = System.Data.CommandType.StoredProcedure;
ConnOpen();
dr = comm.ExecuteReader();
}
catch (Exception e)
{
}
finally
{
//在阅读器没有读取数据前不能关闭数据库连接,否则将读取不到数据
}
return dr;
}
}
}