RSS

Category Archives: ASP.Net

ASP.Net Bulk Insert Excel Data using SqlBulkCopy

This code sample is for a Excel Import programmatically into SQL Server database.

Important points to note.

  • Use excel data in a single sheet.
  • The excel file format should match the table schema.

Excel format:

Id Name Description CreatedAt CreatedBy ModifiedAt ModifiedBy Enabled Deleted

.aspx page

<asp:FileUpload ID="FileUpload1" runat="server" />

<asp:Button ID="Button1" runat="server" Text="Import" CssClass="btn btn-warning" OnClick="ImportExcel" />

Code Behind File

protected void ImportExcel(object sender, EventArgs e)
        {
            if (FileUpload1.HasFile)
            {
                // SQL Server Connection String
                string sqlConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["flexi_stocky"].ConnectionString;

                // Bulk Copy to SQL Server 
                SqlBulkCopy bulkInsert = new SqlBulkCopy(sqlConnectionString);
                try
                {
                   
                    string path = string.Concat(Server.MapPath("~/uploads/" + FileUpload1.FileName));
                    FileUpload1.SaveAs(path);

                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);
                    OleDbConnection connection = new OleDbConnection();
                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    
                    bulkInsert.DestinationTableName = "your_sqlTableName";
                    bulkInsert.WriteToServer(dr);
                    bulkInsert.Close();
                    //Show success
                }
                catch (Exception ex)
                {
                    bulkInsert.Close();
                    //Show error
                }

            }
            else
            {
                //File not set
            }
        }
 
Leave a comment

Posted by on April 12, 2015 in ASP.Net

 

Tags: ,

How to use Sessions in Web Services ASP.Net

Using sessions in web services is little different from its normal usage. Here we can not access sessions with Session as in Page methods. Instead we use HttpContext.Current.Session.

Sessions should be enabled for web methods.

A sample code snippet would be as follows.

/// Summary description for ReceivingService
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
     [System.Web.Script.Services.ScriptService]
    public class ReceivingService : System.Web.Services.WebService
    {

        [WebMethod(EnableSession = true)]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public Dictionary<string, object> removeItem(int Id)
        {
            var response = new Dictionary<string, object>();
            bool found = false;

            if ( HttpContext.Current.Session["rec_cart"] != null)
            {
                List<CartItem> cartItems = (List<CartItem>)Session["rec_cart"];
                if (cartItems.Count > 0)
                {
                    foreach(var item in cartItems){
                        if(item.Id == Id){
                            cartItems.Remove(item);
                             HttpContext.Current.Session["rec_cart"] = cartItems;
                            found = true;
                            break;
                        }
                    }
                }
            }
            if (found)
            {
                response["status"] = true;
                response["total"] = GetGrandTotal();
            }
            else
            {
                response["status"] = false;
            }
            return response;
        }
}

I also configured cookieless sessions in web.config to get web service call properly routed.

<sessionState cookieless="true" regenerateExpiredSessionId="true" timeout="100"/>
 
Leave a comment

Posted by on April 11, 2015 in ASP.Net

 

Tags:

Add Default Item to DropDownList Dynamically

How to add a default item as ‘N/A’ to drop down list and set selected?

gnList.DataSourceID = "GNDS";
gnList.DataTextField = "Name";
gnList.DataValueField = "Id";
gnList.DataBind();
gnList.Items.Insert(0, new ListItem("N/A", "0"));

Another way

gnList.Items.Add(new ListItem("N/A", "0"));
gnList.DataSourceID = "GNDS";
gnList.DataTextField = "Name";
gnList.DataValueField = "Id";
gnList.DataBind();
gnList.Items.FindByValue("0").Selected = true;
 
Leave a comment

Posted by on March 21, 2015 in ASP.Net

 

Tags:

Static DropDownList in GridView

Displaying a DropDownList column in GridView and setting the selected item.

<asp:TemplateField ShowHeader="False">
                            <ItemTemplate>
                                <asp:DropDownList  ID="DropDownList1" runat="server"  SelectedValue='<%# Bind("Status") %>' autocomplete="off" Width="150">
                                    <asp:ListItem Value="1">Enable</asp:ListItem>
                                    <asp:ListItem Value="2">Disable</asp:ListItem>
                                    <asp:ListItem Value="3">Delete</asp:ListItem>
                                </asp:DropDownList>  
                            </ItemTemplate>
                        </asp:TemplateField>
 
Leave a comment

Posted by on March 20, 2015 in ASP.Net

 

Tags:

Add Dynamic Controls to Content PlaceHolders in ASP.Net

            ContentPlaceHolder holder = Page.Master.FindControl("message") as ContentPlaceHolder;
            HtmlGenericControl alertControl = new HtmlGenericControl();
            alertControl.Attributes["class"] = "alert alert-success";
            alertControl.TagName = "div";
            holder.Controls.Add(alertControl);
            Label message = new Label();
            message.Text = "User deleted successfully.";
            alertControl.Controls.Add(message);
 
Leave a comment

Posted by on March 20, 2015 in ASP.Net

 

Tags:

ASP.NET GridView ListBox Column Data Binding


<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" GridLines="None" CssClass="table" DataSourceID="GenericNamesDataSource" DataKeyNames="Id">

<Columns>

<asp:TemplateField HeaderText="Group Name" SortExpression="GroupName">
<EditItemTemplate>
<asp:ListBox ID="ListBox2" runat="server" DataSourceID="GroupsDataSource" DataTextField="Name" DataValueField="Id"  SelectedValue='<%# Bind("GroupId") %>' SelectionMode="Multiple" CssClass="chzn-select" autocomplete="off" data-placeholder="Select Item(s)"></asp:ListBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("GroupName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>

</Columns>

</asp:GridView>

<asp:SqlDataSource ID="GenericNamesDataSource" runat="server" ConnectionString='<%$ ConnectionStrings:flexi_stocky %>'
SelectCommand="SELECT GN.Id, GN.GroupId, GN.CreatedAt, G.Name AS GroupName FROM GenericNames AS GN INNER JOIN Groups AS G ON GN.GroupId = G.Id">

</asp:SqlDataSource>

<asp:SqlDataSource ID="GroupsDataSource" runat="server" ConnectionString='<%$ ConnectionStrings:flexi_stocky %>' SelectCommand="SELECT DISTINCT [Id], [Name] FROM [Groups]"></asp:SqlDataSource>

 
Leave a comment

Posted by on March 6, 2015 in ASP.Net

 

Building ASP.NET MVC3 Applications

ASP.NET MVC3

ASP.NET MVC3

Welcome Back, today we gonna build a simple MVC application using ASP.net MVC3 template. The tutorial does not intend to teach you ASP.net MVC3 framework. There are so many resources on the internet that might help you getting started with this technology. So we focus on building a simple application using models, views, controllers, filters and other ASP.net specific things. Remember  MVC is a design pattern and it can also be  implemented in other programing languages like PHP.

Final result
Book Mgmt

Download Project

1. Creating New Project

creating-project

creating-project

2. Folder Structure

directory structure

3. Changing the default controller.

You can change the default controller in Global.asax file by specifying the controller name and action method.

Changing the default controller

4. Adding Dojo files

We are using Dojo data grid for displaying data records. Therefore please download Dojo latest version and include them in Scripts folder.

5. Creating Book Modal
Right click on Models -> Add -> Class
Book.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;

namespace BooksApp.Models
{
public class Book
{
public int Id { get; set; }
[Required]
public string Name { get; set; }
[Required]
public string Author { get; set; }
[Required]
public float Price { get; set; }
}
}

6. Creating BookFacade class
For a  real application database operations can be placed in this class. For the sake of simplicity we omit mapping entity to  database.
Right click on Models -> Add -> Class
BookFacade.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using BooksApp.Models;

namespace BooksApp.Facade
{
public class BookFacade
{
public static List<Book> bookList { get; set; }

public static List<Book> GetBookList()
{
if (bookList == null)
{
bookList = new List<Book>();
for (int i = 0; i < 10; i++)
{
bookList.Add(new Book {Id = i, Name = "Book " + i, Author = "Author " + i, Price = i * 20 });
}

}
return bookList;
}

public static Book FindBook(int id)
{
var bookList = GetBookList();
return bookList.Find(x => x.Id == id);
}

public static void Add(Book book)
{
var bookList = GetBookList();
book.Id = bookList.Max(x => x.Id) + 1;
bookList.Add(book);
}

public static void Delete(int id)
{
var bookList = GetBookList();
Book book = bookList.Find(x => x.Id == id);
bookList.Remove(book);
}

}
}

7. Creating BookController
Right click on Controllers-> Add -> Controller
BookController

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using BooksApp.Models;
using BooksApp.Facade;
using BooksApp.Filters;

namespace BooksApp.Controllers
{
public class BookController : Controller
{
//
// GET: /Book/

public ActionResult Index()
{
return View();
}

public ActionResult Create()
{
return View();
}

[AcceptVerbs(HttpVerbs.Post)]
public ActionResult Create(Book book)
{
if (book.Name.Length < 5)
{
ModelState.AddModelError("Book name", "Book name should have five or more characters.");
}
if (!ModelState.IsValid)
{
return View("Create",book);
}
BookFacade.Add(book);
return RedirectToAction("Index");
}

public ActionResult GetData(int id)
{
Book book = BookFacade.FindBook(id);
return View(book);
}

[RightChecker(AllowedBookIds = "5,6,7")]
public ActionResult Delete(int id)
{
Book book = BookFacade.FindBook(id);
if (book != null)
{
BookFacade.Delete(id);
}
return RedirectToAction("Index");

}

/////////////////////////AJAX/////////////////////////////

public ActionResult GetJSON()
{
return Json(BookFacade.GetBookList(), JsonRequestBehavior.AllowGet);

}

public ActionResult Ajax()
{
return View();
}

[AcceptVerbs(HttpVerbs.Post)]
public ActionResult Create_AJAX(Book book)
{
BookFacade.Add(book);
return new EmptyResult();
}

}
}

Adding views
8. Adding Create View

 create view

<%@ Page Language="C#" Inherits="System.Web.Mvc.ViewPage<BooksApp.Models.Book>" %>

<!DOCTYPE html>

<html>
<head runat="server">
<title>Create</title>
</head>
<body>
<script src="<%: Url.Content("~/Scripts/jquery-1.5.1.min.js") %>" type="text/javascript"></script>
<script src="<%: Url.Content("~/Scripts/jquery.validate.min.js") %>" type="text/javascript"></script>
<script src="<%: Url.Content("~/Scripts/jquery.validate.unobtrusive.min.js") %>" type="text/javascript"></script>

<% using (Html.BeginForm("Create","Book")) { %>
<%: Html.ValidationSummary(true) %>
<fieldset>
<legend>Book</legend>

<div>
<%: Html.LabelFor(model => model.Name) %>
</div>
<div>
<%: Html.EditorFor(model => model.Name) %>
<%: Html.ValidationMessageFor(model => model.Name) %>
</div>

<div>
<%: Html.LabelFor(model => model.Author) %>
</div>
<div>
<%: Html.EditorFor(model => model.Author) %>
<%: Html.ValidationMessageFor(model => model.Author) %>
</div>

<div>
<%: Html.LabelFor(model => model.Price) %>
</div>
<div>
<%: Html.EditorFor(model => model.Price)%>
<%: Html.ValidationMessageFor(model => model.Price)%>
</div>

<p>
<input type="submit" value="Create" />
</p>
</fieldset>
<% } %>

<div>
<%: Html.ActionLink("Back to List", "Index") %>
</div>
</body>
</html>

9. Adding GetData View

Get Data View

<%@ Page Language="C#" Inherits="System.Web.Mvc.ViewPage<BooksApp.Models.Book>" %>

<!DOCTYPE html>

<html>
<head runat="server">
<title>Book Details</title>
</head>
<body>
<fieldset>
<legend>Book</legend>

<div>Name</div>
<div>
<%: Html.DisplayFor(model => model.Name) %>
</div>

<div>Author</div>
<div>
<%: Html.DisplayFor(model => model.Author) %>
</div>

<div>Price</div>
<div>
<%: Html.DisplayFor(model => model.Price) %>
</div>
</fieldset>
<p>

<%: Html.ActionLink("Edit", "Edit", new { id=Model.Id }) %> |
<%: Html.ActionLink("Back to List", "Index") %>
</p>
</body>
</html>

10. Adding Index View

<%@ Page Language="C#" Inherits="System.Web.Mvc.ViewPage<dynamic>" %>

<!DOCTYPE html>

<html>
<head runat="server">
<title>Index</title>
<style type="text/css">
@import "../../Scripts/dojo/dijit/themes/dijit.css";
@import "../../Scripts/dojo/dojox/grid/resources/Grid.css";
@import "../../Scripts/dojo/dojox/grid/resources/tundraGrid.css";
@import "../../Scripts/dojo/dijit/themes/tundra/tundra.css";
</style>

<script type="text/javascript" src="../../Scripts/dojo/dojo/dojo.js"
djconfig="isDebug:false,  debugAtAllCosts:false"></script>
<script src="../../Scripts/dojo/dijit/dijit.js" type="text/javascript"></script>

<script language="javascript" type="text/javascript">
dojo.require("dojox.grid.DataGrid");
dojo.require("dojo.data.ItemFileWriteStore");

dojo.ready(function () {

DisplayAll();
});

function DisplayAll() {

var that = this;

// The parameters to pass to xhrGet, the url, how to handle it, and the callbacks.
var xhrArgs = {
url: "../Book/GetJSON",
handleAs: "json",
preventCache: true,
load: function (data, ioargs) {
that.PopulateGrid(data); //This will populate the grid
},
error: function (error, ioargs) {
alert(ioargs.xhr.status);
}

}

// Call the asynchronous xhrGet

dojo.xhrGet(xhrArgs);
}

function PopulateGrid(store) {

var jsonString = "{identifier: \"Id\", items: " + dojo.toJson(store) + "}";  //Creates the Json data that supports the grid structure. The identifier value should be unique or errors will be thrown

var dataStore = new dojo.data.ItemFileReadStore({ data: dojo.fromJson(jsonString) }); //Converts it back to an object and set it as the store

/*set up layout of the grid that will be columns*/
var gridStructure = [
{ field: 'Id', name: 'Book Id', styles: 'text-align: center;', width: 20 },
{ field: 'Name', name: 'Name', width: 20 },
{ field: 'Author', name: 'Author', width: 20 },
{ field: 'Price', name: 'Price', width: 30} //"field" matches to the JSON objects field
];
/*create a new grid:*/

var bookGrid = dijit.byId('gridS');
if (bookGrid == null) { //Only create a grid if there grid already created
var grid = new dojox.grid.DataGrid({
id: 'gridS',
store: dataStore,
structure: gridStructure,
rowSelector: '30px',
height: '300px'
},
"gridDivTag"); //The div tag that is used to place the grid
/*Call startup() to render the grid*/
grid.startup();
}
else {

bookGrid._refresh();
bookGrid.setStore(dataStore); //Setting the new datastore after entering new data
}

}

</script>
</head>
<body>
<div>
<h1>Welcome to Book Management Section</h1>
<% //HtmlAnchor %>

<div id="gridDivTag"></div>
<br /><br />
<%: Html.ActionLink("Add New", "Create") %>
<%: Html.ActionLink("Ajax Call", "Ajax") %>
</div>
</body>
</html>

11. Adding AJAX View
I wanna create this view just to demonstrate AJAX operations with ASP.NET MVC3

<%@ Page Language="C#" Inherits="System.Web.Mvc.ViewPage<dynamic>" %>

<!DOCTYPE html>

<html>
<head id="Head1" runat="server">
<title>Ajax</title>
<style type="text/css">
@import "../../Scripts/dojo/dijit/themes/dijit.css";
@import "../../Scripts/dojo/dojox/grid/resources/Grid.css";
@import "../../Scripts/dojo/dojox/grid/resources/tundraGrid.css";
@import "../../Scripts/dojo/dijit/themes/tundra/tundra.css";
.style1
{
width: 134px;
}
</style>

<script type="text/javascript" src="../../Scripts/dojo/dojo/dojo.js" djconfig="isDebug:false,  debugAtAllCosts:false"></script>
<script src="../../Scripts/dojo/dijit/dijit.js" type="text/javascript"></script>

<script language="javascript" type="text/javascript">
dojo.require("dojox.grid.DataGrid");
dojo.require("dojo.data.ItemFileWriteStore");

dojo.ready(function () {

DisplayAll();
});

function book() {
this.Name = dojo.byId("name").value;
this.Author = dojo.byId("author").value;
this.Price = dojo.byId("price").value;
}
function addBook_post() {
var bookObj = new book();

var xhrArgs = {
url: "../Book/Create_AJAX",
headers: { //Adding the request headers
"Content-Type": "application/json; charset=utf-8" // This is important to model bind to the server
},
postData: dojo.toJson(bookObj, true), //Converting the object in to Json to be sent the action method
handleAs: "json",
load: function (data) {
DisplayAll();

},
error: function (error) {
alert('error');
}
}

dojo.xhrPost(xhrArgs);
}

function DisplayAll() {

var that = this;

// The parameters to pass to xhrGet, the url, how to handle it, and the callbacks.
var xhrArgs = {
url: "../Book/GetJSON",
handleAs: "json",
preventCache: true,
load: function (data, ioargs) {
that.PopulateGrid(data); //This will populate the grid
},
error: function (error, ioargs) {
alert(ioargs.xhr.status);
}

}

// Call the asynchronous xhrGet

dojo.xhrGet(xhrArgs);
}

function PopulateGrid(store) {

var jsonString = "{identifier: \"Id\", items: " + dojo.toJson(store) + "}";  //Creates the Json data that supports the grid structure. The identifier value should be unique or errors will be thrown

var dataStore = new dojo.data.ItemFileReadStore({ data: dojo.fromJson(jsonString) }); //Converts it back to an object and set it as the store

/*set up layout of the grid that will be columns*/
var gridStructure = [
{ field: 'Id', name: 'Book Id', styles: 'text-align: center;', width: 20 },
{ field: 'Name', name: 'Name', width: 20 },
{ field: 'Author', name: 'Author', width: 20 },
{ field: 'Price', name: 'Price', width: 30} //"field" matches to the JSON objects field
];
/*create a new grid:*/

var bookGrid = dijit.byId('gridS');
if (bookGrid == null) { //Only create a grid if there grid already created
var grid = new dojox.grid.DataGrid({
id: 'gridS',
store: dataStore,
structure: gridStructure,
rowSelector: '30px',
height: '300px'
},
"gridDivTag"); //The div tag that is used to place the grid
/*Call startup() to render the grid*/
grid.startup();
}
else {

bookGrid._refresh();
bookGrid.setStore(dataStore); //Setting the new datastore after entering new data
}

}

</script>
</head>
<body >
<form id="form1" runat="server">
<div>
<h1>Welcome to Book Management Section</h1>

<div id="gridDivTag"></div>
<br /><br />

<table style="width: 400px;">
<caption>
Add New Book</caption>
<tr>
<td>
&nbsp;
Name</td>
<td>
&nbsp;
<input id="name" type="text" />
</td>
</tr>
<tr>
<td>
&nbsp;
Author</td>
<td>
&nbsp;
<input id="author" type="text" />
</td>
</tr>
<tr>
<td>
&nbsp; Price</td>
<td>
&nbsp;
<input id="price" type="text" />
</td>
</tr>
<tr>
<td>
&nbsp;</td>
<td>
<input id="add_post" type="submit" value="Add Via Post" onclick="addBook_post()" />

</td>
</tr>
</table>

<br /><br />
<%: Html.ActionLink("Add New", "Create") %>      <%: Html.ActionLink("Ajax Call", "Ajax") %>
</div>
</form>
</body>
</html>

Sample testing links
http://localhost:3616/
http://localhost:3616/Book/Create
http://localhost:3616/Book/Ajax
http://localhost:3616/Book/GetData/1
http://localhost:3616/Book/GetJSON

Obviously port number may change according to your environment.

 
1 Comment

Posted by on April 20, 2013 in ASP.Net

 

Tags: