Wednesday, May 27, 2015

Basic Sql Statements for MySQL

These are some basic Sql Statements I have used to work with MySQL database:

To insert new column in an existing table:

To add a column with data type equivalent to nvarchar in MSSQL
ALTER TABLE users ADD remarks varchar(1000) charset utf8;

To add a column after a specific column
ALTER TABLE users ADD status tinyint AFTER name;

To add a column at the first place

To delete a column from an existing table:


To rename a column from an existing table:


To delete a record from an existing table:


Saturday, May 23, 2015

Remember to include MySql.Data.dll

After I have uploaded the files to server, created the database and tables, when I accessed the website, I got the following error:

"Could not load file or assembly 'MySql.Data, Version=, Culture=neutral, PublicKeyToken=c5687fc88969c44d' or one of its dependencies. The system cannot find the file specified."

1. Go to the project.
2. Go to Reference folder, select MySql.Data.
3. Select True for Copy Local.
4. Then copy the bin folder (MySql.Data.dll is included) over to the server.

How to create table in server using script

I just learned how to create tables from scripts. This is really much faster to do.

1. Open Visual Studio, expand Server Explorer, and connect to MySQL database.

2. Right click on the table which you wish to generate the script, select Generate Table Script.

3. Copy the script to a text file, and save the script with extension .sql.

4. Go to phpMyAdmin.

5. Connect to the database and click on Import on the menu.

6. Choose the script file and click Go.

How to create database and tables in server?

Finally, my project has changed from working with MSSQL database to MySQL database. It's time for me to upload it to the server, and run from there.

I am hosting my site in Exabytes, it might be different from how others do in different web hosting.

1. Click on Databases

2. Click on Create Database.

3. Enter the database name and select User(s).

4. Click on Browse Database, and the phpMyAdmin will prompt for username and password to login.

5. Click on Databases on the menu, and select the database you want to connect.

6. As there is no table created, so I am prompted to create a new table.
Enter the Name, Number of columns and click Go.

7. Enter the column name and properties.
Remember for Id field, select PRIMARY from the Index property, and check A_I for auto increment.

8. Click on Insert to insert some data.

9. Enter the values and click Go.

10. You can also go to SQL tab to write the SQL query to insert the data.

I am going to figure out how to create tables from script, so it'll be easier for me to  create the tables in future.

Friday, May 22, 2015

How to rename MySQL table

I have made a mistake in MySQL table, so here is the simple command that helps to make the change:

MySql > RENAME TABLE oldName TO newNew;

How to change codes from MSSQL to MySQL

I have already developed my codes to work with MSSQL (Microsoft SQL Server) database. As I am changing my database to MySQL, I knew there are codes that I need to change. I was quite surprise that it's actually quite easy to make the change, and changes are really minimal.

1. using System.Data.SqlClient ---> using MySql.Data.MySqlClient

2. All classes to change from Sql to MySql.
For example:
SqlConnection ---> MySqlConnection
SqlDataAdapter ---> MySqlDataAdapter
SqlDataReader ---> MySqlDataReader
SqlParameter ---> MySqlParameter
SqlTransaction ---> MySqlTransaction

3. I used to use brackets to enclose my table name, just to be save. But, it seems like MySQL doesn't recognize the brackets. So, I have to remove all the brackets.
SELECT Name FROM [Users] ---> SELECT Name FROM Users

4. To get current  UTC time:
GetUTCDate() ---> UTC_DATE()

5. To select the last inserted Id:

I read that as we call from stored procedure, MySQL recognizes the parameter to prefix with ? instead of @. But, perhaps MySQL has updated this, as it works pretty fine when I am using all @ for the parameters.

Unable to connect to any of the specified MySQL hosts

I was working with MySQL and everything worked just fine. Suddenly, or just after some time, I got error connecting to MySQL database.

I tried to connect to MySQL via Command Line Client, but once I entered the password, it just showed me error.

I had no idea what went wrong. I googled, and found out some possible solutions. There was one suggestion to reset the root password. To reset the password, I need to stop the MySQL server. When I was there, only I found out the MySQL server has stopped. So, I just started it, and it runs well again.

To start MySQL server:
1. Go to Control Panel > Administration Tools > Service
2. Look for MySQL
3. Right click and click on Start if it's already stopped

Thursday, May 21, 2015

How to insert data into MySQL table?

I have just finished creating all the tables in the database, now it's time to insert some data manually.

I have not found out how to use MySQL for Visual Studio to do so, so I just used the MySQL Command Line Client.

mysql> INSERT INTO zef_status
             -> (Name)
             -> VALUES
             -> ("Active");

-> indicate a new line. It occurs when we press Enter.

We can also write the INSERT statement in one line:
mysql > INSERT INTO zef_status (Name) VALUES ("Inactive");

I will need to figure out how to connect to MySQL database in my ASP.NET project soon.

Creating table in MySQL

I have just successfully created my first table in the newly created MySQL database. I'd like to learn how to create the database and tables from script file, so it will be easier for me to re-create the same database in the web hosting server in future, but now I just learned how to create the table from Visual Studio Server Explorer.

1. Connect to the MySQL database in Visual Studio. (You can read how to do so here.)

2. Expand the database, you'll see these folders: Tables, Views, StoredProcedures, StoredFunctions,UDFs.

3. Right click on the Tables folder and click Create Table.

4. Just define the columns almost as how we do in MS SQL database.

5. To define the Primary Key:
1) In Column Properties, set Allow Nulls to No and Autoincrement to Yes.

2) Highlight the primary key column, select Table Designer in the menu, and click on Set Primary Key.

Note: To define like nvarchar() in MSSQL, here we can set the Data Type as varchar(n) and the Character Set as utf8.

This is how I created my first MySQL table in this application. I am going to explore more and convert my MS SQL database to MySQL database very soon.

Connecting MySQL database in Visual Studio

I have installed and set up MySQL Server in my Windows computer. As I have installed MySQL for Visual Studio, I'd like to connect MySQL database in Visual Studio, so it will be easier for me to work with the database while I am developing the application, as how I do with MS SQL database always.

1. Open Visual Studio.

2. Expand Server Explorer.

3. Click on Connect to database icon on top.

4. Change the Data Source to MySQL Database.
(You'll see this option if you have installed MySQL for Visual Studio)

5. Enter the information required to connect to database.
Server name: Enter the Server name as localhost if you are connecting the database locally
Username & password: Enter the user name and password that you've defined while setting up the MySQL Server.
Database name: the database that you wish to connect
Click on Test Connection button to test if the connection to database has been successful.

I refer to this tutorial to get my MySQL database connected in Visual Studio.

Creating MySQL database

I am developing an application which requires me to use MySQL database. I'd just like to write down how I achieve it.

While I am documenting this, I just found out I have written a few blog posts about MySQL too. I did another application with MySQL before, but I totally forgot about how to work with MySQL. So, it's good for me to review and re-learn.

Install & Set up MySQL
Download the installer here.
The version I downloaded is 5.6.24.

I follow this tutorial to set up the MySQL server in my Windows OS.

The products I chose: MySQL Server, MySQL Connector, MySQL for Visual Studio

Create Database (using MySQL Command Line)
1. Launch the MySQL Command Line Client.

2. Enter password

3. To create database:
mysql> create database [database_name];

4. To display a list of available databases (to check if the database has been created):
mysql> show databases;

I refer to this tutorial.

Note: I don't know how to use MySQL for Visual Studio to create database, so I used the command line to do so.

I will write how to connect to MySQL database in Visual Studio in next post.

Wednesday, March 4, 2015

How to add a new tab in administration edit product page in nopCommerce?

Scenario: I need to add a section "Weather Recommender" in Product page, so the admin can configure the related information.

I followed the steps here and here to add the new properties to the Product entity.

In order to add another new tab, I tried these step and I was glad that it works!

1. Go to Presentation > Nop.Admin > Views > Product >  _CreateOrUpdate.cshtml

2. Add these codes in the "product-edit" div in the list.


  •             @T("Admin.Catalog.Products.WeatherRecommender")

    3. Also add this code in the "product-edit" div.


    4. Add this at the end of the class too:
    @helper TabWeatherRecommender()
        @Html.Partial("_CreateOrUpdate.WeatherRecommender", Model)

    I just copied and pasted from the existing tab information.

    5. Create a _CreateOrUpdate.WeatherRecommender.cshtml (Just copy from one of the existing file)

    6. Add the admin content to the class file,
    such as:

                @Html.NopLabelFor(model => model.WRPublished):
                @Html.EditorFor(model => model.WRPublished)
                @Html.ValidationMessageFor(model => model.WRPublished)

    7. Remember to go to AdministrationLanguages > Add new resource for "Admin.Catalog.Products.WeatherRecommender", so the value will be displayed as the tab name.

    Tuesday, February 10, 2015

    How to add a new picture property to Category entity in nopCommerce?

    I wrote a post about how to add a new property to Product entity in nopCommerce earlier, this is quite similar, but it still took me some time to figure out how to do. So I think it's good for me to write another post for this too.

    Scenario: I'd like to add a top banner to the category page. When user clicks on it, it'll navigate to another page.

    1. Go to Database and add new columns TopBannerId & TopBannerLink in Category table.

    It's recommended to add a new column via SQL.
    ALTER TABLE [dbname].[dbo].[Category] ADD [TopBannerId] INT NOT NULL DEFAULT '0'
    ALTER TABLE [dbname].[dbo].[Category] ADD [TopBannerLink] NVARCHAR(500) NULL

    2. Go to Libraries > Nop.Core > Domain > Catalog > Category.cs
    Add this code:
    public int TopBannerId { get; set; }
    public string TopBannerLink { get; set; }

    3. Go to Libraries > Nop.Data > Mapping > Catalog > CategoryMap.cs
    Add this code:

    this.Property(p => p.TopBannerLink).HasMaxLength(500).IsOptional();

    4. Go to Presentation > Nop.Admin > Models > Catalog > CategoryModel.cs
    Add these codes:

            public int TopBannerId { get; set; }


            public string TopBannerLink { get; set; }

    5. Go to Presentation > Nop.Admin > Validators > Catalog > CategoryValidator.cs
    Add this code:

    RuleFor(x => x.TopBannerLink).Length(0, 500);

    6. Go to Presentation > Nop.Admin > Views > Category >  _CreateOrUpdate.Info.cshtml
    Add these codes to where you want to edit the new property:

                    @Html.NopLabelFor(model => model.TopBannerId)
                    @Html.EditorFor(model => model.TopBannerId)
                    @Html.ValidationMessageFor(model => model.TopBannerId)

                    @Html.NopLabelFor(model => model.TopBannerLink):
                    @Html.EditorFor(model => model.TopBannerLink)
                    @Html.ValidationMessageFor(model => model.TopBannerLink)


    5. Go to Presentation Nop.Admin > Controllers > CategoryController.cs
    Add in these codes:

    Method name: Edit(CategoryModel model, bool continueEditing)

    This is to delete the old top banner when deleting or updating.
    I just modified the codes from the Picture, and placed the codes just below it.

    int prevTopBannerId = category.TopBannerId;

    if (prevTopBannerId > 0 && prevTopBannerId != category.TopBannerId)
                        var prevTopBanner = _pictureService.GetPictureById(prevTopBannerId);
                        if (prevTopBanner != null)

    6. Go to Presentation Nop.Web Models > CatalogCategoryModel.cs
    Add in this code:

    public PictureModel TopBannerModel { get; set; }
    public string TopBannerLink { get; set; }

    7. Go to Nop.Web > Infrastructure > Cache > ModelCacheEventConsumer.cs

    Add these codes:

            public const string CATEGORY_TOP_BANNER_MODEL_KEY = "Nop.pres.category.topbanner-{0}-{1}-{2}-{3}-{4}-{5}";
            public const string CATEGORY_TOP_BANNER_PATTERN_KEY = "Nop.pres.category.topbanner";

    8. Go to Presentation Nop.Web Controllers CategoryController.cs

    Method name: Category(CategoryModel model, bool continueEditing)
    Place these codes after var model = category.ToModel();

    int topBannerSize = _mediaSettings.MaximumImageSize;
                var categoryTopBannerCacheKey = string.Format(ModelCacheEventConsumer.CATEGORY_TOP_BANNER_MODEL_KEY, category.Id, topBannerSize, true, _workContext.WorkingLanguage.Id, _webHelper.IsCurrentConnectionSecured(), _storeContext.CurrentStore.Id);
                model.TopBannerModel = _cacheManager.Get(categoryTopBannerCacheKey, () =>
                    var topBanner = _pictureService.GetPictureById(category.TopBannerId);
                    var topBannerModel = new PictureModel
                        FullSizeImageUrl = _pictureService.GetPictureUrl(topBanner),
                        ImageUrl = _pictureService.GetPictureUrl(topBanner, topBannerSize),
                        Title = string.Format(_localizationService.GetResource("Media.Category.ImageLinkTitleFormat"), model.Name),
                        AlternateText = string.Format(_localizationService.GetResource("Media.Category.ImageAlternateTextFormat"), model.Name)
                    return topBannerModel;

    9. Go to Presentation Nop.Web ViewsCatalog > CategoryTemplate.ProductsInGridOrLines.cshtml

    Add in these codes where you want to display the picture.

                         title="@Model.TopBannerModel.Title" />

    10. Go to Admin > Configuration > Languages
    Click on View string resouces for your language.
    Add new record for the newly added field - The name of the resouce is the one we previously set in step 4.

    Monday, February 2, 2015

    How to modify About Us page in nopCommerce?

    My tasks:
    1) Show the About Us link in top menu.
    2) Change the About Us page to only one column.
    3) Modify the contents in About Us.

    There is already an About Us page created in nopCommerce, so we can just make use of that page without creating a new one.

    1. Go to Administration > Content Management > Topics (Pages)

    2. Click on the Edit button for AboutUs page - modify the contents and check include in top menu.

    3. Look for the file : \Presentation\Nop.Web\Views\Topic\TopicDetails.cshtml

    Change this code to:
    Layout = "~/Views/Shared/_ColumnsOne.cshtml";

    How to change the default title in nopCommerce?

    The default title currently showing for each page is Your store. It's a very simple setting for us to change the store name.

    1. Go to Administration > Configuration > Settings >   General And Miscellaneous Settings

    2. Go to SEO Settings tab

    3. Change the Default title 

    You can also set other SEO information here.

    How to remove search box in nopCommerce?

    I do not need the default search box to be displayed on my site, so I found a way to remove it.

    1. Look for the file : \Presentation\Nop.Web\Views\Shared\Header\Header.cshtml

    2. Just remove this code section:

            @Html.Action("SearchBox", "Catalog")

    How to disable wishlist in nopCommerce

    This is just another simple setting.

    1. Go to Administration > Configuration > Access Control List

    2. Just uncheck the last item Public Store. Enable Wishlist accordingly.

    Sunday, February 1, 2015

    How to change currency in nopCommerce?

    By default, the currency displayed on the site is USD, and I'd like to change it to my own country currency, which is MYR.

    This is how I changed:

    1. Go to Administration > Configuration > Currencies

    2. If your currency is already in the list, just publish or mark it as primary store currency, otherwise click on Add New button to add a new currency.

    3. This is the setting I created for my own country currency.

    4. As we do not provide the currency exchange feature, so I just un-publish the other currencies, but just published this newly created currency.

    Sunday, January 18, 2015

    How to add a new property to Product entity in nopCommerce?

    There are many chances we'd like to add some new properties to the existing entity, such as Product or Category. I just learned how to add a new property to the Product entity, and here's how I did it.

    I learned from this tutorial from nopCommerce documentation - Updating an existing entity. How to add a new property.

    Scenario: I need to add a new CTALink property to the Product entity, and show the link in Product page.

    1. Go to Database and add a new column CTALink in Product table.

    2. Go to Libraries > Nop.Core > Domain > Catalog > Product.cs
    Add this code:
    public virtual string CTALink { get; set; }

    3. Go to Libraries > Nop.Data > Mapping > Catalog > ProductMap.cs
    Add this code:
    this.Property(p => p.CTALink).HasMaxLength(500).IsOptional();

    4. Go to Presentation > Nop.Admin > Models > Catalog > ProductModel.cs
    Add this code:
    public string CTALink { get; set; }

    5. Go to Presentation > Nop.Admin > Validators > Catalog > ProductValidator.cs
    Add this code:
    RuleFor(x => x.CTALink).Length(0, 500);

    6. Go to Presentation > Nop.Admin > Views > Product >  _CreateOrUpdate.Info.cshtml
    Add these codes to where you want to edit the new property:

                @Html.NopLabelFor(model => model.CTALink):
                @Html.EditorFor(model => model.CTALink)
                @Html.ValidationMessageFor(model => model.CTALink)

    7. Go to Presentation > Nop.Admin > Controllers > ProductController.cs
    Add in these:
    Method name: PrepareProductModel()
    model.CTALink = product.CTALink;

    Method name: Create(ProductModel model, bool continueEditing)
    product.CTALink = model.CTALink;

    Method name: Edit(ProductModel model, bool continueEditing)
    product.CTALink = model.CTALink;

    8. To display this on product page.
    Go to Presentation > Nop.Web
    Copy the ProductTemplate.Simple.cshtml in Product folder into your theme's Views folder.
    Add the necessary code to display the new property on the Product page.
    @if (!String.IsNullOrEmpty(Model.CTALink))

                  Go Get It!

    Thursday, January 8, 2015

    How do create your own theme in nopCommerce?

    In nopCommerce, we can create our own theme and modify the existing code files without affecting the original ones.

    1. Create a new folder with your theme name in Themes folder in the Nop.Web project.
    e.g. Themes\[new_theme]
    * Or you can copy the DefaultClean folder in the Themes folder and rename it to the new theme.

    2. Whenever you want to modify the codes in any files, you can just copy the file, create the respective folder in your new theme folder, and paste the file over. Then, modify your new file.
    E.g. If you want to change the home page.
    step 1. Create a Views folder in your Themes\[new_theme]
    step 2. Create a Home folder in your Themes\[new_theme]\Views folder
    step 3. Go to Views\Home folder, copy Index.cshtml file over to your Themes\[new_theme]\Views folder
    * Remember to modify the Index.cshtml in your own theme folder whenever you want to customize the home page.
    Same goes to other files.

    3. Modify the theme.config file

    [new theme name]
         previewText="The '[new theme name]' site theme.">

    4. Go to Administration area to select your new theme.
    Configuration >  General And Miscellaneous Settings > Select default store theme.

    ** Please note that I am just a beginner in developing nopCommerce e-commerce website. My ways of doing might not be the perfect or very good method, and just how I am working on them currently while learning.

    Wednesday, January 7, 2015

    How to install a plugin in nopCommerce?

    I have purchased the Nop Store Locator from nop-Templates . Here is how I install the plugin.

    1. Unzip the downloaded plugin file.

    2. Copy the folder into the Presentation\Nop.Web\Plugins folder of your solution.
    * If you do not see the plugin file, you can click on Refresh and Show All Files in the solution explorer.
    * Please note that the plugin folder is to be copied into the Presentation folder, not the Plugins folder in the main folder.

    3. Run the project and go to Administration area.

    4. Go to Configuration > Plugins > Local Plugins, you will see a list of plugins.
    If you do not see the newly added plugin, click on Reload list of plugins button at the top right corner.

    5. Find the newly added plugin and click Install.

    The plugin is installed and ready to use.

    You can also read the documentation from nop-Templates here.

    ** Please note that I am just a beginner in developing nopCommerce e-commerce website. My ways of doing might not be the perfect or very good method, and just how I am working on them currently while learning.

    How to add new categories in nopCommerce?

    I am working on a nopCommerce project now. I think it'd be good for me to write down the steps for doing certain things, so it will help me to remember how to do these in future, and also hopefully it might help others too.

    1. Click on the Administration link in the home page.

    2. Select Catalog > Categories > List from the menu.

    3. Click on Add New button to add new category.
    * You can define the sub category by selecting its parent category.
    * You can choose whether to allow customers select the page size. If not, then you can specify the page size.
    * If you want this category to be displayed on the top menu, check the Include in top menu option.

    4. Click on Save button.

    You'll see the list here:

    When you go to the home page of your store, you'll see the categories listed in the top menu (if you select it to be).

    ** Please note that I am just a beginner in developing nopCommerce e-commerce website. My ways of doing might not be the perfect or very good method, and just how I am working on them currently while learning.

    How to install nopCommerce?

    I just started to learn about nopCommerce, as I have just recently accepted a job which is to develop an e-commerce website with nopCommerce.

    Though it seems to be very easy to install the nopCommerce, it really took me days to do so, especially when setting up the database. It's actually very simple, but I just didn't know how to do it right.

    Here, I'm going to write the simple steps to install nopCommerce.

    1. Go to nopCommerce download page to download the latest version.
    I chose the Source code version. The current latest version is 3.50.

    screen shot from nopCommerce download page

    2. Unzip the downloaded file and open the nopCommerce.sln file. (I am using Visual Studio 2013.)

    3. Set the Nop.Web project as start project. (Nop.Web project is located inside Presentation folder.)

    4. The Install page will be displayed. I have encountered many issues during this stage previously.

    Store information:
    Admin user email:
    Admin user password: admin

    Create sample data: uncheck (unless you want some sample data added to your new database)
    Create database if it doesn't exist: check
    Enter SQL connection values: check
    SQL Server name: [PC name\SQL Named instance]
    Database name: [The database name you'd like to create]

    Previously, I had errors because I didn't enter the SQL server name correctly. I didn't know I have to add the PC name. It's my mistake, as I am not familiar with all these installation and configuration. Though it took me days, it's good for me to learn.

    5. Once the nopCommerce has been successfully installed, you'll see this page.

    Hope you will not take much time to get the nopCommerce installed.

    Happy developing with nopCommerce.

    ** Please note that I am just a beginner in developing nopCommerce e-commerce website. My ways of doing might not be the perfect or very good method, and just how I am working on them currently while learning.