C# + SQL Server 从零到实战：从SQL入门到音乐播放器完整开发之路

前言作为一名C#开发者掌握数据库操作是必不可少的核心技能。本文将从SQL基础开始逐步深入到C#与SQL Server的完整项目实战通过一个音乐播放器的开发案例带您走完从零到一的完整学习路径。一、SQL入门打好基础Day181.1 什么是SQLSQLStructured Query Language是结构化查询语言用于管理和操作关系型数据库。对于C#开发者来说SQL是与数据库交互的桥梁。1.2 核心SQL语句数据查询SELECTsql-- 查询所有数据 SELECT * FROM Users; -- 条件查询 SELECT * FROM Users WHERE Age 18; -- 排序查询 SELECT * FROM Songs ORDER BY CreateTime DESC;数据插入INSERTsqlINSERT INTO Users (UserName, Password, Email) VALUES (张三, 123456, zhangsanqq.com);数据更新UPDATEsqlUPDATE Users SET Password newpassword WHERE UserName 张三;数据删除DELETEsqlDELETE FROM Users WHERE UserId 1;1.3 数据类型详解SQL类型C#对应类型说明intint整数varchar(n)string可变长度字符串nvarchar(n)stringUnicode字符串datetimeDateTime日期时间bitbool布尔值decimal(p,s)decimal精确数值1.4 C#中执行SQL的基本方式csharpusing System.Data.SqlClient; string connectionString Serverlocalhost;DatabaseMusicDB;User Idsa;Password123456;; using (SqlConnection conn new SqlConnection(connectionString)) { conn.Open(); string sql SELECT * FROM Songs; SqlCommand cmd new SqlCommand(sql, conn); SqlDataReader reader cmd.ExecuteReader(); while (reader.Read()) { Console.WriteLine(reader[SongName].ToString()); } }二、SQL脚本建库建表Day192.1 创建数据库脚本sql-- 创建音乐播放器数据库 CREATE DATABASE MusicPlayerDB; GO USE MusicPlayerDB; GO2.2 创建核心数据表歌曲表SongssqlCREATE TABLE Songs ( SongId INT PRIMARY KEY IDENTITY(1,1), SongName NVARCHAR(100) NOT NULL, Singer NVARCHAR(50) NOT NULL, Album NVARCHAR(100), Duration INT, -- 时长秒 FilePath NVARCHAR(500) NOT NULL, Lyric TEXT, PlayCount INT DEFAULT 0, CreateTime DATETIME DEFAULT GETDATE() );歌单表PlaylistssqlCREATE TABLE Playlists ( PlaylistId INT PRIMARY KEY IDENTITY(1,1), PlaylistName NVARCHAR(50) NOT NULL, Description NVARCHAR(200), CreateTime DATETIME DEFAULT GETDATE() );歌单歌曲关联表PlaylistSongssqlCREATE TABLE PlaylistSongs ( Id INT PRIMARY KEY IDENTITY(1,1), PlaylistId INT FOREIGN KEY REFERENCES Playlists(PlaylistId), SongId INT FOREIGN KEY REFERENCES Songs(SongId) );用户表UserssqlCREATE TABLE Users ( UserId INT PRIMARY KEY IDENTITY(1,1), UserName NVARCHAR(30) UNIQUE NOT NULL, Password NVARCHAR(50) NOT NULL, Email NVARCHAR(100), RegisterTime DATETIME DEFAULT GETDATE() );2.3 C#中执行建表脚本csharppublic class DatabaseHelper { private string connectionString; public DatabaseHelper(string connStr) { connectionString connStr; } public void ExecuteScript(string sqlScript) { using (SqlConnection conn new SqlConnection(connectionString)) { conn.Open(); string[] sqlCommands sqlScript.Split(new[] { GO }, StringSplitOptions.RemoveEmptyEntries); foreach (string command in sqlCommands) { if (!string.IsNullOrWhiteSpace(command)) { using (SqlCommand cmd new SqlCommand(command, conn)) { cmd.ExecuteNonQuery(); } } } } } }三、T-SQL模糊查询Day203.1 LIKE模糊匹配sql-- 查询包含周杰伦的歌曲 SELECT * FROM Songs WHERE Singer LIKE %周杰伦%; -- 查询以我开头的歌曲 SELECT * FROM Songs WHERE SongName LIKE 我%; -- 查询以爱结尾的歌曲 SELECT * FROM Songs WHERE SongName LIKE %爱;3.2 通配符详解通配符说明示例%任意多个字符张% 匹配以张开头的_单个字符张_ 匹配张加一个字[]范围内的字符[李张]% 匹配李或张开头[^]不在范围内的字符[^李张]% 不匹配李或张开头3.3 C#中实现搜索功能csharppublic ListSong SearchSongs(string keyword) { ListSong songs new ListSong(); string sql SELECT * FROM Songs WHERE SongName LIKE keyword OR Singer LIKE keyword; using (SqlConnection conn new SqlConnection(connectionString)) { conn.Open(); using (SqlCommand cmd new SqlCommand(sql, conn)) { cmd.Parameters.AddWithValue(keyword, $%{keyword}%); using (SqlDataReader reader cmd.ExecuteReader()) { while (reader.Read()) { songs.Add(new Song { SongId (int)reader[SongId], SongName reader[SongName].ToString(), Singer reader[Singer].ToString() }); } } } } return songs; }四、高级查询Day214.1 多表连接查询JOINsql-- 查询歌单中的歌曲列表 SELECT p.PlaylistName, s.SongName, s.Singer FROM Playlists p INNER JOIN PlaylistSongs ps ON p.PlaylistId ps.PlaylistId INNER JOIN Songs s ON ps.SongId s.SongId WHERE p.PlaylistId 1;4.2 分组统计GROUP BYsql-- 统计每位歌手的歌曲数量 SELECT Singer, COUNT(*) AS SongCount FROM Songs GROUP BY Singer ORDER BY SongCount DESC;4.3 分页查询OFFSET FETCHsql-- SQL Server 2012 分页 SELECT * FROM Songs ORDER BY SongId OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;4.4 C#中实现高级查询csharppublic class AdvancedQueryService { public ListSongPlaylistInfo GetSongsByPlaylist(int playlistId) { string sql SELECT s.SongId, s.SongName, s.Singer, p.PlaylistName FROM Songs s INNER JOIN PlaylistSongs ps ON s.SongId ps.SongId INNER JOIN Playlists p ON ps.PlaylistId p.PlaylistId WHERE p.PlaylistId PlaylistId; // 执行查询并返回结果 // ... } public DataTable GetPagedSongs(int pageIndex, int pageSize) { string sql SELECT * FROM Songs ORDER BY SongId OFFSET Offset ROWS FETCH NEXT PageSize ROWS ONLY; using (SqlConnection conn new SqlConnection(connectionString)) { using (SqlDataAdapter adapter new SqlDataAdapter(sql, conn)) { adapter.SelectCommand.Parameters.AddWithValue(Offset, (pageIndex - 1) * pageSize); adapter.SelectCommand.Parameters.AddWithValue(PageSize, pageSize); DataTable dt new DataTable(); adapter.Fill(dt); return dt; } } } }五、音乐播放器实战Day225.1 项目结构textMusicPlayer/ ├── Models/ # 数据模型 │ ├── Song.cs │ ├── Playlist.cs │ └── User.cs ├── DAL/ # 数据访问层 │ ├── SongDAL.cs │ ├── PlaylistDAL.cs │ └── DatabaseHelper.cs ├── BLL/ # 业务逻辑层 │ ├── SongManager.cs │ └── PlaylistManager.cs ├── UI/ # 界面层 │ ├── MainForm.cs │ ├── PlayerForm.cs │ └── SearchForm.cs └── Utils/ # 工具类 └── AudioPlayer.cs5.2 数据模型定义csharp// Models/Song.cs public class Song { public int SongId { get; set; } public string SongName { get; set; } public string Singer { get; set; } public string Album { get; set; } public int Duration { get; set; } public string FilePath { get; set; } public string Lyric { get; set; } public int PlayCount { get; set; } public DateTime CreateTime { get; set; } // 格式化时长显示 public string DurationText ${Duration / 60:D2}:{Duration % 60:D2}; } // Models/Playlist.cs public class Playlist { public int PlaylistId { get; set; } public string PlaylistName { get; set; } public string Description { get; set; } public ListSong Songs { get; set; } public DateTime CreateTime { get; set; } }5.3 数据访问层实现csharp// DAL/SongDAL.cs public class SongDAL { private string connectionString; public SongDAL(string connStr) { connectionString connStr; } // 获取所有歌曲 public ListSong GetAllSongs() { ListSong songs new ListSong(); string sql SELECT * FROM Songs ORDER BY SongId; using (SqlConnection conn new SqlConnection(connectionString)) { conn.Open(); using (SqlCommand cmd new SqlCommand(sql, conn)) { using (SqlDataReader reader cmd.ExecuteReader()) { while (reader.Read()) { songs.Add(MapToSong(reader)); } } } } return songs; } // 添加歌曲 public int AddSong(Song song) { string sql INSERT INTO Songs (SongName, Singer, Album, Duration, FilePath, Lyric) VALUES (SongName, Singer, Album, Duration, FilePath, Lyric); SELECT SCOPE_IDENTITY();; using (SqlConnection conn new SqlConnection(connectionString)) { conn.Open(); using (SqlCommand cmd new SqlCommand(sql, conn)) { cmd.Parameters.AddWithValue(SongName, song.SongName); cmd.Parameters.AddWithValue(Singer, song.Singer); cmd.Parameters.AddWithValue(Album, (object)song.Album ?? DBNull.Value); cmd.Parameters.AddWithValue(Duration, song.Duration); cmd.Parameters.AddWithValue(FilePath, song.FilePath); cmd.Parameters.AddWithValue(Lyric, (object)song.Lyric ?? DBNull.Value); return Convert.ToInt32(cmd.ExecuteScalar()); } } } // 更新播放次数 public void UpdatePlayCount(int songId) { string sql UPDATE Songs SET PlayCount PlayCount 1 WHERE SongId SongId; using (SqlConnection conn new SqlConnection(connectionString)) { conn.Open(); using (SqlCommand cmd new SqlCommand(sql, conn)) { cmd.Parameters.AddWithValue(SongId, songId); cmd.ExecuteNonQuery(); } } } // 删除歌曲 public bool DeleteSong(int songId) { string sql DELETE FROM Songs WHERE SongId SongId; using (SqlConnection conn new SqlConnection(connectionString)) { conn.Open(); using (SqlCommand cmd new SqlCommand(sql, conn)) { cmd.Parameters.AddWithValue(SongId, songId); return cmd.ExecuteNonQuery() 0; } } } private Song MapToSong(SqlDataReader reader) { return new Song { SongId (int)reader[SongId], SongName reader[SongName].ToString(), Singer reader[Singer].ToString(), Album reader[Album]?.ToString(), Duration (int)reader[Duration], FilePath reader[FilePath].ToString(), Lyric reader[Lyric]?.ToString(), PlayCount (int)reader[PlayCount], CreateTime (DateTime)reader[CreateTime] }; } }5.4 歌单管理功能csharp// DAL/PlaylistDAL.cs public class PlaylistDAL { private string connectionString; public PlaylistDAL(string connStr) { connectionString connStr; } // 创建歌单 public int CreatePlaylist(string name, string description) { string sql INSERT INTO Playlists (PlaylistName, Description) VALUES (Name, Description); SELECT SCOPE_IDENTITY();; using (SqlConnection conn new SqlConnection(connectionString)) { conn.Open(); using (SqlCommand cmd new SqlCommand(sql, conn)) { cmd.Parameters.AddWithValue(Name, name); cmd.Parameters.AddWithValue(Description, (object)description ?? DBNull.Value); return Convert.ToInt32(cmd.ExecuteScalar()); } } } // 添加歌曲到歌单 public bool AddSongToPlaylist(int playlistId, int songId) { string sql IF NOT EXISTS (SELECT 1 FROM PlaylistSongs WHERE PlaylistId PlaylistId AND SongId SongId) BEGIN INSERT INTO PlaylistSongs (PlaylistId, SongId) VALUES (PlaylistId, SongId) END; using (SqlConnection conn new SqlConnection(connectionString)) { conn.Open(); using (SqlCommand cmd new SqlCommand(sql, conn)) { cmd.Parameters.AddWithValue(PlaylistId, playlistId); cmd.Parameters.AddWithValue(SongId, songId); return cmd.ExecuteNonQuery() 0; } } } // 获取歌单中的所有歌曲 public ListSong GetPlaylistSongs(int playlistId) { ListSong songs new ListSong(); string sql SELECT s.* FROM Songs s INNER JOIN PlaylistSongs ps ON s.SongId ps.SongId WHERE ps.PlaylistId PlaylistId ORDER BY ps.Id; using (SqlConnection conn new SqlConnection(connectionString)) { conn.Open(); using (SqlCommand cmd new SqlCommand(sql, conn)) { cmd.Parameters.AddWithValue(PlaylistId, playlistId); using (SqlDataReader reader cmd.ExecuteReader()) { while (reader.Read()) { songs.Add(MapToSong(reader)); } } } } return songs; } }5.5 音乐播放器主界面逻辑csharp// UI/MainForm.cs public partial class MainForm : Form { private SongDAL songDAL; private PlaylistDAL playlistDAL; private ListSong currentPlaylist; private int currentIndex -1; private AudioPlayer audioPlayer; public MainForm() { InitializeComponent(); string connStr Serverlocalhost;DatabaseMusicPlayerDB;Integrated SecurityTrue;; songDAL new SongDAL(connStr); playlistDAL new PlaylistDAL(connStr); audioPlayer new AudioPlayer(); LoadSongs(); } private void LoadSongs() { currentPlaylist songDAL.GetAllSongs(); dgvSongs.DataSource currentPlaylist; dgvSongs.Columns[SongId].Visible false; dgvSongs.Columns[FilePath].Visible false; dgvSongs.Columns[Lyric].Visible false; dgvSongs.Columns[DurationText].HeaderText 时长; } private void btnPlay_Click(object sender, EventArgs e) { if (dgvSongs.SelectedRows.Count 0) { currentIndex dgvSongs.SelectedRows[0].Index; PlayCurrentSong(); } } private void PlayCurrentSong() { if (currentIndex 0 currentIndex currentPlaylist.Count) { Song song currentPlaylist[currentIndex]; audioPlayer.Play(song.FilePath); lblNowPlaying.Text $正在播放{song.SongName} - {song.Singer}; songDAL.UpdatePlayCount(song.SongId); // 显示歌词 ShowLyric(song.Lyric); } } private void ShowLyric(string lyric) { if (!string.IsNullOrEmpty(lyric)) { txtLyric.Text lyric; } else { txtLyric.Text 暂无歌词; } } private void btnSearch_Click(object sender, EventArgs e) { string keyword txtSearch.Text.Trim(); if (!string.IsNullOrEmpty(keyword)) { var results currentPlaylist.Where(s s.SongName.Contains(keyword) || s.Singer.Contains(keyword)).ToList(); dgvSongs.DataSource results; } else { LoadSongs(); } } }5.6 音频播放器封装csharp// Utils/AudioPlayer.cs using WMPLib; // 需要添加 Windows Media Player 引用 public class AudioPlayer { private WindowsMediaPlayer player; public AudioPlayer() { player new WindowsMediaPlayer(); } public void Play(string filePath) { player.URL filePath; player.controls.play(); } public void Pause() { player.controls.pause(); } public void Stop() { player.controls.stop(); } public void Resume() { player.controls.play(); } public void SetVolume(int volume) { player.settings.volume volume; } public int GetVolume() { return player.settings.volume; } public double GetCurrentPosition() { return player.controls.currentPosition; } public double GetDuration() { return player.currentMedia.duration; } public void SetPosition(double position) { player.controls.currentPosition position; } }六、总结与进阶建议6.1 学习路径回顾天数内容核心知识点Day18SQL入门SELECT、INSERT、UPDATE、DELETEDay19SQL脚本建库建表CREATE DATABASE、CREATE TABLE、约束Day20T-SQL模糊查询LIKE、通配符、参数化查询Day21高级查询JOIN、GROUP BY、分页、子查询Day22音乐播放器完整项目实战6.2 进阶方向使用ORM框架学习Entity Framework Core减少SQL编写异步编程使用async/await提升性能MVVM模式使用WPF开发更专业的桌面应用网络播放集成在线音乐API推荐系统基于播放历史实现智能推荐6.3 项目源码获取本文涉及的音乐播放器完整源码包含数据库脚本和C#项目文件可通过相关渠道获取。通过这五天的系统学习您已经从SQL零基础成长到能够独立完成C#音乐播放器项目的水平。数据库操作是C#开发的核心技能掌握好这些知识将为后续的Web开发、移动开发打下坚实基础。持续练习不断实践您一定能成为优秀的C#开发者