ADO.Net的基本语句

引入命名空间：

Using system.Data;

Using system.Data.SqlCllent;

 

建立连接：

        

String connString = “Server=.;DataBase=StudentManageDB;Uid=sa;PWd=password”;

SqlConnetion conn = new SqlConnection(connString);

Conn.Open();

//关闭连接

Conn.Close();

增加：

        

String sql = “insert into Students(StudentName,Gender,Bithday,StudentIdNo,Age,PhoneNum,StudentAddress)”;

Sql+=”calues(‘{0}’,’{1}’, ’{2}’, ’{3}’, ’{4}’, ’{5}’, ’{6}’, ’{7}’)”;

Sql=string.Format(sql,”xxx”,”xxx”…);

//创建Command对象

SqlCommand cmd = new SqlCommand(sql,conn);

//执行操作

Int result = cmd.ExecutNonQuery();

//关闭连接

修改：

        

 String sql = “update Students set StudentName=’{0}’ where StudentId=’{1}’”;

SQL = string.Format(sql,”Name”,StudentNo);

//创建Command对象

SqlCommand cmd = new SqlCommand(sql,conn);

//执行操作

Int result = cmd.ExecutNonQuery();

删除：

        

String sql = “delete from Students where StudentId=”+StudentNo;

SQL = string.Format(sql,”Name”,StudentNo);

//创建Command对象

SqlCommand cmd = new SqlCommand(sql,conn);

//执行操作

Int result = cmd.ExecutNonQuery();

 

同时递交多个Sql语句

         

Sql = sql1+”;”+sql2+”;”+sql3;

//创建Command对象

SqlCommand cmd = new SqlCommand(sql,conn);

//执行操作

Int result = cmd.ExecutNonQuery();

 

                   每条SQL语句成功Reslut就会+1

返回单一结果的查询

        

String sql = “select Count(*) from Students”;

SqlCommand cmd = new SqlCommand(sql,conn);

Object result = cmd.ExecuteScalar();

        

String sql = “select StudentName from Students where StudentId=100004”;

SqlCommand cmd = new SqlCommand(sql,conn);

Object result = cmd.ExecuteScalar();

返回一个结果集(DataReader)

         读取完成前必须保持连接

         关闭连接前必须先关闭读取器

        

String sql = “select StudentId,StudentName,Gender from Students;”

SqlCommand cmd = new SqlCommand(sql,conn);

         //执行查询方法，返回Reader对象

        

SqlDataReader objReader = cmd.ExecuteReader();

While(objReader.Read())//判断是否有数据

{

   Consle.WriteLine(onjReader[“StudentId”].Tostring() + objReader[“StudentName”]);

}

//关闭读取器

objReader.Close();

返回多个数据集

        

String sql = “Select StudentId,StudentName frome Students;Select ClassId,ClassName form StudentClass”

SqlCommand cmd = new SqlCommand(sql,conn);

SqlDataReader objReader = cmd.ExecuteReader();

//读取第一个结果

While(objReader.Read())

{

    Consle.WriteLine(onjReader[“StudentId”].Tostring() + objReader[“StudentName”]);

}

//判断是否有下一个结果

If(objReader.NextReslut())

{

   While(objReader.Read())

   {

        Consle.WriteLine(onjReader[“StudentId”].Tostring() + objReader[“StudentName”]);

   }

}

总结

数据集在项目中的运用(DataAdapterà用于DataSet的数据源填充)

         创建DataSetà将数据库缓存至本地

        

qlCommand cmd = new SqlCommand(sql,conn);

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

Try

{

         Conn.Open();

         Da.Fill(ds);//填充

         Return ds;

}

Catch(Exception ex)

{Throw ex};

将DataSet递交给控件的Resource属性

DataView控件

动态筛选

        

This.dtScoreList.DefaultView.RowFilter = string.Format(“ClassName=’{0}’,this.cmbClass.Text”);

This.dtScoreList.DefaultView.RowFilter = string.Format(“ClassName like ‘%%’”);

范围筛选

         

This.dtScoreList.DefaultView.RowFilter = string.Format(“CSharp>’{0}’,this.txtScore.Text’”);

DataAdapter无需封装 DataReader要封装但是占用资源