c#实验解答（8）

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

            }

        }

    }

}