嵌入式sql语言实验报告

1、实验项目名称 实验四：嵌入式SQL的使用 一、实验目的1、明确嵌入式SQL的意义。2、学会使用不用游标的DML语句。3、学会使用使用游标的DML语句。二、实验环境在Win7-32系统下,用Microsoft SQL Server Management Studio实现的三、实验内容以学生选课数据库为中心，使用PowerBuilder语言(Vb或Delphi)，设计一个数据录入、查询窗口，进行练习。不用游标语句 2、使用游标的语句实验结果1、不用游标语句（1）查询结果为单记录的SELECT语句 格式：SELECT 目标列INTO 主变量FROM 基本表（或视图）WHERE 条件表达式； 如：根据

2、主变量GIVENS#的值找该学生的信息SELECT SN，SD，SAINTO :SNAME，:DEPT，:AGEFROM SWHERE S#=:GIVENS#；（2）UPDATE语句 如：UPDATE SSET SA=NULLWHERE SD=”CS”（3）DELETE语句 如：DELETEFROM SCWHERE :DEPT=（SELECT SD FROM S WHERE S.S#=SC.S#);2、使用游标的语句（1）查询结果为单记录的SELECT语句格式：SELECT 目标列INTO 主变量FROM 基本表（或视图）WHERE 条件表达式；如：根据主变量GIVENS#的值找该学生的信息S

3、ELECT SN，SD，SAINTO :SNAME，:DEPT，:AGEFROM SWHERE S#=:GIVENS#；2.查询数据Update语句UPDATE SSET SA=NULLWHERE SD=”MA”（3）DELETE语句如：DELETEFROM SCWHERE :sd=（SELECT SD FROM S WHERE S.S#=SC.S#);已删除4）INSERT语句如：INSERTINTO S(S#,SN,SD,SG,SEX)VALUES(:GIVENS#,:SNAME,:DEPT)使用C#操作的代码:using System;using System.Collections.G

4、eneric;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Data.SqlClient;namespace Sql public partial class Form1 : Form public Form1() InitializeComponent(); private void Form1_Load(object sender, EventArgs

5、e) l1.Text = 请连接数据库!; static SqlConnection sql = null;/创建连接对象 static SqlCommand stu = new SqlCommand();/创建对象 private void button1_Click(object sender, EventArgs e) if (textBox1.Text = ) MessageBox.Show(null,请输入数据库名称,0,MessageBoxIcon.Error); else try string sc = server=A506;database= + textBox1.Text.

6、Trim() + ;uid=sa;pwd=123; sql = new SqlConnection(sc); sql.Open(); if (sql.State = ConnectionState.Open) l1.Text = 连接成功; button3.Enabled = true; catch(Exception ex) MessageBox.Show(ex.Message); /l1.Text= 连接失败请重试; private void button2_Click(object sender, EventArgs e) try sql.Close(); if (sql.State =

7、 ConnectionState.Closed) l1.Text = 关闭成功; catch l1.Text = 关闭失败，请连接数据库！; private void sqls(string str)/数据库操作函数用接SQL语句? try stu.Connection = sql;/设置连接属性 DataSet ds = new DataSet(); SqlDataAdapter sda = new SqlDataAdapter(str, sql); sda.Fill(ds, student); dataGrid1.DataSource = ds.Tablesstudent.DefaultV

8、iew; catch (Exception ex) / 查询失败！? MessageBox.Show(ex.Message); private void updata(string str) try stu.Connection = sql; stu = new SqlCommand(str, sql); stu.ExecuteNonQuery(); l1.Text = 更新成功！; catch MessageBox.Show(更新失败请重试！); private void button3_Click(object sender, EventArgs e) string select = se

9、lect * from student where sno= + textBox2.Text.Trim(); sqls(select); private void button5_Click(object sender, EventArgs e) string select = select * from student where sdept= + textBox2.Text.Trim()+; sqls(select); private void button4_Click(object sender, EventArgs e) string upda=UPDATE student SET

10、sage=null WHERE sdept=+textBox2.Text.Trim()+; updata(upda); private void sqlinsert(string str) try stu.Connection = sql; stu = new SqlCommand(str, sql); stu.ExecuteNonQuery(); l1.Text = 插入成功，请查询！; catch(Exception ex) MessageBox.Show(插入失败请重试?+ex.Message); private void button6_Click(object sender, Eve

11、ntArgs e) try string sno, sname, sd, sage, sex;/读取单元格内容 sno = data1.CurrentRow.Cells0.Value.ToString(); sname = data1.CurrentRow.Cells1.Value.ToString(); sd = data1.CurrentRow.Cells2.Value.ToString(); sage = data1.CurrentRow.Cells3.Value.ToString(); sex = data1.CurrentRow.Cells4.Value.ToString(); l1

12、.Text = sno + sname + sd + sage + sex; string insert = INSERT INTO student(sno,sname,sdept,sage,ssex) VALUES( + sno.Trim() + , + sname.Trim() + , + sd.Trim() + , + sage.Trim() + , + sex.Trim() + ); sqlinsert(insert); catch MessageBox.Show(请检查输入内容!); private void button7_Click(object sender, EventArg

13、s e) data1.Visible = true; private void sqldelete(string str) try stu.Connection = sql; stu = new SqlCommand(str, sql); stu.ExecuteNonQuery(); l1.Text = 删除成功请查询?; catch(Exception ex) MessageBox.Show(删除失败请重试+ex.Message); private void button8_Click(object sender, EventArgs e) string delete = delete from student where sno= + textBox2.