Asp.Net就业课之案例实践第二次课
【摘要】
一.测试连接是否成功
//String str = "server=(local);database=MyDb;uid=asp;pwd=asp";
String str =...
一.测试连接是否成功
//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);
- 1
- 2
- 3
- 4
- 5
- 6
二.增删改实现
代码实践：
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();
}
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
三.参数化的增删改实践
参考代码：
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();
}
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
四.聚合函数查询
参考代码：
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);
}
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
五.查询所有学生信息显示到页面
参考代码如下：
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
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
文章来源: aaaedu.blog.csdn.net，作者：tea_year，版权归原作者所有，如需转载，请联系作者。
原文链接：aaaedu.blog.csdn.net/article/details/109065154
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）