My Professional IT Blog

Tuesday, April 23, 2013

Draw line/image using mouse and save/display on/from database (MSSQL/VB.NET)

HTML5 Canvas element is a drawing API which can be used for rendering graphs, game graphics, to draw line/image etc. Canvas is a rectangle area which can be added to our HTML5 page. To get the image data URL of the canvas, we can use the toDatURL() method of the canvas object which converts the canvas drawing into a 64 bit encoded PNG/JPEG/BITMAP URL.

PaintingApplicationTest.aspx


<%@ Page Language="VB" AutoEventWireup="false"  
CodeFile="PaintingApplicationTest.aspx.vb"
    Inherits="PaintingApplicationTest" %>

<!DOCTYPE html />
<html lang="en">
<head>
    <title>Paint Testing App</title>
      

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


  // setup our test canvas
  // and a simple drawing function
   window.onload = function () {

   var bMouseIsDown = false;

   var oCanvas = document.getElementById("thecanvas");
        var oCtx = oCanvas.getContext("2d");

   var iWidth = oCanvas.width;
   var iHeight = oCanvas.height;

   oCanvas.onmousedown = function (e) {
     bMouseIsDown = true;

     iLastX = e.clientX - oCanvas.offsetLeft + 
              (window.pageXOffset || 
               document.body.scrollLeft || 
               document.documentElement.scrollLeft);
     iLastY = e.clientY - oCanvas.offsetTop + 
               (window.pageYOffset || 
                document.body.scrollTop || 
                document.documentElement.scrollTop);
            } 


      oCanvas.onmouseup = function () {
                bMouseIsDown = false;
                iLastX = -1;
                iLastY = -1;
            }


      oCanvas.onmousemove = function (e) {
          if (bMouseIsDown) {
          var iX = e.clientX - oCanvas.offsetLeft +                    (window.pageXOffset || 
                 document.body.scrollLeft || 
                 document.documentElement.scrollLeft);
          var iY = e.clientY - oCanvas.offsetTop + 
                 (window.pageYOffset || 
                  document.body.scrollTop || 
                  document.documentElement.scrollTop);
                    
                    oCtx.moveTo(iLastX, iLastY);
                    oCtx.lineTo(iX, iY);
                    oCtx.stroke();
                    iLastX = iX;
                    iLastY = iY;
                }
            }
        }
</script>
<script type="text/javascript">


// Send the canvas image to the server.
$(function () {
$("#btnSave").click(function () {
var image = document.getElementById("thecanvas").toDataURL("image/png");
 image = image.replace('data:image/png;base64,', '');
  $.ajax({
     type: 'POST',
      url: 'PaintingApplicationTest.aspx/UploadImage',
      data: '{ "imageData" : "' + image + '" }',
      contentType: 'application/json; charset=utf-8',
      dataType: 'json',
        success: function (msg) {
           alert('Image saved successfully !');
                  }
                });
            });
        });
    </script>
</head>
<body>
  <form id="form1" runat="server">
  <canvas width="400" height="200" 
   style="border: 1px solid black;" id="thecanvas"></canvas>  

<input type="button" id="btnSave" 
        name="btnSave" value="Save" />

<asp:Button runat="server" ID="btnShow" 
    Text="Show Saved Image" OnClick="btnShow_Click" />

<asp:Image ID="Image1" style="width:200px" Runat="server" />
 </form>
</body>
</html>

PaintingApplicationTest.aspx.cs

Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.IO
Imports System.Web.Script.Services
Imports System.Web.Services
Imports System.Data.SqlClient
Imports System.Data

<ScriptService()> _
Partial Public Class PaintingApplicationTest
    Inherits System.Web.UI.Page
    <WebMethod()> _
 
Public Shared Sub UploadImage(imageData As String)

Dim constr As String = System.Web.Configuration.
            WebConfigurationManager.
            ConnectionStrings("SqlServerReadOnly").
            ConnectionString
Dim con As New SqlConnection(constr)
        Dim cmd As New SqlCommand()
        cmd.Connection = con
//make sure you use SP or Parameterized query
Dim sql As String = "INSERT INTO HTML5CanvasImage
                    (imageData) VALUES('" + 
                    imageData + "')"
        cmd.CommandText = sql
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
    End Sub


Protected Sub btnShow_Click(sender As Object, 
                 e As EventArgs)

Dim constr As String = System.Web.Configuration.
              WebConfigurationManager.
              ConnectionStrings("SqlServerReadOnly").
              ConnectionString

Dim sql As String = "SELECT ImageData FROM 
                     HTML5CanvasImage"
Dim cmd As SqlCommand = New SqlCommand(sql, 
                        New SqlConnection(constr))
        cmd.Connection.Open()

Dim imgString As String = cmd.ExecuteScalar().
                          ToString()
Image1.Attributes("src") = "data:image/png;base64," 
                          + imgString
cmd.Connection.Close()

 End Sub
End Class

Output:

Draw and save the image:

Display saved image from SQL database:

More information about HTML5:
Click here
Click here
Click here

Happy Coding!!

Wednesday, February 6, 2013

Disable Dates in Calendar Extender & Calendar Control

CalenderExtenders don't have OnDayRender Event, It is specially for Calendar Control. For CalenderExtender we can use some JS function:

CalenderExtenders:

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

 <title></title>

   <script type="text/javascript">

     function checkDate(sender, args) {

       // 6 days        
       var ms = new Date().getTime() + 6 * 86400000; 

        var weekFar = new Date(ms);

          if (sender._selectedDate < weekFar) {           

              //getTime() gives milliseconds  
              //and 86400000 is the number of 
              //milliseconds in a day. 
             alert("You can't select a day 
                     earlier than a week!");

 
           // set the date back to a week from today
            var ms1 = new Date().getTime() 
                         + 7 * 86400000; 

            var weekFar1 = new Date(ms1);
            sender._selectedDate = weekFar1; 
            sender._textbox.set_Value(sender.
                 _selectedDate.format(sender._format))

          }

      }

    </script>
</head>

<body>

    <form id="form1" runat="server">

     <asp:ToolkitScriptManager 
        ID="ToolkitScriptManager1" runat="server">
                </asp:ToolkitScriptManager> 

  

         <asp:TextBox ID="txtDateNeeded" 
              runat="server" Width="5em">
              </asp:TextBox>   

          <asp:CalendarExtender   
             ID="CalendarExtender1" 
             OnClientDateSelectionChanged="checkDate"
             TargetControlID="txtDateNeeded"  
             runat="server"/>

</form></body></html>

Calendar Control:

<asp:Calendar ID="Calendar1" runat="server"
      Caption="" CaptionAlign="Top" CellPadding="2"
      CellSpacing="2" ShowTitle="true"
      OnDayRender="Calendar1_DayRender" 
      Font-Names="verdana"
      Font-Size="Small">
      <DayHeaderStyle BackColor="CadetBlue" 
      HorizontalAlign="center" 
      VerticalAlign="Middle" />
      <WeekendDayStyle BackColor="gray">
      </WeekendDayStyle>
      <SelectedDayStyle BackColor="BurlyWood"
      ForeColor="DarkBlue" Font-Names="Calibri"
      Font-Bold="true" />
</asp:Calendar>

'Code behind
'calendar disable certain dates
Protected Sub Calendar1_DayRender(sender As Object, e As DayRenderEventArgs)

 If e.Day.Date <= DateTime.Now.AddDays(6) Then
       'disable the date a week from today
       e.Cell.Enabled = False
       e.Day.IsSelectable = False
       e.Cell.ToolTip = "This date is not available"
   End If
End Sub

Friday, August 24, 2012

Get Email Addresses and Send Email SQL

Let say, you have a form to order books and 'Customer' table to save the information with 'Customer Name', 'Email', 'Shipped Date', 'Received' columns.

I want to write an auto email query in SQL and would like to run by SQL agent so that the customer would get an e-mail reminder if they forget to return the books in 3 weeks from the shipped date.

Create Procedure [dbo].[AutoEmail] AS

BEGIN

SET NOCOUNT ON

DECLARE @EmailList varchar(MAX)

SELECT @EmailList = COALESCE(@EmailList + ';', '') + Email

FROM Customer

WHERE (Email IS NOT NULL)

AND

(received = 0) 'bit value

AND

(ShippedDate < DATEADD(day, -21, GETDATE()))

SELECT @EmailList

exec msdb.dbo.sp_send_dbmail

@profile_name = 'Your Profile Name',

@recipients='abc@hotmail.com',

@blind_copy_recipients = @EmailList,

@subject = 'Books return time reminder',

@execute_query_database='Database Name',

@body = 'Hello, this is a reminder email, please send us back
the books which you received 3 weeks ago. If you
have already sent them please disregard this
message.'

End

Wednesday, July 11, 2012

SQL Select result displays Horizontally Separate by comma

Let say you have a table structure and data as follow:

Id Grade
1 3
1 9

then you would need a SQL select output as

Id Grade
1 3, 9

then do this:

DECLARE @grade_list VARCHAR(MAX)

SELECT @ grade_list = CASE WHEN @ grade_list IS NULL THEN CONVERT(VARCHAR,Grade)
ELSE @ grade_list + ', ' + CONVERT(VARCHAR,Grade) END
FROM Table1where Id = 1
SELECT @grade_list as Grade

or you could do like this:

SELECT Id,
SUBSTRING(
(
SELECT (', ' + Grade)
FROM table1 t2
WHERE t1.Id = t2.Id
ORDER BY t1.Id, t2.Id
FOR XML PATH('')), 3, 1000)
FROM table1 t1
GROUP BY Id

Happy Coding!!