一：所说的简单的三层构架，就是说没有业务逻辑层，将各层没有放到单独的项目中，解决方案如下：
二：form1.cs的详细代码
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms; namespace product
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
/// <summary>
/// 插入一条数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
product p= new product(); p.Name="红马車";
//p.Number=50;
// p.Price=5165;
p.C_id = ;
productDAL.insertInfor(p); }
/// <summary>
/// 获得所有信息的总条数
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button2_Click(object sender, EventArgs e)
{
int result= productDAL.getInforNum();
MessageBox.Show(result.ToString());
}
/// <summary>
/// 删除指定id的一条数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button3_Click(object sender, EventArgs e)
{
productDAL.delectInfor();
}
/// <summary>
/// 查询指定id的这条信息
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button4_Click(object sender, EventArgs e)
{
product p = new product();
p= productDAL.getInfor();
MessageBox.Show(p.C_id+p.Name+p.Number); }
}
}
三：product.cs的详细代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text; namespace product
{
public class product
{
public int Id {get;set; }
public string Name{get;set;}
public decimal? Price{get;set;}//decimal数据库中,对应vs里的decimal
public int? Number{get;set;}
public int C_id{set;get;} }
}
四：productDAL.cs的详细代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms; namespace product
{
public static class productDAL
{
/// <summary>
/// 方法:将vs里的可空类型转换为数据库认识的可空类型
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
static object toDbValue(object value)
{
if (value == null)
return DBNull.Value;//数据库的空值类型
else
return value;
}
/// <summary>
/// 方法:将数据库的可空类型转换为vs认识的可空类型
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
static object fromDbValue(object value)
{
if (value == DBNull.Value)
return null;//vs里的空值类型
else
return value;
}
/// <summary>
/// 增加一条数据
/// </summary>
public static void insertInfor(product produ)
{
sqlhelper.ExecuteNon(@"insert into product(name,price,number,c_id)values(@name,@price,@number,@c_id)",
new SqlParameter("@name",produ.Name),
new SqlParameter("@price",toDbValue( produ.Price)),
new SqlParameter("@number",toDbValue( produ.Number)),
new SqlParameter("@c_id", produ.C_id));//添加用values,不用value.(@"insert into product(name,price,number,c_id)values(@name,@price,@number,@c_id)"加上@能识别换行符和空格,增加了字符串的长度 }
/// <summary>
/// 返回信息的总条数
/// </summary>
/// <returns></returns>
public static int getInforNum()
{
//int result = sqlhelper.ExecuteNon("select * from product" );
//select * from product和select count(*) from product都是没有受影响的行数
//select count(*) from product用数据库查询就是一个行数,所以返回首行首列就可以了
int result = sqlhelper.ExecuteSca("select count(*) from product");//这里本来是两个参数,那个长度可变参数可以不赋值
return result;
}
/// <summary>
/// 删除指定id的这条数据
/// </summary>
/// <param name="id"></param>
public static void delectInfor(int id)
{
sqlhelper.ExecuteNon("delete product where id=@id",
new SqlParameter("@id",id));//delete,不要写成delect }
/// <summary>
/// 查询指定id的这条数据
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static product getInfor(int id)
{
DataTable table= sqlhelper.datatable("select * from product where id=@id",
new SqlParameter("@id",id)); if (table.Rows.Count <= )
return null;//没有找到这个id的数据,返回null
else if (table.Rows.Count > )
throw new Exception("id重复了");
else
{
product pro = new product();
DataRow row = table.Rows[]; pro.Name = (string)row["name"];
pro.Price = (decimal?)fromDbValue(row["price"]);//表里是可空类型的用double?
pro.Number = (int?)fromDbValue(row["number"]);
pro.C_id = (int)row["c_id"];
return pro;
} }
}
}
五：sqlhelper.cs的详细代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration; namespace product
{
public static class sqlhelper
{
static SqlConnection conn;
static SqlCommand cmd;
/// <summary>
/// 返回受影响的行数
/// </summary>
private readonly static string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
public static int ExecuteNon(string sql,params SqlParameter[] parameter )
{
using (conn = new SqlConnection(constr))
{
conn.Open();
using (cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameter);
int result = cmd.ExecuteNonQuery();
return result; }
} } /// <summary>
///返回结果的首行首列
/// </summary>
/// <param name="sql"></param>
/// <param name="parameter"></param>
/// <returns></returns>
public static int ExecuteSca(string sql, params SqlParameter[] parameter)
{
using (conn = new SqlConnection(constr))
{
conn.Open();
using (cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameter);
int result = (int) cmd.ExecuteScalar();
return result;
}
}
} /// <summary>
/// 返回一个数据流
/// </summary>
/// <param name="sql"></param>
/// <param name="parameter"></param>
/// <returns></returns>
public static object SqldataRea(string sql, params SqlParameter[] parameter)
{
using (conn = new SqlConnection(constr))
{
conn.Open();
using (cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameter);
SqlDataReader sdr= sdr=cmd.ExecuteReader(); return sdr;
}
}
} /// <summary>
/// 返回一个表
/// </summary>
/// <param name="sql"></param>
/// <param name="parameter"></param>
/// <returns></returns>
public static DataTable datatable(string sql, params SqlParameter[] parameter)
{
using (conn = new SqlConnection(constr))
{
conn.Open();
using (cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameter);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds.Tables[];
}
}
}
}
}