c#实验解答（8）

来源：互联网发布：windows返回桌面快捷键编辑：程序博客网时间：2024/05/02 02:58

1、使用保持连接方式编写程序，计算各年级平均成绩，并显示结果。

【解答】

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace习题9_1

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

//添加Button按钮在ListBox中显示结果

private void button1_Click(object sender, EventArgs e)

{

listBox1.Items.Add("年级平均成绩");

string connectionString = Properties.Settings.Default.MyDatabaseConnectionString;

//根据连接字符串创建SqlConnection实例

SqlConnection conn = new SqlConnection(connectionString);

//创建SqlCommand实例，并设置SQL语句和使用的连接实例

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "select substring(学号,1,2) as 年级,avg(成绩) as 平均成绩 from MyTable2 group by substring(学号,1,2)";

cmd.Connection = conn;

try

{

conn.Open();

SqlDataReader r = cmd.ExecuteReader();

while (r.Read() == true)

{

listBox1.Items.Add(string.Format("{0}级 {1}", r[0], r[1]));

}

r.Close();

}

catch (Exception err)

{

MessageBox.Show(err.Message, "计算成绩失败");

}

finally

{

conn.Close();

}

2、使用保持连接方式编写程序，查询MyTable2中不及格学生的学号、姓名、性别和成绩。并将结果在ListBox中显示出来。

【解答】

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace习题9_2

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

private void button1_Click(object sender, EventArgs e)

{

listBox1.Items.Add(" 学号姓名性别成绩");

string connectionString = Properties.Settings.Default.MyDatabaseConnectionString;

//根据连接字符串创建SqlConnection实例

SqlConnection conn = new SqlConnection(connectionString);

//创建SqlCommand实例，并设置SQL语句和使用的连接实例

SqlCommand cmd = new SqlCommand();

cmd.CommandText =

"Select学号,姓名,性别,成绩 From MyTable2 Where (成绩<60)";

cmd.Connection = conn;

try

{

conn.Open();

SqlDataReader r = cmd.ExecuteReader();

while (r.Read() == true)

{

listBox1.Items.Add( string.Format("{0} {1} {2} {3}", r[0], r[1], r[2], r[3]));

}

r.Close();

}

catch (Exception err)

{

MessageBox.Show(err.Message, "查询成绩失败");

}

finally

{

conn.Close();

}

3、编写程序，以“[编码]名称”的样式在comboBox1中显示MyTable1的内容。

【解答】

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace习题9_3

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

private void Form1_Load(object sender, EventArgs e)

{

string connectionString = Properties.Settings.Default.MyDatabaseConnectionString;

//根据连接字符串创建SqlConnection实例

SqlConnection conn = new SqlConnection(connectionString);

//创建SqlCommand实例，并设置SQL语句和使用的连接实例

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "Select * From MyTable1";

cmd.Connection = conn;

try

{

conn.Open();

SqlDataReader r = cmd.ExecuteReader();

while (r.Read() == true)

{

comboBox1.Items.Add(string.Format("[{0}] {1}", r[0], r[1]));

}

comboBox1.SelectedIndex = 0;

r.Close();

}

catch (Exception err)

{

MessageBox.Show(err.Message, "显示数据失败");

}

finally

{

conn.Close();

}

4、已知数据库MyDb.mdf中定义了一张person表，表中有一个“年龄”字段，编写存储过程并调用存储过程显示该数据表中年龄大于18的所有纪录。

【解答】

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace习题9_4

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

private void button1_Click(object sender, EventArgs e)

{

string connectionstring = Properties.Settings.Default.MyDatabaseConnectionString ;

SqlConnection conn = new SqlConnection(connectionstring);

try

{

conn.Open();

SqlDataAdapter adapter = new SqlDataAdapter(

"select性别 from person where性别> 18 ", conn);

DataSet dataset = new DataSet();

adapter.Fill(dataset, "person");

dataGridView1.DataSource = dataset.Tables["person"];

adapter.Dispose();

}

catch (Exception err)

{

MessageBox.Show(err.Message);

}

finally

{

conn.Close();

}

5、调用存储过程，设计程序完成下列功能：任意给出一个汉字，统计MyTable2中所有包含该汉字的人数，并显示统计结果。

【解答】

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace习题9_5

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

private void button1_Click(object sender, EventArgs e)

{

SqlConnection conn =

new SqlConnection(Properties.Settings.Default.MyDatabaseConnectionString);

SqlCommand cmd = new SqlCommand();

cmd.Connection = conn;

//设置SQL语句为存储过程名，命令类型为存储过程

cmd.CommandText = "SelectFilterStudentsNum";

cmd.CommandType = CommandType.StoredProcedure;

//添加存储过程中参数需要的初始值，注意参数名要和存储过程定义的参数名相同

if( textBox1.Text=="")

{

MessageBox.Show("请输入有效信息","错误");

textBox1.Focus();

return ;

}

cmd.Parameters.AddWithValue("@surname", textBox1.Text);

cmd.Parameters.AddWithValue("@record", 0);

//指定哪些参数需要返回结果

cmd.Parameters["@record"].Direction = ParameterDirection.Output;

try

{

conn.Open();

//执行存储过程

cmd.ExecuteNonQuery();

//显示返回的结果

MessageBox.Show(string.Format("有{0}条含 {1}的记录",

cmd.Parameters["@record"].Value,textBox1.Text));

}

catch (Exception err)

{

MessageBox.Show(err.Message);

}

finally

{

conn.Close();

}

阅读全文

0 0