五道口北大青鸟校区
KTV项目
指导老师:袁玉明
SQL数据库关系图
C#解决方案类图
第一步:创建数据库连接方法和打开方法和关闭方法!
1 public class DBHelper 2 { 3 private string str = "server=.;database=MyKtv;uid=sa"; 4 private SqlConnection _conection; 5 6 public SqlConnection Conection 7 { 8 get 9 {10 if (_conection==null)11 {12 _conection = new SqlConnection(str);13 }14 return _conection;15 }16 }17 /// <summary>18 /// 打开方法19 /// </summary>20 public void OpenConnection() 21 {22 if (Conection.State == ConnectionState.Closed)23 {24 Conection.Open();25 } if (Conection.State == ConnectionState.Broken)26 {27 Conection.Close();28 Conection.Open();29 }30 }31 /// <summary>32 /// 关闭方法`33 /// </summary>34 public void CloseConnection() 35 {36 if (Conection.State == ConnectionState.Open && Conection.State == ConnectionState.Broken)37 {38 Conection.Close();39 }40 41 }
第二步:歌曲首页
歌曲首页:用到了窗体之间的转换和读取路径表中的图片路径放到filepath上
1 public partial class FormMain : Form 2 { 3 DBHelper db = new DBHelper(); 4 public FormMain() 5 { 6 InitializeComponent(); 7 } 8 private void Form1_Load(object sender, EventArgs e) 9 {10 //读取路径表中的图片路径放到filepath上11 string sql = "select resource_path from resource_path where resource_id=1";12 SqlCommand cmd = new SqlCommand(sql,db.Conection);13 db.OpenConnection();14 KtvUtil.FilePath = cmd.ExecuteScalar().ToString();15 db.CloseConnection();16 }17 18 private void pictureBox1_Click(object sender, EventArgs e)19 {20 //打开明星点歌窗体21 FrmSinger frmSinger = new FrmSinger();22 frmSinger.Show();23 }24 25 private void toolStripButton4_Click(object sender, EventArgs e)26 {27 this.Close();28 }29 30 private void pictureBox4_Click(object sender, EventArgs e)31 {32 //打开拼音点歌窗体33 FrmOrderBySongName fobsn = new FrmOrderBySongName();34 fobsn.ShowDialog();35 }
第三步:歌星点歌
歌星点歌:用到了3个ListView之间的跳转,
1 public partial class FrmSinger : Form 2 { 3 DBHelper db = new DBHelper(); 4 public string SingerType = "组合"; 5 public int SingerId = 0; 6 7 public FrmSinger() 8 { 9 InitializeComponent(); 10 } 11 12 private void panel1_Paint(object sender, PaintEventArgs e) 13 { 14 15 } 16 /// <summary> 17 /// 点击歌手类型后加载相应的信息 18 /// </summary> 19 public void ShowSingerDiQu() 20 { 21 if (lvtype.SelectedItems[0]!=null) 22 { 23 lvtype.Visible = false; 24 lvSinger.Visible = true; 25 lvSinger.Location = lvtype.Location; 26 27 28 this.SingerType = Convert.ToString(lvtype.SelectedItems[0].Tag); 29 } 30 string sql = "select singertype_name,singertype_id from singer_type"; 31 SqlCommand cmd = new SqlCommand(sql,db.Conection); 32 SqlDataReader sdr; 33 try 34 { 35 db.OpenConnection(); 36 sdr = cmd.ExecuteReader(); 37 lvtype.Items.Clear(); 38 if (sdr.HasRows) 39 { 40 int result = 0; 41 while (sdr.Read()) 42 { 43 ListViewItem lvitem = new ListViewItem(); 44 string typename = Convert.ToString(sdr["singertype_name"]); 45 int typeid = Convert.ToInt32(sdr["singertype_id"]); 46 lvitem.Text = typename; 47 lvitem.Tag = typeid; 48 lvitem.ImageIndex = result; 49 lvSinger.Items.Add(lvitem); 50 result++; 51 } 52 sdr.Close(); 53 } 54 } 55 catch (Exception ex) 56 { 57 MessageBox.Show("第二个系统报错" + ex.Message); 58 } 59 finally 60 { 61 db.CloseConnection(); 62 } 63 } 64 65 private void listView2_SelectedIndexChanged(object sender, EventArgs e) 66 { 67 68 } 69 70 private void FrmOrderBySinger_Load(object sender, EventArgs e) 71 { 72 this.lvSinger.Visible = false; 73 this.lvContry.Visible=false; 74 } 75 76 private void listView1_Click(object sender, EventArgs e) 77 { 78 ShowSingerDiQu(); 79 } 80 /// <summary> 81 /// 读取对应地区的歌手名称 82 /// </summary> 83 public void ShowSingerName() 84 { 85 if (lvSinger.SelectedItems[0]!=null) 86 { 87 lvSinger.Visible = false; 88 lvContry.Visible = true; 89 lvContry.Location = lvtype.Location; 90 SingerId = Convert.ToInt32(lvSinger.SelectedItems[0].Tag); 91 StringBuilder sb = new StringBuilder(); 92 string sum = SingerType; 93 if (sum!="组合") 94 { 95 sum = SingerType == "女歌手" ? "男" : "女"; 96 } 97 string sql = string.Format("select singer_name,singer_photo_url,singer_id from singer_info where singertype_id='{0}' and singer_sex='{1}'", SingerId,sum); 98 SqlCommand cmd = new SqlCommand(sql, db.Conection); 99 try100 {101 db.OpenConnection();102 SqlDataReader read = cmd.ExecuteReader();103 if (read.HasRows)104 {105 //歌手头像索引106 int imageindex = 0;107 //清空图片集合108 imageName.Images.Clear();109 //清空listview列表集合110 lvContry.Items.Clear();111 if (read.HasRows)112 {113 while (read.Read())114 {115 //图片的地址116 string path = KtvUtil.FilePath + @"" + Convert.ToString(read["singer_photo_url"]);117 imageName.Images.Add(Image.FromFile(path));118 ListViewItem lvitem = new ListViewItem();119 string typename = Convert.ToString(read["singer_name"]);120 int typeid = Convert.ToInt32(read["singer_id"]);121 lvitem.Text = typename;122 lvitem.Tag = typeid;123 lvitem.ImageIndex = imageindex;124 lvContry.Items.Add(lvitem);125 imageindex++;126 }127 read.Close();128 }129 }130 131 }132 catch (Exception ex)133 {134 MessageBox.Show("第三个系统报错!" + ex.Message);135 }136 finally 137 {138 db.CloseConnection();139 }140 }141 142 }143 private void lvName_Click(object sender, EventArgs e)144 {145 ShowList();146 }147 148 private void listView2_Click(object sender, EventArgs e)149 {150 ShowSingerName();151 }152 153 private void listView1_ChangeUICues(object sender, UICuesEventArgs e)154 {155 156 }157 /// <summary>158 /// 打开第三层ListView159 /// </summary>160 public void ShowList() 161 {162 //定义一个StringBuilder对象163 StringBuilder sb = new StringBuilder();164 //sql语句165 string sql = string.Format("select song_id,song_name,singer_name='{0}',song_url from song_info where singer_id={1}",lvContry.SelectedItems[0].Text,Convert.ToInt32(lvContry.SelectedItems[0].Tag));166 //定义歌曲列表窗体的对象167 FrmSongList sl=new FrmSongList();168 //把sql语句传到第三个窗体上169 sl.Sql=sql;170 sl.ShowDialog();171 this.Close();172 }173 174 private void toolStripButton4_Click(object sender, EventArgs e)175 {176 this.Close();177 }178 179 private void toolStripButton1_Click(object sender, EventArgs e)180 {181 if (lvSinger.Visible)182 {183 lvSinger.Visible = false;184 lvContry.Visible = true;185 }186 else if (lvContry.Visible)187 {188 lvContry.Visible = false;189 lvtype.Visible = true;190 }191 else if (lvtype.Visible)192 {193 FormMain dd = new FormMain();194 dd.Show();195 this.Close();196 }197 }
第四步:歌曲列表
歌曲列表:实现点击一下歌曲列表中的一行信息就到以点里面
1 public partial class FrmSongList : Form 2 { 3 DBHelper db = new DBHelper(); 4 DataSet ds = new DataSet(); 5 6 private string sql = ""; 7 //歌曲的查询语句 8 public string Sql 9 {10 get { return sql; }11 set { sql = value; }12 }13 public FrmSongList()14 {15 InitializeComponent();16 }17 18 private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)19 {20 //第一步判断datagridView有没有选中的行21 //第二步从实例化SongList类22 //第三步调取歌曲song类的字段进行赋值(从datagridview中的列赋值);23 //第四部把从datagridview中获取到的数据添加到播放类里的数组中24 if (this.dataGridView1.SelectedRows[0]!=null)25 {26 SongList song = new SongList();27 song.SongName1 = this.dataGridView1.SelectedRows[0].Cells[1].Value.ToString();28 song.SongUl1 = this.dataGridView1.SelectedRows[0].Cells[3].Value.ToString();29 PalyList.AddSong(song);30 }31 32 }33 34 private void FrmSongList_Load(object sender, EventArgs e)35 {36 SqlDataAdapter sda = new SqlDataAdapter(sql,db.Conection);37 sda.Fill(ds,"songinfo");38 dataGridView1.DataSource = ds.Tables["songinfo"];39 }40 41 private void toolStripButton4_Click(object sender, EventArgs e)42 {43 this.Close();44 }45 46 private void toolStripButton6_Click(object sender, EventArgs e)47 {48 FrmSong frm = new FrmSong();49 frm.ShowDialog();50 }
第五步:已点歌曲
已点歌曲:进入了就是已播放状态一次往下添加都是未播放,如果到了下面的播放状态就还得改变成已播放
1 private void FrmOrderedSongList_Load(object sender, EventArgs e) 2 { 3 //遍历播放类里面的数组 4 foreach (SongList item in PalyList.song) 5 { 6 7 if (item!=null) 8 { 9 ListViewItem lvitem = new ListViewItem(item.SongName1);10 string type = item.PlaySong == PalySongState.unplayed ? "未播放" : "已播放";11 lvitem.SubItems.Add(type);12 this.listView1.Items.Add(lvitem);13 }14 15 }16 }17 18 private void toolStripButton1_Click(object sender, EventArgs e)19 {20 FrmSinger dd = new FrmSinger();21 dd.Show();22 }
第六步:拼音点歌
1 public partial class FrmSpeall : Form 2 { 3 private DBOpetion db = new DBOpetion(); 4 private SqlDataAdapter adapter = null; 5 private DataSet ds = new DataSet(); 6 public FrmSpeall() 7 { 8 InitializeComponent(); 9 } 10 [DllImportAttribute("user32.dll")] 11 private static extern bool AnimateWindow(IntPtr hwnd, int dwTime, int dwFlags); 12 private void FrmSpeall_Load(object sender, EventArgs e) 13 { 14 AnimateWindow(this.Handle, 300, FrmMain.AW_SLIDE + FrmMain.AW_VER_POSITIVE); 15 //动态效果可以取消注释 16 //for (int i = 97; i < 123; i++) 17 //{ 18 // for (int j = 0; j < 4; j++) 19 // { 20 // Label label = new Label(); 21 // label.BackColor = Color.Yellow; 22 // label.Font=new System.Drawing.Font("宋体",14.25F,System.Drawing.FontStyle.Regular, 23 // System.Drawing.GraphicsUnit.Point,((byte)(134))); 24 // label.AutoSize = false; 25 // label.Size = new System.Drawing.Size(50, 25); 26 // label.Text = ((char)i).ToString(); 27 // label.TextAlign = ContentAlignment.MiddleCenter; 28 // label.Location = new Point(60 + (i * 90), 60 + (j * 60)); 29 // label.Parent = panel1; 30 // } 31 //} 32 ////for (int i = 97; i < 123; i++) 33 ////{ 34 //// Console.WriteLine((char)i); 35 ////} 36 } 37 38 private void pictureBox1_Click(object sender, EventArgs e) 39 { 40 this.textBox1.Text = this.textBox1.Text + "a"; 41 } 42 43 private void pictureBox2_Click(object sender, EventArgs e) 44 { 45 this.textBox1.Text = this.textBox1.Text + "b"; 46 } 47 48 private void pictureBox3_Click(object sender, EventArgs e) 49 { 50 this.textBox1.Text = this.textBox1.Text + "c"; 51 } 52 53 private void pictureBox4_Click(object sender, EventArgs e) 54 { 55 this.textBox1.Text = this.textBox1.Text + "d"; 56 } 57 58 private void pictureBox5_Click(object sender, EventArgs e) 59 { 60 this.textBox1.Text = this.textBox1.Text + "e"; 61 } 62 63 private void pictureBox6_Click(object sender, EventArgs e) 64 { 65 this.textBox1.Text = this.textBox1.Text + "f"; 66 } 67 68 private void pictureBox7_Click(object sender, EventArgs e) 69 { 70 this.textBox1.Text = this.textBox1.Text + "g"; 71 } 72 73 private void pictureBox8_Click(object sender, EventArgs e) 74 { 75 this.textBox1.Text = this.textBox1.Text + "h"; 76 } 77 78 private void pictureBox9_Click(object sender, EventArgs e) 79 { 80 this.textBox1.Text = this.textBox1.Text + "i"; 81 } 82 83 private void pictureBox10_Click(object sender, EventArgs e) 84 { 85 this.textBox1.Text = this.textBox1.Text + "j"; 86 } 87 88 private void pictureBox11_Click(object sender, EventArgs e) 89 { 90 this.textBox1.Text = this.textBox1.Text + "k"; 91 } 92 93 private void pictureBox12_Click(object sender, EventArgs e) 94 { 95 this.textBox1.Text = this.textBox1.Text + "l"; 96 } 97 98 private void pictureBox13_Click(object sender, EventArgs e) 99 {100 this.textBox1.Text = this.textBox1.Text + "m";101 }102 103 private void pictureBox14_Click(object sender, EventArgs e)104 {105 this.textBox1.Text = this.textBox1.Text + "n";106 }107 108 private void pictureBox15_Click(object sender, EventArgs e)109 {110 this.textBox1.Text = this.textBox1.Text + "o";111 }112 113 private void pictureBox16_Click(object sender, EventArgs e)114 {115 this.textBox1.Text = this.textBox1.Text + "p";116 }117 118 private void pictureBox17_Click(object sender, EventArgs e)119 {120 this.textBox1.Text = this.textBox1.Text + "q";121 }122 123 private void pictureBox18_Click(object sender, EventArgs e)124 {125 this.textBox1.Text = this.textBox1.Text + "r";126 }127 128 private void pictureBox19_Click(object sender, EventArgs e)129 {130 this.textBox1.Text = this.textBox1.Text + "s";131 }132 133 private void pictureBox20_Click(object sender, EventArgs e)134 {135 this.textBox1.Text = this.textBox1.Text + "t";136 }137 138 private void pictureBox21_Click(object sender, EventArgs e)139 {140 this.textBox1.Text = this.textBox1.Text + "u";141 }142 143 private void pictureBox22_Click(object sender, EventArgs e)144 {145 this.textBox1.Text = this.textBox1.Text + "v";146 }147 148 private void pictureBox23_Click(object sender, EventArgs e)149 {150 this.textBox1.Text = this.textBox1.Text + "w";151 }152 153 private void pictureBox24_Click(object sender, EventArgs e)154 {155 this.textBox1.Text = this.textBox1.Text + "x";156 }157 158 private void pictureBox25_Click(object sender, EventArgs e)159 {160 this.textBox1.Text = this.textBox1.Text + "y";161 }162 163 private void pictureBox26_Click(object sender, EventArgs e)164 {165 this.textBox1.Text = this.textBox1.Text + "z";166 }167 private void button1_Click(object sender, EventArgs e)168 {169 string text = this.textBox1.Text;170 int index = text.Length - 1;171 if (index >= 0)172 {173 textBox1.Text = text.Remove(index);174 }175 }176 /// <summary>177 /// 按拼音查找歌曲178 /// </summary>179 private void SeleSong() 180 {181 string sql =string.Format("select song_id,song_name,song_url,singer_name from song_info,singer_info" +182 " where song_info.singer_id=singer_info.singer_id and song_ab like '%{0}%'",this.textBox1.Text);183 adapter = new SqlDataAdapter(sql, db.Conntion);184 if (ds.Tables["song_info"]!=null)185 {186 ds.Tables.Clear();187 }188 adapter.Fill(ds, "song_info");189 this.dataGridView1.DataSource = ds.Tables["song_info"];190 }191 private void textBox1_TextChanged(object sender, EventArgs e)192 {193 if (this.textBox1.Text !=string.Empty)194 {195 SeleSong();196 this.dataGridView1.AutoGenerateColumns = false;197 }198 else 199 {200 this.dataGridView1.DataSource = null;201 }202 203 }204 205 private void FrmSpeall_FormClosing(object sender, FormClosingEventArgs e)206 {207 AnimateWindow(this.Handle, 300, FrmMain.AW_SLIDE + FrmMain.AW_VER_POSITIVE);208 }209 210 private void dataGridView1_DoubleClick(object sender, EventArgs e)211 {212 if (this.dataGridView1.SelectedRows[0] != null)213 {214 Song song = new Song();215 song.SongName = this.dataGridView1.SelectedRows[0].Cells[3].Value.ToString();216 song.SongPath = this.dataGridView1.SelectedRows[0].Cells[2].Value.ToString();217 ListSong.AddSong(song);218 MessageBox.Show("添加成功,请在已点歌曲中查看!!!");219 this.Close();220 }221 }
本章未完结待续^^^^^^^
原标题:KTV项目 SQL数据库的应用 结合C#应用窗体
关键词:C#