插入数据 exec .config mod ted web mman update
首先,创建一个 MVC 项目,
然后,创建一个 Person 类来获取数据库的表 Persons 的 "单一个体" 数据
- public class Person { [Key] public string Id {
- get;
- set;
- }
- public string Name {
- get;
- set;
- }
- public string Sex {
- get;
- set;
- }
- public int Age {
- get;
- set;
- }
- }
再则,创建一个 Interface1 接口,为了实现 CURD 的操作
- public interface Interface1<T> where T:class,new()
- {
- IEnumerable<T> Get();
- void Create(T Item);
- void Update(T Item);
- void Delete(T Item);
- }
所以,我们就可以创建一个 Northwind 类,使用接口 Interface1 来获取 CURD 数据库 的多个 Person 的数据(类使于使用 Model 的 {get;Set;},我们这里只是用到 get)
- public class Northwind
- {
- private Interface1<Person> _personOperation = null;
- public Interface1<Person> Persons
- {
- get
- {
- if (this._personOperation == null)
- {
- this._personOperation = new Method();
- }
- return this._personOperation;
- }
- }
- }
当然,有了这个接口的话,我们就可以对数据库有一个直接的统一调用操作(当然,接口必须去实现它),所以 需要去实现它的 CURD 操作,
然后,创建一个继承这个接口并去实现它的方法类 Method
引用
- using System.Data;
- using System.Data.SqlClient;
- using System.Web.Configuration;
使用参数化查询
- public class Method : Interface1
- {
- private string _path = Environment.CurrentDirectory;
- private string _connectionString = WebConfigurationManager.ConnectionStrings["CURD"].ToString();//获取我们Web.Config的数据库配置
- //@"Server=.;Initial Catalog=CURD;Integrated Security=true;";也可以 使用这个代替 _connectionString 的值
- public void Create(Person Item)
- {
- IDbConnection connection = new SqlConnection(this._connectionString);
- IDbCommand cmd = new SqlCommand(@"Insert Into Persons(Id,Name,Sex,Age) Values(@Id,@Name,@Sex,@Age)");
- cmd.Connection = connection;
- cmd.Parameters.Add((Item.Id == null)
- ? new SqlParameter("@Id", DBNull.Value)
- : new SqlParameter("@Id", Item.Id));
- cmd.Parameters.Add(new SqlParameter("@Name", Item.Name));
- cmd.Parameters.Add(new SqlParameter("@Sex", Item.Sex));
- cmd.Parameters.Add(new SqlParameter("@Age", Item.Age));
- connection.Open();
- cmd.ExecuteNonQuery();
- connection.Close();
- }
- public void Delete(Person Item)
- {
- IDbConnection connection = new SqlConnection(this._connectionString);
- IDbCommand cmd = new SqlCommand(@"Delete From Persons Where [email protected]");
- cmd.Connection = connection;
- cmd.Parameters.Add((Item.Id == null)
- ? new SqlParameter("@Id", DBNull.Value)
- : new SqlParameter("@Id", Item.Id));
- cmd.Parameters.Add(new SqlParameter("@Name", Item.Name));
- cmd.Parameters.Add(new SqlParameter("@Sex", Item.Sex));
- cmd.Parameters.Add(new SqlParameter("@Age", Item.Age));
- connection.Open();
- cmd.ExecuteNonQuery();
- connection.Close();
- }
- public IEnumerable Get()
- {
- IDbConnection connection = new SqlConnection(this._connectionString);
- IDbCommand cmd = new SqlCommand("SELECT * FROM Persons");
- cmd.Connection = connection;
- connection.Open();
- IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleResult);
- while (reader.Read())
- {
- Person person = new Person()
- {
- Id = reader.GetValue(reader.GetOrdinal("Id")).ToString(),
- Sex = reader.GetValue(reader.GetOrdinal("Sex")).ToString(),
- Name = reader.GetValue(reader.GetOrdinal("Name")).ToString(),
- Age = Convert.ToInt32(reader.GetValue(reader.GetOrdinal("Age")))
- };
- yield return person;
- }
- connection.Close();
- }
- public void Update(Person Item)
- {
- IDbConnection connection = new SqlConnection(this._connectionString);
- IDbCommand cmd = new SqlCommand(@"Update Persons Set [email protected],[email protected],[email protected]Where[email protected]);
- cmd.Connection = connection;
- cmd.Parameters.Add((Item.Id == null)
- ? new SqlParameter("@Id", DBNull.Value)
- : new SqlParameter("@Id", Item.Id));
- cmd.Parameters.Add(new SqlParameter("@Name", Item.Name));
- cmd.Parameters.Add(new SqlParameter("@Sex", Item.Sex));
- cmd.Parameters.Add(new SqlParameter("@Age", Item.Age));
- connection.Open();
- cmd.ExecuteNonQuery();
- connection.Close();
- }
- }
补充:
Web.Config 的数据库配置
- <connectionStrings>
- <add name="CURD" connectionString="Data Source=.; Initial Catalog=CURD;Integrated Security=True" providerName="System.Data.SqlClient" />
- </connectionStrings>
方法使用
- Northwind db = new Northwind();
- IEnumerable < Person > pList = db.Persons.Get(); //获取Person表的数据
- Person pl = new Person {
- Id = "5",
- Name = "ddd",
- Age = 28,
- Sex = "男"
- };
- db.Persons.Create(pl); //插入数据
- db.Persons.Update(pl); //更新数据
- db.Persons.Delete(pl); //删除数据
(初级试水)MVC 使用 ADO.Net + 泛型 操作数据库
来源: http://www.bubuko.com/infodetail-2083373.html