SqlHelper 工具类

SqlHelper 是一个自定义的工具类 将对数据库的操作封装起来 方便进行调用；

• 创建类库

  • 右键解决方案-新建项目-选择 类库（.NET Framework） - 命名 Common
  • 删除 初始化类，新建类 - 命名 SqlHelper

• 引用该类库

  • 右键引用-添加引用-项目

• 获取数据库连接

  • string sqlcon = "Data Source=.;Initial Catalog=MyCar;User ID=sa;Password=1234";
    

• 查询封装

• 返回受影响行数封装（增删改）

  • SqlHelper类

  • public   class SqlHelper
        {
    
            //获取数据库连接
            static string  sqlStr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
    
            /// <summary>
            /// 获取数据集
            /// </summary>
            /// <param name="sql">要执行的Sql语句</param>
            /// <param name="param">Sql语句中的参数</param>
            /// <returns>结果集表</returns>
            public static DataTable ExecuteDataTable(string sql,params 
                SqlParameter [] param)
            {
                DataTable table = new DataTable();
    
                using (SqlConnection con =new SqlConnection(sqlStr))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(sql,con);
                    
                    // 不为空 就添加参数
                    if(param.Length != 0)
                    {
                        adapter.SelectCommand.Parameters.AddRange(param);
                    }
    
                    adapter.Fill(table);
                }
                    return table;
    
            }
    
            
            /// <summary>
            /// 返回受影响行数
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="param"></param>
            /// <returns></returns>
            public static  int ExecuteNonquery(string sql, params
                SqlParameter[] param)
            {
                int i = -1;
    
                using (SqlConnection con = new SqlConnection(sqlStr))
                {
                    con.Open();
                    SqlCommand com = new SqlCommand(sql,con);
                    com.Parameters.AddRange(param);
                   
                    i =  com.ExecuteNonQuery();
                }
    
                    return   i;
            }
            
            
            /// <summary>
            /// 返回首行首列
            /// </summary>
            /// <param name="sql">sql语句</param>
            /// <param name="param">sql中的参数</param>
            /// <returns></returns>
            public static object ExecuteScalar(string sql,params
                SqlParameter[] param)
            {
                using (SqlConnection con = new SqlConnection(sqlStr))
                {
                    con.Open();
                    SqlCommand com = new SqlCommand(sql,con);
    
                    if(param.Length != 0)
                    {
                        com.Parameters.AddRange(param);
                    }
    
                  return   com.ExecuteScalar();
                }
            }
        
       		/// <summary>
            /// 流方式返回数据
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="param"></param>
            /// <returns></returns>
            public static SqlDataReader ExecuteReader(string sql,params
                SqlParameter[] param)
            {
                SqlDataReader reader;
                SqlConnection con = new SqlConnection(sqlStr);
    
                using (SqlCommand com = new SqlCommand(sql, con))
                {
                    com.Parameters.AddRange(param);
                    con.Open();
    
                    //CommandBehavior.CloseConnection 
                    //如果关闭SqlDataReader，SqlConnection也随之关闭
                    reader = com.ExecuteReader(CommandBehavior.CloseConnection);
                }
    
                    return reader;
            }
    
    
        }
    

  • 表现类

  • /// <summary>
    /// 通过id删用户
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public int deleteUserById(int id)
    {
        string sql = "delete from Student where sid = @id";
    
        SqlParameter p = new SqlParameter("@id",id);
    
        return SqlHelper.ExecuteNonquery(sql,p);
    }
    
    //初始化数据 
    public DataTable initDataTable()
    {
        label1.Text = "总人数："+initDataTableCount();
        string sql = " select * from  Student";
        return SqlHelper.ExecuteDataTable(sql);
    
    }
    
    //显示总人数
    public  string initDataTableCount()
    {
        string sql = "select COUNT(*) from  Student";
        return SqlHelper.ExecuteScalar(sql).ToString();
    }
    
    
    //流方式显示结果集
    private void button1_Click_1(object sender, EventArgs e)
    {
        string sql = "select * from  Student";
    
        SqlDataReader reader =  SqlHelper.ExecuteReader(sql);
        while (reader.Read())
        {
            Console.WriteLine(reader["sName"].ToString());
        }
        reader.Close();
    }