formsqlcommand.cs

来自「csharp课本的源代码」· CS 代码 · 共 103 行

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 SqlCommandExample
{
    public partial class FormSqlCommand : Form
    {
        public FormSqlCommand()
        {
            InitializeComponent();
        }

        

        private void buttonShowMyTable1_Click(object sender, EventArgs e)
        {
            SqlConnection conn =
       new SqlConnection(Properties.Settings.Default.MyDatabaseConnectionString);
            SqlCommand cmd = new SqlCommand("select * from [MyTable1] ", 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();
            }
        }

        private void buttonAddGrade_Click(object sender, EventArgs e)
        {
            //读取连接字符串
            string connectionString = Properties.Settings.Default.MyDatabaseConnectionString;
            //根据连接字符串创建SqlConnection实例
            SqlConnection conn = new SqlConnection(connectionString);
            //创建SqlCommand实例，并设置SQL语句和使用的连接实例
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "update [MyTable2] set [成绩]=[成绩]+10 where [姓名]='张三玉'";
            cmd.Connection = conn;
            try
            {
                conn.Open();
                //将执行SQL语句影响的记录数赋值给number
                int number = cmd.ExecuteNonQuery();
                MessageBox.Show(string.Format("修改了{0}条记录", number));
            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message, "修改记录失败");
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }
        private void buttonCount_Click(object sender, EventArgs e)
        {
            SqlConnection conn =
        new SqlConnection(Properties.Settings.Default.MyDatabaseConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            try
            {
                conn.Open();
                cmd.CommandText = "select count(*) from  [MyTable2] where [姓名] like '王%'";
                int record = (int)cmd.ExecuteScalar();
                cmd.CommandText = "select sum([成绩]) from  [MyTable2] where [姓名] like '王%'";
                double sumValue = Convert.ToDouble(cmd.ExecuteScalar());
                MessageBox.Show(string.Format("有{0}条姓王的记录，合计成绩为{1}",
                    record, sumValue));
            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message);
            }
            finally
            {
                conn.Close();
            }

        }
    }
}