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

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

一.测试连接是否成功

//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();
        }
    }
}


目录
相关文章
|
1月前
mvc.net分页查询案例——实体层(HouseModel.cs)
mvc.net分页查询案例——实体层(HouseModel.cs)
15 0
|
1月前
mvc.net分页查询案例——DLL数据访问层(HouseDLL.cs)
mvc.net分页查询案例——DLL数据访问层(HouseDLL.cs)
8 0
|
1月前
mvc.net分页查询案例——PagerExtension
mvc.net分页查询案例——PagerExtension
9 0
|
1月前
mvc.net分页查询案例——PagedList
mvc.net分页查询案例——PagedList
9 0
|
6天前
|
开发框架 前端开发 JavaScript
采用C#.Net +JavaScript 开发的云LIS系统源码 二级医院应用案例有演示
技术架构:Asp.NET CORE 3.1 MVC + SQLserver + Redis等 开发语言:C# 6.0、JavaScript 前端框架:JQuery、EasyUI、Bootstrap 后端框架:MVC、SQLSugar等 数 据 库:SQLserver 2012
|
1月前
mvc.net分页查询案例——控制器(HomeController.cs)
mvc.net分页查询案例——控制器(HomeController.cs)
8 0
|
1月前
mvc.net分页查询案例——mvc-paper.css
mvc.net分页查询案例——mvc-paper.css
5 0
|
2月前
|
开发框架 前端开发 .NET
福利来袭,.NET Core开发5大案例,30w字PDF文档大放送!!!
为了便于大家查找,特将之前开发的.Net Core相关的五大案例整理成文,共计440页,32w字,免费提供给大家,文章底部有PDF下载链接。
33 1
福利来袭,.NET Core开发5大案例,30w字PDF文档大放送!!!
|
4月前
net实践记录
net实践记录
18 0