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语句成功Reslu就会+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要封装但是占用资源

转载于:https://my.oschina.net/viperli/blog/3020414