零基础开发--歌曲管理系统
本文主要是从头开始讲如何创建一个项目，本文以创建一个歌曲管理系统为例。
首先创建数据库：MyMusic（我的音乐）
其中添加表：SongInformation（歌曲信息表）
if DB_ID('MyMusic') is not null
drop database MyMusic
go
create database MyMusic --创建MyMusic（我的音乐）数据库
on
(
name=MyMusic,
filename='D:\CS架构\学习\窗体\DB\MyMusic.mdf'
)
--打开数据库
use MyMusic
--创建表歌曲
if OBJECT_ID('SongInformation') is not null
drop table SongInformation --歌曲信息表
go
create table SongInformation
(
Songid int primary key identity(10000,1), --编号
SongName varchar(50) not null, --歌名
Singer varchar(50) not null, --演唱者
Album varchar(50) --专辑
)
--查询表SongInformation
select *from SongInformation
--添加测试数据
insert SongInformation select '演员','薛之谦','绅士'union
select '尽头','赵方倩','音阙诗听'union
select '当你','王心凌','遇上爱'union
select '七里香','周杰伦','七里香'union
select '微微一笑很倾城','杨洋','微微一笑很倾城'union
select '岁月神偷','金玟岐','金玟岐作品集'union
select '带你去旅行','校长','带你去旅行'
创建主窗口：FrmMain(歌曲信息管理系统
创建窗口：歌曲修改（增加）窗口FrmModifySong
准备工作 --建立帮助类：DBHelper
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data; using System.Data.SqlClient; namespace 窗口 { public class DBHelper { public static string ConStr = "server=.;uid=sa;pwd=sa;database=MyMusic"; public static SqlConnection con = null; #region 创建连接对象 public static SqlConnection GetConnection() { if (con == null || con.ConnectionString == "") { con = new SqlConnection(ConStr); } return con; } #endregion #region 打开连接 public static void OpenConnection() { if (con.State == ConnectionState.Closed) { con.Open(); } } #endregion #region 关闭连接 public static void CloseConnection() { if (con.State == ConnectionState.Open) { con.Close(); } } #endregion #region 查询多行多列的值 public static SqlDataReader ExecuteReader(string sql, params SqlParameter [] para) { SqlConnection con = GetConnection(); OpenConnection(); SqlCommand com = new SqlCommand(sql, con); com.Parameters.AddRange(para); SqlDataReader dr = com.ExecuteReader(); return dr; } #endregion #region 动作查询 public static int ExecuteNonQuery(string sql, params SqlParameter[] para) { SqlConnection con = GetConnection(); OpenConnection(); SqlCommand com = new SqlCommand(sql, con); com.Parameters.AddRange(para); int n = com.ExecuteNonQuery(); CloseConnection(); return n; } #endregion } }
建立实体类：Song（对应数据库中的表SongInformation）
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace 窗口 { public class Song { public int Songid { get; set; } public string SongName { get; set; } public string Singer { get; set; } public string Album { get; set; } } }
然后实现窗体加载功能（将数据加载到DataGirdView中）
#region 将后台信息加载到网格 private void LoadDOV(string sql) { if (sql == "") { sql = "select*from SongInformation"; } SqlConnection con = new SqlConnection(DBHelper.ConStr); SqlDataAdapter sda = new SqlDataAdapter(sql, con); DataTable dt = new DataTable(); sda.Fill(dt); dgvSong.DataSource = dt; } #endregion #region 窗体加载事件方法 private void FrmMain_Load(object sender, EventArgs e) { LoadDOV(""); } #endregion
实现查询功能：
#region 查询事件方法 private void btnQuery_Click(object sender, EventArgs e) { string sql = "select*from SongInformation where 1=1"; if (txtSongName.Text != "") { sql += "and SongName like '%" + txtSongName.Text + "%'"; } if (txtSinger.Text != "") { sql += "and Singer like '%" + txtSinger.Text + "%'"; } LoadDOV(sql); } #endregion
最后！也是难点！传参，将DataGridView中的数据传到FrmModifySong窗口中对应的Textbox中
首先创建实体操作类SongManage
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data.SqlClient; namespace 窗口 { public class SongManage { //传参 public static List<Song> SelectCarsAll() { List<Song> song = new List<Song>(); string sql = "select*from SongInformation"; SqlDataReader dr = DBHelper.ExecuteReader(sql); while (dr.Read()) { Song songs = new Song(); songs.Songid = Convert.ToInt32(dr["Songid"]); songs.SongName = Convert.ToString(dr["SongName"]); songs.Singer = Convert.ToString(dr["Singer"]); songs.Album = Convert.ToString(dr["Album"]); song.Add(songs); } dr.Close(); DBHelper.CloseConnection(); return song; } //修改 public static Song SelectCarsByCarId(int Songid) { Song song = null; string sql = "select * from SongInformation where Songid=" + Songid; SqlDataReader dr = DBHelper.ExecuteReader(sql); if (dr.Read()) { song = new Song(); song.Songid = Convert.ToInt32(dr["Songid"]); song.SongName = Convert.ToString(dr["SongName"]); song.Singer = Convert.ToString(dr["Singer"]); song.Album = Convert.ToString(dr["Album"]); } dr.Close(); DBHelper.CloseConnection(); return song; } //添加 public static int InsertSong(Song song) { string sql = string.Format("insert SongInformation (SongName,Singer,Album) values('{0}','{1}','{2}')", song.SongName, song.Singer, song.Album); return DBHelper.ExecuteNonQuery(sql); } //更新 public static int UpdateSong(Song song) { string sql = "update SongInformation set SongName=@SongName,Singer=@Singer,Album=@Album where Songid=@Songid"; return DBHelper.ExecuteNonQuery(sql,new SqlParameter[] { new SqlParameter("@SongName",song.SongName), new SqlParameter("@Singer",song.Singer), new SqlParameter("@Album",song.Album), new SqlParameter("@SongId",song.Songid), }); } } }
然后回到主窗口FrmMain中创建修改、添加的方法
#region 修改 /// <summary> /// 修改事件方法 /// </summary> private void TsmModify_Click(object sender, EventArgs e) { if (dgvSong.SelectedRows.Count > 0) { int Songid = (int)dgvSong.SelectedRows[0].Cells["Songid"].Value; FrmModifySong frm = new FrmModifySong(); frm.Songid = Songid; frm.ShowDialog(); } } #endregion #region 添加事件方法 private void tsmAdd_Click(object sender, EventArgs e) { FrmModifySong frm = new FrmModifySong(); frm.Show(); } #endregion
然后回到歌曲添加（删除）窗口FrmModifySong实现保存功能和取消功能
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; namespace 窗口 { public partial class FrmModifySong : Form { public int Songid { get; set; } private string type = "添加"; public FrmModifySong() { InitializeComponent(); } #region 取消按钮事件方法 private void btnCancel_Click(object sender, EventArgs e) { this.Close(); } #endregion #region 保存按钮事件方法 private void btnSave_Click(object sender, EventArgs e) { Song song = new Song { SongName=txtSongName.Text, Singer=txtSinger.Text, Album=txtAlbum.Text }; int n = 0; if (type == "添加") { n = SongManage.InsertSong(song); } else { song.Songid = Songid; n = SongManage.UpdateSong(song); } if (n > 0) { MessageBox.Show(type + "成功！"); } else { MessageBox.Show(type + "失败！"); } } #endregion private void FrmModifySong_Load(object sender, EventArgs e) { if (Songid != 0) { type = "修改"; txtSongid.ReadOnly = true; Song song = SongManage.SelectCarsByCarId(Songid); if (song != null) { txtSongid.Text = song.Songid.ToString(); txtSongName.Text = song.SongName; txtSinger.Text = song.Singer; txtAlbum.Text = song.Album; } this.Text = "修改歌曲信息"; } else { txtSongid.ReadOnly = false; this.Text = "添加歌曲信息"; } } } }
最后实现删除功能
#region 删除事件方法 //删除事件方法 private void TsmDelete_Click(object sender, EventArgs e) { int Songid = (int)dgvSong.SelectedRows[0].Cells[0].Value; string sql = "delete SongInformation where SongId=" + Songid; int n = DBHelper.ExecuteNonQuery(sql); if (n > 0) { MessageBox.Show("删除成功", "删除提示"); LoadDOV(""); } else { MessageBox.Show("删除失败", "删除提示"); } } #endregion
OK，一个简单的项目就这么完成了！
