using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient; //引入
using SuperKTV.Common; // 引入
namespace SuperKTV.Admin
{
public partial class SingerManagerForm : Form
{
public SingerManagerForm()
{
InitializeComponent();
}
private void tsb_add_Click(object sender, EventArgs e)
{
AddSingerForm form = new AddSingerForm();
form.ShowDialog();
}
private void SingerManagerForm_Load(object sender, EventArgs e)
{
GetCount();
// LoadSinger();
LoadSingerNonConnection();
}
// 统计方法
private void GetCount()
{
// 聚合函数的操作:非断开式访问
// 创建Connection对象
SqlConnection conn = new SqlConnection(DBHelper.connString);
// 打开连接
conn.Open();
// 定义sql语句:聚合函数
string sqlString = "select count(*) from Singer";
string maleString = "select count(*) from Singer where gender='男'";
string femaleString = "select count(*) from Singer where gender='女'";
string groupString = "select count(*) from Singer where gender='组合'";
// 创建Command对象
SqlCommand cmd = new SqlCommand(sqlString, conn);
// 执行命令
int total = (int)cmd.ExecuteScalar();
// 执行后续语句
cmd.CommandText = maleString;
int male = (int)cmd.ExecuteScalar();
cmd.CommandText = femaleString;
int female = (int)cmd.ExecuteScalar();
cmd.CommandText = groupString;
int group = (int)cmd.ExecuteScalar();
// 关闭连接
conn.Close();
// 显示
this.tsl_msg.Text = string.Format("共有歌手{0}人,其中男歌手{1}人,女歌手{2}人,组合有{3}个",
total,male,female,group);
}
// 加载歌手列表方法:非断开式
private void LoadSinger()
{
// 非断开式数据库查询访问
// 1. 创建Connection对象
SqlConnection conn = new SqlConnection(DBHelper.connString);
// 2. 打开连接
conn.Open();
// 3. 定义sql语句
string sqlString = "select * from Singer";
// 4. 创建Command对象
SqlCommand cmd = new SqlCommand(sqlString, conn);
// 5. 执行命令,返回DataReader对象
SqlDataReader rd = cmd.ExecuteReader();
// 6. 逐行读取数据
while (rd.Read())
{
// 创建 ListViewItem 对象
ListViewItem item = new ListViewItem(rd["SingerID"].ToString());
// subitems属性实现后续列的数据显示
item.SubItems.Add(rd["SingerName"].ToString());
item.SubItems.Add(rd["Gender"].ToString());
item.SubItems.Add(((DateTime)rd["Birthday"]).ToString("yyyy-MM-dd"));
item.SubItems.Add(rd["Area"].ToString());
// 把ListViewItem 对象添加listview控件中
this.lv_singermanager.Items.Add(item);
}
// 7. 关闭 读取器
rd.Close();
// 8. 关闭数据库连接
conn.Close();
}
// 加载歌手列表方法:断开式访问
private void LoadSingerNonConnection()
{
// 1. 创建Connection对象
SqlConnection conn = new SqlConnection(DBHelper.connString);
// 2. 定义sql语句
string sqlString = "select * from Singer";
// 3. 创建DataSet对象
DataSet ds = new DataSet("myDataSet");
// 4. 创建适配器对象
SqlDataAdapter dap = new SqlDataAdapter(sqlString, conn);
// 5. 填充
dap.Fill(ds, "singerInfo");
// 6. 遍历ds的数据
foreach (DataTable dt in ds.Tables)
{
foreach (DataRow dr in dt.Rows)
{
// 创建 ListViewItem 对象
ListViewItem item = new ListViewItem(dr["SingerID"].ToString());
// subitems属性实现后续列的数据显示
item.SubItems.Add(dr["SingerName"].ToString());
item.SubItems.Add(dr["Gender"].ToString());
item.SubItems.Add(((DateTime)dr["Birthday"]).ToString("yyyy-MM-dd"));
item.SubItems.Add(dr["Area"].ToString());
// 把ListViewItem 对象添加listview控件中
this.lv_singermanager.Items.Add(item);
}
}
}
private void tsb_refresh_Click(object sender, EventArgs e)
{
this.lv_singermanager.Items.Clear(); // 刷新前需要清空项
GetCount();
LoadSinger();
}
}
}