、、 .NET MVC连接Mysql的那些事

.NET MVC连接Mysql的那些事

B/S开发框架中除了MSSQL外,还有一款主流的数据库MySQL,其开源免费,易用强大,不失为一种好的数据库选择,那么在.net代码连接Mysql数据库该怎么做,有些什么坑需要注意的呢,请看我们的分解。
连接
mysql常用sql举例

vs mysql
连接
 <!--web.config mysql连接串-->
 <add name="YunMFrameworkDbContext" connectionString="Data Source=localhost;database=YunMFramework-Demo;User Id=root;Password=huawei123,;port=3306;" providerName="MySql.Data.MySqlClient"/>
 <!--EF mysql提供者-->
<entityFramework>
    <!--mysql-->
    <defaultConnectionFactory type="MySql.Data.Entity.MySqlConnectionFactory, MySql.Data.Entity.EF6" />
    <providers>
      <!--<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer"/>-->
      <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6"></provider>
    </providers>
</entityFramework>

安装mysql-connector-net-6.8.3(提取密码:gknf),开发工具vs2103。

web框架.net将mysql连接设置为默认连接:

       /// <summary>
        /// Initialize connection factory
        /// </summary>
        public virtual void InitConnectionFactory()
        {
            var connectionFactory = new MySqlConnectionFactory();
            //TODO fix compilation warning (below)
            #pragma warning disable 0618
            Database.DefaultConnectionFactory = connectionFactory;
        }

初始化数据库操作上下文

       /// <summary>
        /// Set database initializer
        /// </summary>
        public virtual void SetDatabaseInitializer()
        {
            //pass some table names to ensure that we have YunMFramework 1.X installed
            var tablesToValidate = new[] { "Department", "SystemRole", "SystemFunction" };
            //custom commands (stored proedures, indexes)
            var customCommands = new List<string>();
            //use webHelper.MapPath instead of HostingEnvironment.MapPath which is not available in unit tests
            customCommands.AddRange(ParseCommands(HostingEnvironment.MapPath("~/App_Data/Install/MySql.CreateTables.sql"), false));
            //use webHelper.MapPath instead of HostingEnvironment.MapPath which is not available in unit tests
            customCommands.AddRange(ParseCommands(HostingEnvironment.MapPath("~/App_Data/Install/MySql.StoredProcedures.sql"), false));
            //use webHelper.MapPath instead of HostingEnvironment.MapPath which is not available in unit tests ,初始化 
            customCommands.AddRange(ParseCommands(HostingEnvironment.MapPath("~/App_Data/Install/MySql.DataInit.sql"), false));
            initializer = new CreateMyTablesIfNotExist<DefaultDbContext>(tablesToValidate, customCommands.ToArray());
            Database.SetInitializer(initializer);
        }

引用Mysql类库

       using MySql.Data;
       using MySql.Data.MySqlClient;
mysql常用sql举例

建表

CREATE TABLE Template(
	Id int NOT NULL AUTO_INCREMENT,
	TemplateName varchar(500) NOT NULL,
	TemplateContent varchar(500) NOT NULL,
	TemplateExplain varchar(500) NULL,
	TemplateType int NOT NULL,
	IsEnabled bit NOT NULL,
	CreateDate datetime NOT NULL,
	CreateBy varchar(500) NULL,
	ModifyDate datetime NULL,
	ModifyBy varchar(500) NULL,
  CONSTRAINT PRIMARY KEY (Id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ;

建立主键和外键

 CONSTRAINT PRIMARY KEY (Id),
 CONSTRAINT `IX_SystemRole_DepartmentID` FOREIGN KEY (`DepartmentID`) REFERENCES `Department` (`Id`)

建立索引

CREATE INDEX `IX_DataAuth_UserID` ON `DataAuth` (`SystemUser_UserID` ASC);

坑1:VS2013编译环境下务必安装mysql-connector-net-6.8.3(提取密码:gknf)。略去各种各样的错误。
坑2:建立主键和外键对字段要用单引号引起来。
坑3:对不同的项目工程,务必引用同一Mysql.Data.dll,否则运行时会起冲突,如图:
mysql dll冲突

标签: Web开发框架开发指南红宝书标签

网站&系统开发技术学习交流群:463167176

本站文章除注明转载外,均为本站原创或翻译,欢迎任何形式的转载,但请务必注明出处,尊重他人劳动,共创和谐网络环境。
转载请注明:文章转载自:华晨软件-云微开发平台 » MVC.NET连接Mysql的那些事
本文标题:.NET MVC连接Mysql的那些事
本文地址:http://www.hocode.com/OrgTec/DB/0005.html

相关文章: 初识Web框架-Linq技术有那么深奥吗

电话
电话 18718672256

扫一扫
二维码