首先,创建一个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<Person>
{
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 Id=@Id");
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<Person> 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 Name=@Name,Sex=@Sex,Age=@Age Where Id=@Id);
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);//删除数据