当前位置： 首页 > news >正文

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

news 2026/4/19 1:35:18

前言

作为一名C#开发者，掌握数据库操作是必不可少的核心技能。本文将从SQL基础开始，逐步深入到C#与SQL Server的完整项目实战，通过一个音乐播放器的开发案例，带您走完从零到一的完整学习路径。

一、SQL入门：打好基础（Day18）

1.1 什么是SQL？

SQL（Structured Query Language）是结构化查询语言，用于管理和操作关系型数据库。对于C#开发者来说，SQL是与数据库交互的桥梁。

1.2 核心SQL语句

数据查询（SELECT）

sql

-- 查询所有数据 SELECT * FROM Users; -- 条件查询 SELECT * FROM Users WHERE Age > 18; -- 排序查询 SELECT * FROM Songs ORDER BY CreateTime DESC;
数据插入（INSERT）

sql

INSERT INTO Users (UserName, Password, Email) VALUES ('张三', '123456', 'zhangsan@qq.com');
数据更新（UPDATE）

sql

UPDATE Users SET Password = 'newpassword' WHERE UserName = '张三';
数据删除（DELETE）

sql

DELETE 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的基本方式

csharp

using System.Data.SqlClient; string connectionString = "Server=localhost;Database=MusicDB;User Id=sa;Password=123456;"; 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脚本：建库建表（Day19）

2.1 创建数据库脚本

sql

-- 创建音乐播放器数据库 CREATE DATABASE MusicPlayerDB; GO USE MusicPlayerDB; GO

2.2 创建核心数据表

歌曲表（Songs）

sql

CREATE 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() );
歌单表（Playlists）

sql

CREATE TABLE Playlists ( PlaylistId INT PRIMARY KEY IDENTITY(1,1), PlaylistName NVARCHAR(50) NOT NULL, Description NVARCHAR(200), CreateTime DATETIME DEFAULT GETDATE() );
歌单歌曲关联表（PlaylistSongs）

sql

CREATE TABLE PlaylistSongs ( Id INT PRIMARY KEY IDENTITY(1,1), PlaylistId INT FOREIGN KEY REFERENCES Playlists(PlaylistId), SongId INT FOREIGN KEY REFERENCES Songs(SongId) );
用户表（Users）

sql

CREATE 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#中执行建表脚本

csharp

public 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模糊查询（Day20）

3.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#中实现搜索功能

csharp

public List<Song> SearchSongs(string keyword) { List<Song> songs = new List<Song>(); 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; }

四、高级查询（Day21）

4.1 多表连接查询（JOIN）

sql

-- 查询歌单中的歌曲列表 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 BY）

sql

-- 统计每位歌手的歌曲数量 SELECT Singer, COUNT(*) AS SongCount FROM Songs GROUP BY Singer ORDER BY SongCount DESC;

4.3 分页查询（OFFSET FETCH）

sql

-- SQL Server 2012+ 分页 SELECT * FROM Songs ORDER BY SongId OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

4.4 C#中实现高级查询

csharp

public class AdvancedQueryService { public List<SongPlaylistInfo> 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; } } } }

五、音乐播放器实战（Day22）

5.1 项目结构

text

MusicPlayer/ ├── 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.cs

5.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 List<Song> 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 List<Song> GetAllSongs() { List<Song> songs = new List<Song>(); 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 List<Song> GetPlaylistSongs(int playlistId) { List<Song> songs = new List<Song>(); 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 List<Song> currentPlaylist; private int currentIndex = -1; private AudioPlayer audioPlayer; public MainForm() { InitializeComponent(); string connStr = "Server=localhost;Database=MusicPlayerDB;Integrated Security=True;"; 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、DELETE
Day19SQL脚本建库建表CREATE DATABASE、CREATE TABLE、约束
Day20T-SQL模糊查询LIKE、通配符、参数化查询
Day21高级查询JOIN、GROUP BY、分页、子查询
Day22音乐播放器完整项目实战

6.2 进阶方向

  1. 使用ORM框架：学习Entity Framework Core，减少SQL编写

  2. 异步编程：使用async/await提升性能

  3. MVVM模式：使用WPF开发更专业的桌面应用

  4. 网络播放：集成在线音乐API

  5. 推荐系统：基于播放历史实现智能推荐

6.3 项目源码获取

本文涉及的音乐播放器完整源码，包含数据库脚本和C#项目文件，可通过相关渠道获取。

通过这五天的系统学习，您已经从SQL零基础成长到能够独立完成C#音乐播放器项目的水平。数据库操作是C#开发的核心技能，掌握好这些知识，将为后续的Web开发、移动开发打下坚实基础。持续练习，不断实践，您一定能成为优秀的C#开发者！

http://www.jsqmd.com/news/663458/

相关文章：

  • 反射光电管ITR9909驱动能力不够？试试这颗达林顿管BC517
  • Winhance中文版：Windows系统优化的终极解决方案，免费提升电脑性能与个性化体验
  • 从SX1278到SX1262：手把手教你升级老旧LoRa模块，并实测功耗与传输距离变化
  • WorkshopDL：免费下载Steam创意工坊模组的终极解决方案
  • 构建高精度无人机编队控制仿真系统的工程实践
  • 做 GEO 之前要准备哪些资料：基础信息、内容素材与信号资产清单
  • 告别UNet！用Mirror Networking在Unity 2022 LTS里快速搭建你的第一个多人坦克对战Demo
  • 仅限奇点大会注册参会者获取的检测模型权重+训练数据集（含127万对人工标注克隆样本）：AI代码克隆检测从入门到合规上线的7天闭环路径
  • W5500 MACRAW模式实战：在ESP32上抓取并解析原始以太网数据包
  • 别再用Excel硬扛了！用Python的sklearn库5分钟搞定PCA降维（附实战代码）
  • WIN7最新的Chrome内核浏览器
  • 表单django
  • STM32 HAL库RTC配置实战：从CubeMX到解决F1系列掉电日期丢失
  • 5大核心功能揭秘：AKShare财经数据获取的完整实战指南
  • Windows右键菜单的“数字园艺师“：ContextMenuManager深度解析与实战手册
  • 武昌老酒回收电话
  • 避坑指南：在Arduino IDE 1.8.x中编译STM32 Marlin固件报错‘attachInterrupt’的解决方法
  • SSH Client推荐集
  • 手办管理系统|基于springboot + vue手办商城系统(源码+数据库+文档)
  • HC32F460 FPU实战：从零开启硬件浮点加速
  • 从心跳到监护——CANOpen网络管理实战解析
  • 实用CLI工具：命令行下的高效选择
  • LCD1602自定义字符避坑指南：为什么你的5x7点阵汉字显示不全？
  • Android应用调试利器——Fiddler抓包实战全解析
  • 技术挑战：模块交互中的条件替换异常分析与解决
  • 如何决定是否需要创建索引_数据区分度与基数Cardinality计算
  • 我受够了。即梦一个视频要排两天队，橘子AI三分钟搞定。
  • 结合上篇文“怪奇物语物流假设”的对死亡搁浅3的构想
  • 数据库开发总结
  • 改变人类世界算法与方程