新手ADO存储过程MVC增删查改模糊查询分页登录加批量删除2

---恢复内容开始---

 

 

样式  

  以上只是为了个初学者一个借鉴，可以联系增删查改，批量删除，模糊查询，分页，拼串；

有需要的可以看看，然后说点啥呢，只要能保存到明天晚上就行了

多少个字了，有没有一百五十个了，差不多了吧。

现就这样吧 。试试看

SQL server内容：只写了一个表 这是为了简单的写一下

USE rikao 
CREATE TABLE exam
(
id INT PRIMARY KEY IDENTITY ,
NAME NVARCHAR(24),
sex NVARCHAR(24)
)
CREATE TABLE gender
(
genderId INT PRIMARY KEY IDENTITY,
genders NVARCHAR(24)
)
SELECT * FROM exam WHERE 1=1 AND [NAME]='天明'
 
IF OBJECT_ID('p_cha','p') IS NOT NULL
DROP PROC p_cha
GO
CREATE PROC p_cha
@name NVARCHAR(24),
@sex NVARCHAR(24)
AS
BEGIN
    IF(@name=null)
    BEGIN
    SELECT * FROM exam WHERE  sex=@sex
    END
    ELSE IF(@sex=null)
    BEGIN
    SELECT * FROM exam WHERE name=@name 
    END
    ELSE IF(@name=NULL AND @sex=NULL)
    BEGIN
    SELECT * FROM exam
    END
END
EXEC p_cha '天明','男'
---拼串模糊查询
if exists(select * from sys.objects where name='proc_getallByCondition' and type='p')
drop procedure proc_getallByCondition
go
create procedure proc_getallByCondition
(
@StudentNme nvarchar(50),
@Gender nvarchar(50)
)
as
declare @sql nvarchar(200)
set @sql= ('select  StudentId, StudentNme, Gender, PhoneNumber, CreationTime from [dbo].[TStudent] where 1=1 ')
if(@StudentNme<>'')
set @sql=CONCAT(@sql,' and StudentNme='+''''+@StudentNme+'''')
if(@Gender<>3)
set @sql=CONCAT(@sql,' and Gender='+@Gender)
exec(@sql)

CREATE TABLE exam
(
id INT PRIMARY KEY IDENTITY ,
NAME NVARCHAR(24),
sex NVARCHAR(24)
)
IF OBJECT_ID('p_find','p') IS NOT NULL
DROP PROC p_find
GO
CREATE PROC p_find
@name NVARCHAR(24),
@sex NVARCHAR(24)
as
DECLARE @sql NVARCHAR(200)
SET @sql=('select id,name,sex from exam where 1=1')
IF(@name<>'')
SET @sql=CONCAT(@sql,' and name='+''''+@name+'''')
IF(@sex<>'')
SET @sql=CONCAT(@sql,' and sex='+''''+@sex+'''')
EXEC(@sql)
EXEC p_find '',''

/*CREATE   TABLE Pager
(
CurrentPage  INT ,
PageData int,
TotalPage INT
)

IF OBJECT_ID('p_page','p') IS NOT NULL
DROP PROC p_page
GO
CREATE PROC p_page
@CurrentPage INT,
@PageDataCount INT,
@TotalPage INT OUTPUT
AS
BEGIN
    DECLARE @datacount INT
      SET @datacount=(SELECT COUNT(*) FROM dbo.exam)
      SET @TotalPage =(@datacount/@PageDataCount)
      IF((@datacount%@PageDataCount)<>0)
      SET @TotalPage=@TotalPage+1
      SELECT id,name,sex FROM dbo.exam ORDER BY id offset ((@CurrentPage-1)*@PageDataCount) ROW FETCH NEXT @PageDataCount ROW only
END
go
DECLARE @TotalPage int
EXEC p_page 2,2,@TotalPage output
SELECT @TotalPage

if (object_id('pro_page', 'P') is not null)
    drop proc pro_stu
go
create procedure pro_stu(
    @pageIndex int,
    @pageSize int
)
as
    declare @startRow int, @endRow int
    set @startRow = (@pageIndex - 1) * @pageSize +1
    set @endRow = @startRow + @pageSize -1
    select * from (
        select *, row_number() over (order by id asc) as number from exam 
    ) t
    where t.number between @startRow and @endRow;
    EXEC pro_stu 2,2
*/
IF OBJECT_ID('p_pages','p') IS NOT NULL
DROP PROC p_pages
go
CREATE PROC p_pages
(
@name NVARCHAR(24),
@sex NVARCHAR(24),
@pageSize INT,--每页显示几条
@currPage INT,--当前页面
@totalPage INT OUTPUT --总页面
)
AS
DECLARE @sql NVARCHAR(500)--查询语句
DECLARE @Condition NVARCHAR(500)--追加的条件
DECLARE @contdatastr NVARCHAR(500)--计算总数据的语句
DECLARE @paging NVARCHAR(500) --分页的语句
DECLARE @countData float--浮点型意为向上取整
SET @paging=' order by id offset '+CONVERT(NVARCHAR(50),((@currPage-1)*@pageSize))+' 
row fetch next  '+CONVERT(NVARCHAR(50),@pageSize)+' row only'
--offset(跳过多少条)    row fetch NEXT 取多少条 ROW only
SET @sql='select id,name,sex from exam where 1=1'
SET @Condition=''
IF(@name!='')
SET @Condition=CONCAT(@Condition,' and name='+''''+@name+'''')
IF(@sex!='')
SET @Condition=CONCAT(@Condition,' and sex='+''''+@sex+'''')

SET @sql=CONCAT(@sql,@Condition)
SET @sql=CONCAT(@sql,@paging)
SET @contdatastr=CONCAT('select *  from exam where 1=1',@Condition)
EXEC(@sql)
EXEC(@contdatastr)
set @countData=@@rowcount
SET @totalPage=CEILING(@countData/@pageSize)

go
DECLARE @totalPage INT
EXEC p_pages '','女',2,1,@totalPage OUTPUT
SELECT @totalPage AS 总


--简单增加
IF OBJECT_ID('p_AddExam') IS NOT NULL
DROP PROC p_AddExam
go
CREATE PROC p_AddExam
@name NVARCHAR(24),
@sex NVARCHAR(24)
AS
BEGIN
INSERT INTO exam  VALUES (@name,@sex)
END
EXEC p_AddExam '张小凡','男'
SELECT * FROM dbo.exam

--简单删除
IF OBJECT_ID('p_DelExam') IS NOT NULL
DROP PROC p_DelExam
go 

CREATE PROC p_DelExam
@id int
as
BEGIN
DELETE FROM exam WHERE id=@id
end

EXEC p_DelExam 8
SELECT * FROM dbo.exam
--创建登录表 账号密码
CREATE TABLE logins
(
Log_id INT PRIMARY KEY IDENTITY,
Log_name nvarchar(24),
Log_password nvarchar(24)
)
SELECT * FROM logins
--简单登录
IF OBJECT_ID('p_log') IS NOT NULL
DROP PROC p_log
GO
CREATE PROC p_log
@Log_name nvarchar(24),
@Log_password nvarchar(24)
AS
BEGIN
SELECT COUNT(*) FROM logins WHERE Log_name=@Log_name AND Log_password =@Log_password
end

EXEC p_log 'admin','123'
--批量删除
IF OBJECT_ID('p_DelAll','p') IS NOT NULL
DROP  PROC p_DelAll
GO
CREATE PROC p_DelAll
(
@id NVARCHAR(24)
)
AS
BEGIN
EXEC ('delete from exam where id in('+@id+')')
END
EXEC p_DelAll '12,13,14'
select * FROM exam
--修改

IF OBJECT_ID('p_upd','p') IS NOT NULL
DROP PROC p_upd
go 
CREATE PROC p_upd

@name NVARCHAR(24),
@sex NVARCHAR(24),
@id int
AS
BEGIN
UPDATE dbo.exam SET name=@name,sex=@sex where id=@id
end

EXEC p_upd '地狱','男',5
UPDATE dbo.exam SET name='天堂',sex='男' where id=2
SELECT * FROM exam

现在是数据访问层：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using System.Data;
using System.Data.SqlClient;
using Newtonsoft.Json;
using System.Data.Entity;
using System.Configuration;
using System.Reflection;
namespace Dal
{
    public class examDal
    {
        SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=rikao;Integrated Security=True");

        public List<examModel> show()
        {
            conn.Open();
            string sql = "SELECT * FROM exam";
            SqlDataAdapter dr = new SqlDataAdapter(sql, conn);
            DataTable dt = new DataTable();
            dr.Fill(dt);
            var i = JsonConvert.SerializeObject(dt);
            return JsonConvert.DeserializeObject<List<examModel>>(i);

        }
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="querys"></param>
        /// <returns></returns>
        public List<examModel> query(string name, string sex)
        {
            try
            {

                List<examModel> list = new List<examModel>();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlDataAdapter dr = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                SqlParameter[] pare =
                {
                    new SqlParameter("@name",name),
                    new SqlParameter("@sex",sex),
                };
                cmd.Parameters.AddRange(pare);
                cmd.CommandText = "p_find";
                cmd.CommandType = CommandType.StoredProcedure;
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                dr.Fill(dt);
                // cmd.ExecuteNonQuery();
                // SqlDataReader sdr=cmd
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    examModel exams = new examModel();
                    exams.id = (int)dt.Rows[i]["id"];
                    exams.name = dt.Rows[i]["name"].ToString();
                    exams.sex = dt.Rows[i]["sex"].ToString();
                    list.Add(exams);
                }
                return list;
            }

            catch (Exception)
            {

                throw;
            }
            finally
            {
                conn.Close();
            }
        }
        //public List<gender> GetGender()
        //{
        //    using (var con = new ModelContext())
        //    {
        //        return con.gender.ToList();
        //    }
        //}
        //获取性别表
        public List<gender> GetGenderShow()
        {
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Open();
                string sql = "SELECT * FROM gender";
                SqlDataAdapter dr = new SqlDataAdapter(sql, conn);
                DataTable dt = new DataTable();
                dr.Fill(dt);
                //for (int j = 0; j < dt.Rows.Count; j++)
                //{
                //    gender g = new gender();
                //    g.genderId = (int)dt.Rows[j]["genderId"];
                //    g.genders = dt.Rows[j]["genderId"].ToString();
                //}
                var i = JsonConvert.SerializeObject(dt);
                return JsonConvert.DeserializeObject<List<gender>>(i);
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conn.Close();
            }

        }

        /// <summary>
        /// 查询心法
        /// </summary>
        /// <param name="name"></param>
        /// <param name="sex"></param>
        /// <returns></returns>
        public List<examModel> finds(string name, string sex)
        {
            List<examModel> list = new List<examModel>();
            try
            {
                string procsql = "p_find";
                conn.Open();
                SqlCommand cmd = new SqlCommand(procsql, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@name", name));
                cmd.Parameters.Add(new SqlParameter("@sex", sex));
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    examModel m = new examModel();
                    m.id = (int)dr["id"];
                    m.name = dr["name"].ToString();
                    m.sex = dr["sex"].ToString();
                    list.Add(m);
                }
                dr.Close();
                conn.Close();
                return list;
            }
            catch (Exception)
            {

                throw;
            }
        }
        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="current"></param>
        /// <param name="pageDataCount"></param>
        /// <returns></returns>
        public string GetexamList(string name, string sex, int currPage)
        {
            try
            {
                const int PAGESIZE = 3;
                List<examModel> examlist = new List<examModel>();
                Page pageList = new Page();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;

                SqlParameter[] pare = new SqlParameter[]
                {
                    new SqlParameter("@name",name),
                    new SqlParameter("@sex",sex),
                    new SqlParameter("@pageSize",PAGESIZE),
                    new SqlParameter("@currPage",currPage),
                    new SqlParameter("@TotalPage",System.Data.SqlDbType.Int)
                };
                pare[4].Direction = System.Data.ParameterDirection.Output;
                cmd.CommandText = "p_pages";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(pare);
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                SqlDataAdapter dr = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                dr.Fill(dt);
                string liststr = JsonConvert.SerializeObject(dt);
                examlist = JsonConvert.DeserializeObject<List<examModel>>(liststr);
                pageList.CurrPage = currPage;
                pageList.PageData = examlist;
                pageList.TotalPage = (int)pare[4].Value; ;
                return JsonConvert.SerializeObject(pageList);
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conn.Close();
            }

        }

        /// <summary>
        /// 存储过程 单表 添加
        /// </summary>
        /// <param name="name"></param>
        /// <param name="sex"></param>
        /// <returns></returns>
        public int ExamAdd(string name, string sex)
        {
            List<examModel> list = new List<examModel>();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            SqlParameter[] pare =
                {
                    new  SqlParameter("@name",name),
                    new SqlParameter("@sex",sex)
                };
            cmd.Parameters.AddRange(pare);
            cmd.CommandText = "p_AddExam";
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
            return    cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 存储过程单表删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelExam(int id)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            SqlParameter[] pare =
            {
                new SqlParameter("@id",id),
            };
            cmd.CommandText = "p_DelExam";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(pare);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            return cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// 批量删除尝试
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int AllDel(string id)
        {
            try
            {
         
           // string sql = string.Format("delete from exam where id in ({0})",id);
            SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlParameter[] pare =
                {
                    new SqlParameter("@id",id)
                };
                cmd.Parameters.AddRange(pare);
                cmd.CommandText = "p_DelAll";
                cmd.CommandType = CommandType.StoredProcedure;
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {

                throw;
            }
  

        }
        //返填
        public DataTable ft(int id)
        {
            try
            {
                conn.Open();
                string sql = "select * from exam where id=" + id + "";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter dr = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                dr.Fill(dt);
                return dt;
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 修改尝试
        /// </summary>
        /// <param name="upd"></param>
        /// <returns></returns>
        public int Update(examModel upd)
        {
            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "p_upd";
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter[] pare =
                {
                    new SqlParameter("@name",upd.name),
                    new SqlParameter("@sex",upd.sex),
                    new SqlParameter("@id",upd.id)
                };
                cmd.Parameters.AddRange(pare);
                return cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conn.Close();
            }
        }
        adoDbContext context = new adoDbContext();
        public DbSet<T> GetDbset<T>() where T : class
        {
            Type getType = context.GetType();
            if (getType != null)
            {
                PropertyInfo[] getProperties = getType.GetProperties();
                if (getProperties != null)
                {
                    Type CurrentType = typeof(T);
                    foreach (PropertyInfo item in getProperties)
                    {
                        if (item.Name.Equals(CurrentType.Name))
                        {
                            DbSet<T> dbset = (DbSet<T>)item.GetValue(context);
                            return dbset;
                        }
                    }
                }
            }
            return null;
        }
 
    }
}

Dal中的 登录 和  反射

 
 using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Reflection;
using System.Data.Entity;
using Model;
namespace Dal
{
   public class adoDbContext:DbContext
    {
        public adoDbContext() : base()
        {

        }
        public DbSet<examModel> Users { get; set; }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using System.Data;
using System.Data.SqlClient;
using Newtonsoft.Json;
using System.Data.Entity;
using System.Configuration;
using System.Reflection;
namespace Dal
{
    public class examDal
    {
        SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=rikao;Integrated Security=True");

        public List<examModel> show()
        {
            conn.Open();
            string sql = "SELECT * FROM exam";
            SqlDataAdapter dr = new SqlDataAdapter(sql, conn);
            DataTable dt = new DataTable();
            dr.Fill(dt);
            var i = JsonConvert.SerializeObject(dt);
            return JsonConvert.DeserializeObject<List<examModel>>(i);

        }
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="querys"></param>
        /// <returns></returns>
        public List<examModel> query(string name, string sex)
        {
            try
            {

                List<examModel> list = new List<examModel>();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlDataAdapter dr = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                SqlParameter[] pare =
                {
                    new SqlParameter("@name",name),
                    new SqlParameter("@sex",sex),
                };
                cmd.Parameters.AddRange(pare);
                cmd.CommandText = "p_find";
                cmd.CommandType = CommandType.StoredProcedure;
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                dr.Fill(dt);
                // cmd.ExecuteNonQuery();
                // SqlDataReader sdr=cmd
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    examModel exams = new examModel();
                    exams.id = (int)dt.Rows[i]["id"];
                    exams.name = dt.Rows[i]["name"].ToString();
                    exams.sex = dt.Rows[i]["sex"].ToString();
                    list.Add(exams);
                }
                return list;
            }

            catch (Exception)
            {

                throw;
            }
            finally
            {
                conn.Close();
            }
        }
        //public List<gender> GetGender()
        //{
        //    using (var con = new ModelContext())
        //    {
        //        return con.gender.ToList();
        //    }
        //}
        //获取性别表
        public List<gender> GetGenderShow()
        {
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Open();
                string sql = "SELECT * FROM gender";
                SqlDataAdapter dr = new SqlDataAdapter(sql, conn);
                DataTable dt = new DataTable();
                dr.Fill(dt);
                //for (int j = 0; j < dt.Rows.Count; j++)
                //{
                //    gender g = new gender();
                //    g.genderId = (int)dt.Rows[j]["genderId"];
                //    g.genders = dt.Rows[j]["genderId"].ToString();
                //}
                var i = JsonConvert.SerializeObject(dt);
                return JsonConvert.DeserializeObject<List<gender>>(i);
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conn.Close();
            }

        }

        /// <summary>
        /// 查询心法
        /// </summary>
        /// <param name="name"></param>
        /// <param name="sex"></param>
        /// <returns></returns>
        public List<examModel> finds(string name, string sex)
        {
            List<examModel> list = new List<examModel>();
            try
            {
                string procsql = "p_find";
                conn.Open();
                SqlCommand cmd = new SqlCommand(procsql, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@name", name));
                cmd.Parameters.Add(new SqlParameter("@sex", sex));
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    examModel m = new examModel();
                    m.id = (int)dr["id"];
                    m.name = dr["name"].ToString();
                    m.sex = dr["sex"].ToString();
                    list.Add(m);
                }
                dr.Close();
                conn.Close();
                return list;
            }
            catch (Exception)
            {

                throw;
            }
        }
        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="current"></param>
        /// <param name="pageDataCount"></param>
        /// <returns></returns>
        public string GetexamList(string name, string sex, int currPage)
        {
            try
            {
                const int PAGESIZE = 3;
                List<examModel> examlist = new List<examModel>();
                Page pageList = new Page();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;

                SqlParameter[] pare = new SqlParameter[]
                {
                    new SqlParameter("@name",name),
                    new SqlParameter("@sex",sex),
                    new SqlParameter("@pageSize",PAGESIZE),
                    new SqlParameter("@currPage",currPage),
                    new SqlParameter("@TotalPage",System.Data.SqlDbType.Int)
                };
                pare[4].Direction = System.Data.ParameterDirection.Output;
                cmd.CommandText = "p_pages";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(pare);
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                SqlDataAdapter dr = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                dr.Fill(dt);
                string liststr = JsonConvert.SerializeObject(dt);
                examlist = JsonConvert.DeserializeObject<List<examModel>>(liststr);
                pageList.CurrPage = currPage;
                pageList.PageData = examlist;
                pageList.TotalPage = (int)pare[4].Value; ;
                return JsonConvert.SerializeObject(pageList);
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conn.Close();
            }

        }

        /// <summary>
        /// 存储过程 单表 添加
        /// </summary>
        /// <param name="name"></param>
        /// <param name="sex"></param>
        /// <returns></returns>
        public int ExamAdd(string name, string sex)
        {
            List<examModel> list = new List<examModel>();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            SqlParameter[] pare =
                {
                    new  SqlParameter("@name",name),
                    new SqlParameter("@sex",sex)
                };
            cmd.Parameters.AddRange(pare);
            cmd.CommandText = "p_AddExam";
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
            return    cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 存储过程单表删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelExam(int id)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            SqlParameter[] pare =
            {
                new SqlParameter("@id",id),
            };
            cmd.CommandText = "p_DelExam";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(pare);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            return cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// 批量删除尝试
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int AllDel(string id)
        {
            try
            {
         
           // string sql = string.Format("delete from exam where id in ({0})",id);
            SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlParameter[] pare =
                {
                    new SqlParameter("@id",id)
                };
                cmd.Parameters.AddRange(pare);
                cmd.CommandText = "p_DelAll";
                cmd.CommandType = CommandType.StoredProcedure;
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {

                throw;
            }
  

        }
        //返填
        public DataTable ft(int id)
        {
            try
            {
                conn.Open();
                string sql = "select * from exam where id=" + id + "";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter dr = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                dr.Fill(dt);
                return dt;
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 修改尝试
        /// </summary>
        /// <param name="upd"></param>
        /// <returns></returns>
        public int Update(examModel upd)
        {
            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "p_upd";
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter[] pare =
                {
                    new SqlParameter("@name",upd.name),
                    new SqlParameter("@sex",upd.sex),
                    new SqlParameter("@id",upd.id)
                };
                cmd.Parameters.AddRange(pare);
                return cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                conn.Close();
            }
        }
        adoDbContext context = new adoDbContext();
        public DbSet<T> GetDbset<T>() where T : class
        {
            Type getType = context.GetType();
            if (getType != null)
            {
                PropertyInfo[] getProperties = getType.GetProperties();
                if (getProperties != null)
                {
                    Type CurrentType = typeof(T);
                    foreach (PropertyInfo item in getProperties)
                    {
                        if (item.Name.Equals(CurrentType.Name))
                        {
                            DbSet<T> dbset = (DbSet<T>)item.GetValue(context);
                            return dbset;
                        }
                    }
                }
            }
            return null;
        }
 
    }
}

控制器：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using System.Data.SqlClient;
using Newtonsoft.Json;
using Model;
using Bll;
namespace SexFind.Controllers
{
    public class HomeController : Controller
    {
        examBll bb = new examBll();
        LoginsBll logbb = new LoginsBll();

        // GET: Home
        public ActionResult Index(string name = "", string sex = "")
        {
            //ViewBag.genderId = new SelectList(new Bll.examBll().GetGenderShow(), "genderId", "genders");

            // ViewBag.cha = bb.query(name, sex);
            //ViewBag.show = bb.show();
            ViewBag.gender = bb.GetGenderShow();

            //ViewBag.finds = bb.finds(name, sex);
            return View();
        }

        public string GetexamList(int currPage, string name , string sex)
        {
            return bb.GetexamList(name, sex, currPage);
        }
        //public  string cha1(string name,string sex)
        //{
        //    return JsonConvert.SerializeObject(bb.query(name,sex));
        //}
        //[HttpPost]
        //public string cha2(string name, string sex)
        //{
        //    return JsonConvert.SerializeObject(bb.finds(name, sex));

        //}
        //[HttpGet]
        //public List<gender> GetGenderList()
        //{
        //    return bb.GetGender();
        //}
        public ActionResult Add()
        {

            return View();
        }
        public ActionResult addo(string name, string sex)
        {
            int i = bb.ExamAdd(name, sex);
            if (i > 0)
            {
                return Content("<script>alert('添加成功！');location.href='/Home/Index'</script>");
            }
            else
            {
                return Content("<script>alert('添加失败！');</script>");
            }
        }
        public ActionResult DelExam(int id)
        {
            int i = bb.DelExam(id);
            if (i > 0)
            {
                return Content("<script>alert('删除成功！');location.href='/Home/Index'</script>");
            }
            else
            {
                return Content("<script>alert('删除失败！');</script>");
            }
        }
        public ActionResult Login()
        {
            return View();
        }
        public ActionResult Logins(string Log_name, string Log_password)
        {
            int i = logbb.Logins(Log_name, Log_password);
            if (i > 0)
            {
                return Content("<script>alert('登录成功！');location.href='/Home/Index'</script>");
            }
            else
            {
                return Content("<script>alert('登录失败！');location.href='/Home/Login'</script>");
            }
        }
        public int AllDel(string id)
        {
            return bb.AllDel(id);
        }
        public ActionResult Update(int id)
        {
            Session["id"]=id;
            DataTable dt = bb.ft(id);
            ViewBag.str = dt.Rows[0];
            return View();
        }
        [ValidateInput(false)]
        [HttpPost]
        public ActionResult gaiDo(examModel m)
        {
            var i = bb.Update(m);
            if (i > 0)
            {
                return Content("<script>alert('修改成功！'); location.href = '/Home/Index';</script>");
            }
            else
            {
                return Content("<script>alert('修改失败！'); location.href = '/Home/Index';</script>");
            }
            //return View();
        }

    }
}

显示视图

@{
    Layout = null;
}

<!DOCTYPE html>

<html>

<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <script src="~/jquery-1.10.2.min.js"></script>
    <link href="~/BootstrapSouce/css/bootstrap.min.css" rel="stylesheet" />
    <script src="~/BootstrapSouce/js/jquery.min.js"></script>
    <script src="~/BootstrapSouce/js/bootstrap.min.js"></script>
    @*.form-control{
        width:10px;
        }*@
</head>
@{
    //var show = ViewBag.show;
    var cha = ViewBag.cha;
    var gender = ViewBag.gender;
}
<body>


    <!-- 这里是注释代码
        <form id="form1" method="post">



        @*<select name="sex">
                   <option value="男">男</option>
                   <option value="女">女</option>
            </select>*@
        @*------我是分割线------*@
        性别：
        @*@Html.DropDownList("genderId")*@

        @*</form>*@

        @*<input type="button" id="btn1" value="查询" />*@
    -->
    <p class="nav nav-tabs modal-dialog">
        姓名：<input class="text-uppercase " id="name" type="text"  value="" />

        <select name="sex" id="genter">
            <option value="">---请选择---</option>
            @foreach (var item in gender)
            {
                <option value="@item.genders">@item.genders</option>
            }
        </select>

        <input id="btn1" class="btn btn-primary btn-sm" type="button" value="查询" />
        <a class="btn btn-primary btn-sm" href="/Home/Add">添加信息</a>
        <a class="btn btn-primary btn-sm" id="btndel">批量删除所选项</a>
    </p>

    <div class="modal-dialog">
        <table class="table table-hover table-bordered table-striped" id="table1">
            <tr>
                <td><input type="checkbox" id="checkboxAll"  /></td>
                <td>编号</td>
                <td>名字</td>
                <td>性别</td>
                <td>操作</td>
            </tr>
            @*@foreach (var item in cha)
                {
                    <tr>
                        <td>@item.id</td>
                        <td>@item.name</td>
                        <td>@item.sex</td>
                    </tr>
                }*@
        </table>
        <table id="tableShow">
            <tr>
                <td><a href="#" onclick="FirstPage()">首页&nbsp;&nbsp;&nbsp;</a></td>
                <td><a href="#" onclick="UpPage()">上一页&nbsp;&nbsp;&nbsp;</a></td>
                <td><a href="#" onclick="NextPage()">下一页&nbsp;&nbsp;&nbsp;</a></td>
                <td><a href="#" onclick="LastPage()">尾页</a></td>
            </tr>
            
        </table>
       
    </div>
</body>
</html>
<script>
    var curr = 1;
    var total = 0;
    var genders = '';
    // $("#btn1").click(function () {
    //    var name = $("#TxtName").val();
    //    var sex = $("#selectone").val();
    //    location.href = '/Home/Index?name=' + name + "&sex=" + sex;
    //})
    $(function () {
        show();
        $("#btn1").click(function () {
            var name = $("#name").val();
            var sex = $("#genter").val();
           // alert(name)
            //alert(sex)
            curr = 1;
            genders = sex;
            //  location.href = '/Home/Index?name=' + name + "&sex=" + sex;
            show();


        })
    });
    //    $.ajax({
    //        url: "/Home/cha1",
    //        type: "get",
    //        // data: jQuery("#form1").serialize(),
    //        data:{
    //            name:name,
    //            sex:sex
    //        },
    //        success: function (data) {
    //           // $("#table1 tr:gt(0)")
    //            data = JSON.parse(data);
    //            $("#table1 tr:gt(0)").remove() ;
    //            var tableHtml="<tr>"+
    //               "<td>编号</td>"+
    //               "<td>名字</td>"+
    //               "<td>性别</td>"+
    //               "</tr>;"
    //            for (var i = 0; i < data.length; i++) {
    //                tableHtml+=
    //                    "<tr>" +
    //               "<td>"+data[i].id+"</td>" +
    //               "<td>"+data[i].name+"</td>" +
    //               "<td>"+data[i].sex+"</td>" +
    //               "</tr>;"
    //            }
    //            $("#table1").html(tableHtml);
    //        }
    //})

    //function GetGenderList() {
    //    $.ajax({
    //        url: "/Home/GetGenderList",
    //        type: "get",
    //        success: function (data) {
    //            $("#gender option:gt(0)").remove();
    //            $.each(data, function (i, d) {
    //                $("#gender").append("<option value='" + d.genderId + "'>" + d.genders + "</option>")
    //            })
    //            GetGenderList()
    //        }

    //批量删除
    //$("#btndel").click(function () {
    //    var arry = new Array();
    //    var str = "";
    //    var checks = $("input:checkbox[name='times']:checked");
    //   // var selectRow = $("#table1 tr :checkbox:checked");
    //   // var checks = $("input:checkbox[name='times']:checked").attr("checked", true);
    //    if(checks.length<=0)
    //    {
    //        alert("请至少选择一项");
    //        return;
    //    }
    //   else if (confirm("确认删除吗？")) {
    //       $(checks).each(function(index,da){
    //           arry[index] = $(this).val();
    //       })
    //        //for (var i = 0; i < selectRow.length; i++) {
    //        //    id += selectRow[i].val + ",";
    //        //}
    //        str = arry.join(",");
    //        $.ajax({
    //            type: "post",
    //            url: "/Home/AllDel",
    //            data: { id: str },
    //            success: function (asd) {
    //                if(asd>0)
    //                {
    //                    alert("删除成功");
    //                    show();
    //                    //dataload();
    //                    //$("#checkbox1").attr("checked", false);
    //                }
    //                else
    //                {
    //                    alert("删除失败");
    //                }

    //删除
    $("#btndel").click(function () {
        var selectedRow = $("#table1 tr :checkbox:checked");
        if (selectedRow.length == 0) {
            alert("请至少选择一项");
            return;
        }
        if (confirm("确定要删除吗?")) {
            var id = "";
            for (var i = 0; i < selectedRow.length; i++) {
                id += selectedRow[i].value + ",";
            }
            id = id.substr(0, id.length - 1);
            $.ajax({
                type: "post",
                url: "/Home/AllDel",
                data: { id: id },
                success: function (obj) {
                    if (obj > 0) {
                        alert("删除成功");
                        show();
                        //刷新列表数据
                        //dataload();
                        //清除所有选中
                         //$("#Checkbox1").attr("checked", false);

                    }
                    else {
                        alert("删除失败");
                    }
                }
            })
        }
    });
    //全选
    $("#checkboxAll").click(function () {
        if (this.checked) {
            alert("1");
            $("[name=times]").attr("checked",true);
            //$("[name=hxy]").attr("checked", true);
        }
        else {
            alert("2")
            $("[name=times]").attr('checked',false);
        }
        alert("3")
    });
    function show() {
        var name = $("#name").val();
        var sex = $("#genter").val();

        $.ajax({
            url: "/Home/GetexamList",
            type: "get",
            data: {
                name: name,
                sex: genders,
                currPage: curr,
            },
            success: function (data) {
                data = JSON.parse(data);
                total = data.TotalPage;
                curr = data.CurrPage;
               // alert(curr)
                $("#table1 tr:gt(0)").remove();


                //alert(total);

                // alert("1"+data+"2");
                // console.log(data);
                $(data.PageData).each(function (index, data) {
                    var Str = "";
                    Str += "<tr>";
                    Str += "<td><input type='checkbox' name='times' id='checkbox1'  value='"+data.id+"'/></td>";
                    Str += "<td>" + data.id + "</td>";
                    Str += "<td>" + data.name + "</td>";
                    Str += "<td>" + data.sex + "</td>";
                    Str += "<td>" + "<a href='/Home/DelExam/" + data.id + "'>删除</a><a href='#' onclick='xiu("+data.id+")'>修改</a>"+"</td>";
                    Str += "</tr>";
                    $("#table1").append(Str);
                });
            }
        })
    };
    function FirstPage() {

        curr = 1;

        show();
    }
    function UpPage() {


        if (curr == 1)
            alert("已经在第一页了");
        else {
            curr = curr - 1;
            show();
        }
    }
    function NextPage() {

        if ((curr + 1) > total)
            alert("已经是最后一页了")
        else {
            curr = curr + 1;

            show();
        }
    }
    function LastPage() {
        curr = total;
        show();
    }
    function xiu(id) {
        location.href = '/Home/Update/' + id;
    }
</script>

添加视图

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Add</title>
    <script src="~/jquery-1.10.2.min.js"></script>
    <link href="~/BootstrapSouce/css/bootstrap.min.css" rel="stylesheet" />
    <script src="~/BootstrapSouce/js/jquery.min.js"></script>
    <script src="~/BootstrapSouce/js/bootstrap.js"></script>
</head>
<body>
    <div class="top modal-dialog"> 
        <form action="/Home/addo" method="post">
            <h1 class="h1">添加信息</h1>
            <table class="table table-hover table-bordered ">
                <tr>
                    <td>请输入姓名：</td>
                    <td><input type="text" name="name" value="" /></td>
                </tr>
                <tr>
                    <td>请选择性别：</td>
                    <td>
                        <select name="sex">
                            <option >---请选择---</option>
                            <option value="男">男</option>
                            <option value="女">女</option>
                        </select>

                    </td>
                </tr>
                <tr>
                    <td align="center" colspan="2">
                    <input class="btn btn-primary btn-sm" type="submit" value="添加" />
                        <input class="btn btn-primary btn-sm" onclick="jump()" type="button" value="返回" />
                    </td>
                </tr>
            </table>

        </form>
    </div>
</body>
</html>
<script>
    function jump() {
        location.href="/Home/Index"
    }

</script>

修改视图

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Update</title>
    <script src="~/jquery-1.10.2.min.js"></script>
</head>
<body>
    <div>
        <form action="/Home/gaiDo" method="post">
            <table>
                <tr>
                    <td>姓名：<input type="text" name="name" value="@ViewBag.str["name"]" /></td>
                </tr>
                <tr>
                    <td>性别：<input type="text" name="sex" value="@ViewBag.str["sex"]" /></td>
                </tr>
                <tr>
                    <td><input type="submit" id="btn" value="确认修改" /></td>
                </tr>
            </table>
        </form>
    </div>
</body>
</html>
<script>
    //$("#btn").click(function () {
    //    alert("1")
    //    var m = {
    //        name: $("#name").val(),
    //        sex: $("#sex").val()
    //    }
    //    alert("2")
    //    $.ajax({
    //        type: "post",
    //        url: "/Home/upd",
    //        data: m,
    //        dataType: "json",
    //        success: function (data) {
    //            if (data > 0) {
    //                alert("修改成功");
    //                location.href = '/Home/Index';
    //            }
    //            else {
    //                alert("修改失败");
    //                location.href = '/Home/Index';
    //            }
    //        }

    //    })
    //})
</script>

登录视图

@{
    Layout = null;
}

<!DOCTYPE html>

<html>

<head>
    
    <meta name="viewport" content="width=device-width" />
    <title>Login</title>
    <link href="~/BootstrapSouce/css/bootstrap.min.css" rel="stylesheet" />
    <script src="~/BootstrapSouce/js/jquery.min.js"></script>
    <script src="~/BootstrapSouce/js/bootstrap.min.js"></script>
    <style type="text/css">


    </style>
</head>
<body>
    <div align="center" class="page-header">
        <h1 >欢迎您的到来</h1>
    </div>
    <div id="div1"   class="top modal-dialog">
        <form action="/Home/Logins" method="post">
            <table class="table table-hover table-bordered " >
                <tr>
                    
                    <td>账号：</td>
                    <td><input class=" form-control" type="text" name="Log_name" value="" /></td>
                </tr>
                <tr>
                    <td>密码：</td>
                    <td><input class=" form-control" type="password" name="Log_password" value="" />  </td>
                </tr>
                <tr>
                    <td colspan="2" align="center"><input  class="btn btn-primary btn-sm" type="submit" value="添加" /></td>
                </tr>
            </table>
        </form>
    </div>
</body>
</html>

 

---恢复内容结束---