Web.config
<connectionStrings>
<add name="connStr" connectionString="server=.;database=MyWeb;uid=sa;pwd=123"/>
</connectionStrings>
SqlHelper.cs
/// <summary>
/// SqlHelper(数据库帮助类)
/// </summary>
public static class SqlHelper
{
/// <summary>
/// SQL连接字符串
/// </summary>
private static string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
/// <summary>
/// 获取数据库列表数据
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="ps">SQL参数</param>
/// <returns>返回DataTable</returns>
public static DataTable GetList(string sql, params SqlParameter[] ps)
{
//创建Sql连接对象(参数:连接字符串)
using (SqlConnection conn = new SqlConnection(connStr))
{
//填充DataSet和更新SQL Server数据库(一组数据命令,一个数据库连接)
SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
//设置存储过程,在数据源中选择记录
sda.SelectCommand.Parameters.AddRange(ps);
//表示内存中数据的一个表
DataTable dt = new DataTable();
//添加或刷新指定范围中的行DataSet,以匹配中使用数据源的那些DataTable名称
sda.Fill(dt);
//返回表数据
return dt;
}
}
/// <summary>
/// 执行SQL查询,并返回受影响的行数
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="ps">SQL参数</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteNonQuery(string sql, params SqlParameter[] ps)
{
//创建Sql连接对象(参数:连接字符串)
using (SqlConnection conn = new SqlConnection(connStr))
{
//表示对SQL Server数据库执行Sql语句或存储过程
SqlCommand cmd = new SqlCommand(sql, conn);
//对命令传入参数
cmd.Parameters.AddRange(ps);
//打开数据库连接
conn.Open();
//执行Sql语句,返回受影响的行数
return cmd.ExecuteNonQuery();
}
}
/// <summary>
/// 执行查询,返回结果集中第一行的第一列
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="ps">SQL参数</param>
/// <returns>返回结果集第一行的第一列</returns>
public static object ExecuteScalar(string sql, params SqlParameter[] ps)
{
//创建Sql连接对象(参数:连接字符串)
using (SqlConnection conn = new SqlConnection(connStr))
{
//对SQL Server数据库执行Sql语句
SqlCommand cmd = new SqlCommand(sql, conn);
//传入命令参数
cmd.Parameters.AddRange(ps);
//打开数据库
conn.Open();
//返回结果集第一行的第一列
return cmd.ExecuteScalar();
}
}
}
CantusDal.cs
/// <summary>
/// Lrc歌词数据层
/// </summary>
public class CantusDal : ICantusDal
{
/// <summary>
/// 获取数据库列表数据.Dal
/// </summary>
/// <returns></returns>
public List<Cantus> GetList()
{
//sql查询语句
string sql = "select * from cantus";
//创建内存数据库表,接收查询到的数据
DataTable dt = SqlHelper.GetList(sql);
//创建List<Cantus>泛型集合
List<Cantus> list = new List<Cantus>();
//循环遍历(DataTable每一行数据)
foreach (DataRow row in dt.Rows)
{
//list集合添加数据单元
list.Add(new Cantus()
{
Id = Convert.ToInt32(row["Id"]),//Id(id)
Serial = Convert.ToByte(row["Serial"]),//Serial(曲目)
Song = row["Song"].ToString(),//Song(歌词)
Album = row["Album"].ToString(),//Album(专辑)
Number = (short)row["Number"],//Number(张数)
Date = Convert.ToDateTime(row["Date"]),//Date(年份)
Writer = row["Writer"].ToString(),//Writer(作词)
Composer = row["Composer"].ToString(),//Composer(作曲)
Arranger = row["Arranger"].ToString(),//Arranger(编曲)
Singing = row["Singing"].ToString(),//Singing(原唱)
Lyric = row["Lyric"].ToString()//Lyric(歌词)
});
}
return list;
}
/// <summary>
/// 添加数据
/// </summary>
/// <returns></returns>
public bool Add(Cantus ct)
{
string sql = "insert into cantus values(@Serial,@Song,@Album,@Number,@Date,@Writer,@Composer,@Arranger,@Singing,@Lyric)";
SqlParameter[] ps = {
new SqlParameter("@Serial",ct.Serial),
new SqlParameter("@Song",ct.Song),
new SqlParameter("@Album",ct.Album),
new SqlParameter("@Number",ct.Number),
new SqlParameter("@Date",ct.Date),
new SqlParameter("@Writer",ct.Writer),
new SqlParameter("@Composer",ct.Composer),
new SqlParameter("@Arranger",ct.Arranger),
new SqlParameter("@Singing",ct.Singing),
new SqlParameter("@Lyric",ct.Lyric),
};
return SqlHelper.ExecuteNonQuery(sql, ps) > ;
}
}
CantusBll.cs
/// <summary>
/// 业务逻辑层.BLL
/// </summary>
public class CantusBll : ICantusBll
{
/// <summary>
/// 声明Dal操作对象
/// </summary>
private CantusDal ctDal;
/// <summary>
/// 构造函数()
/// </summary>
public CantusBll()
{
//创建Dal操作对象
ctDal = new CantusDal();
}
/// <summary>
/// 返回数据层获取的数据
/// </summary>
/// <returns></returns>
public List<Cantus> GetList()
{
return ctDal.GetList();
}
/// <summary>
/// 添加数据
/// </summary>
/// <param name="ct"></param>
/// <returns></returns>
public bool Add(Cantus ct)
{
return ctDal.Add(ct);
}
}
BackController.cs
public class BackController : Controller
{
CantusBll ctBll = new CantusBll();
// GET: Back
public ActionResult Index()
{
List<Cantus> ct = ctBll.GetList();
JavaScriptSerializer js = new JavaScriptSerializer();
string result = js.Serialize(ct);
Response.Write(result);
return View();
}
}