使用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
{
///
/// 方法:将vs里的可空类型转换为数据库认识的可空类型
///
///
///
static object toDbValue(object value)
{
if (value == null)
return DBNull.Value;//数据库的空值类型
else
return value;
}
///
/// 方法:将数据库的可空类型转换为vs认识的可空类型
///
///
///
static object fromDbValue(object value)
{
if (value == DBNull.Value)
return null;//vs里的空值类型
else
return value;
}
///
/// 增加一条数据
///
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;
///
/// 返回受影响的行数
///
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];
            }
        }
    }
}

}