sqlserver数据库查询

 帮助类

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

namespace ConsoleApp2
{
    public static class SqlServer
    {
        #region 样本
        private static readonly string defaultConnectString = "Server=.;DataBase=zjJunHaoCarChain;uid=sa;pwd=123456";
        //"data source=.;initial catalog=zjJunHaoCarChain;user id=sa;pwd=123456";

        #endregion
        #region 变量
        private static SqlConnection _con = null;
        public static string _constr = "Server=.;DataBase=zjJunHaoCarChain;uid=sa;pwd=123456";
        //"data source=.;initial catalog=zjJunHaoCarChain;user id=sa;pwd=123456";

        #endregion
        #region 属性

        public static string constr
        {
            get
            {
                if (_constr == null || _constr.Equals(String.Empty))
                {
                    _constr = defaultConnectString;
                }
                return _constr;
            }
            set
            {
                _constr = value;
            }
        }

        /// <summary>  
        /// 获取或设置数据库连接对象  
        /// </summary>  
        public static SqlConnection Con
        {
            get
            {

                if (SqlServer._con == null)
                {
                    SqlServer._con = new SqlConnection();
                }
                if (SqlServer._con.ConnectionString == null || SqlServer._con.ConnectionString.Equals(string.Empty))
                {
                    SqlServer._con.ConnectionString = SqlServer.constr;
                }
                return SqlServer._con;
            }
            set
            {
                SqlServer._con = value;
            }
        }
        #endregion

        /// <summary>
        /// 获取数据表
        /// </summary>
        /// <param name="commandText">select命令</param>
        /// <param name="param">参数表</param>
        /// <returns></returns>
        #region MyRegion
        public static DataTable GetDataTable(string commandText, params SqlParameter[] param)
        {
            DataTable result = new DataTable();
            try
            {
                using (SqlCommand cmd = new SqlCommand(commandText, SqlServer.Con))
                {
                    if (param != null)
                        cmd.Parameters.AddRange(param);
                    try
                    {
                        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                        adapter.Fill(result);
                    }
                    catch (Exception ex)
                    {
                        result = null;
                    }
                }
            }
            finally
            {
                if (SqlServer.Con.State != ConnectionState.Closed)
                {
                    SqlServer.Con.Close();
                }
            }
            return result;
        }
        #endregion


        /// <summary>  
        /// 执行不查询的数据库操作  
        /// </summary>  
        /// <param name="commandText">Oracle语句或存储过程名</param>  
        /// <param name="commandType">Oracle命令类型</param>  
        /// <param name="param">Oracle命令参数数组</param>  
        /// <returns>受影响的行数</returns>  
        #region MyRegion       
        public static int ExecuteNonQuery(string commandText, CommandType commandType, params SqlParameter[] param)
        {
            int result = 0;
            try
            {
                using (SqlCommand cmd = new SqlCommand(commandText, SqlServer.Con))
                {
                    try
                    {
                        //cmd.CommandType = commandType;
                        if (param != null)
                        {
                            cmd.Parameters.AddRange(param);
                        }
                        SqlServer.Con.Open();
                        result = cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        result = -1;
                    }
                }
            }
            finally
            {
                if (SqlServer.Con.State != ConnectionState.Closed)
                {
                    SqlServer.Con.Close();
                }
            }
            return result;
        }
        #endregion


    }
}

 

调用

/// <summary>
        /// SqlServer数据库增删改查
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button2_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();//DataTable接收传回的数据
            dt = SqlServer.GetDataTable("select * from Base_UserInfo;", null);//查询
          //  SqlServer.ExecuteNonQuery(@"INSERT INTO ZLCB01A (ZLCB01A005) VALUES('912')", CommandType.Text, null);//增加
            //SqlServer.ExecuteNonQuery("delete from  ZLCB01A WHERE ZLCB01A005='955'", CommandType.Text, null);//删除
            //SqlServer.ExecuteNonQuery(@"update  ZLCB01A SET ZLCB01A005='955' where ZLCB01A005='912'", CommandType.Text, null);//修改
        }