MySQL

Labels
AJAX(112) App Studio(7) Apple(1) Application Builder(245) Application Factory(207) ASP.NET(95) ASP.NET 3.5(45) ASP.NET Code Generator(72) ASP.NET Membership(28) Azure(18) Barcode(2) Barcodes(3) BLOB(18) Business Rules(1) Business Rules/Logic(140) BYOD(13) Caching(2) Calendar(5) Charts(29) Cloud(14) Cloud On Time(2) Cloud On Time for Windows 7(2) Code Generator(54) Collaboration(11) command line(1) Conflict Detection(1) Content Management System(12) COT Tools for Excel(26) CRUD(1) Custom Actions(1) Data Aquarium Framework(122) Data Sheet(9) Data Sources(22) Database Lookups(50) Deployment(22) Designer(177) Device(1) DotNetNuke(12) EASE(20) Email(6) Features(101) Firebird(1) Form Builder(14) Globalization and Localization(6) How To(1) Hypermedia(2) Inline Editing(1) Installation(5) JavaScript(20) Kiosk(1) Low Code(3) Mac(1) Many-To-Many(4) Maps(6) Master/Detail(36) Microservices(4) Mobile(63) Mode Builder(3) Model Builder(3) MySQL(10) Native Apps(5) News(18) OAuth(9) OAuth Scopes(1) OAuth2(13) Offline(20) Offline Apps(4) Offline Sync(5) Oracle(11) PKCE(2) Postgre SQL(1) PostgreSQL(2) PWA(2) QR codes(2) Rapid Application Development(5) Reading Pane(2) Release Notes(183) Reports(48) REST(29) RESTful(29) RESTful Workshop(15) RFID tags(1) SaaS(7) Security(81) SharePoint(12) SPA(6) SQL Anywhere(3) SQL Server(26) SSO(1) Stored Procedure(4) Teamwork(15) Tips and Tricks(87) Tools for Excel(2) Touch UI(93) Transactions(5) Tutorials(183) Universal Windows Platform(3) User Interface(338) Video Tutorial(37) Web 2.0(100) Web App Generator(101) Web Application Generator(607) Web Form Builder(40) Web.Config(9) Workflow(28)
Archive
Blog
MySQL
Friday, November 9, 2012PrintSubscribe
Basic Membership Provider for MySQL

Requirements

A basic membership provider requires a dedicated table to keep track of user names, passwords, and emails.

A role provider will require two tables to keep track of roles and associations of users with roles.

These are the basic membership and role provider tables with “identity” primary keys.

'Users' membership and provider tables with identity primary keys.

SQL:

create table Users (
    UserID int not null AUTO_INCREMENT primary key,
    UserName varchar(128) not null,
    Password varchar(128) not null,
    Email varchar(256)
    );
    
create table Roles (
    RoleID int not null AUTO_INCREMENT primary key,
    RoleName varchar(128) not null
    );
    
create table UserRoles (
    UserID int not null,
    RoleID int not null,
    primary key (UserID, RoleID),
    foreign key (UserID) references Users(UserID),
    foreign key (RoleID) references Roles(RoleID)
    );

These are the basic membership and role provider tables with “unique identifier” primary keys.

'Users' membership and provider tables with unique identifier primary keys.

SQL:

create table Users (
    UserID varchar(36) not null primary key default '',
    UserName varchar(128) not null,
    Password varchar(128) not null,
    Email varchar(256)
    );
    
create table Roles (
    RoleID varchar(36) not null primary key default '',
    RoleName varchar(128) not null
    );

create table
UserRoles ( UserID varchar(36) not null, RoleID varchar(36) not null, primary key (UserID, RoleID), foreign key (UserID) references Users(UserID), foreign key (RoleID) references Roles(RoleID) );
delimiter $$ create trigger userinsert before insert on Users for each row begin set New.UserID = UUID(); end $$ create trigger roleinsert before insert on Roles for each row begin set New.RoleID = UUID(); end $$

Configuration

Use one of the scripts above to create the tables in your database.

Start Code On Time web application generator, select the project name on the start page, and choose Settings. Select Authentication and Membership.

Select “Enable custom membership and role providers” option and enter the following configuration settings.

table Users = Users
column [int|uiid] UserID = UserID
column [text] UserName = UserName
column [text] Password = Password
column [text] Email = Email

table Roles = Roles
column [int|uiid] RoleID = RoleID
column [text] RoleName = RoleName

table UserRoles = UserRoles
column [int|uiid] UserID = UserID
column [int|uiid] RoleID = RoleID

The configuration will guide the code generator in mapping the logical tables Users, Roles, and UserRoles to the physical tables in the database.

Generate the project to create the custom membership and role provider.

Friday, November 9, 2012PrintSubscribe
Minimal Membership Provider for MySQL

Requirements

A minimal membership provider requires a dedicated table to keep track of user names and passwords.

This is a sample “Users” table with “identity” primary key.

'Users' table in MySQL implemented with identity primary keys.

SQL:

create table Users (
    UserID int not null AUTO_INCREMENT primary key,
    UserName varchar(128) not null,
    Password varchar(128) not null
    );

Here is how the table may look if a “unique identifier” primary key is implemented. MySQL does not have built-in unique identifier capabilities, so the ID must be generated by a trigger.

Users table in MySQL is implemented with unique identifier primary keys.

SQL:

create table Users (
    UserID varchar(36) not null primary key default '',
    UserName varchar(128) not null,
    Password varchar(128) not null
    );

delimiter $$
create trigger userinsert
before insert on Users
for each row
begin
    set New.UserID = UUID();
end $$

User roles are hardcoded in the minimal Role Provider implementation.

Configuration

Create a table in your database using one of the scripts specified above.

Select the project name on the start page of the application generator and choose Settings.

Proceed to Authentication and Membership.

Select “Enable custom membership and role providers” option and enter the following configuration settings.

table Users=Users
column [int|uiid] UserID = UserId
column [text] UserName = UserName
column [text] Password = Password

role Administrators = admin
role Users = admin, user
role Everybody = *

The configuration maps logical table Users required for membership provider implementation to the physical database table Users. It also defines three user roles – Administrators, Users, and Everybody.

Generate the project.

Friday, March 9, 2012PrintSubscribe
Sakila Sample Database

A majority of examples rely on the Northwind sample database available for Microsoft SQL Server.

If your backend database is MySQL, then try Sakila sample database instead.

Getting MySQL on Your Computer

Download and install the MySQL Installer for Windows at http://dev.mysql.com/tech-resources/articles/mysql-installer-for-windows.html.

If you already have MySQL installed, then you can skip the above step.

By default, Sakila database comes with the MySQL installation. If you do not have the sample database, then you can download the database scripts at http://downloads.mysql.com/docs/sakila-db.zip.

Creating a Web Application

Run Code On Time web application generator and create a new Web Site Factory project.

Create Web Site Factory project in Code On Time web application generator

Give this project the name of “Sakila” and select the programming language of your choice (C# or Visual Basic.NET).

Selecting Name and Language of new web application project

Click Create to create the project. Press Next to reach the Database Connection page.

Change the Data Provider to “MySql.Data.MysqlClient” and press the button next to the Connection String string field.

Choosing MySQL data provider in web application generator

On the MySQL Connection page, enter your connection string settings. If you installed MySQL locally, enter:

Server Name localhost
User Name root
Password [Your Password]
Database Sakila

Entering MySQL Connection information in Code On Time generator

Press Next until you reach the Authentication and Membership page. MySQL can automatically install support for ASP.NET Membership in your database. Just check the first box on the page to enable membership support.

Enable ASP.NET Membership in Code On Time web application generator

Press Next until you reach the Themes page. Select “Vantage” theme.

Vantage theme in Code On Time web application generator

Hold down Shift key and press Next. This shortcut is available on any page of the web application generator wizard, and will skip to the Summary page.

Summary page in Code On Time generator

Click Generate to start the code generation.

A web browser window will open up when code generation has been completed.

Start using and customizing your own Sakila sample.

'Sakila' web application created with Code On Time

You can reference the same database in other projects whenever you need.

The next screenshot shows the built-in membership manager in an application created from MySQL database.

Code On Time Membership Manager in MySQL web application