使用sqlhelper的简单增删改查
一：所说的简单的三层构架，就是说没有业务逻辑层，将各层没有放到单独的项目中，解决方案如下：
二：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 = 2; 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(32); } /// <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(18); 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 <= 0) return null;//没有找到这个id的数据,返回null else if (table.Rows.Count > 1) throw new Exception("id重复了"); else { product pro = new product(); DataRow row = table.Rows[0]; 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[0]; } } } } }
