MVC3CRUDDemo(一)

最新推荐文章于 2025-04-18 12:59:00 发布

weixin_30810239

最新推荐文章于 2025-04-18 12:59:00 发布

阅读量68

点赞数

CC 4.0 BY-SA版权

文章标签：测试数据库

原文链接：http://www.cnblogs.com/8090sns/archive/2012/08/09/MVC3CRUDDemo.html

本文详细介绍了使用MVC3框架进行CRUD操作的实现过程，并阐述了日期验证的方法，包括数据库操作、表结构设计以及关键属性的约束。

项目结构图：

DB:

set ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
USE master;
GO
if exists(select * from sys.databases where name='Test')
drop database Test;
go

-- Create the Test database
create database Test;
go
-- Specify a simple recovery model 
-- to keep the log growth to a minimum.
ALTER DATABASE Test 
    SET RECOVERY SIMPLE;
GO

use Test; 
-- Create People table
IF NOT EXISTS (SELECT * FROM sys.objects 
        WHERE object_id = OBJECT_ID(N'People') 
        AND type in (N'U'))
begin
create table People(
       ID int IDENTITY,
       Name nvarchar(200) not null,
       Sex nvarchar(100) not null,
       Age int not null,
       BorthDate datetime not null,
       BorthPlace varchar(200) not null,
       constraint PK_Person primary key clustered
       (
          ID ASC
       ) WITH(IGNORE_DUP_KEY=OFF) ON [Primary]
       )on [primary]
end
go
use Test;
go
insert into People(Name,Sex,Age,BorthDate,BorthPlace)
values('denny','male',25,'1988-01-18','shanghai,china');
insert into People(Name,Sex,Age,BorthDate,BorthPlace)
values('scott','male',30,'1982-01-18','shenzhen,china')

Web.Config:

<?xml version="1.0"?>
<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=152368
  -->

<configuration>
  <connectionStrings>
    <add name="ApplicationServices"
         connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"
         providerName="System.Data.SqlClient" />
    <add name="SQLServer" connectionString="data source=.;Initial Catalog=Test;Persist Security Info=True;User ID=test;Password=123456" providerName="System.Data.SqlClient"/>
  </connectionStrings>

  <appSettings>
    <add key="ClientValidationEnabled" value="true"/> 
    <add key="UnobtrusiveJavaScriptEnabled" value="true"/> 
  </appSettings>

  <system.web>
    <compilation debug="true" targetFramework="4.0">
      <assemblies>
        <add assembly="System.Web.Abstractions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
        <add assembly="System.Web.Helpers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
        <add assembly="System.Web.Routing, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
        <add assembly="System.Web.Mvc, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
        <add assembly="System.Web.WebPages, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
      </assemblies>
    </compilation>

    <authentication mode="Forms">
      <forms loginUrl="~/Account/LogOn" timeout="2880" />
    </authentication>

    <membership>
      <providers>
        <clear/>
        <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="ApplicationServices"
             enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false"
             maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10"
             applicationName="/" />
      </providers>
    </membership>

    <profile>
      <providers>
        <clear/>
        <add name="AspNetSqlProfileProvider" type="System.Web.Profile.SqlProfileProvider" connectionStringName="ApplicationServices" applicationName="/" />
      </providers>
    </profile>

    <roleManager enabled="false">
      <providers>
        <clear/>
        <add name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider" connectionStringName="ApplicationServices" applicationName="/" />
        <add name="AspNetWindowsTokenRoleProvider" type="System.Web.Security.WindowsTokenRoleProvider" applicationName="/" />
      </providers>
    </roleManager>

    <pages>
      <namespaces>
        <add namespace="System.Web.Helpers" />
        <add namespace="System.Web.Mvc" />
        <add namespace="System.Web.Mvc.Ajax" />
        <add namespace="System.Web.Mvc.Html" />
        <add namespace="System.Web.Routing" />
        <add namespace="System.Web.WebPages"/>
      </namespaces>
    </pages>
  </system.web>

  <system.webServer>
    <validation validateIntegratedModeConfiguration="false"/>
    <modules runAllManagedModulesForAllRequests="true"/>
  </system.webServer>

  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-2.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>

MVC3 DBHelper:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace MVC3CRUDDemo.DBHelper
{
    public static class DBHelper
    {
        //public static string connStr = "Data Source=LIUHENG\\SQL2005L;Initial Catalog=webTest;User ID=sa;Password=liuheng0429";
        private static string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["SQLServer"].ConnectionString;
        private static SqlConnection conn;
        private static SqlCommand cmd;

        /// <summary>
        /// 打开链接
        /// </summary>
        /// <returns></returns>
        private static SqlConnection GetConn()
        {
            if (conn == null)
            {
                conn = new SqlConnection(connStr);
            }
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            else if (conn.State == ConnectionState.Broken)
            {
                conn.Close();
                conn.Open();
            }

            return conn;

        }

        public static int ExecuteNonQuery(string sql)
        {
            try
            {
                cmd = new SqlCommand(sql, GetConn());
                return cmd.ExecuteNonQuery();
            }
            catch
            {
                return 0;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 读数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string sql)
        {
            try
            {
                cmd = new SqlCommand(sql, GetConn());
                return cmd.ExecuteReader();
            }
            catch
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 得到该表数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable GetTable(string sql)
        {
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, GetConn());
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
        }



    }
}

MVC3 Models:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.ComponentModel;
using Mvc3DBCRUDDemo.Common;

namespace MVC3CRUDDemo.Models
{
    public class PeopleModels
    {
        private static string tableName = "People";

        #region Models

        [Display(Name = "ID")]
        public int ID { get; set; }

        [ReadOnly(false)]
        [Display(Name = "Name")]
        [DataType(DataType.Text)]
        [Required(ErrorMessage = "Name is required")]
        public string Name { get; set; }

        [Display(Name = "Sex")]
        //Range 不能判断DropDownListFor的取值
        //[Range(typeof(string),"男","女")]
        [Required(ErrorMessage = "Name is required")]
        public string Sex { get; set; }

        //MVC中，整型最小值是0，如果不写就是NULL，此时不加Required也会检查出错。
        //如果实际中该值不要求非空，可这样解决：public int? Age { get; set; },如果"?"和"Required"同时存在，"Required"会覆盖"?"功能。
        [Display(Name = "Age")]
        [Required(ErrorMessage = "Sex is required")]
        [Range(1, 120)]
        public int Age { get; set; }

        
        [Display(Name = "BorthDate")]
        [DataType(DataType.Date)]
        [Required(ErrorMessage = "BorthDate is required")]
        //[DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:dd/MM/yyyy}")]
        //[RegularExpression(@"((^((1[8-9]\d{2})|([2-9]\d{3}))([-\/\._])(10|12|0?[13578])([-\/\._])(3[01]|[12][0-9]|0?[1-9])$)|(^((1[8-9]\d{2})|([2-9]\d{3}))([-\/\._])(11|0?[469])([-\/\._])(30|[12][0-9]|0?[1-9])$)|(^((1[8-9]\d{2})|([2-9]\d{3}))([-\/\._])(0?2)([-\/\._])(2[0-8]|1[0-9]|0?[1-9])$)|(^([2468][048]00)([-\/\._])(0?2)([-\/\._])(29)$)|(^([3579][26]00)([-\/\._])(0?2)([-\/\._])(29)$)|(^([1][89][0][48])([-\/\._])(0?2)([-\/\._])(29)$)|(^([2-9][0-9][0][48])([-\/\._])(0?2)([-\/\._])(29)$)|(^([1][89][2468][048])([-\/\._])(0?2)([-\/\._])(29)$)|(^([2-9][0-9][2468][048])([-\/\._])(0?2)([-\/\._])(29)$)|(^([1][89][13579][26])([-\/\._])(0?2)([-\/\._])(29)$)|(^([2-9][0-9][13579][26])([-\/\._])(0?2)([-\/\._])(29)$))",
            //ErrorMessage="Please input valid date format")]
        //如果这样写觉得太长就单独写个类如DateExpressionAttribute继承RegularExpressionAttribute, IClientValidatable即可。
        [DateExpressionAttribute]
        public DateTime BorthDate { get; set; }

        [Display(Name = "BorthPlace")]
        [Required(ErrorMessage = "BorthPlace is required")]
        public string BorthPlace { get; set; }

        #endregion Models

        #region Methods


        /// <summary>
        /// Too much hard code, need update
        /// </summary>
        public string Create()
        {
            //Insert into People ...
            //into 和 People之间有空格
            string str = "insert into" +" "+ tableName + "(Name,Sex,Age,BorthDate,BorthPlace) values(" +
                "\'" + Name +"\'"+ "," +
                "\'"+Sex +"\'"+ "," + 
                Age.ToString() + "," +
                "'" + BorthDate.ToShortDateString() + "'" + "," + 
                "\'"+BorthPlace +"\'"+ ")";
            if (DBHelper.DBHelper.ExecuteNonQuery(str) != 0)
            {
                // Hard code, need to update
                return "Create sccuess!";
            }
            else
            {
                return "Create failed! Please try again!";
            }
        }

        public DataTable ReadAll()
        {
            string str = "select * from " + tableName;
            DataTable table = DBHelper.DBHelper.GetTable(str);
            return table;
        }

        /// <summary>
        /// Too much hard code, need update
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="fieldName"></param>
        /// <param name="fieldValue"></param>
        /// <returns></returns>
        public DataTable ReadByName(string tableName, object fieldName, object fieldValue)
        {
            if (!String.IsNullOrEmpty(tableName))
            {
                DataTable table = DBHelper.DBHelper.GetTable("select * from " + tableName + " where " + fieldName.ToString() + "='" + fieldValue.ToString() + "'");
                return table;
            }
            else return null;
        }

        /// <summary>
        /// Too much hard code, need update
        /// </summary>
        public void Update()
        {
            DBHelper.DBHelper.ExecuteNonQuery("update" + tableName + "set name=" + Name + "," + "Sex=" + Sex + "," + "Age=" + Age + "," + "BortheDate=" + BorthDate + "BorthPlace=" + BorthPlace);
        }

        /// <summary>
        /// Too much hard code, need update
        /// </summary>
        public void Delete()
        {
            DBHelper.DBHelper.ExecuteNonQuery("delete from" + tableName);
        }

        #endregion Methods
    }
}