I checked google but found nothing good. I am searching for usinf Traditional SQL
queries in MVC
instead of Entity framework etc. So it would be good if you guys provide me some examples.
我检查谷歌但没有发现任何好处。我在MVC中搜索usinf传统SQL查询而不是Entity框架等。所以如果你们给我一些例子会很好。
I started to learn MVC
but lot of examples uses linq
to SQL
and EF
etc which I don't want to use at all, I want to use simple old SQL
queries in Model layer.
我开始学习MVC但是很多例子都使用linq到SQL和EF等我根本不想使用它,我想在Model层中使用简单的旧SQL查询。
4 个解决方案
#1
11
Simplest example:
//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();
}
}
}
}
And here's my controller class
这是我的控制器类
//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());
}
}
}
My ViewModel class
我的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; }
}
}
And my 'Create' 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" />
}
#2
0
how about writing stored procedures and just invoke them as methods using by just adding a LinQ to Sql Class
into your project this will make it much easier for dealing with database
after making the needed stored procedures :
如何编写存储过程,只需将它们作为方法调用,只需在项目中添加LinQ到Sql类,这将使得在创建所需的存储过程后处理数据库变得更加容易:
- right click in your project then add new item then choose a LinQ to SQL Class and name it
右键单击项目,然后添加新项,然后选择LinQ to SQL Class并命名
- drag and drop your previously made stored procedures
拖放以前制作的存储过程
- make an instance of your
linq to sql class
将你的linq实例改为sql类
then you can invoke your stored procedures and members of this class
http://msdn.microsoft.com/en-us/library/bb386946(v=vs.110).aspx this link may help you as well
然后你可以调用你的存储过程和这个类的成员http://msdn.microsoft.com/en-us/library/bb386946(v=vs.110).aspx这个链接也可能对你有所帮助
#3
0
So just add a Helper class
and write whatever you need (Sql connections,commands,queries...), then call these helper methods from your controller.It's not different than old style
所以只需添加一个Helper类并编写你需要的任何东西(Sql连接,命令,查询...),然后从你的控制器中调用这些辅助方法。它与旧式没有区别
Static Helper Class:
静电助手类:
public static class HelperFunctions
{
private static string connString = "your connection string";
public static IEnumerable<User> GetAllUsers()
{
using (var conn = new SqlConnection(connString))
using (var cmd = new SqlCommand(connection:conn))
{
// set your command text, execute your command
// get results and return
}
}
In your Controller:
在您的控制器中:
public ActionResult Users()
{
// Get user list
IEnumerable<User> users = HelperFunctions.GetAllUsers();
// Return your view and pass it to your list
return View(users);
}
In your View set your View model:
在您的视图中设置您的View模型:
@model IEnumerable<User>
Then you can access your user list from your View, for example:
然后,您可以从View中访问您的用户列表,例如:
foreach(var user in Model)
{
<p> @user.Name </p>
}
Model
represents your actual View Model.If you want access more than one list from your View, you can use ViewBag or ViewData. Check this article for more information: http://www.codeproject.com/Articles/476967/WhatplusisplusViewData-2cplusViewBagplusandplusTem
Model表示您的实际View Model。如果您想从View中访问多个列表,可以使用ViewBag或ViewData。查看此文章以获取更多信息:http://www.codeproject.com/Articles/476967/WhatplusisplusViewData-2cplusViewBagplusandplusTem
#4
0
Using entity framework it can be done
使用实体框架可以完成
Entitiesdb db = new Entitiesdb();
string query="delete from tableA where id=@id"
db.Database.ExecuteSqlCommand(query, @id);
#1
11
Simplest example:
//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();
}
}
}
}
And here's my controller class
这是我的控制器类
//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());
}
}
}
My ViewModel class
我的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; }
}
}
And my 'Create' 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" />
}
#2
0
how about writing stored procedures and just invoke them as methods using by just adding a LinQ to Sql Class
into your project this will make it much easier for dealing with database
after making the needed stored procedures :
如何编写存储过程,只需将它们作为方法调用,只需在项目中添加LinQ到Sql类,这将使得在创建所需的存储过程后处理数据库变得更加容易:
- right click in your project then add new item then choose a LinQ to SQL Class and name it
右键单击项目,然后添加新项,然后选择LinQ to SQL Class并命名
- drag and drop your previously made stored procedures
拖放以前制作的存储过程
- make an instance of your
linq to sql class
将你的linq实例改为sql类
then you can invoke your stored procedures and members of this class
http://msdn.microsoft.com/en-us/library/bb386946(v=vs.110).aspx this link may help you as well
然后你可以调用你的存储过程和这个类的成员http://msdn.microsoft.com/en-us/library/bb386946(v=vs.110).aspx这个链接也可能对你有所帮助
#3
0
So just add a Helper class
and write whatever you need (Sql connections,commands,queries...), then call these helper methods from your controller.It's not different than old style
所以只需添加一个Helper类并编写你需要的任何东西(Sql连接,命令,查询...),然后从你的控制器中调用这些辅助方法。它与旧式没有区别
Static Helper Class:
静电助手类:
public static class HelperFunctions
{
private static string connString = "your connection string";
public static IEnumerable<User> GetAllUsers()
{
using (var conn = new SqlConnection(connString))
using (var cmd = new SqlCommand(connection:conn))
{
// set your command text, execute your command
// get results and return
}
}
In your Controller:
在您的控制器中:
public ActionResult Users()
{
// Get user list
IEnumerable<User> users = HelperFunctions.GetAllUsers();
// Return your view and pass it to your list
return View(users);
}
In your View set your View model:
在您的视图中设置您的View模型:
@model IEnumerable<User>
Then you can access your user list from your View, for example:
然后,您可以从View中访问您的用户列表,例如:
foreach(var user in Model)
{
<p> @user.Name </p>
}
Model
represents your actual View Model.If you want access more than one list from your View, you can use ViewBag or ViewData. Check this article for more information: http://www.codeproject.com/Articles/476967/WhatplusisplusViewData-2cplusViewBagplusandplusTem
Model表示您的实际View Model。如果您想从View中访问多个列表,可以使用ViewBag或ViewData。查看此文章以获取更多信息:http://www.codeproject.com/Articles/476967/WhatplusisplusViewData-2cplusViewBagplusandplusTem
#4
0
Using entity framework it can be done
使用实体框架可以完成
Entitiesdb db = new Entitiesdb();
string query="delete from tableA where id=@id"
db.Database.ExecuteSqlCommand(query, @id);