Windows 2012 Hosting - MVC 4 and SQL 2012 BLOG

Tutorial and Articles about Windows Hosting, SQL Hosting, MVC Hosting, and Silverlight Hosting

ASP.NET MVC 6 Hosting - ASPHostPortal :: Bootstrap Tree View in ASP.Net MVC

clock August 24, 2015 07:42 by author Kenny

Bootstrap Tree View in ASP.Net MVC

The ASP.NET MVC is an almost open source web application framework that implements the model–view–controller (MVC) pattern. The ASP.NET MVC framework is a lightweight, highly testable presentation framework that (as with Web Forms-based applications) is integrated with existing ASP.NETfeatures, such as master pages and membership-based authentication. Bootstrap is the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first web sites.

We create two classes, one is AuthorViewModel and another is BookViewModel. AuthorViewModel is the main class that has an association with the BookViewModel class. In other words each AuthorViewModel class object has a list of BookViewModel class objects. The following is the code snippet for the BookViewModel class. 

namespace TreeView.Models   
{  
    public class BookViewModel   
    {  
        public long Id   
        {  
            get;  
            set;  
        }  
        public string Title   
        {  
            get;  
            set;  
        }  
        public bool IsWritten   
        {  
            get;  
            set;  
        }  
    }  
}  

The following is the code snippet for the AuthorViewModel class.

using System.Collections.Generic;  
 
namespace TreeView.Models {  
    public class AuthorViewModel   
    {  
        public AuthorViewModel()   
        {  
            BookViewModel = new List < BookViewModel > ();  
        }  
        public int Id   
        {  
            get;  
            set;  
        }  
        public string Name   
        {  
            get;  
            set;  
        }  
        public bool IsAuthor   
        {  
            get;  
            set;  
        }  
        public IList < BookViewModel > BookViewModel   
        {  
            get;  
            set;  
        }  
    }  
}

Now we create a controller “HomeController” that has two action methods for both GET and POST requests. The action method's name is “Index”. The Get request action method returns a tree view in the UI whereas the POST request method gets the posted data from the UI. The following is the code snippet for HomeController.

using System.Collections.Generic;  
using System.Linq;  
using System.Web.Mvc;  
using TreeView.Models;  
 
namespace TreeView.Controllers  
{  
    public class HomeController : Controller  
    {  
        [HttpGet]  
        public ActionResult Index()  
        {  
            List<AuthorViewModel> model = new List<AuthorViewModel>();  
 
            AuthorViewModel firstAuthor = new AuthorViewModel  
            {  
                Id = 1,  
                Name = "User1",  
                BookViewModel = new List<BookViewModel>{  
                    new BookViewModel{  
                        Id=1,  
                        Title = "JQuery",  
                        IsWritten = false  
                    }, new BookViewModel{  
                        Id=1,  
                        Title = "JavaScript",  
                        IsWritten = false  
                    }  
                }  
            };  
 
            AuthorViewModel secondAuthor = new AuthorViewModel  
            {  
                Id = 2,  
                Name = "User2",  
                BookViewModel = new List<BookViewModel>{  
                    new BookViewModel{  
                        Id=3,  
                        Title = "C#",  
                        IsWritten = false  
                    }, new BookViewModel{  
                        Id=4,  
                        Title = "Entity Framework",  
                        IsWritten = false  
                    }  
                }  
            };  
            model.Add(firstAuthor);  
            model.Add(secondAuthor);  
            return View("Index", model);  
        }  
 
        [HttpPost]  
        public ActionResult Index(List<AuthorViewModel> model)  
        {  
            List<AuthorViewModel> selectedAuthors = model.Where(a => a.IsAuthor).ToList();  
            List<BookViewModel> selectedBooks = model.Where(a => a.IsAuthor)  
                                                .SelectMany(a => a.BookViewModel.Where(b => b.IsWritten)).ToList();  
            return View();  
        }  
    }  

The preceding code shows how books are associated with an author in the GET action method and how to get a selected tree node (authors and books) in the POST request. 

Bootstrap CSS is already added to the application but we write a new custom CSS for the tree view design. The following is the code snippet for the tree CSS.

.tree li {  
    margin: 0px 0;    
    list-style-type: none;  
    position: relative;  
    padding: 20px 5px 0px 5px;  
}  
 
.tree li::before{  
    content: '';  
    position: absolute;   
    top: 0;  
    width: 1px;   
    height: 100%;  
    right: auto;   
    left: -20px;  
    border-left: 1px solid #ccc;  
    bottom: 50px;  
}  
.tree li::after{  
    content: '';  
    position: absolute;   
    top: 30px;   
    width: 25px;   
    height: 20px;  
    right: auto;   
    left: -20px;  
    border-top: 1px solid #ccc;  
}  
.tree li a{  
    display: inline-block;  
    border: 1px solid #ccc;  
    padding: 5px 10px;  
    text-decoration: none;  
    color: #666;      
    font-family: 'Open Sans',sans-serif;  
    font-size: 14px;  
    font-weight :600;  
    border-radius: 5px;  
    -webkit-border-radius: 5px;  
    -moz-border-radius: 5px;  
}  
 
/*Remove connectors before root*/  
.tree > ul > li::before, .tree > ul > li::after{  
    border: 0;  
}  
/*Remove connectors after last child*/  
.tree li:last-child::before{   
      height: 30px;  
}  
 
/*Time for some hover effects*/  
/*We will apply the hover effect the the lineage of the element also*/  
.tree li a:hover, .tree li a:hover+ul li a {  
    background: #dd4814; color: #ffffff; border: 1px solid #dd4814;  
}  
/*Connector styles on hover*/  
.tree li a:hover+ul li::after,   
.tree li a:hover+ul li::before,   
.tree li a:hover+ul::before,   
.tree li a:hover+ul ul::before{  
    border-color:  #dd4814;  
}  
.tree-checkbox{  
    margin :4px !important;  
}  
 
   
.tree:before {  
    border-left:  1px solid #ccc;  
    bottom: 16px;  
    content: "";  
    display: block;  
    left: 0;  
    position: absolute;  
    top: -21px;  
    width: 1px;  
    z-index: 1;  
}  
 
.tree ul:after {  
    border-top: 1px solid #ccc;  
    content: "";  
    height: 20px;  
    left: -29px;  
    position: absolute;  
    right: auto;  
    top: 37px;  
    width: 34px;  
}  
*:before, *:after {  
    box-sizing: border-box;  
}  
*:before, *:after {  
    box-sizing: border-box;  
}  
.tree {  
    overflow: auto;  
    padding-left: 0px;  
    position: relative;  

Now we create an Index view that renders in the browser and shows the tree view for the author and book. The following is the code snippet for the Index view.

@model List  
<TreeView.Models.AuthorViewModel>  
@section head{  
@Styles.Render("~/Content/css/tree.css")  
}  
    <div class="panel panel-primary">  
        <div class="panel-heading panel-head">Author Book Tree View</div>  
        <div id="frm-author" class="panel-body">  
@using (Html.BeginForm())  
{  
            <div class="tree">  
@for (int i = 0; i < Model.Count(); i++)  
{  
                <ul>  
                    <li>  
                        <a href="#">  
@Html.CheckBoxFor(model => model[i].IsAuthor, new { @class = "tree-checkbox parent", @id = @Model[i].Id })  
                            <label for=@i>  
                                <strong>Author:</strong>  
@Html.DisplayFor(model => model[i].Name)  
                            </label>  
                        </a>  
                        <ul>  
@for (int j = 0; j < Model[i].BookViewModel.Count(); j++)  
{  
int k = 1 + j;  
@Html.HiddenFor(model => model[i].BookViewModel[j].Id)  
                            <li>  
                                <a href="#">  
@Html.CheckBoxFor(model => model[i].BookViewModel[j].IsWritten, new { @class = "tree-checkbox node-item", @iid = i + "" + j })  
                                    <label for=@i@j>  
                                        <strong>Book @(k):</strong> @Html.DisplayFor(model => model[i].BookViewModel[j].Title)  
                                    </label>  
                                </a>  
                            </li>  
}  
                        </ul>  
                    </li>  
                </ul>  
}  
            </div>  
            <div class="form-group">  
                <div class="col-lg-9"></div>  
                <div class="col-lg-3">  
                    <button class="btn btn-success" id="btnSubmit" type="submit">  
Submit  
</button>  
                </div>  
            </div>  
}  
        </div>  
    </div>  
@section scripts{  
@Scripts.Render("~/Scripts/tree.js")  

Thereafter we create an important part of this example. We create the JavaScript file tree.js with the following code.

(function($)   
{  
    function Tree() {  
        var $this = this;  
        function treeNodeClick()   
        {  
            $(document).on('click', '.tree li a input[type="checkbox"]', function() {  
                $(this).closest('li').find('ul input[type="checkbox"]').prop('checked', $(this).is(':checked'));  
            }).on('click', '.node-item', function() {  
                var parentNode = $(this).parents('.tree ul');  
                if ($(this).is(':checked')) {  
                    parentNode.find('li a .parent').prop('checked', true);  
                } else {  
                    var elements = parentNode.find('ul input[type="checkbox"]:checked');  
                    if (elements.length == 0) {  
                        parentNode.find('li a .parent').prop('checked', false);  
                    }  
                }  
            });  
        };  
        $this.init = function() {  
            treeNodeClick();  
        }  
    }  
    $(function() {  
        var self = new Tree();  
        self.init();  
    })  
}(jQuery)) 

As in the preceding JavaScript code, the create tree view has the following features.

  1. When we select an author parent node then all the associated books will be selected.
  2. When a book is selected the associated parent author will be selected automatically.
  3. When all child book nodes are selected for a parent author node then the parent node will be selected.
  4. When the parent node is unselected then the child books will be automatically unselected.

Figure 1 shows the parent child (author-book) tree view.



Entity Framework 7 Hosting - ASPHostPortal :: Introduction Entity Framework 7, ASP.NET 5 and ASP.NET MVC 6

clock August 20, 2015 08:44 by author Jervis

Introduction ASP.NET 5 MVC 6 Web API and Entity Framework 7

In this post, we will only show simple example using ASP.NET 5 and new Entity Framework 7. We will cover about:

- Using the ASP.NET 5 empty template to build the Web API from scratch.
- Overview of the new project structure in VS 2015 and how to use the new dependency management tool.
- Configuring ASP.NET 5 pipeline to add only the components needed for our Web API.
- Using EF 7 commands and the K Version Manager (KVM) to initialize and apply DB migrations.

First – Create an Empty Project

Open your Visual Studio 2015 and create new project named “Registration_MVC6WebApi”

Then, select template named “ASP.NET 5 Empty”

Second Step – Adding the Dependencies

Once you have created this project, you will see there is file named “project.json” and this file contains all your project settings along with a section for managing project dependencies on other frameworks/components.

We’ve used to manage packages/dependencies by using NuGet package manager, and you can do this with the new enhanced NuGet package manager tool which ships with VS 2015, but in our case we’ll add all the dependencies using the “project.json” file and benefit from the IntelliSense provided as the image below:

So we will add the dependencies needed to configure our Web API, so open file “project.json” and replace the section “dependencies” with the section below:

"dependencies": {
        "Microsoft.AspNet.Server.IIS": "1.0.0-beta1",
        "EntityFramework": "7.0.0-beta1",
        "EntityFramework.SqlServer": "7.0.0-beta1",
        "EntityFramework.Commands": "7.0.0-beta1",
        "Microsoft.AspNet.Mvc": "6.0.0-beta1",
        "Microsoft.AspNet.Diagnostics": "1.0.0-beta1",
        "Microsoft.Framework.ConfigurationModel.Json": "1.0.0-beta1"
    }

The use for each dependency we’ve added as the below:

  • Microsoft.AspNet.Server.IIS: We want to host our Web API using IIS, so this package is needed. If you are planning to self-host your Web API then no need to add this package.
  • EntityFramework & EntityFramework.SqlServer: Our data provider for the Web API will be SQL Server. Entity Framework 7 can be configured to work with different data providers and not only relational databases, the data providers supported by EF 7 are: SqlServer, SQLite, AzureTableStorage, and InMemory. More about EF 7 data providers here.
  • EntityFramework.Commands: This package will be used to make the DB migrations command available in our Web API project by using KVM, more about this later in the post.
  • Microsoft.AspNet.Mvc: This is the core package which adds all the needed components to run Web API and MVC.
  • Microsoft.AspNet.Diagnostics: Basically this package will be used to display a nice welcome page when you request the base URI for the API in a browser. You can ignore this if you want, but it will be nice to display welcoming page instead of the 403 page displayed for older Web API 2.
  • Microsoft.Framework.ConfigurationModel.Json: This package is responsible to load and read the configuration file named “config.json”. We’ll add this file in a later step. This file is responsible to setup the “IConfiguration” object. 

Last thing we need to add to the file “project.json” is a section named “commands” as the snippet below:

"commands": {
        "ef": "EntityFramework.Commands"
}

We’ve added short prefix “ef” for EntityFramework.Commands which will allow us to write EF commands such as initializing and applying DB migrations using KVM.

Third Step - Adding config.json configuration file

Now right click on your project and add new item of type “ASP.NET Configuration File” and name it “config.json”, you can think of this file as a replacement for the legacy Web.config file, for now this file will contain only our connection string to our SQL DB, I’m using SQL Express here and you can change this to your preferred SQL server.

{
    "Data": {
        "DefaultConnection": {
            "Connectionstring": "Data Source=.\\sqlexpress;Initial Catalog=RegistrationDB;Integrated Security=True;"
        }
    }
}

Step no. 4 – Configure ASP.NET 5 Pipeline

This is the class which is responsible for adding the components needed in our pipeline, currently with the ASP.NET 5 empty template, the class is empty and our web project literally does nothing, I’ll add all the code in our Startup class at once then describe what each line of code is responsible for, so open file Startup.cs and paste the code below:

using System;
using Microsoft.AspNet.Builder;
using Microsoft.AspNet.Http;
using Microsoft.AspNet.Hosting;
using Microsoft.Framework.ConfigurationModel;
using Microsoft.Framework.DependencyInjection;
using Registration_MVC6WebApi.Models;

namespace Registration_MVC6WebApi
{
    public class Startup
    {
        public static IConfiguration Configuration { get; set; }

        public Startup(IHostingEnvironment env)
        {
            // Setup configuration sources.
            Configuration = new Configuration().AddJsonFile("config.json").AddEnvironmentVariables();
        }
        public void ConfigureServices(IServiceCollection services)
        {
            // Add EF services to the services container.
            services.AddEntityFramework().AddSqlServer().AddDbContext<RegistrationDbContext>();

            services.AddMvc();

            //Resolve dependency injection
            services.AddScoped<IRegistrationRepo, RegistrationRepo>();
            services.AddScoped<RegistrationDbContext, RegistrationDbContext>();
        }
        public void Configure(IApplicationBuilder app)
        {
            // For more information on how to configure your application, visit http://go.microsoft.com/fwlink/?LinkID=398940
            app.UseMvc();

            app.UseWelcomePage();

        }
    }
}

Step 5 – Adding Models, Database Context, and Repository

Now we’ll add a file named “Course” which contains two classes: “Course” and “CourseStatusModel”, those classes will represents our domain data model, so for better code organizing add new folder named “Models” then add the new file containing the the code below:

using System;
using System.ComponentModel.DataAnnotations;

namespace Registration_MVC6WebApi.Models
{
    public class Course
    {
        public int Id { get; set; }
        [Required]
        [StringLength(100, MinimumLength = 5)]
        public string Name { get; set; }
        public int Credits { get; set; }
    }

    public class CourseStatusModel
    {
        public int Id { get; set; }
        public string Description { get; set; }
    }
}

Now we need to add Database context class which will be responsible to communicate with our database, so add new class and name it “RegistrationDbContext” then paste the code snippet below:

using Microsoft.Data.Entity;
using System;
using Microsoft.Data.Entity.Metadata;

namespace Registration_MVC6WebApi.Models
{
    public class RegistrationDbContext :DbContext
    {
        public DbSet<Course> Courses { get; set; }

        protected override void OnConfiguring(DbContextOptions options)
        {
            options.UseSqlServer(Startup.Configuration.Get("Data:DefaultConnection:ConnectionString"));
        }
    }
}

Basically what we’ve implemented here is adding our Courses data model as DbSet so it will represent a database table once we run the migrations, note that there is a new method named “OnConfiguration” where we can override it so we’ll be able to specify the data provider which needs to work with our DB context.

In our case we’ll use SQL Server, the constructor for “UseSqlServer” extension method accepts a parameter of type connection string, so we’ll read it from our “config.json” file by specifying the key “Data:DefaultConnection:ConnectionString” for the “Configuration” object we’ve created earlier in Startup class.

Lastly we need to add the interface “IRegistrationRepo” and the implementation for this interface “RegistrationRepo”, so add two new files under “Models” folder named “IRegistrationRepo” and “RegistrationRepo” and paste the two code snippets below:

IRegistrationRepo Code

using System;
using System.Collections;
using System.Collections.Generic;

namespace Registration_MVC6WebApi.Models
{
    public interface IRegistrationRepo
    {
        IEnumerable<Course> GetCourses();
        Course GetCourse(int courseId);
        Course AddCourse(Course course);
        bool DeleteCourse(int courseId);
    }
}

RegistrationRepo implementation

using System;
using System.Collections.Generic;
using System.Linq; 


namespace Registration_MVC6WebApi.Models
{
    public class RegistrationRepo : IRegistrationRepo
    {
        private readonly RegistrationDbContext _db;

        public RegistrationRepo(RegistrationDbContext db)
        {
            _db = db;
        }
        public Course AddCourse(Course course)
        {
            _db.Courses.Add(course);

            if (_db.SaveChanges() > 0)
            {
                return course;
            }
            return null;

        }

        public bool DeleteCourse(int courseId)
        {
            var course = _db.Courses.FirstOrDefault(c => c.Id == courseId);
            if (course != null)
            {
                _db.Courses.Remove(course);
                return _db.SaveChanges() > 0;
            }
            return false;
        }

        public Course GetCourse(int courseId)
        {
            return _db.Courses.FirstOrDefault(c => c.Id == courseId);
        }

        public IEnumerable<Course> GetCourses()
        {
            return _db.Courses.AsEnumerable();
        }
    }
}

The implementation here is fairly simple, what worth noting here is how we’ve passed “RegistrationDbContext” as parameter for our “RegistrationRepo” constructor so we’ve implementedConstructor Injection, this will not work if we didn’t configure this earlier in our “Startup” class.

Step 6 - Installing KVM (K Version Manager)

After we’ve added our Database context and our domain data models, we can use migrations to create the database, with previous version of ASP.NET we’ve used NuGet package manager for these type of tasks, but with ASP.NET 5 we can use command prompt using various K* commands.

What is KVM (K Version Manager)?  KVM is a Powershell script used to get the runtime and manage multiple versions of it being on the machine at the same time, you can read more about it here.

Now to install KVM for the first time you have to do the following steps:

1. Open a command prompt with Run as administrator.
2. Run the following command:

@powershell -NoProfile -ExecutionPolicy unrestricted -Command "iex ((new-object net.webclient).DownloadString('https://raw.githubusercontent.com/aspnet/Home/master/kvminstall.ps1'))"

3. The script installs KVM for the current user.
4. Exit the command prompt window and start another as an administrator (you need to start a new command prompt to get the updated path environment).
5. Upgrade KVM with the following command:

KVM upgrade

Next Step - Initializing and applying migrations Database

Now our command prompt is ready to understand K commands and Entity Framework commands, first step to do is to change the directory to the project directory. The project directory contains the “project.json” file as the image below:

So in the command prompt we need to run the 2 following commands:

k ef migration add initial
k ef migration apply

Basically the first command will add migration file with the name format (<date>_<migration name>) so we’ll end up having file named “201411172303154_initial.cs” under folder named “Migrations” in our project. This auto generated file contains the code needed to to add our Courses table to our database, the newly generated files will show up under your project as the image below:

The second command will apply those migrations and create the database for us based on the connection string we’ve specified earlier in file “config.json”.

Note: the “ef” command comes from the settings that we’ve specified earlier in file “project.json” under section “commands”.

Step 8 - Adding GET methods for Courses Controller

The controller is a class which is responsible to handle HTTP requests, with ASP.NET 5 our Web API controller will inherit from “Controller” class not anymore from “ApiController”, so add new folder named “Controllers” then add new controller named “CoursesController” and paste the code below:

using Microsoft.AspNet.Mvc;
using Registration_MVC6WebApi.Models;
using System;
using System.Collections.Generic;

namespace Registration_MVC6WebApi.Controllers
{
    [Route("api/[controller]")]
    public class CoursesController : Controller
    {
        private IRegistrationRepo _registrationRepo;

        public CoursesController(IRegistrationRepo registrationRepo)
        {
            _registrationRepo = registrationRepo;
        }

        [HttpGet]
        public IEnumerable<Course> GetAllCourses()
        {
            return _registrationRepo.GetCourses();
        }

        [HttpGet("{courseId:int}", Name = "GetCourseById")]
        public IActionResult GetCourseById(int courseId)
        {
            var course = _registrationRepo.GetCourse(courseId);
            if (course == null)
            {
                return HttpNotFound();
            }

            return new ObjectResult(course);
        }

    }
}

What we’ve implemented in the controller class is the following:

1. The controller is attribute with Route attribute as the following [Route("api/[controller]")] so any HTTP requests that match the template are routed to the controller. The “[controller]” part in the template URL means to substitute the controller class name, minus the “Controller” suffix. In our case and for “CoursesController” class, the route template is “api/courses”.

2. We’ve defined two HTTP GET methods, the first one “GetAllCourses” is attributed with “[HttpGet]” and it returns a .NET object which is serialized in the body of the response using the default JSON format.

3. The second HTTP GET method “GetCourseById” is attributed with “[HttpGet]“. For this method we’ve specified a constraint on the parameter “courseId”, the parameter should be of integer data type. As well we’ve specified a name for this method “GetCourseById” which we’ll use in the next step. Last thing this method returns object of type IActionResult which gives us flexibility to return different actions results based on our logic, in our case we will return HttpNotFound if the course does not exist or we can return serialized JSON object of the course when the course is found.

4. Lastly notice how we are passing the “IRegistrationRepo” as a constructor for our CoursesController, by doing this we are implementing Constructor Injection.

Last Step - Adding POST and DELETE methods for Courses Controller

Now we want to implement another two HTTP methods which allow us to add new Course or delete existing one, so open file “CoursesController” and paste the code below:

[HttpPost]
public IActionResult AddCourse([FromBody] Course course)
{
                if (!ModelState.IsValid)
                {
                                Context.Response.StatusCode = 400;
                                return new ObjectResult(new CourseStatusModel { Id = 1 , Description= "Course model is invalid" });
                }
                else
                {
                   var addedCourse =  _registrationRepo.AddCourse(course);

                                if (addedCourse != null)
                                {
                                                string url = Url.RouteUrl("GetCourseById", new { courseId = course.Id }, Request.Scheme, Request.Host.ToUriComponent());

                                                Context.Response.StatusCode = 201;
                                                Context.Response.Headers["Location"] = url;
                                                return new ObjectResult(addedCourse);
                                }
                                else
                                {
                                                Context.Response.StatusCode = 400;
                                                return new ObjectResult(new CourseStatusModel { Id = 2, Description = "Failed to save course" });
                                }                
                }

}



SQL Hosting with ASPHostPortal :: Using SQLBulkCopy and C# to Upload File

clock August 12, 2015 08:17 by author Jervis

In this article I am going to write about SQLBulkCopy and its major properties and methods. This article will give you the code for high performance transfer of rows from XML file to SQL server with SQLBulkCopy and C#.

SQLBulkCopy introduced as part of .Net framework 2.0. It is simple and easy tool to transfer complicated or simple data from one data source to other. You can read data from any data source as long as that data can be load to DataTable or read by IDataReader and transfer the data with high performance to SQL Server using SQLBulkCopy.

In real time applications every day millions of records get transferred from one data store to other. There are multiple ways to transfer the data like command prompt bcp utility of SQL Server, creating INSERT statements, creating SSIS packages and SQLBulkCopy. SQLBulkCopy gives you significant performance gain over other tools.

SQLBulkCopy Constructor

SQLBulkCopy initializes instance in four different way.

1. Accepts already open SqlConnection for destination.
2. Accepts connection string of SQLConnection. This constructor actually opens and initializes new instance of SQLConnection for destination.
3. Accepts connection string of SQLconnection and enum value of SqlBulkCopyOptions. This constructor actually opens and initializes new instance of SQLConnection for destination.
4. Accepts already opened SQLConnection and enum value of SqlBulkCopyOptions.

SqlBulkCopy bulkCopy =
            new SqlBulkCopy(destinationConnection.ConnectionString, 
                SqlBulkCopyOptions.TableLock))

BatchSize

SQLBulkCopy BatchSize is integer property with default value of 0. It decides how many rows need to be send to the server in one batch. If you do not set any value for this property or set it as 0, all the records will be send in single batch.

Following example sets BatchSize property as 50.

bulkCopy.BatchSize = 50;

ColumnMappings

SQLBulkCopy ColumnMappings is a collection of columns which needs to be map from source table to destination table's columns. You do not need to map the columns if column names are same. However it is very important to map the columns if column names are different. If matching SQLBulkCopy does not found the matching column it throws System.InvalidOperationException.

You can map the columns in different ways, giving both column names is easy and readable method.

Below code match the column OrderID from source table with columnNewOrderID of destination column.

bulkCopy.ColumnMappings.Add("OrderID", "NewOrderID");  

Data Type issue while mapping the column

SqlBulkCopy is particular about matching column DataType. Both the columns has to be of same DataType. If you have nullable columns, you explicitly have to convert such columns into desired DataType.

Below code converts Null to varchar(2) and can be mapped to any varchar(2) column of destination table.

SELECT  CAST(ISNULL(ShipRegion,'') as varchar(2))
            as ShipRegion FROM Orders

Quick note: If you are having computed columns like SUM, AVG etc. make sure it returns in expected DataType. If your destination table expects columns with decimal(15,7) you will have to explicitly convert the source column as decimal(15,7) because SUM will by default return decimal(38,7).

DestinationTableName

It sets the name of destination table. The method WriteToServer will copy the source rows to this particular table.

Below code will set the destination table as "TopOrders".

bulkCopy.DestinationTableName = "TopOrders";   

NotifyAfter and SqlRowsCopied

NotifyAfter is an integer property with default value of 0 and SqlRowsCopied is an event. The value of NotifyAfter indicates when to raise eventSqlRowsCopied.

The below code shows after processing 100 rows, event SqlRowsCopied will be executed.

bulkCopy.SqlRowsCopied +=
    new SqlRowsCopiedEventHandler(OnSqlRowsTransfer);
bulkCopy.NotifyAfter = 100;

private static void
    OnSqlRowsTransfer(object sender, SqlRowsCopiedEventArgs e)
{
        Console.WriteLine("Copied {0} so far...", e.RowsCopied);
}

WriteToServer

WriteToServer is a method which actually processes your source table data to destination table. It accepts array of DataRows or DataTable or IDataReader. With DataTable you can also specify the state of the rows that needs to be processed.

The following code will process rows from sourceData DataTable which has RowState as Added to DestinationTable.

bulkCopy.WriteToServer(sourceData, DataRowState.Added);



Windows Hosting - ASPHostPortal.com :: Which is better Online Or Offline Windows Server Backup?

clock June 25, 2015 06:23 by author Dan

The backup can also be initiated with certain scheduled activities or based mostly on time. A very good support will provide a total answer alongside with round the clock guidance and remote management functionalities. Knowledge forms the core of any company, and windows server backup plays a essential position in securing your data in the experience of all hazards and offers seamless recoverability following a catastrophe.

Backing up server (computer systems, numerous server plans and other apps) is essential in purchase to keep organization continuity. And offered the wants and scope of the IT nowadays, remote server backup for digital setup is getting to be increasingly crucial. Companies are deploying virtual servers into their IT atmosphere and so it is crucial to style and employ a disaster restoration prepare for digital setting.

Remote server backup equipment for digital surroundings

There are many choices accessible in the market place when it will come to backing up virtual servers, but when picking a 3rd party resource for distant server backup, it is essential o choose a instrument which can differentiate among a physical and virtualized atmosphere. A digital equipment (VM) exists as an picture file on a bodily server, housing the OS with configuration options, programs and the corresponding info. Say if a VM file is allotted 200GB on the difficult travel and only 100GB is utilized, the digital server backup will identify it as a VM file and will back again up only that 100GB of information in distinction to a standard backup instrument, which would back again it up as a 200GB file.

Assigning storage place for distant server backup (digital server)

Even though a virtual server setting may possibly have a storage area community (SAN) keeping the VM documents, it is advisable to execute disk-to-disk backup to economical storage disks. Digital server backup resources consider a level-in-time snapshot of the VM impression and only the changes manufactured in the original are stored in the backup copy. Consequently it is observed that storage needs for virtual backup would not increase substantially above the time period and receiving more disks would not confirm to be an high-priced affair. Nevertheless it should be remembered that deploying a SAN just for virtual server backup can be high-priced. Additionally introducing far more disks to fulfill the mounting storage calls for can additional incorporate to the costs.

Computing and networking prerequisite for remote server backup (in scenario of digital server)

In circumstance of a digital server backup that creates a backup VM, the adjustments produced to the first are synchronized with the backup duplicate. As a result it is important that you 1st decide the methods needed to take care of peak workloads throughout all VMs and then boost CPU allocation so that backups do not consume into the methods allocated to apps. Not like the bodily servers, every single of which is offered a committed network interface card (NIC) for I/O functions, I/O from all VMs is envisioned to use a one NIC creating an I/O bottleneck. For that reason determine the total I/O throughput necessity for backing up VMs and if it is discovered to be on the greater aspect go with SAN-based mostly digital backup that can assistance great LAN pace.

Best Windows Shared Hosting Recommendation

ASPHostPortal.com provides our customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in Our Shared Hosting. We offers Windows hosting starts from $5/month only. We also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable Windows Shared Hosting, we should be your best choice.



WIndows Hosting - ASPHostPortal.com :: Windows Server Hosting Regarding Superior Productivity

clock June 5, 2015 06:22 by author Dan

Windows server is a chain of operating systems. Windows server can be used on business desktop, laptop, computers and servers. It is a widely acknowledged popular server operating system program. It is known for its ability under different application settings. Windows Server hosting bestows many benefits on the end user organization.

Anytime Anywhere Accessibility and Cost-Effective Solution

Using hosted dedicated Windows Server software ensures lot of time and capital saving. Windows Server hosting service provider enables users to access the hosted data at any time from any place. Users of a cloud computing and application hosting service provider only need an Internet connected device such as a laptop or personal computer or an iPhone, etc. This feature provides ease to the user.

It also allows multiple authorized users to access data simultaneously without any trouble. With this feature you can be far away from each other and still work together without any trouble. It improves collaboration between team members and enhances the ultimate output.

Reliable and Scalable

Windows Server host offers a scalable environment so that variations in business demands can be managed with no trouble. This helps user to save resources and improve return on investment. A cloud hosting service vendor provides high quality data center facilities for storing data. The data centers are situated at regions free from the risk of natural calamities. These data centers are well equipped with all the security tools required to keep the data safe.

Windows Server on Cloud Offers Acute Data Security

Windows Server hosting service providers host software via cloud computing technology, they deploy sophisticated security tools and technologies such as superb robust firewalls, top-notch antispyware, ultra sensitive antivirus software and much more. Windows Dedicated Server hosting service vendor also makes use of burglar and threat detection system, round the clock network checking, etc. The data centers are well equipped with highest possible standards.

The architecture of the threat discovery system provides a novel way of intrusion detection. It moves from identifying attacks to detecting threats and their properties. The architecture provides elasticity to the system so that it can be modified according to different environments. It is also able to use third party components when offered. The intrusion detection system detects the attacks on a network. Each attack is indicated by a warning that is forwarded to the threat classifier for further handling. There are many intrusion detection systems available such as Modsecurity, Snort, Bro, and OSSEC.

OSSEC is an open source and host based IDS. It executes log analysis, file integrity inspection, windows registry monitoring, real time alerting, rootkit detection, and active response. It runs on various operating systems, including AIX, Linux, MacOS, Solaris, HP-UX, and Windows operating system.

Technical Support Services

A cloud hosting service provider which offers Windows server Hosting solution provides round the clock support services to its users. The technical support service is generally free of cost. In case of any problem or query users can contact the certified technical experts and get their issue resolved. This feature eliminates the requirement of hiring in-house IT staff.

Rolling Data Backup

Users of cloud computing service provider get thirty days of rolling data backups. So users with Windows server hosting services don’t have to worry about data storage. If data gets lost or damaged then it can be easily retrieved via host’s server.

Best Windows Shared Hosting Recommendation

ASPHostPortal.com provides our customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in Our Shared Hosting. We offers Windows hosting starts from $5/month only. We also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable Windows Shared Hosting, we should be your best choice.



Windows Shared Hosting - ASPHostPortal.com :: Right Solution For Small Business

clock May 29, 2015 06:39 by author Dan

Today the vast reach and popularity of internet has forced most of the businessman to use it as a strong medium for promotion and increasing the sale of their products and services globally. Since windows is the most popular OS in the world, and has captured more than half of the world's market, Windows Hosting is widely in demand. Such type of web service is compatible on both dedicated servers as well as shared hosting.

The user-friendly nature and simple working of Windows has made hosting highly affordable, reliable, worthy and feature rich. Sites that are created using different Microsoft applications and scripts can only be hosted on Windows servers. The corporate hosting for small and medium enterprises is ideally done on shared web servers whereas for large organizations dedicated servers will be the best. The streamlined working of MS system allows SQL, ASP and other similar scripts to run easily on dedicated and shared web hosting services.

A professional, eye-catching web site can effectively increase the sales and services of organization. Any growing company would like to get a state of the art website to promote its business in the best possible manner, but at the most reasonable price. And since Microsoft provides the best and simple developing platform, the site designed using MS specific resources are highly customized and attractive.

In order to be on web, one needs to host the site either using dedicated server or shared web server. But as compare to dedicated web hosting, shared one is economical and a good choice for firms having limited budget to spend on hosting their business site. Shared hosting done on windows platform stands for a web-hosting service in which one server is inhabited by many websites. Connected to the Internet, the web server is divided into different parts. Each part or section is occupied by one website so that it is separated from the other websites. The maintenance of the web server is shared by many users or website owners; therefore a system administration is a must requirement for proper maintenance.

The advanced web-based control panel system like Plesk, cPanel, Interworx, DirecAdmin and H-sphere are used for Windows Shared Hosting. There are many companies round the globe engaged in web services with different hosting packages.

They offer a complete plan that includes:

- Installing server on Windows OS
- Managing servers
- Regular security updates
- 24/7 Technical support

On the whole the advantages of Windows Shared Hosting are that it is very cost-effective and highly reliable. These email server are provided with sufficient resources to meet the requirements of professional websites that get restricted within a given time period. Because of the scalable feature the required amount of space on these web hosting servers can be increased in accordance with the requirements of the users.

Best Windows Shared Hosting Recommendation

ASPHostPortal.com provides our customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in Our Shared Hosting. We offers Windows hosting starts from $5/month only. We also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable Windows Shared Hosting, we should be your best choice.



SQL Server 2014 Hosting - ASPHostPortal :: How to Solve SQL Cannot Generate SSPI Context

clock May 21, 2015 06:24 by author Dan

Everyone knows that it is good practice to use a domain or service account to run the SQL service. I’m sure you do too! However, once you do the right thing and change the SQL Service account, you may start getting the following error message when attempting to connect to the sql server:

“The target principal name is incorrect.  Cannot generate SSPI context.”

The explanation, as given by Microsoft in this KB article

    If you run the SQL Server service under the LocalSystem account, the SPN is automatically registered and Kerberos authentication interacts successfully with the computer that is running SQL Server. However, if you run the SQL Server service under a domain account or under a local account, the attempt to create the SPN will fail in most cases because the domain account and the local account do not have the right to set their own SPNs. When the SPN creation is not successful, this means that no SPN is set up for the computer that is running SQL Server. If you test by using a domain administrator account as the SQL Server service account, the SPN is successfully created because the domain administrator-level credentials that you must have to create an SPN are present.

There are 3 ways to fix the problem:

    - Revert to using the Network Service or Local System account (NOT RECOMMENDED)
    - Assign the domain account to the Domain Admins group (NOT IDEAL – due to the elevated permissions)
    - Fix the problem by giving the domain account just the appropriate permissions in Active Directory. Permissions required are
        >> ServicePrincipalName: Read
        >> ServicePrincipalName: Write

We will use the 3rd option to fix the error. First, it is good practice to verify that the problem is actually due to permission issues. Log in to the server where you SQL Instance is running. Go to the error logs and look for the last time that the SQL service was restarted. You should find an error message similar to this:

Date                   10/17/2013 9:29:50 AM
Log                    SQL Server (Archive #1 - 10/17/2013 10:53:00 AM)
Source                Server
Message
The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/servername.domainname.net:1433 ] for the SQL Server service. Windows return code: 0x2098, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

This is great. At least now we have verified that the problem is related to the SPN and we are ready to apply the fix.

Log in to the server running your Active Directory service and execute the following steps:

    - Run Adsiedit.msc
   
- In the ADSI Edit snap-in, expand Domain [YourDomainName], expand DC= RootDomainName, expand CN=Users, right-click CN= [YourAccountName, and then click Properties.
   
- In the CN= AccountName Properties dialog box, click the Security tab.
   
- On the Security tab, click Advanced.
   
- In the Advanced Security Settings dialog box, select one (any) of "SELF"'s row
   
- Click Edit, Open Permission Entry dialog box.
   
- Make sure Pricipal is "SELF", Type is "Allow" and "Applied to" is "This Object Only", in Properties section, select the properties below:
        >> Read servicePrincipalName
        >> Write servicePrincipalName

Click OK to apply all changes and exit the ADSI Edit snap-in

Finally, you need to restart the SQL Service(s) that use the account in question.

You can verify that the SPN has been registered successfully upon the restart by going to the SQL Server logs. You should now see an entry similar to this:

Date                   10/17/2013 10:53:58 AM
Log                    SQL Server (Current - 10/17/2013 10:54:00 AM)
Source                Server
Message
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/servername.domainname.net:1433 ] for the SQL Server service.

Connections to SQL Server should now succeed.

Best SQL Server 2014 Hosting Recommendation

ASPHostPortal.com provides our customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in SQL Server 2014 . We offers Windows hosting starts from $5/month only. We also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable SQL SERVER 2014 Hosting, we should be your best choice.



SQL Server 2014 Hosting - ASPHostPortal :: Identify ErrorLog with xp_ReadErrorLog

clock May 7, 2015 07:14 by author Dan

To read error logs in SQL Server using T-SQL you can use extended stored procedure xp_ReadErrorLog to read SQL Server and SQL Server Agent error logs. xp_ReadErrorLog has seven parameters that can be used to filter error logs.

Syntax for xp_ReadErrorLog:

EXEC xp_ReadErrorLog   <LogNumber>, <LogType>,

<SearchTerm1>, <SearchTerm2>,

<StartDate>, <EndDate>, <SortOrder>

The parameter values can be as follows:

You can use the stored procedure as:

EXEC xp_ReadErrorLog

– Reads current SQL Server error log

Below are some more examples of xp_ReadErrorLog:

EXEC xp_ReadErrorLog 1
– Reads SQL Server error log from ERRORLOG.1 file

EXEC xp_ReadErrorLog 0, 1
– Reads current SQL Server error log

EXEC xp_ReadErrorLog 0, 2
– Reads current SQL Server Agent error log

EXEC xp_ReadErrorLog 0, 1, 'Failed'
– Reads current SQL Server error log with text 'Failed'

EXEC xp_ReadErrorLog 0, 1, 'Failed', 'Login'
– Reads current SQL Server error log with text ‘Failed’ AND 'Login'

EXEC xp_ReadErrorLog 0, 1, 'Failed', 'Login', '20121101', NULL
– Reads current SQL Server error log with text ‘Failed’ AND ‘Login’ from 01-Nov-2012

EXEC xp_ReadErrorLog 0, 1, 'Failed', 'Login', '20121101', '20121130'
– Reads current SQL Server error log with text ‘Failed’ AND ‘Login’ between 01-Nov-2012 and 30-Nov-2012

EXEC xp_ReadErrorLog 0, 1, NULL, NULL, '20121101', '20121130'
– Reads current SQL Server error between 01-Nov-2012 and 30-Nov-2012

EXEC xp_ReadErrorLog 0, 1, NULL, NULL, '20121101', '20121130', 'DESC'
– Reads current SQL Server error log between 01-Nov-2012 and 30-Nov-2012 and sorts in descending order

Hope This Helps!

Best SQL Server 2014 Hosting Recommendation

ASPHostPortal.com provides our customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in SQL Server 2014 . We offers Windows hosting starts from $5/month only. We also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable SQL SERVER 2014 Hosting, we should be your best choice.



SQL SERVER 2014 Hosting - ASPHostPortal :: How to use Output Parameters in SQL Server and ASP.NET

clock May 4, 2015 05:57 by author Mark

The out parameters in SQL Server, when used in Stored Procedures, allow developers to pass a value in the database to the front-end controls like label. They are most commonly used in web application development.
Let us discuss how to create and use them in ASP.Net with a practical example. First we will design our database.

  • Create a database in SQL Server.
  • Let us create a table with three columns, say username, password and confirmation password.

Create table logintable(username varchar(max),password varchar(max),confirmpassword varchar(max))

  • Let us create our Stored Procedure.

Here we have created a Stored Procedure named usplogintable with username, password and confirmpassword as input parameters. The next variable that I have created is the @error variable of varchar type. You can see the keyword "out" near the varchar. Yes, your guess is correct, the keyword "out" stands for the output parameter in SQL Server.

We will execute this Stored Procedure as a batch so we have begins and ends. Then, "set nocount on" avoids returning the number of rows affected.
The if condition checks whether the username exists in a database and if the answer for it is yes, the @error variable is set with the username already taken or it inserts the values into the table and sets the @error variable as the username inserted.

Executing the Stored Procedure also requres a different style. First you need to declare a variable, you need to specify the output parameter on execution and you need to write a select query at the end to make it execute. Here is my sample for the preceding sp.

I have inserted the table with the values markus, mark and confirm password as mark.

  • I can guess what you are thinking. “How can I use it in my server-side code?”. Yeah, I am an ASP.Net developer and I have the solution for this. Here are the ways.

Additionally, you must also open your Visual Studio or press Ctrl+r and type devenv.
Create an ASP.Net web application with the framework being above 2.0. First create a form in ASP.Net with three labels and three textboxes with names as username.password and confirmpassword as shown in the screen below.

Okay. Let me take you through a tour of the server-side code on it. I will use ADO.Net here for the database connectivity. I will add my logic on my button click. Add using statements for the namespaces System.data and System.Data.SqlClient since these are not the default namespaces in .Net.

Add the following code by double-clicking the submit button.

  • Here I have created the connection string in my fashion and you can use your own style in your application as usual.
  • Thats it. We are done. Press Ctrl+F5.
  • If you provide the inserted usename.
  • And if you provide a new username, yuppy, it is inserted.

Best SQL Server 2014 Hosting Recommendation

ASPHostPortal.com provides our customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in SQL Server 2014 . We offers Windows hosting starts from $5/month only. We also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable SQL SERVER 2014 Hosting, we should be your best choice.

 



Windows 2008 Hosting with Free ASP.NET :: 9 Steps to Solve Login Failed Error : 18456 in Microsoft SQL Server 2008

clock April 23, 2015 07:31 by author Dan

In this posting, I'll coach you on how you can figure out what user features administrative legal rights within the server along with walk you Step-By-Step how you can configure SQL Server permitting some other end users to sign in as Administrators to SQL Server. This simple information “Login Failed for User (Microsoft SQL Server, Error: 18456)” indicates anyone came into unacceptable recommendations as soon as logging into SQL Server. Inside the beneath display screen chance, We are logging into 'microsoft' SQL Server Managing Facilities which has a user that will not have administrative permissions for connecting to the server.

You must to decide what user is equipped with privileges to SQL server. Normally this can be a user which you logged with having once you put in SQL Server or the user that is the default owner on computer. To obtain a set of user on the unit (Assuming Microsoft windows Server 2012 or above).

Step-By-Step

Launch Server Manager – Start – Click Server Manager



In the upper right corner, click Tools, then Computer Management



Expand Local Users and Groups (Under Computer Management-System Tools) then click Users

Notice under Description… There is a Built-in account for administering… This is very likely an account that has access to SQL server.  Try logging onto windows with that account that is Built-in account for administering then we can grant rights to the user you want to use to login to SQL Server.



Now that you are logged into Windows with an account that has access to connect to SQL Server, Let’s go in and grant rights to the user that you want to be able to use to access SQL Server.

Step-By-Step

Launch SQL Server Management Studio again and you should be able to Connect

Expand your ServerName, then Expand Security, then Logins.  Click Logins

Right-Click Logins Select New Login



Click the Search Button

Type in the Windows User Name you would like to add as an SQL Administrator then click Check Names  (or you can click advanced and select from a list)



Click Server Roles in the left pane; then turn on the sysadmin checkbox, then click OK



You can then confirm you have sa rights by double-clicking the user name in the left Object Explorer and clicking on Securables



Now you can log out of Windows, login as the user you just granted rights to and all should work fine.

Best Windows Server 2008 Hosting Recommendation

ASPHostPortal.com provides our customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in Windows server 2008. We offers Windows hosting starts from $5/month only. We also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable Windows server 2008 Hosting, we should be your best choice.



About ASPHostPortal.com

We’re a company that works differently to most. Value is what we output and help our customers achieve, not how much money we put in the bank. It’s not because we are altruistic. It’s based on an even simpler principle. "Do good things, and good things will come to you".

Success for us is something that is continually experienced, not something that is reached. For us it is all about the experience – more than the journey. Life is a continual experience. We see the Internet as being an incredible amplifier to the experience of life for all of us. It can help humanity come together to explode in knowledge exploration and discussion. It is continual enlightenment of new ideas, experiences, and passions


Author Link


Corporate Address (Location)

ASPHostPortal
170 W 56th Street, Suite 121
New York, NY 10019
United States

Sign in