Asp.Net就业课之案例实践第二次课

Green

一.测试连接是否成功

//String str = "server=(local);database=MyDb;uid=asp;pwd=asp";
      String str = "server=.;database=MyDb;uid=asp;pwd=asp";
      SqlConnection conn = new SqlConnection(str);
      conn.Open();
      Response.Write(conn.State);

二.增删改实现

代码实践：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
namespace demo01
{
  public partial class SqlCommandOne : System.Web.UI.Page
  {
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    //新增
    protected void Button1_Click(object sender, EventArgs e)
    {
      string str = "server=.;database=mydb;uid=asp;pwd=asp";
      SqlConnection conn = new SqlConnection(str);
      conn.Open(); //记得打开;
      //创建命令对象;
      SqlCommand cmd = new SqlCommand();
      cmd.CommandText = "insert student values(4,'王帅',19,'男',1)";//指定的是cmd的要执行的sql语句
      cmd.Connection = conn;//命令对象与哪个连接对象关联;
      //要执行了;
      int result = cmd.ExecuteNonQuery();
      if (result > 0)
        Response.Write("增加数据成功!");
      else
        Response.Write("增加数据失败!");
      //最后需要关闭连接对象
      conn.Close();
    }
    protected void btnDel_Click(object sender, EventArgs e)
    {
      string str = "server=.;database=mydb;uid=asp;pwd=asp";
      SqlConnection conn = new SqlConnection(str);
      conn.Open(); //记得打开;
      //创建命令对象;
      SqlCommand cmd = new SqlCommand();
      //from可以省略，但是一般不省略；
      cmd.CommandText = "delete  student where stuid=1";//指定的是cmd的要执行的sql语句
      cmd.Connection = conn;//命令对象与哪个连接对象关联;
      //要执行了;
      int result = cmd.ExecuteNonQuery();
      if (result > 0)
        Response.Write("删除数据成功!");
      else
        Response.Write("删除数据失败!");
      //最后需要关闭连接对象
      conn.Close();
    }
    protected void btnEdit_Click(object sender, EventArgs e)
    {
      string str = "server=.;database=mydb;uid=asp;pwd=asp";
      SqlConnection conn = new SqlConnection(str);
      conn.Open(); //记得打开;
      //创建命令对象;
      SqlCommand cmd = new SqlCommand();
      cmd.CommandText = "update student set deptId=1,stuName='王亚亚' where stuId=3";//指定的是cmd的要执行的sql语句
      cmd.Connection = conn;//命令对象与哪个连接对象关联;
      //要执行了;
      int result = cmd.ExecuteNonQuery();
      if (result > 0)
        Response.Write("修改数据成功!");
      else
        Response.Write("修改数据失败!");
      //最后需要关闭连接对象
      conn.Close();
    }
  }
}

三.参数化的增删改实践

参考代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
//引用命名空间;
using System.Data.SqlClient;
namespace demo01
{
  public partial class SqlCommandTwo : System.Web.UI.Page
  {
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void btnAdd_Click(object sender, EventArgs e)
    {
      //定义了一个变量，来表示连接数据库的字符串;
      string str = "server=.;database=mydb;uid=asp;pwd=asp";
      SqlConnection conn=new SqlConnection(str);
      conn.Open();
      //命名对象,由conn创建，就不用写连接属性了;
      SqlCommand cmd = conn.CreateCommand();
      //cmd.CommandText = "insert student values(5,'胡鲁瓦',8,'男',1)";
      //cmd.CommandText = "insert student values("+txtStuId.Text+",'胡鲁瓦',8,'男',1)"; 不拼接了
      string gender=RadioButton1.Checked?"男":"女";
      //StringBuilder,追加;
      String sql = string.Format(@"insert student values({0},'{1}',{2},'{3}',{4})",txtStuId.Text,txtStuName.Text,txtAge.Text,gender,txtDeptID.Text);
      cmd.CommandText = sql;
      //执行
      int result = cmd.ExecuteNonQuery();
      if (result > 0)
        Response.Write("成功");
      else
        Response.Write("失败");
      //关闭
      conn.Close();
      
    }
    protected void btnEdit_Click(object sender, EventArgs e)
    {
      //定义了一个变量，来表示连接数据库的字符串;
      string str = "server=.;database=mydb;uid=asp;pwd=asp";
      SqlConnection conn = new SqlConnection(str);
      conn.Open();
      //命名对象,由conn创建，就不用写连接属性了;
      SqlCommand cmd = conn.CreateCommand();
      //获取数据;
      string gender = RadioButton1.Checked ? "男" : "女";
      //**************************SqlCommand的参数化设置，比较流行;
      //sql语句的变化 
      String sql = "update student set stuName=@stuName,age=@age,gender=@gender,deptid=@deptId where stuId=@stuId";
      //@stuId等，是动态参数;需要我们给它赋值;
      cmd.CommandText = sql;
      //SqlCommand,有参数化的属性;
      //Add("动态参数",值);
      cmd.Parameters.Add("@stuId",txtStuId.Text.Trim());
      cmd.Parameters.Add("@stuName", txtStuName.Text.Trim());
      cmd.Parameters.Add("@age", txtAge.Text);
      cmd.Parameters.Add("@gender", gender);
      cmd.Parameters.Add("@deptId", txtDeptID.Text.Trim());
      //**************************
      //执行
      int result = cmd.ExecuteNonQuery();
      if (result > 0)
        Response.Write("成功");
      else
        Response.Write("失败");
      //关闭
      conn.Close();
    }
  }
}

四.聚合函数查询

参考代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
namespace demo01
{
  public partial class selectOne : System.Web.UI.Page
  {
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void btnSelectAll_Click(object sender, EventArgs e)
    {
      //定义了一个变量，来表示连接数据库的字符串;
      string str = "server=.;database=mydb;uid=asp;pwd=asp";
      SqlConnection conn = new SqlConnection(str);
      conn.Open();
      //命名对象,由conn创建，就不用写连接属性了;
      SqlCommand cmd = conn.CreateCommand();
      //查看总条数的命令语句
      //string sql = "select count(*) from student";
      string sql = "select MIN(age),max(age) from student";
      cmd.CommandText = sql;
      //执行查询只能得到第一行，一列的值;返回类型：object-->int      
      //int num = Convert.ToInt32(cmd.ExecuteScalar());
      //int num = (int)cmd.ExecuteScalar();
      string num = (String)cmd.ExecuteScalar();
      Response.Write("最小年龄：" + num);
    }
  }
}

五.查询所有学生信息显示到页面

参考代码如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Text;  //扩充
namespace demo01
{
  public partial class selectTwo : System.Web.UI.Page
  {
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void btnSelect_Click(object sender, EventArgs e)
    {
      //定义了一个变量，来表示连接数据库的字符串;
      string str = "server=.;database=mydb;uid=asp;pwd=asp";
      SqlConnection conn = new SqlConnection(str);
      conn.Open();
      string sql = "select * from student";
      //命令对象;
      SqlCommand cmd = new SqlCommand(sql, conn);
      //查询,查询完毕之后要存放到某个地方;数据集对象，包含若干  数据表对象;
      SqlDataAdapter sda = new SqlDataAdapter();
      sda.SelectCommand = cmd;
      DataSet ds = new DataSet();
      sda.Fill(ds, "student");
      DataTable dt=ds.Tables[0];  //获取第一个数据表对象
      //这时候就填充过去了;
      //查看一下列名;
      for (int i = 0; i < dt.Columns.Count; i++) {
        Response.Write(dt.Columns[i].ColumnName+" ");
      }
      Response.Write("<br/>");
      //最终想要的是数据
      for (int i = 0; i < dt.Rows.Count; i++) {
        //老的方式，参考教材;
        //最新方式
        StringBuilder sb = new StringBuilder();
        sb.Append(dt.Rows[i]["stuId"].ToString()+" ");
        sb.Append(dt.Rows[i]["stuName"].ToString() + " ");
        sb.Append(dt.Rows[i]["age"].ToString() + " ");
        sb.Append(dt.Rows[i]["gender"].ToString() + " ");
        sb.Append(dt.Rows[i]["deptId"].ToString() + "<br/>");
        Response.Write(sb.ToString());
             
      }
      //关闭
      conn.Close();
    }
  }
}