Code Snippets

Codeigniter Shield Authorization

2025-01-08T14:50:00.004+05:30

Codeigniter Shield Authorization

CodeIgniter Shield is the official authentication and authorization framework for CodeIgniter 4. It provides a flexible role-based access control (RBAC) system, allowing you to manage user permissions effectively.

Key Concepts:

Groups: Users can belong to multiple groups, which can represent traditional roles (e.g., admin, moderator) or be used to group users based on features or other criteria.

Permissions: Permissions define what actions a user is allowed to perform within your application. Permissions are typically associated with groups.

Users: Each user has a set of permissions based on the groups they belong to, and can also have permissions directly assigned to them.

Defining Groups:

Groups are defined in the Shield\Config\AuthGroups configuration file. Each group has a key, a title, and an optional description.

php
public array $groups = [
    'superadmin' => [
        'title'       => 'Super Admin',
        'description' => 'Optional description of the group.',
    ],
    'editor' => [
        'title' => 'Editor',
        'description' => 'Can edit content.'
    ],
    'user' => [
        'title' => 'User',
        'description' => 'Basic user access.'
    ]
];

Defining Permissions:

php
public array $permissions = [
    'superadmin.access'   => 'Can access the sites admin area',
    'superadmin.settings' => 'Can access the main site settings',
    'editor.create'       => 'Can view/edit site content',
    'users.create'        => 'Can create new non-admin users',
    'users.edit'          => 'Can edit existing non-admin users',
    'users.delete'        => 'Can delete existing non-admin users',
];

Checking Permissions:

You can check if a user has a specific permission using the can() method on the User entity. This method checks permissions within the groups the user belongs to, as well as any permissions directly assigned to the user.

php
if (! auth()->user()->can('users.create')) {
    return redirect()->back()->with('error', 'You do not have permissions to access that page.');
}

Example:

Let's say you have a 'superadmin' group with the permission users.create. You can check if the current user has this permission like this:

php
if (auth()->user()->can('users.create')) {
    // Allow user to create a new user
} else {
   // Redirect or show an error message
}

Getting the Current User:

You can get the current user using the auth() helper function:

php
$user = auth()->user();

Adding Group To a User:

php

$user = auth()->user();
$user->addGroup('superadmin', 'editor');

Removing Group From a User:

php
$user = auth()->user();
$user->removeGroup('superadmin', 'editor');

Check if User Belongs to a Group:

php
$user = auth()->user();
if ($user->inGroup('superadmin')) {
    // do something
}

In summary, CodeIgniter Shield's authorization system allows you to:

Define user groups with specific roles and permissions.

Assign users to one or more groups.

Check if a user has the necessary permissions to perform an action.

This provides a flexible and robust way to manage access control in your CodeIgniter application.

JBL Wave Buds 2 Ear Buds Wireless BluetoothV5.3, Active Noise Cancellation EarBuds,Multi Connect, App for Customized Extra Bass Eq, Relax Mode,Speed Charge, 40H Playback, Fast Pair, 4 Mics,IP54(Black)

Sony WF-C710N | Dual Noise Cancellation Wireless Bluetooth in Ear Earbuds | AI Call Quality | 40Hrs Battery W/O ANC | 30Hrs Battery with ANC - Blue

Codeigniter Shield - Account Activation & Two-Factor Authentication

2024-04-27T10:30:00.004+05:30

Account Activation & Two-Factor Authentication

Ads

PHILIPS Audio TAS1400BL Wireless Bluetooth Speaker with Deep Bass, Passive Radiator, 12W Sound Output, 1200mAh Rechargable Battery, RGB Light Modes, 10H Playtime, Supports TF/USB/BT Modes (Blue)

E GATE Phantom 630DW Dolby Audio 540W Soundbar, Home Theatre 5.1 Channel Sound bar for TV with Wireless Subwoofer (16.5cm) & 2 Satellite Speakers, 3 EQ Mode, Bluetooth 5.3, HDMI-ARC, Optical | eGate

OnePlus Nord Buds 3 Truly Wireless Bluetooth Earbuds with up to 32dB Active Noise Cancellation, 10mins for 11Hours Fast Charging with Up to 43h Music Playback -Harmonic Gray

Electron app with React and Electron Forge

2024-03-21T11:45:00.004+05:30

Build Desktop applications with React and Electron Forge

Create cross-platform desktop applications using modern web development tools and integration of third-party libraries and frameworks with Electron. Build your electron apps with the commonly used web technologies like HTML, JavaScript, CSS. Supports native interface for all platforms like windows, macOS, Linux. Integrate JavaScript frameworks such as React, Vue for front-end tooling. Publish electron apps with built-in templates. Currently Electron apps have support for these templates for publishing,

Vite
Webpack

Codeigniter Shield - Install and Configure

2024-03-11T10:43:00.007+05:30

Install Codeigniter Shield

Codeigniter Shield is the official authentication and authorization framework for codeigniter 4. It provides session authentication, in which the user ID and password is stored in the session for the subsequent requests to authenticate against. Basically, it has all the features that the modern website offers.

Codeigniter in Visual Studio Code - Getting started

2024-03-05T22:12:00.008+05:30

Getting started with Codeigniter in VSCode

Codeigniter is a PHP Framework for building robust and flexible websites. Basically, codigniter can be downloaded from official sources as a zip package and install in your system to start development. But, there are other ways to download and install codeigniter to build web applications. Here, we will demonstrate two ways to get started with codeigniter app development in visual studio code. The two approaches used here are,

Clone Git repository
Composer (tool to install packages and dependencies)

Setup Laravel in Visual Studio Code - Getting started

2024-03-04T21:36:00.004+05:30

Setup Laravel in Visual Studio Code

Laravel is a very popular PHP Framework for developing web applications. There are many ways to get started with Laravel development in different platforms. But, the most commonly used IDE by developers is visual studio code because it is open source and has support for multiple languages. It even has extensions enabling to integrate third-party tools and services, thus allows for faster development. Here, two approaches are used to download and install Laravel in vscode.

Clone Git repository
Composer (tool to get packages and dependencies)

After you download Laravel, first thing that you have to is create a .env file and copy the configurations from the .env.example to .env file. Then, Generate app key and assign to the setting variable.

BigPassport Gas Spring Monitor Arm Stand | Heavy Duty VESA Mount for 13–32 Inch Screens | Adjustable Aluminium Desk Mount for PC, Laptop Monitor – Holds 2–9kg

Install WordPress on IIS (Internet Information Services)

2024-02-29T21:58:00.005+05:30

Install WordPress on IIS

WordPress is an open-source Content Management System (CMS) built using core PHP. It is a production ready web application package which can be setup in minutes to publish content for the end users. Administrators have complete control over the information being displayed on the site. Managing all aspects of the site is made possible through admin panel.

Deploying WordPress on IIS requires you to configure IIS Manager to handle PHP requests. In order to handle PHP requests enable CGI module from windows features. Install PHP package and register in IIS Manager using PHP Manager for IIS, which is a tool to manage different versions of PHP.

Configure Visual Studio Code for PHP development using IIS Express

2024-02-14T21:52:00.004+05:30

Setup VS Code for PHP Development using IIS Express

Visual studio code has been the preferred IDE for software development among the developers and the most popular because of the support for building software in multiple languages and platforms, third-party extensions that boosts productivity. Developing PHP applications in visual studio code requires configuring it to handle PHP requests.

First, you need to have PHP installed on your machine. In VS Code, open the settings and set the path to the PHP executable in the validation setting.

Next, to configure IIS Express settings download and install IIS Express which is a lightweight web server for development purposes. In the VS Code settings set the path to the executable for both iisexpress Appcmd and iisexpress.

MySQL InnoDB Cluster - Configure MySQL Instances and create InnoDB cluster

2024-02-12T11:07:00.003+05:30

Configure MySQL Instances and create InnoDB cluster

MySQL InnoDB Cluster - Install MySQL and create multiple instances

2024-02-12T10:16:00.004+05:30

Create multiple instances of MySQL server locally

Trigger function in DIV

2021-08-20T19:18:00.004+05:30

How to trigger function in DIV

The events in the object fires when the page loads, mouse events , keyboard events and other types of inputs. It is not possible to trigger an event in the DIV for example, onload event when the object is loaded. Some elements like body, script, link, image, style support firing up of events. However, it is required for the developer to trigger the event in object DIV. In order to do that we can use jquery trigger method to fire the event.

HTML

Javascript/Jquery

function welcome(){

alert('Hello world! in func welcome');

}

$(function(){

$('div[onload]').trigger('onload');

});

Move column data from one table to another

2020-09-11T14:38:00.001+05:30

There may be situations when you want to move MYSQL column data from one table to another.

MySQL Query:

UPDATE accounts_users, accounts_membership SET accounts_users.username=accounts_membership.username WHERE accounts_users.user_id=accounts_membership.user_id AND accounts_users.user_id >= 1;

Retrieve duplicate column values in MySQL

2020-06-04T12:30:00.002+05:30

How to find duplicate column values in MySQL

Let's see some of the many ways to retrieve duplicate column value from MySQL table. In this example, we are going to find and return duplicate contact names from the contacts table. It is up to you to decide which is applicable according to the scenario.

Approach 1:

SELECT customer_name FROM contacts group by customer_name having count(*) > 1;

SELECT

customer_name,

COUNT(customer_name)

FROM

contacts

GROUP BY customer_name

HAVING COUNT(customer_name) > 1;

Result:

Approach 2:

SELECT contact_id,

contact_type,

contacts.customer_name

FROM contacts

INNER JOIN (SELECT customer_name

FROM contacts

GROUP BY customer_name

HAVING COUNT(contact_id) > 1) dup

ON contacts.customer_name = dup.customer_name;

Result:

Export to excel using PHP and Jquery

2020-05-25T09:39:00.002+05:30

Export to excel using PHP and Jquery

In order to export MySQL table data to excel let's create a HTML page and put two anchor tags, one is to export to excel and the other to export to CSV (Comma Seperated Value). Write a jquery function to submit the form which makes call to the PHP function to export data returned to excel.

HTML Page

<form id="site" method="post" enctype="multipart/form-data">
  <div>
    <a id="studentsExportExcel" href="javascript:void(0)" onclick="exportExcel(this)">Export to excel (.xls)</a>
    <a id="studentsExportCSV" href="javascript:void(0)" onclick="exportExcel(this)">Export to csv</a>
  </div>
  <input type="hidden" value="" id="hidden-type" name="studentsExportType"/>
</form>

Jquery

$(document).ready(function () {
	function exportExcel(obj) {
    	var target = $(obj).attr('id');
    	switch (target) {
        case 'studentsExportExcel':
        case 'studentsExportCSV':
            $('#hidden-type').val(target);
            $('#site').submit();
            $('#hidden-type').val('');
            break;
    	}
	}
});

PHP Function

public function students()
    {
        if(isset($_POST["studentsExportType"]))
        {
            switch($_POST["studentsExportType"])
            {
                case "studentsExportExcel" :
                    // Submission from
                    $filename = "students". date('dMY') .".xls";       
                    header("Content-Type: application/vnd.ms-excel");
                    header("Content-Disposition: attachment; filename=\"$filename\"");
                    students_export_excel();
                    $_POST["studentsExportType"] = '';
                    exit();
                case "studentsExportCSV":
                    $filename = "students". date('dMY') . ".csv";
                    header("Content-type: application/csv");
                    header("Content-Disposition: attachment; filename=\"$filename\"");
                    header("Pragma: no-cache");
                    header("Expires: 0");

                    $handle = fopen('php://output', 'w');
                    $data = get_students();
                    foreach ($data as $data)
                    {
                        fputcsv($handle, $data);
                    }
                    fclose($handle);
                    exit();
                default :
                    die("Unknown action : ".$_POST["action"]);
                    break;
            }
        }
    }


function students_export_excel()
    {
        $heading = false;
        $students = get_students();
        
        if(!empty($students))
          foreach($students as $row) {
            if(!$heading) {
              // display field/column names as a first row
              echo implode("\t", array_keys($row)) . "\n";
              $heading = true;
            }
            echo implode("\t", array_values($row)) . "\n";
          }
        exit;
    }


function get_students()
{
        $servername = "localhost";
        $username = "username";
        $password = "password";
        $dbname = "myDB";
 
        // Create connection
        $conn = new mysqli($servername, $username, $password, $dbname);
        // Check connection
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }
 
        $sql = "SELECT id, firstname, lastname FROM students";
        $result = $conn->query($sql);
 
        return $result;
}

Sum columns in multiple tables - MySQL

2020-05-18T20:31:00.008+05:30

How to sum columns in multiple tables in MySQL

We will see two methods to sum columns from multiple tables. The first method is more preferred than the second method. Therefore consider using first approach.

METHOD 1:

SELECT credit.contact_id, (IFNULL(credit, 0) - IFNULL(debit, 0)) as balance
     FROM (
    (SELECT SUM(credit) as credit, contact_id FROM table_a GROUP BY contact_id) as credit
       LEFT JOIN
    (SELECT SUM(debit) as debit, contact_id FROM table_b GROUP BY contact_id) as debit
     ON debit.contact_id = credit.contact_id);

METHOD 2:

SELECT contact_id, SUM(balance) FROM
    (SELECT contact_id ,credit as balance FROM table_a
       UNION ALL
    SELECT contact_id,- debit as balance FROM table_b
   ) as x
  GROUP BY contact_id;

Ads

Serplex® 180-IN-1 Precision Screwdriver Set with 3 Torque Modes and 162 Magnetic Precision Bits

XERGY LED Desk Lamp with USB Charging Output – Dimmable, Touch Control, Timer, Night Mode, 5 Color Modes

Infinite scroll for div element

2020-05-02T14:24:00.004+05:30

Infinite scroll loading for div element

This article explains how to incrementally load items as you scroll down div element in the page. In this example a HTML page is created with an unordered list element where the list items returned by the function gets appended. Here difference of the scroll height of the div container and its client height is compared against the scroll top offset value.

HTML Page

<div>
  <div id="containerName" style="overflow: scroll;height: 500px;"></div>
  <div style="text-align: center;"><img alt="Loading..." src="../assets/images/svg/three-dots.svg" class="loading-info" /></div>
  <div id="notifyMessage"></div>
</div>
Jquery
$(document).ready(function () {
  var offset = 0;
  var loading = false;
  $('.loading-info').hide();
  appendItems(offset, loading);
  $(window).scroll(function () {
        if ($('#notifyMessage').html() === "") {
            var divHeight = $('#containerName')[0].scrollHeight - $('#containerName')[0].clientHeight;            if ($('#containerName').scrollTop() === divHeight) {
                offset += 20;
                appendItems(offset, loading);
            }
        }
    });
});
function appendItems(offset, loading) {
    if (loading == false) {
        loading = true;
        $('.loading-info').show();
        $.post('/admin/items/append', { 'offset': offset }, function (data) {
            loading = false;
            if (data.trim().length == 0) {
                $('.loading-info').hide();
                $('#notifyMessage').html('<p> End of records</p>');
                return;
            }
            $('.loading-info').hide();
 
            $("#containerName").append(data);
        }).fail(function (xhr, ajaxOptions, thrownError) {
            alert(thrownError);
        })
    }
}
PHP Function
function append()
{
        $list = NULL;
        $offset = $_POST['offset'];
        $servername = "localhost";
        $username = "username";
        $password = "password";
        $dbname = "myDB";
 
        // Create connection
        $conn = new mysqli($servername, $username, $password, $dbname);
        // Check connection
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }
 
        $sql = "SELECT id, firstname, lastname FROM employees limit $offset, 20";
        $result = $conn->query($sql);
 
        if ($result->num_rows > 0) {
            // output data of each row
            while($row = $result->fetch_assoc()) {
                $list +=  "<div>id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "</div>";
            }
        } 
        $conn->close();
 
        echo $list;
}

Scroll Loading using Jquery and PHP

2020-05-02T12:03:00.003+05:30

Scroll loading using jquery and PHP

This article explains how to incrementally load items as you scroll down the page. In this example a HTML page is created with an unordered list element where the list items returned by the function gets appended.

HTML Page

<div>
  <ul id="containerName"></ul>
  <div style="text-align: center;"><img alt="Loading..." src="../assets/images/svg/three-dots.svg" class="loading-info" /></div>
  <div id="notifyMessage"></div>
</div>
Jquery
$(document).ready(function () {
  var offset = 0;
  var loading = false;
  $('.loading-info').hide();
  appendItems(offset, loading);
  $(window).scroll(function () {
        if ($('#notifyMessage').html() === "") {
            if ($(window).scrollTop() + $(window).height() >= $(document).height() - 100) {
                offset += 20;
                appendItems(offset, loading);
            }
        }
    });
});
function appendItems(offset, loading) {
    if (loading == false) {
        loading = true;
        $('.loading-info').show();
        $.post('/admin/items/append', { 'offset': offset }, function (data) {
            loading = false;
            if (data.trim().length == 0) {
                $('.loading-info').hide();
                $('#notifyMessage').html('<p> End of records</p>');
                return;
            }
            $('.loading-info').hide();
 
            $("#containerName").append(data);
        }).fail(function (xhr, ajaxOptions, thrownError) {
            alert(thrownError);
        })
    }
}
PHP Function
function append()
{
        $list = NULL;
        $offset = $_POST['offset'];
        $servername = "localhost";
        $username = "username";
        $password = "password";
        $dbname = "myDB";
 
        // Create connection
        $conn = new mysqli($servername, $username, $password, $dbname);
        // Check connection
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }
 
        $sql = "SELECT id, firstname, lastname FROM employees limit $offset, 20";
        $result = $conn->query($sql);
 
        if ($result->num_rows > 0) {
            // output data of each row
            while($row = $result->fetch_assoc()) {
                $list +=  "<li>id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "</li>";
            }
        } 
        $conn->close();
 
        echo $list;
}

HTML Scalar Vector Graphics(SVG)

2020-04-28T08:54:00.001+05:30

Left/Right Half Circle Using SVG

<svg width="100" height="100">
<circle cx="50" cy="50" r="50" fill="grey" />
<path d="M50,0 a1,1 0 100,100 0,0" fill="orange" />
</svg>

Bottom/Top Half Circle Using SVG

<svg width="100" height="100">
<circle cx="50" cy="50" r="50" fill="grey" />
<path d="M0,50 a1,1 0 0,0 100,0" fill="orange" />

</svg>

Host PHP Content on IIS (Internet Information Services)

2019-08-23T10:31:00.001+05:30

How to center a div in the middle of the viewport

2014-11-06T18:43:00.000+05:30

Note: If you use <form> tag in the page, it should have the same css style as for the html, body

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <meta charset="utf-8" />
        <title>Login</title>
        <style type="text/css">
            html, body {
                margin: 0px;
                padding: 0px;
                width: 100%;
                height: 100%;
                display: table;
                font-family: Verdana;
                font-size: small;
            }
            .login-container {
                display: table-cell;
                text-align: center;
                vertical-align: middle;
            }
            .content {
                background-color: grey; /* sample usage */
                display: inline-block;
                text-align: left;
                line-height: 25px;
            }
        </style>
    </head>
    <body>
        <section class="login-container">
            <div class="content">
                <input type="text" name="username" id="userName" maxlength="50" placeholder="Username">
                <br>
                <input type="password" name="password" maxlength="50" placeholder="Password">
                <br>
                <div>
                    <label><input type="checkbox" name="rememberLogin" id="rememberLogin" value="1">Stay logged in</label>
                    &nbsp;
                    <input type="button" name="login" value="Login">
                    <br>
                </div>
            </div>
        </section>
    </body>
</html>

Download Files from a specific folder

2012-09-12T13:04:00.001+05:30

If you want to allow the user to download files from a specific folder in the server the following snippet of code lets you accomplish the task. Initially display all the files the user want to download in a Gridview. Put a download link to let the user download files.

HTML Markup

using System.IO;

protected void Page_Load(object sender, EventArgs e)
{
if(!IsPostBack)
{
BindDownloadFiles();
}
}
private void BindDownloadFiles()
{
string DataDirectory= "~/DownloadFiles";

DirectoryInfo dirInfo = new DirectoryInfo(Server.MapPath(DataDirectory));
FileInfo[] files = dirInfo.GetFiles();
gvFiles.DataSource = files;
gvFiles.DataBind();
}
private void DownloadFile(string fileName, string fullFilePath)
{
Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
Response.TransmitFile(fullFilePath);
Response.End();
}
protected void gvFiles_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "Download")
{
Uri uri = new Uri(e.CommandArgument.ToString());
string[] fileInfo = uri.LocalPath.Split(';');
string fullPath = fileInfo[0];
string fileName = fileInfo[1];
DownloadFile(fileName, fullPath);
}
}
protected void gvFiles_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gvFiles.PageIndex = e.NewPageIndex;
BindDownloadFiles();
}

Check/Uncheck all checkbox in gridview

2012-08-28T16:02:00.000+05:30

The following snippet will demostrate how to check/uncheck all checkboxes in a Gridview. Using javascript will take few lines of code to accomplish the task. But using Jquery minimizes the number of lines to code to implement.

Add reference to the jquery library on the page,

<script src="http://ajax.aspnetcdn.com/ajax/jquery/jquery-1.8.0.min.js" type="text/javascript"></script>

The script to check/uncheck the gridview checkboxes and the html code would be,

protected void Page_Load(object sender, EventArgs e)
{
gvProducts.DataSource = GetProducts();
gvProducts.DataBind();
}

public DataTable GetProducts()
{
DataTable dtProducts = new DataTable();
SqlDataAdapter adpProducts = new SqlDataAdapter("GetProducts", sqlcon);
adpProducts.SelectCommand.CommandType = CommandType.StoredProcedure;
try
{
adpProducts.Fill(dtProducts);
return dtProducts;
}
catch (Exception)
{
throw;
}
finally
{
sqlcon.Close();
}
}

Make ajax calls using jquery to bind dropdownlist

2012-08-22T15:28:00.000+05:30

Introduction

This article describes how to make AJAX calls using Jquery to bind databound control like dropdownlist with data from database. To Bind the data to any databound control, return data as json format.

Default.aspx

It is important to register the client script for event validation, since we bind the dropdownlist dynamically using client script it should be registered for event validation. Otherwise, the page will raise an error on postback. Though there is a possibility to bypass this scenario by setting either,

<%@ Page EnableEventValidation="false">

or Globally in the Web.config

<pages enableEventValidation="false"></pages>

The setting above may allow hackers to inject sql commands making the application vulnerable. To overcome this problem during the override the Render method as shown below.

Default.aspx.cs

protected override void Render(HtmlTextWriter writer)
{
ClientScript.RegisterForEventValidation(ddlBooks.UniqueID,"1");
ClientScript.RegisterForEventValidation(ddlBooks.UniqueID, "2");
ClientScript.RegisterForEventValidation(ddlBooks.UniqueID, "3");
base.Render(writer);
}

App_Code/Books.cs

public class Books
{
public Books()
{

}
public string ID { get; set; }
public string BookName { get; set; }
public string Author{ get; set; }
}

Don't forget to uncomment the line shown below in the .asmx file if it does not exists add one.This enables us to make asynchronous calls to the function.

[System.Web.Script.Services.ScriptService]

App_Code/BookService.cs

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

// SQL Server Connection String
SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString);

[WebMethod]
public List<Books> GetBooks()
{
DataTable dtBooks = new DataTable();
List<Books> books = new List<Books>();
SqlDataAdapter adpBooks = new SqlDataAdapter("GetBooks", sqlcon);
adpBooks.SelectCommand.CommandType = CommandType.StoredProcedure;
//adpBooks.SelectCommand.Parameters.AddWithValue("@BookId", bookId);
try
{
adpBooks.Fill(dtBooks);
Books book;
foreach (DataRow dr in dtBooks.Rows)
{
book = new Books();
book.ID = dr["ID"].ToString();
book.BookName = dr["Name"].ToString();
book.Author = dr["Author"].ToString();
books.Add(book);
}
return books;
}
catch (Exception)
{
throw;
}
finally
{
sqlcon.Close();
}
}

Call server side functions using PageMethods in AJAX

2012-08-19T01:54:00.004+05:30

Call server side functions using PageMethods in AJAX

This post illustrates how to call server side function using PageMethod. Enabling PageMethods property in the ScriptManager allows us to call functions directly from the server side. Therefore significant performance improvement of the page is noticed.

Create a stored procedure to get Books from the database.

CREATE PROCEDURE [dbo].[GetBooks]
AS
BEGIN
SELECT [ID]
,[BookName]
FROM [Books]
END
GO

We will start by adding ScriptManager on the page and setting the EnablePageMethods property to true.

<ajaxToolkit:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server" EnablePageMethods="true">
</ajaxToolkit:ToolkitScriptManager>

Iterate through the list of books returned by the function using loops and bind the data to controls.

Default.aspx

Add a class in the code behind or in a seperate file and write the function to query the SQL server to get books. Create a WebMethod to which will return list of Books.

Default.aspx.cs

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;

protected void Page_Load(object sender, EventArgs e)
{

}

[WebMethod]
public static List<Books> getBooks(Books b)
{
DataTable dtBooks = new DataTable();
dtBooks = b.GetBooks();
List<Books> books = new List<Books>();

Books book;
foreach (DataRow dr in dtBooks.Rows)
{
book = new Books();
book.ID = dr["ID"].ToString();
book.BookName = dr["BookName"].ToString();
books.Add(book);
}
return books;
}

public class Books
{
// SQL Server Connection String
SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString);

public string ID { get; set; }
public string BookName { get; set; }

public DataTable GetBooks()
{
DataTable dtBooks = new DataTable();
SqlDataAdapter adpBooks = new SqlDataAdapter("GetBooks", sqlcon);
adpBooks.SelectCommand.CommandType = CommandType.StoredProcedure;
//adpBooks.SelectCommand.Parameters.AddWithValue("@BookId", bookId);
try
{
adpBooks.Fill(dtBooks);
return dtBooks;
}
catch (Exception)
{
throw;
}
finally
{
sqlcon.Close();
}
}
}

Export data from SQL Server to Excel in asp.net

2012-08-14T17:00:00.002+05:30

Export data from SQL Server to Excel in asp.net

This sample introduces how to export data from SQL Server to an Excel spreadsheet and in the next article we shall see how to generate an Excel spreadsheet with data from SQL Server.

Default page retrieves data from SQL Server using a DataTable and then exports the DataTable to an Excel 2003/2007 spreadsheet on server disk.

In the previous post we have seen how to import data from excel to sql server. Please refer the table used in the previous post for this sample. First, create a procedure to select books from the database. Then create a function in the code-behind page to return Books in a DataTable. Use StringWriter and HtmlTextWriter to iterate through the data in the DataTable. Create an object for DataGrid and Bind the DataTable to it.

Stored procedure to get Books

CREATE PROCEDURE [dbo].[GetBooks]
AS
BEGIN
SELECT [ID]
,[Name]
,[Author]
FROM [Books]
END
GO

Default.aspx

Default.aspx.cs

using System.Configuration;
using System.Data.SqlClient;
using System.IO;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

// SQL Server Connection String
SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString);

protected void Page_Load(object sender, EventArgs e)
{
gvBooks.DataSource = GetBooks();
gvBooks.DataBind();
}

protected void btnExport_Click(object sender, EventArgs e)
{
ExportToExcel(GetBooks(), "DownloadBooksToExcel.xls");
}

public void ExportToExcel(DataTable dt, string fileName)
{
if (dt.Rows.Count > 0)
{
StringWriter sw = new StringWriter();
HtmlTextWriter htmlWriter = new HtmlTextWriter(sw);
DataGrid dg = new DataGrid();
dg.DataSource = dt;
dg.DataBind();

dg.RenderControl( htmlWriter);
Response.ContentType = "application/vnd.ms-excel";
//Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);
this.EnableViewState = false;
Response.Write(sw.ToString());
Response.End();
}
}
public DataTable GetBooks()
{
DataTable dtBooks = new DataTable();
SqlDataAdapter adpBooks = new SqlDataAdapter("GetBooks", sqlcon);
adpBooks.SelectCommand.CommandType = CommandType.StoredProcedure;
try
{
adpBooks.Fill(dtBooks);
return dtBooks;
}
catch (Exception)
{
throw;
}
finally
{
sqlcon.Close();
}
}