Microsoft.Net Developer

SQL Server - Find Foreign key on table column

How to find foreign key on specific column in the table in SQL Server. Below is the script...

SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS [table],
    col1.name AS [column],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
where tab1.name='tablename'
and  col1.name ='columnname'

Hope this will help you.

Enjoy !!!

Mongodb - Create instance

Download Mongodb

Open url - https://www.mongodb.com

It will download mongodb-win32-x86_64-2008plus-ssl-4.0.0-signed.msi file, install it in your computer.

Setup Configfilename.conf file

create file and type below commands

storage.dbPath: "D:/Data/Mongo/dbname"
systemLog:
   destination: file
   path: "d:/Data/Mongo/dbname/log/mongo.log"
   logAppend: true
net:
   port: portnumber (e.g. 20005)

Command

mongod.exe --config D:\Data\Mongo\configfilename.conf --service --install --serviceName "MongoDB dbname PortNumber" --serviceDisplayName "MongoDB dbname PortNumber"

Execute command

Open my computer and goto path C:\Program Files\MongoDB\Server\3.0\bin

Type cmd on path, it will open command window

Execute above command

Then open "Services.msc" and start service which you created

Enjoy !!!

Host .Net Core Web API on IIS - ISSUE

After uploaded .Net Core web API on IIS server, I faced below issue , but I found solution after some trial and error.

Issue

<div class = "content-container">
 <h3> HTTP Error 502.5 - Process Failure </h3>
</div>
<div class = "content-container">
 <fieldset>
  <h4> Common causes of this issue: </h4>
  <ul>
   <li> The application process failed to start </li>
   <li> The application process started but then stopped </li>
   <li> The application process started but failed to listen on the configured port </li>
  </ul>
 </fieldset>
</div>

Solution

Open web.config file and make some changes as below

Existing Configuration

<aspNetCore processPath="dotnet" arguments=".\CoreApp.dll -argFile IISExeLauncherArgs.txt" forwardWindowsAuthToken="false" startupTimeLimit="3600" requestTimeout="23:00:00" stdoutLogEnabled="false" stdoutLogFile=".\logs\stdout" />

Working Configuration:

<aspNetCore processPath="dotnet" arguments=".\CoreApp.dll -argFile IISExeLauncherArgs.txt" forwardWindowsAuthToken="false" startupTimeLimit="3600" requestTimeout="23:00:00" stdoutLogEnabled="false" stdoutLogFile=".\logs\stdout" />

1. I had removed ".\" before dll name, as I hosted my application on root folder on server.

2. Removed -argFile IISExeLauncherArgs.txt

I hope that you also resolve your issue by using this solution.

Enjoy !!!

Code First Migration in ASP.Net Core

Generally we create database first and then we create data model classes in project, and while we publish application we need to take care about database creation and table.

Code first, its approach that we first create data model classes and code itself create database and while publishing application, we don't need to take care about database and its tables, code automatically manage.

While executing code first migration command in .Net Core C#, we are facing some issues sometimes, here I faced some issues and found some solutions as below.

Issue 1

Getting error, while executing command

Add-Migration -Context "DatabaseContext"

 Unable to create an object of type 'DatabaseContext'. Add an implementation of 'IDesignTimeDbContextFactory<DatabaseContext>' to the project, or see https://go.microsoft.com/fwlink/?linkid=851728 for additional patterns supported at design time.

Solution

public class DesignTimeDbContextFactory : IDesignTimeDbContextFactory<DatabaseContext>
{
 public DatabaseContext CreateDbContext(string[] args)
 {
  IConfigurationRoot configuration = new ConfigurationBuilder()
   .SetBasePath(Directory.GetCurrentDirectory())
   .AddJsonFile("appsettings.json")
   .Build();
  var builder = new DbContextOptionsBuilder<DatabaseContext>();
  var connectionString = configuration.GetConnectionString("dbConStr");
  builder.UseSqlServer(connectionString);
  return new DatabaseContext(builder.Options);
 }
}

Issue 2

Getting error, while executing below command

Add-Migration -Name "Migration Name" -Context "DatabaseContext"

GenericArguments[0], 'SampleWebAPI.Migrations.DatabaseContext', on 'Microsoft.EntityFrameworkCore.Design.IDesignTimeDbContextFactory`1[TContext]' violates the constraint of type 'TContext'.

Solution

1. First remove migration folder (if any) from project which was created by above command

Issue 3

Getting error, while executing command

Update-Database

More than one DbContext was found. Specify which one to use. Use the '-Context' parameter for PowerShell commands and the '--context' parameter for dotnet commands.

Solution

Update-Database -Context "DatabaseContext"

Finally

Use below migration commands for .Net Core

1. Add-Migration -Name "Migration Name" -Context "DatabaseContext"

2. Update-Database -Context "DatabaseContext"

Enjoy !!!

Code First Steps in ASP.Net C#

Generally we create database first and then we create data model classes in project, and while we publish application we need to take care about database creation and table.

Code first, its approach that we first create data model classes and code itself create database and while publishing application, we don't need to take care about database and its tables, code automatically manage.

Lets see, how to work with code first in .Net C#

Steps

1.First provide connection string in web.config file.

<connectionStrings>
    <add name="conDemoDB" connectionString="data source=ServerName; initial catalog=dbName; user id=username; password=password;" providerName="System.Data.EntityClient"/>
  </connectionStrings>

2. Create data model classes as per your requirement

3. Install Entity Framework using nu-get package installer or command

4. Create DataContext Class

using System.Data.Entity;
using System.Data.Entity.Infrastructure;
 
namespace DemoApp.Reporsitory
{
    public class dataContext : DbContext
    {
        public dataContext()
            : base("name=conDemoDB")
        {
        }
 
        public virtual DbSet<Menu> Menus { get; set; }
        public virtual DbSet<MenuItem> MenuItems { get; set; }
    }
}

5. Open Package Manager Console from Tools -> NuGet Package Manager

1. Enable-Migrations  

2. Add-Migration "Comments"

3. Update-Database -Verbose

Once command executes successfully, you can open database and check that tables are created.

for more detail, https://docs.microsoft.com/en-us/ef/ef6/modeling/code-first/migrations/automatic

Enjoy !!!

:)

Swagger with ASP.Net Core 2.0 Web API

Swagger is a open source framework, which helps to create API documentation.

Using swagger, developer does not need to mention to developer who want to consume API, its straight forward to visualization.

Here, we will see that how to use swagger with ASP.Net core 2.0 web API.
First install "Swashbuckle.AspNetCore" using nuget package manager.

Add swagger service to application services, Open startup.cs and do following changes.

public void ConfigureServices(IServiceCollection services)
{
 services.AddMvc();

 services.Configure<IISOptions>(options =>
 {
  options.ForwardClientCertificate = false;
 });

 services.AddSwaggerGen(c =>
 {
  c.SwaggerDoc("v1", new Info
  {
   Version = "v1",
   Title = "Demo Service",
   Description = "Demo Service Description",
   TermsOfService = "None",
   Contact = new Contact() { Name = "Rohit Rathod", Email = "anrorathod@gmail.com", Url = "www.mywebsite.com" }
  });
 });
}

Here, SwaggerDoc Info properties are optional (Version, Title, Description, TermsOfService and Contact).

Now, write below code to enable swagger UI,

public void Configure(IApplicationBuilder app, IHostingEnvironment env)
{
 if (env.IsDevelopment())
 {
  app.UseDeveloperExceptionPage();
 }

 app.UseMvc();

 string appPath = "/DemoService.WebAPI/";
 app.UseSwagger();
 app.UseSwaggerUI(c =>
 {
  c.SwaggerEndpoint(appPath + "swagger/v1/swagger.json", "Demo Service");
 });
}

Now, build web API application, open web browser and run application and you will see API documentation.

http://localhost/DemoService.WebAPI/swagger/

I hope that this will helpful in your web API project.

Enjoy !!!

Consume web API in MVC or Windows Application or Console Application

Sometime, we need to consume web API mostly in MVC or Windows Service or Windows Application or Console Application.

Below code posts dynamic object in API call,

var url = "http://localhost/DemoWebAPIProject/api/ControllerName/Method";

Uri requestUri = new Uri(url);  
dynamic dynamicObject = new ExpandoObject();
dynamicObject.UserName = "anorathod".ToString();
dynamicObject.Password = "password";

string json = "";
json = Newtonsoft.Json.JsonConvert.SerializeObject(dynamicObject);

var httpClient = new HttpClient();
HttpResponseMessage response = await httpClient.PostAsync(requestUri, new StringContent(json, System.Text.Encoding.UTF8, "application/json"));

string responJsonText = await response.Content.ReadAsStringAsync()

I hope that this will helpful in your project.

Enjoy !!!

:)