ado.net mysql linq_LinQ和ADO.Net增删改查 备忘

是否些倦了 SqlConnection conn=new SqlConnection();一系列繁冗的代码? 来试试Linq吧

查:

usingSystem.Data.SqlClient;namespaceobj

{public classStudentDA

{

SqlConnection conn;

SqlCommand cmd;publicStudentDA()

{

conn= new SqlConnection("server=.;database=aaaaa;user=sa;pwd=123");

cmd=conn.CreateCommand();

}public ListSearch()

{

List 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();while(dr.Read())

{

type a= newtype();

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();returndata;

}

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= newtype();

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();returnSs;

}

LinQ:

需要先建一个 LinQ to SQL类,在服务器资源管理器中建立新的连接

然后设置数据库地址和登录设置,然后选取数据库

确定建好类文件

.dbml 选择数据空中的表拖入,系统自动会生成以列名相同的类,

然后见一个类 来写方法

public classInfoBF

{private MyDBDataContext _Context = newMyDBDataContext();public List 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() > 0)

{returnquery.First(); //取第一条

}return null;

}

增:

public voidInsert(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, stringnation, DateTime birthday)

{

Info data= newInfo

{

Code=code,

Name=name,

Sex=sex,

Nation=nation,

Birthday=birthday

};

_Context.Info.InsertOnSubmit(data);

_Context.SubmitChanges();

}

public voidInsert(Info data)

{

_Context.Info.InsertOnSubmit(data);

_Context.SubmitChanges();

}

改:

public voidUpdate(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, stringnation, DateTime birthday)

{var query = _Context.Info.Where(p => p.Code ==code);if (query.Count() > 0)

{

Info data=query.First();

data.Name=name;

data.Sex=sex;

data.Nation=nation;

data.Birthday=birthday;

_Context.SubmitChanges();

}

}

删:

public void Delete(stringid)

{

cmd.CommandText= "";

cmd.Parameters.Clear();

cmd.Parameters.Add("@id", id);

conn.Open();

cmd.ExecuteNonQuery();

conn.Close();

}

LinQ:

public void Delete(stringcode)

{var query = _Context.Info.Where(p=>p.Code ==code);if (query.Count() > 0)

{

Info data=query.First();

_Context.Info.DeleteOnSubmit(data);

_Context.SubmitChanges();

}

}