一.测试连接是否成功
//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(); } } }
