使用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];
                }
            }
        }
    }
}