c# - 在 ASP.NET MVC 中使用简单查询

Question

我查了谷歌，但没找到什么好东西。我正在搜索 MVC 中的 usinf 传统 SQL 查询，而不是 Entity 框架等。所以如果你们能给我一些例子就更好了。

我开始学习 MVC 但很多示例使用 linq 到 SQL 和 EF 等我不根本不想使用，我想在模型层中使用简单的旧式 SQL 查询。

Answer 1

最简单的例子:

//Domain Class
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace BanjoOnMyKnee.Models
{
    public class DomainModel
    {
        public string connectionString = ".\\SQLEXPRESS; Initial-Catalog=YourDBName; Integrated-Security=true";
        public void CreateSomething(ViewModel model)
        {
            using(SqlConnection connection = new SqlConnection(connectionString))
            using (SqlCommand command = new SqlCommand("",connection))
            {
                command.CommandText = "insert into Names values(@Name)";
                command.Parameters.AddWithValue("@Name", model.Name);
                command.ExecuteNonQuery();
            }
        }

        public ViewModel FindSomething(int id)
        {
            var model = new ViewModel();
            using (SqlConnection connection = new SqlConnection(connectionString))
            using (SqlCommand command = new SqlCommand("", connection))
            {
                command.CommandText = "select * from Names where Id=@Id";
                command.Parameters.AddWithValue("@Id",id);
                SqlDataReader reader = command.ExecuteReader();
                model.Id = id;
                model.Name = reader["Name"].ToString();
            }
            return model;
        }

        public void DeleteSomething(ViewModel model)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            using (SqlCommand command = new SqlCommand("", connection))
            {
                command.CommandText = "delete from Names where Id=@Id";
                command.Parameters.AddWithValue("@Id", model.Id);
                command.ExecuteNonQuery();
            }
        }

        public void EditSomething(ViewModel model)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            using (SqlCommand command = new SqlCommand("", connection))
            {
                command.CommandText = "Update Names set Name=@Name where Id=@Id";
                command.Parameters.AddWithValue("@Name", model.Name);
                command.Parameters.AddWithValue("@Id", model.Id);
                command.ExecuteNonQuery();
            }
        }
    }
}

这是我的 Controller 类

//My Controller class
public class HomeController : Controller
{
    //
    // GET: /Home/

    public ActionResult Index()
    {
        return View();
    }

    //
    // GET: /Home/Create

    public ActionResult Create()
    {
        return View(new ViewModel());
    }

    //
    // POST: /Home/Create

    [HttpPost]
    public ActionResult Create(ViewModel vm)
    {
        try
        {
            var domainModel = new DomainModel();
            domainModel.CreateSomething(vm);
            return RedirectToAction("Index");
        }
        catch
        {
            return View(new ViewModel());
        }
    }

    //
    // GET: /Home/Edit/5

    public ActionResult Edit(int id)
    {
        ViewModel model = new DomainModel().FindSomething(id);
        return View(model);
    }


    [HttpPost]
    public ActionResult Edit(ViewModel editModel)
    {
        try
        {
            var dm = new DomainModel();
            dm.EditSomething(editModel);
            return RedirectToAction("Index");
        }
        catch
        {
            return View(new ViewModel());
        }
    }
 }

我的 ViewModel 类

//My ViewModel
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace BanjoOnMyKnee.Models
{
    public class ViewModel
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
}

还有我的“创建” View

//My view
@model BanjoOnMyKnee.Models.ViewModel

@{
    ViewBag.Title = "Create";
}

<h2>Create</h2>

@using(Html.BeginForm()){
    @Html.HiddenFor(m => m.Id);
    <p> Name : 
        Html.EditorFor(m=>m.Name);</p>
    <input type="submit" value="Create" />
}