是否些倦了 SqlConnection conn=new SqlConnection();一系列繁冗的代码? 来试试Linq吧
查:
using System.Data.SqlClient; namespace obj
{
public class StudentDA
{
SqlConnection conn;
SqlCommand cmd;
public StudentDA()
{
conn = new SqlConnection("server=.;database=aaaaa;user=sa;pwd=123");
cmd = conn.CreateCommand();
}
public List<type> Search()
{
List<type> data = null;
cmd.CommandText = "select Student.sno,sname,Course.Cno,cname,tname,degree from Score join Student on student.sno=score.sno join course on course.Cno=score.Cno join teacher on teacher.tno=course.tno";
cmd.Parameters.Clear();
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
data = new List<type>();
while (dr.Read())
{
type a = new type();
a.Sno = dr["Sno"].ToString();
a.Sname = dr["Sname"].ToString();
a.Cno = dr["Cno"].ToString();
a.Cname = dr["Cname"].ToString();
a.Tname = dr["Tname"].ToString();
a.Degree = dr["Degree"].ToString();
data.Add(a);
}
}
cmd.Dispose();
conn.Close();
return data;
}
public type Ss(string id,string od) //两个参数值,数出 type类型结果
{
type Ss = null;
cmd.CommandText = "select Student.sno,sname,Course.Cno,cname,tname,degree from Score join Student on student.sno=score.sno join course on course.Cno=score.Cno join teacher on teacher.tno=course.tno where course.cno=@Cno and student.sno=@Sno";
cmd.Parameters.Clear();
cmd.Parameters.Add("@Cno", od);
cmd.Parameters.Add("@Sno", id);
conn.Open();
SqlDataReader dr =cmd.ExecuteReader();
if(dr.Read())
{
Ss = new type();
Ss.Sno = dr["Sno"].ToString();
Ss.Sname = dr["Sname"].ToString();
Ss.Cno = dr["Cno"].ToString();
Ss.Cname = dr["Cname"].ToString();
Ss.Tname = dr["Tname"].ToString();
Ss.Degree = dr["Degree"].ToString();
}
cmd.Dispose();
conn.Close();
return Ss;
}
LinQ:
需要先建一个 LinQ to SQL类,在服务器资源管理器中建立新的连接
然后设置数据库地址和登录设置,然后选取数据库
确定建好类文件 
.dbml 选择数据空中的表拖入,系统自动会生成以列名相同的类,
然后见一个类 来写方法
public class InfoBF
{
private MyDBDataContext _Context = new MyDBDataContext();
public List<Info> Select() //查询info类型的所有
{
return _Context.Info.ToList();
}
public Info Select(string code) //一个输入参数返回 info类型
{
var query = _Context.Info.Where(p=>p.Code == code); //lambda表达式
if (query.Count() > )
{
return query.First(); //取第一条
}
return null;
}
增:
public void Insert(Student stu)
{
cmd.CommandText = "insert into student values(@Sno,@Sname,@Ssex,@Sbirthday,@Class)";
cmd.Parameters.Clear();
cmd.Parameters.Add("@Sno", stu.Sno);
cmd.Parameters.Add("@Sname", stu.Sname);
cmd.Parameters.Add("@Ssex", stu.Ssex);
cmd.Parameters.Add("@Sbirthday", stu.Sbirthday);
cmd.Parameters.Add("@Class", stu.Class);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
LinQ:
public void Insert(string code, string name, bool sex, string nation, DateTime birthday)
{
Info data = new Info
{
Code = code,
Name = name,
Sex = sex,
Nation = nation,
Birthday = birthday
};
_Context.Info.InsertOnSubmit(data);
_Context.SubmitChanges();
}
public void Insert(Info data)
{
_Context.Info.InsertOnSubmit(data);
_Context.SubmitChanges();
}
改:
public void Update(type data)
{
cmd.CommandText = "update student set sname=@Sname where sno=@Sno;update score set degree=@Degree where cno=@Cno and sno=@Sno;update course set cname=@Cname where cno=@Cno;update teacher set tname=@Tname where tno =(select tno from course where cno=@Cno)";
cmd.Parameters.Add("@Sno", data.Sno);
cmd.Parameters.Add("@Sname", data.Sname);
cmd.Parameters.Add("@Cno", data.Cno);
cmd.Parameters.Add("@Cname", data.Cname);
cmd.Parameters.Add("@Tname", data.Tname);
cmd.Parameters.Add("@Degree", data.Degree);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
LinQ:
public void Update(string code, string name, bool sex, string nation, DateTime birthday)
{
var query = _Context.Info.Where(p => p.Code == code);
if (query.Count() > )
{
Info data = query.First();
data.Name = name;
data.Sex = sex;
data.Nation = nation;
data.Birthday = birthday;
_Context.SubmitChanges();
}
}
删:
public void Delete(string id)
{
cmd.CommandText = "";
cmd.Parameters.Clear();
cmd.Parameters.Add("@id", id);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
LinQ:
public void Delete(string code)
{
var query = _Context.Info.Where(p=>p.Code == code);
if (query.Count() > )
{
Info data = query.First();
_Context.Info.DeleteOnSubmit(data);
_Context.SubmitChanges();
}
}