DotNetSlackers: ASP.NET News for lazy Developers

Showing posts with label MySql. Show all posts
Showing posts with label MySql. Show all posts

Tuesday, October 13, 2015

MySQL .NET Entity Framework Code-First Migration

I was recently downloading and trying to run a MySQL docker image  which worked just fine on top of a Hyper-V based Ubuntu 14.04 LTS. Don’t worry, this is still Entity Framework Code-First migration article with Visual Studio 2015 and MySQL 5.6.27 (latest as of today).
I setup a Docker MySQL Image on my Ubuntu based Docker host. I was thinking what could be a best way to test this instance out with .NET than testing it with our good friend Entity Framework Code-First migration. This is really going to hit the MySQL instance hard by creating DB on its own and creating all the Model Tables on its own with little or no involvement of MYSQL DDL /DMLs.
Prerequisites : –
To start using Entity Framework 6 and Visual Studio 2015 is necessary to install
  • MySQL Connector/Net 6.9.7 (GA) (It may work with earlier releases, but this is the one I used for my POC)
  • MySQL for Visual Studio 1.2.4 GA (Starting with version 6.7, Connector/Net will no longer include the MySQL for Visual Studio integration. That functionality is now available in a separate product called MySQL for Visual Studio available using the MySQL Installer for Windows)
Action Begins
  1. Create a new Console Application. Select Framework as 4.5 or 4.0. This is because MySQL.Data.Entity Nuget package is supported only on these frameworks, though EntityFramework 6 is supported on .net 2.0, .net 4.0 and .net 4.5.
  2. Open Nuget Package Manager Console by navigating through Toos –> Nuget Package Manager –> Package Manager Console…  and run the below commands to install the Nuget packages needed for this project. Make sure that you select correct Project in Package Manager console if you are having multiple Projects in same Solution.
    1. Install-package EntityFramework -Version 6.0.0
    2. Install-package MySql.Data # as of today it installs 6.9.7
    3. Install-package MySql.Data.Entity # as of today it installs 6.9.7 version
    4. Install-package MySql.Data.Entities # as of today it installs 6.8.3 version (required for MYSQLGenerator in Step #9 below)
  3. Add MySQL ConnectionString to App.config / Web.config. Ensure the providerName is exactly same as provider defined in entityFramework-> providers section
<connectionStrings>
<add name=”mysqlconn” connectionString=”server=192.168.0.15;uid=root;pwd=root;database=testdb” providerName=”MySql.Data.MySqlClient“/>
</connectionStrings>
  1. You can remove any defaultConnectionFactory configs from entityFramework section.
  2. Add a DBContext class
class myDBContext : DbContext {
public myDBContext() : base(“mysqlconn”)
{
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
}
public DbSet<Person> People { get; set; }
}
  1. Add a Person class in Model folder
class Person
{
public int Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
}
  1. Compile the Project. this is most important as all further steps depend on ‘Reflection’ libs of the Project.
  2. Enable the Migrations from Nuget Package Manager console
PM> enable-migrations
Checking if the context targets an existing database…
Code First Migrations enabled for project mysqltester.
PM>
  1. Set SQL Generator in the Migration Configurations class.In the Migrations/Configuration.cs – Constructor. Add below line to set SQLGenerator attribute.
SetSqlGenerator(“MySql.Data.MySqlClient”, new MySql.Data.Entity.MySqlMigrationSqlGenerator());
  1. Create initial migration, which means the SQL Generation works for the Code-First entity Migration defined above.
PM> add-migration initial
Scaffolding migration ‘initial’.
The Designer Code for this migration file includes a snapshot of your current Code First model. This snapshot is used to calculate the changes to your model when you scaffold the next migration. If you make additional changes to your model that you want to include in this migration, then you can re-scaffold it by running ‘Add-Migration initial’ again.
PM>
  1. Check if update-database works , which means the EF was able to use the DBContext defined and connection from app / web.config.
PM> update-database
Specify the ‘-Verbose’ flag to view the SQL statements being applied to the target database.
Applying explicit migrations: [201510041122429_initial].
Applying explicit migration: 201510041122429_initial.
Running Seed method.
PM>
  1. Write a Sample Program to test/check the DBContext and data..
class Program
{
static void Main(string[] args)
{
myDBContext dbc = new myDBContext();
foreach (var item in dbc.People)
{
Console.WriteLine(“Name {0}, Age {1}”, item.Name, item.Age);
}
string ans = “n”;
do
{
Person newperson = new Person();
string temp=””;
int age = 0;
do
{
Console.WriteLine(“Enter Age”);
temp = Console.ReadLine();
} while (! (int.TryParse(temp, out age) && age > 0));
temp = null;
newperson.Age = age;
Console.WriteLine(“Enter Name “);
newperson.Name = Console.ReadLine();
dbc.People.Add(newperson);
Console.Write(“More People ?”);
ans = Console.ReadLine();
} while (ans.Equals(“y”, StringComparison.CurrentCultureIgnoreCase));
dbc.SaveChanges();
}
}
All the source code for this article can be found at GitHub repository here.

Personal and Technical Interview Question for Fresher in TOP IT Companies

Today, I am sharing interview question which I have faced during interview at fresher time. I have combined all round interview question which had asked to me in one group. I hope it will help to fresher. 



First Interview [HCL]


1. Tell about your technical profile.
2. What was your role in your project?
3. In which module you have worked explain?
4. Describe GridView events (Paper).
5. How to change Label's color based on Label's value  (Paper)?
6. Write the code to perform edit and delete operations using  GridView (Paper).
7. What are the Validation controls in ASP.Net?
8. How you will implement Custom Validator Control functionality (Paper)?
9. What are Generics?
10. What is the  difference between HashTable and Dictionary?
11. What is Ajax and  Jquery?
12. Which control have you used in AJAX?
13. What is the use of ModalPopUpExtender (Paper)?
14. WCF Basics (Types of binding)
15. What are Database Constraints?
16. Difference between Primary and Unique key?
17. Difference between Function and Procedure?
18. Can we store DataSet in View State?
19. When we will store DataSet in Session then which memory will be filled client side or server side?
20. Difference between reference and out parameter?
21. How to execute stored procedure?


Second Inerview [ ExcelSoft Technologies Pvt. Ltd. ]

1. What is View State?
2. Where is View State is saved and how View State value is retained
between Post Back. (Practical)?
3. Form Authentication Process (Using Web.Config file and Database in paper).
4. If View State value is "X" and I have changed it to "Y" in Page_Load
then what will be the final value of View State?
5. Page Life cycle with use.
6. Performance Analyzer tool.
7. How to declare unique key?
8. Diff. between Equi join and Right outer join (Paper)?
9. Define Caching types.
10. How to implement SQL Cache (Paper)?
11. How to call Web service using AJAX (Paper)?
12. How to change Color of Label using Jquery (Paper)?
13. What is Table Object/Variable?
14. How to call stored procedure using Entity Framework?
15. What is the difference between Overloading and Overriding?
16. What is the difference between ExecuteScalar() and ExecuteNonQuery()?
17. What if I will pass Select * in ExecuteScalar()?

Third Inteview [ NIIT ]

1. Tell me about projects.
2. Your role in project.
3. Which performance tool you have used?
4. Define abstract class and interface?
5. Why to use static members?
6. What is partial class and advantages?
7. GridView and DataList differences.
8. State Management type.
9. What is view state and use?
10. Caching techniques.
11. WCF and Web service differences.
12. Define WCF contracts.
13. Define design pattern.
14. What is facade pattern?
15. Triggers use and types.
16. Define cursor.
17. Difference between clustered and non-clustered index.
18. How many clustered index can be declared for a table.
19. What is view?
20. What is AJAX and Update-panel?
21. If you have 2 Update-panel then how to update one update panel from second?


Fourth Interview [ IDC Technology]


1. Define your Technical skills.

2. Your Role in project.
3. Define features of OOPS.
4. How you will replace the functionality of parent class function in
child class.(Paper)
5. Difference between Interface and abstract class (Use).
6. Functions of CLR.
7. Where you will use Cookie, Session and View State.
8. What are InProc and OutProc Session?
9. If you will save session in OutProc then where it will be saved.
and when it will be expired.
10. Difference between Web service and WCF
11. Design pattern.
12. Write sequence of select query-

 [ WITH ]
SELECT select_list [ INTO new_table ]
 [ FROM table_source ]
 [ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]

The UNION, EXCEPT and INTERSECT operators can be used between queries
to combine or compare their results into one result set.


Fifth Interview [ JAGBROS ]

1. What is ROW_NUMBER () in SQL Server?
2.  Difference between Union and Join.
3. Return value for ExecuteNonQuery().
4. Can we execute DML command in ExecuteScalar()?
5. Difference between DataSet and DataReader.
6. If provider is given OracleClient then can we connect with SQL Server?

7. Where you have used JQuery in your project?
8. Namespace for web-part (SharePoint)    - System.Web.UI.WebControls.WebParts Namespace
9. For creating a site what are the main concepts you need to consider in UL layer, middle-ware and      database Layer.



Building Node.js Projects in Visual Studio



Node.js Tools for Visual Studio ( NTVS), is an open-source Visual Studio plugin that transforms Visual Studio into a IDE for developing Node.js apps. Node.js has become extremely popular, as a server-side runtime environment for JavaScript apps; and this plugin brings the possibility for Visual Studio users to take advantage of their knowledge of the IDE to work with Node.js code. It includes IntelliSense, a Node.js interactive window, debugging and profiling, among other features. In this article, I provide an example of the usage of these features.

Writing Node.js code in Visual Studio and Using IntelliSense

You can download the latest version of Node.js Tools for Visual Studio here. At the time of writing, the latest version for Visual Studio 2013 was 1.0 Release Candidate. It is very stable and allows you to use Visual Studio 2013 as your IDE for Node.js projects without major problems. In the event you've worked with the previous versions of the plugin, you should upgrade because this release resolved many bugs and crashes. The project is developed and supported by Microsoft and the community. It is important to note that there is already a version that works with the Visual Studio 2015 preview, so you can expect that the forthcoming Visual Studio version will also allow you to use it as the IDE for Node.js projects.

Node.js Tools works with your existing Node.js installation. In fact, when you execute your Node.js project, the IDE will run the installed node.exe. The Visual Studio debugger uses the node V8 debugger under the hood, but you work with the IDE in the same way and with the same windows you are used to interacting with when coding in C# or other Visual Studio supported languages.
Once you install the tools, the New Project dialog box in Visual Studio provides the following new templates under Installed | Templates | JavaScript | Node.js:
  • Azure Cloud Service
  • From Existing Node.js code
  • Blank Node.js Console Application
  • Blank Node.js Web Application
  • Basic Node.js Express 3 Application
  • Starter Node.js Express 3 Application
  • Basic Node.js Express 4 Application
  • Blank Azure Node.js Web Application
  • Blank Azure Node.js Express 3 Application
  • Starter Azure Node.js Express 3 Application
  • Basic Azure Node.js Express 4 Application
Start a new project with the Blank Node.js Web Application template. You can use IntelliSense to replace the code in server.js with the following lines. This code uses the HTTP and URL libraries to create a very simple Node.js REST API that is capable of processing an HTTP GET status method with some parameters.

Listing One: Creating a simple REST API.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
// Require the HTTP library
var http = require("http");
// Require the URL library
var url = require('url');
var port = process.env.port || 1337;
var server = http.createServer(function (request, response) {
     
    var parsedUrl = url.parse(request.url, true);
    var query = parsedUrl.query;
     
    if ((request.method == 'GET') && (parsedUrl.pathname == '/status')) {
        status(query, response);
    }
});
function status(query, response) {
     
    var cameraToQuery = 0;
    var detectedFacesCount = 0;
    var globalStatus = 'N/A';
    var lensStatus = 'N/A';
    var environmentScore = 0;
    if (query.position) {
        switch (query.position) {
            case 'front': cameraToQuery = 1;
                globalStatus = 'OK';
                lensStatus = 'OK';
                environmentScore = 4;
                detectedFacesCount = 300;
                break;
            case 'back': cameraToQuery = 2;
                globalStatus = 'OK';
                lensStatus = 'OK';
                environmentScore = 3;
                detectedFacesCount = 150;
                break;
            case 'left': cameraToQuery = 3;
                globalStatus = 'OK';
                lensStatus = 'OK';
                environmentScore = 3;
                detectedFacesCount = 200;
                break;
            case 'right': cameraToQuery = 4;
                globalStatus = 'OK';
                lensStatus = 'OK';
                environmentScore = 2;
                detectedFacesCount = 170;
                break;
        }
    }
     
    console.log("Requesting status information for camera #: " + cameraToQuery);   
     
    // Write the response HEAD
    response.writeHead(200, {
        "Content-Type": "application/json",
        "Access-Control-Allow-Origin": "*",
        "Access-Control-Allow-Headers": "X-Requested-With"
    });
     
    var obj = {
        cameraId: cameraToQuery,
        globalStatus: globalStatus,
        lensStatus: lensStatus,
        environmentScore: environmentScore,
        detectedFacesCount: detectedFacesCount,
    };
     
    // Write the response body
    response.write(JSON.stringify(obj));
    // End of the response
    response.end();
}
// Start listening at port 1337 or the port number specified in process env port
server.listen(port);
console.log("Server is listening at port " + port);
As you might expect from your experience with Visual Studio, after you declare a variable, you can take advantage of IntelliSense to check the available methods and properties. For example, after you declare the http variable on line 2, you can check the methods available for http by typing the dot (.) after http in the following line (see Figure 1):



Node.JS
Figure 1: IntelliSense provides information about the available methods.

You will see the createServer function in the displayed list. When you select createServer, the IDE displays the documentation for the function. After you press Enter to auto-complete the http.createServer method and you open parenthesis to specify the arguments, the IDE displays the argument names list, in this case, requestListener.

The code in Listing One uses the HTTP and URL libraries to create a very simple Node.js REST API that is capable of processing an HTTP GET status method with some parameters. The server listens on port 1337 or the port specified in process.env.port. When the server receives an HTTP GET for the status method, it executes the status function. This function checks whether a position parameter value has been specified. The following are the four accepted values for position: front, back, left and right. The code converts the specified position value to a camera id (cameraToQuery) and generates values to simulate status data provided by cameras that detect and count faces in real-time. Then, the code sends the JSON response with values for the following keys: cameraId, globalStatus, lensStatus, environmentScore, and detectedFacesCount.

There are simpler ways to achieve the same goal by using other Node.js libraries, such as Express. However, I want to keep the sample code simple to allow you to understand the different features included in Node.js Tools for Visual Studio.

Select View | Other Windows | Node.js Interactive Window and the IDE will display the Node.js REPL (short for Read-Eval-Print Loop), which allows you to test code snippets. The interactive window supports IntelliSense.

Debugging the Application with the Local Debugger

Right-click on the project name within Solution Explorer and the IDE will display the General properties for the project where you can configure the Node.exe path, arguments, and the debugger port, among other properties. In this case, it isn't necessary to start a Web browser when the app is launched, and so, you can uncheck the Start web browser on launch checkbox.

You can set the desired breakpoints in the server.js file and launch the Web application. By default, the tools use node's local V8-based debugger. The debugging experience and the UI is exactly the same that you already know from working with other supported Visual Studio programming languages. Visual Studio connects to the node V8 debugger over a socket, and so you can also debug Node.js remotely running on another Windows computer or a different platform supported by Node.js.

For example, set a breakpoint at the first line of the status function:

var cameraToQuery = 0;

Then, start debugging the Web app. A node.exe window will appear and this window will display the Node.js console output. The window will display the following two lines (the port number might be different in case you have a configured value for process.env.port):

debugger listening on port 5858

Server is listening at port 1337


You can use a Web browser, Telerik Fiddler, or the curl utility to compose and send a GET request to localhost and the port in which the server is listening (1337 or process.env.port), adding /status and one of the possible values for the position parameter. For example, you can enter http://localhost:1337/status?position=left in your Web browser or run the following curl command:

curl -X GET 'http://localhost:1337/status?position=left'

Visual Studio will hit the breakpoint at the first line of the status function. You can use the Autos, Locals, and Call Stack windows (see Figure 2). You can also inspect variables and hover over either selected expressions or valid identifiers to display a popup with the results of their evaluation.

Node.JS
Figure 2: Using the Locals and Call Stack windows when debugging the Node.js Web app in Visual Studio.

This way, you can easily check that the query argument has a position property with a value equal to left. You can step over each line of code and use the Immediate Window to quickly evaluate expressions.

Once you finish executing the code in the status function, you will see the following JSON response in the Web browser or as a result of the curl command:
{"cameraId":3,"globalStatus":"OK","lensStatus":"OK","environmentScore":3,"detectedFacesCount":200}
Remember that the node.exe window displays the messages sent with console.log, consequently, you will see a new line in this window:
Requesting status information for camera #: 3

In this case, I'm using a conventional breakpoint. You can also take advantage of the advanced breakpoints. Go to the Breakpoints window and right click on a breakpoint to see all the available options. For example, the Hit Count… option allows you to make the breakpoint hit when the location is reached and a specific hit count condition is satisfied.

You can also configure how Break on exceptions should work. You just need to select Debug | Exceptions, expand Node.js Exceptions and check all the Thrown checkboxes for the different kinds of exceptions that you would like to make the debugger break when they are thrown.
You realize that back isn't the appropriate position name for camera number 2, and therefore, you want to change it from back to rear. Enter http://localhost:1337/status?position=rear in your Web browser or run the following curl command:

curl -X GET 'http://localhost:1337/status?position=rear'

The IDE will hit the previously set breakpoint again within the status function. Luckily, the IDE supports edit and continue. Thus, you can replace the value in the case statement from case 'back' to case 'rear', save the changes and continue executing the code. Once you finish executing the code in the status function, you will see the following JSON response in the Web browser or as a result of the curl command:

{"cameraId":2,"globalStatus":"OK","lensStatus":"OK","environmentScore":3,"detectedFacesCount":150}

Installing npm Packages with the Integrated NPM UI

The previous example was a very simple Node.js REST API that uses two standard libraries. However, in your real-life Node.js projects, you will want to install additional npm packages and use them. You can run npm from the Node.js Interactive Window (.npm) or the command line. Visual Studio watches the file system and will update the project with the additions.

However, the tools include an integrated NPM UI that enables you to easily search and install npm packages without running commands. In case you are running the project, stop its execution. Right-click on npm within the Solution Explorer and select Install new NPM packages…. A dialog box allows you to search for the available npm packages.

You just need to enter some text from the package name and the list will be updated with the package names and descriptions that match this text. For example, enter express and the dialog box will display all the packages that include express in either the name or the description. Select express 4.10.2 and notice that the right-hand side of the dialog box displays additional information about the selected package (see Figure 3), including its homepage. Then, click on Install Package. By default, the options make NPM install the latest version of the selected package with a standard dependency and automatically add it to package.json.

Node.JS
Figure 3: Installing a new NPM package with the integrated NPM UI.

If you have experience working with Node.js, it is difficult to avoid working with npm in the command line. However, the UI might be helpful to easily search for packages and check whether newer versions are available.

Node.js Tools for Visual Studio transforms Visual Studio into a very good Windows IDE for Node.js projects. If you work with other programming languages and are happy and productive using Visual Studio, you will want to install this add-in to code Node.js projects there as well.