sqlServer增删改查简单封装类

1.封装类

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace hsBankZjdzManager.Utils
{
    /// <summary>
    /// sqlserverAPI公共类
    /// </summary>
    public class SQLConnPublicClass
    {
        /// <summary>
        /// 属性连接数据库语句
        /// </summary>
        private string CONNSTR;

        /// <summary>
        /// 有参构造
        /// </summary>
        /// <param name="conStr">属性连接数据库语句</param>
        public SQLConnPublicClass(string conStr)
        {
            CONNSTR = conStr;
        }

        /// <summary>
        /// Connection属性的封装
        /// </summary>
        private SqlConnection _conn;
        public SqlConnection Conn
        {
            get
            {
                if (_conn != null)
                {
                    // 为了安全，连接数据库时如果数据库连接断开或者正在打开，我们都先将其关闭
                    if (_conn.State == ConnectionState.Broken || _conn.State == ConnectionState.Open)
                    {
                        _conn.Close();
                    }
                }
                else
                {
                    // 连接数据库
                    _conn = new SqlConnection(CONNSTR);
                }
                _conn.Open();
                return _conn;
            }
        }

        /// <summary>
        /// Command属性的封装
        /// </summary>
        private SqlCommand _cmd;
        public SqlCommand Cmd
        {
            get
            {
                _cmd = new SqlCommand();
                // 使用Conn的数据库连接
                _cmd.Connection = Conn;
                return _cmd;
            }
        }

        /// <summary>
        /// 查询方法-返回全部结果集
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(string sql)
        {
            SqlCommand cmd = Cmd;
            cmd.CommandText = sql;
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return reader;
            //在调用ExecuteReader()方法完成之后将其关闭
            //关闭代码为：reader.Close();
        }
        /// <summary>
        /// 查询方法-只返回第一行
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public object ExecuteScalar(string sql)
        {
            SqlCommand cmd = Cmd;
            cmd.CommandText = sql;
            object obj = cmd.ExecuteScalar();
            //关闭数据库，返回查询对象
            cmd.Connection.Close();
            return obj;
        }
        /// <summary>
        /// 增、删、改 方法
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql)
        {
            SqlCommand cmd = Cmd;
            cmd.CommandText = sql;
            int result = cmd.ExecuteNonQuery();
            //关闭数据库，返回受影响行数 
            cmd.Connection.Close();
            return result;
        }
    }
}

2.增删改查测试：

using hsBankZjdzManager.Utils;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace hsBankZjdzManager
{
    public partial class Form1 : Form
    {
        /// <summary>
        /// 创建数据库对象
        /// </summary>
        SQLConnPublicClass sQLConnPublicClass = new SQLConnPublicClass("server=192.168.1.169;uid=sa;password=123456;database=chm;");

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                string strQuery = "select * from tUser where FName = 'fmz';";
                SqlDataReader commQueryRead = sQLConnPublicClass.ExecuteReader(strQuery);
                //判断数据表中是否含有数据  
                if (commQueryRead.HasRows)
                {
                    while (commQueryRead.Read())
                    {
                        MessageBox.Show(commQueryRead[0].ToString() + "," + commQueryRead["FName"].ToString() + "," + commQueryRead[2].ToString());
                    }
                }
                else
                {
                    MessageBox.Show("数据表中没有数据");
                }
                commQueryRead.Close();
            }
            catch (SqlException e1)
            {
                MessageBox.Show("错误信息：" + e1.Message, "出现错误");
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                string strQuery = "insert into tUser (FName,FPass,FTypeID,FBan,FNote) values ('test','1',1,0,'测试');";
                int count = sQLConnPublicClass.ExecuteNonQuery(strQuery);
                //判断是否插入成功 
                if (count > 0)
                {
                    MessageBox.Show("数据插入成功");
                }
                else
                {
                    MessageBox.Show("数据插入失败");
                }
            }
            catch (SqlException e1)
            {
                MessageBox.Show("错误信息：" + e1.Message, "出现错误");
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            try
            {
                string strQuery = "update tUser set FNote = 'ceshi' where FName = 'test';";
                int count = sQLConnPublicClass.ExecuteNonQuery(strQuery);
                //判断是否修改成功 
                if (count > 0)
                {
                    MessageBox.Show("数据修改成功");
                }
                else
                {
                    MessageBox.Show("数据修改失败");
                }
            }
            catch (SqlException e1)
            {
                MessageBox.Show("错误信息：" + e1.Message, "出现错误");
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            try
            {
                string strQuery = "delete from tUser where FName = 'test';";
                int count = sQLConnPublicClass.ExecuteNonQuery(strQuery);
                //判断是否删除成功 
                if (count > 0)
                {
                    MessageBox.Show("数据删除成功");
                }
                else
                {
                    MessageBox.Show("数据删除失败");
                }
            }
            catch (SqlException e1)
            {
                MessageBox.Show("错误信息：" + e1.Message, "出现错误");
            }
        }
    }
}