My Professional IT Blog

Thursday, January 16, 2014

Merge Two Rows in SQL

Let's create a table:

Create Table School (

StudentId int,

Course varchar(50),

Semester varchar(10),

Primary Key(StudentId, Semester));

Insert into School Values(1234, 'Math', 'Fall');

Insert into School Values (1234, 'Science', 'Spring');

Both rows are for a student with ID:1234. If you would like to combine these rows into one, you could do by different ways: 1.) do max for each column with Group by clause, 2.) do the inner join with same table, 3.) rank by semester and then pivot based on the rank. If you have one row data and another row null then you can do max and group by with common Id.

I am going to show you the third one here: Rank by semester and then pivot on the rank:

Select StudentId,

Max(Case when rk = 1 then Course end) as Course1,

Max(Case when rk = 1 then Semester end) as Semester1,

Max(Case when rk = 2 then Course end) as Course2,

Max(Case when rk = 2 then Semester end) as Semester2

From

(Select StudentId, Course, Semester,

Row_Number() Over
(Partition by StudentId Order by Semester) as rk

From School) as A

Group by StudentId;

Output:

Happy Programming!!

Monday, November 25, 2013

Dynamic jQuery calls from code behind VB.NET

Simple Function call: You can call the jQuery from code behind with the help of ClientScript.RegisterStartupScript():

  
ClientScript.RegisterStartupScript(this.GetType(), 
           "blah", "YourJsFunction();", true);

Assign via the control: If you have multiple controls on a page and need to make an individual jQuery call based on condition (not for every time; let say after the button click)

<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" 
                                        runat="Server">
       
    <asp:Literal runat="server" ID="ltlJsFadeCode"></asp:Literal>
     
    Result: 
     <asp:Label runat="server" ID="lblResult"></asp:Label>
     
    Id:     
     <asp:TextBox runat="server" ID="tbId"></asp:TextBox>
     
    Save Button: 
     <asp:Button runat="server" ID="btnSave" Text="Save" />
</asp:Content>

Code behind:

       
        Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As 
                               System.EventArgs) Handles btnSave.Click
          ltlJsFadeCode.Text = JsFadeCode(lblResult.ClientID)
          lblResult.Text = "<span style=""font-weight:bold;color: #0C0;"">
                                          Saved</span>"
        End Sub 

        Protected Function JsFadeCode(ByVal clientId As String) As String
        Dim sb As New StringBuilder
        sb.Append(vbNewLine)
        sb.Append("<scr")
        sb.Append(vbNewLine)
        sb.Append("$(document).ready(function() {")
        sb.Append(vbNewLine)
        sb.Append("$('#")
        sb.Append(clientId)
        sb.Append("').animate({ opacity: 1.0 }, 3000).fadeOut('slow');")
        sb.Append(vbNewLine)
        sb.Append("});")
        sb.Append(vbNewLine)
        sb.Append("</scr"
        sb.Append("ipt>")
        sb.Append(vbNewLine)
        sb.Append(vbNewLine)
        Return sb.ToString
    End Function

Hence, the literal control ltlJsFadeCode behaves like jQuery ready function:

<script type=""text/javascript">
   $(document).ready(function(){
     $('#lblResult').animate({ opacity: 1.0 }, 3000).
                                fadeOut('slow');
       };
<script>

Happy Coding!!

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!!

Wednesday, March 28, 2012

How to force a pdf file to download ASP.NET

If you try to open pdf files on a browser, those are automatically open but if you need to force the browser to directly download the pdf instead you have to add file header and give the full path. see the example:

<span style="color:red">Click <a href="download.aspx"

          target="_blank"><u> here </u></a> to download resume.</span>

then on the page load method of the download.aspx page, write this:

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load

Response.Clear()

Response.AddHeader("content-disposition", "attachment;filename=documents/resume.pdf")

Response.ContentType = "application/pdf"      Response.WriteFile(Server.MapPath("~/documents/resume.pdf"))

Response.[End]()

End Sub

Happy Coding!!

Wednesday, February 22, 2012

Read & Display SQL data into Label

Code:

Dim conn As New SqlConnection
(ConfigurationManager.ConnectionStrings("SqlServerExecSP").ToString())
conn.Open()
Dim sql As String = "Select Name from People where Id = 1"
Dim cmd As SqlCommand = New SqlCommand(sql, conn)
Dim rd As SqlDataReader = cmd.ExecuteReader()

While rd.Read()
label1.Text = rd("Name")
End While

Change the connection string and table name in the above code.

Happy Programming!!

Checkbox-- Select at least one box using jQuery

Code:

<script>

$(document).ready(function () {

$('.error').hide();

$('.submit').click(function (event) {

var count = $('input:checked').length;

if (count == 0) {

alert("select at least one");

return false;

}

else

{
return true;
}
});
});
</script>

<input id="app" class="box" value="5" type="checkbox" name="apple">Apple$5

<input id="ora" class="box" value="2" type="checkbox" name="orange">Orange $2

<input id="mel" class="box" value="1" type="checkbox" name="melon">Melon$1

<input id="gra" class="box" value="3" type="checkbox" name="grape">Grapes $3

<input class="submit" value="Submit" type="submit">

more:
http://code.google.com/p/m-jq-projects/wiki/enableControlOnCheck_jQuery

Tuesday, May 24, 2011

Redirects handled by Google Analytics

Google Analytics won't track a visit to a page unless that page runs the analytics tracking code. Although, there are at least 2 ways to track the redirects:
-Tracking a redirect using 301 redirect.
-Tracking a redirect using a JavaScript redirect.

In both instances, make sure the directory actually exists as a file (/it/growthmodel.aspx). I always use the JavaScript redirect so, let's talk about it:
Let say, you have a pdf file (growthmodel.pdf), you want to post it online and also want to count the number of visitors and their behaviours. For this, create a page growthmodel.aspx under the /it directory and redirect this page to pdf file using javascript redirect. Put the below code at growthmodel.aspx page:

<!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>
<script type="text/javascript">

var _gaq = _gaq [];
_gaq.push(['_setAccount', 'UA-XXXXXX-X']);
_gaq.push(['_trackPageview']);

(function() {
var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
})();
</script>

</head>
<body onLoad="_gaq.push(['_trackEvent', 'category', 'action', 'opt_label'])">
<script type="text/javascript">

function redirect()
{
window.location = "/growthmodel.pdf"
}

var temp = setInterval("redirect()", 1000);

</script>
</body>
</html>

setInterval=> 1000 so that tracking doesn't get missed.
'UA-XXXXXX-X' => please replace xxxx with your actual analytics account number.

Happy Programming!!

Thursday, May 19, 2011

Startup JavaScript Code from Content Page to Master Page

Let say, you have Master and Content pages and you want to add some JavaScript code from your Content pages that would run when the page is loaded. Content pages do not have the HTML elements like body tag to add your script on its onload event.

For this, you can use RegisterStartupScript method. Add the following code to your Content Page code behind Page_Load Event:

protected void Page_Load(object sender, EventArgs e)
{
Type type = GetType();
const string scriptName = "alertPopup";
if (!ClientScript.IsStartupScriptRegistered(type,scriptName))
{
ClientScript.RegisterStartupScript(type, scriptName, "alert('Hello World!')", true);
}
}
Happy Programming!!

Thursday, April 14, 2011

Reset MSSQL Table Identity

We can use DBCC CHECKIDENT to reset the indentity value of SQL table. Let Say, you have a SQL table with Auto-Increment ID. If you delete the records from ID numbers 10-15 and insert the new record, it'll take the next ID i.e. 16.

But if you want the new record starts from ID: 11, you can do this with the help of simple single query:

DBCC CHECKIDENT ('TableName', RESEED, 10)

So, it'll reset the indentity to 10 and next ID will be autoIncrement by 1 i.e. 11.

Happy Programming !!

Friday, February 4, 2011

ROW_NUMBER Function--Paging Records Using SQL Server 2005 Database

ROW_NUMBER returns a sequential number for each row returned in a resultset, starting from 1. It can help with paging records for the database applications.
Let see the Example:

Select name, sender, date from (select ROW_NUMBER() over (order by date ASC)
as row, name, date from table) as table_Row_Numbers

If you have lots of records on your database and you want certain number of records to retrieve (at a time) from database to make it faster and efficient, then this is the best idea.

Select name, sender, date from (select ROW_NUMBER() over (order by date ASC)
as row, name, date from table) as table_Row_Numbers where row>=1 and row<=50

Let say you have 100 thousands records and you made a interface to access those records. If you try to get all the records at a time, it might crash the internet explorer. In this situation, you can write stored procedure, that would accept 'startRowIndex' and 'MaximumRows' (where you define the maximum rows to display in a page). In the mean time, if you display those records in a Gridview control and want to do the header sorting when click on it, you can pass sortExpression parameter in your SP. I have written this SP for my database search application last week:

Create procedure RajSearch
@StartRowIndex INT,
@MaximumRows INT,
@SortExpression nvarchar(100),
@StringPass nvarchar(100)

As
declare @RajTable table
(rowId int identity(1,1), ID int,
Name nvarchar(255),
Sender nvarchar(255),
Recipient nvarchar(255),
date varchar(255),
Reel nvarchar(255),
Reference_URL nvarchar(255) )

Declare @ID int,
@Name nvarchar(255),
@Sender nvarchar(255),
@Recipient nvarchar(255),
@Date nvarchar(255),
@Reel nvarchar(255),
@Reference_URL nvarchar(255)

--Define a cursor
Declare Search cursor Fast_Forward for

with FixedList as(
SELECT ID,Name,Sender,Recipient,Date,Reel,Reference_URL,
ROW_NUMBER() OVER
(ORDER BY
(case when @sortExpression = 'Sender ASC' THEN Sender END) ASC,
(case when @sortExpression = 'Sender DESC' THEN Sender END) DESC,
(case when @sortExpression = 'Recipient ASC' THEN Recipient END) ASC,
(case when @sortExpression = 'Recipient DESC' THEN Recipient END) DESC,
(case when @SortExpression = 'Date ASC' THEN Date END) ASC,
(case when @SortExpression = 'Date DESC' THEN Date END) DESC,
(case when @sortExpression = 'Reel ASC' THEN Reel END) ASC,
(case when @sortExpression = 'Reel DESC' THEN Reel END) DESC )

AS [RowNo] from TestTable

where (Name like '%' + @stringPass + '%' or
Sender LIKE '%' + @stringPass + '%' or
Recipient LIKE '%' + @stringPass + '%' or
Date LIKE '%' + @stringPass + '%' or
Reel LIKE '%' + @stringPass + '%' or
Reference_URL LIKE '%' + @stringPass + '%'))

Select Sender, Recipient, Date, Reel, Reference_URL from FixedList
where RowNo BETWEEN @StartRowIndex AND @StartRowIndex + @MaximumRows

open Search
while 1=1
begin
fetch next from Search
into @Sender, @Recipient,@Date,@Reel,@Reference_URL

if @@FETCH_STATUS<>0
break
if(@Reel is not null and @Reference_URL is not null)
begin
--open in a new window
set @Reel = '<a href=' + '"' + @Reference_URL + '"' + ' target="_blank">' + @Reel + '</a>'
end

insert into @RajTable(ID,Name, Sender,
Recipient, Date, Reel,Reference_URL)
values
(@ID,@Name,@Sender, @Recipient, @Date@Reel,@Reference_URL)

end
close Search
deallocate Search

select Sender,Recipient,Date,Reel from @RajTable order by Date
return

Happy Programming!!

Tuesday, January 11, 2011

Encode and Decode the QueryString parameter value

Let say, I have a web application where I send data from a page to another page via QueryString. If the Querysting value contains '&' sign, it'll truncate the data after this sign and we won't get the matched data:

<td>
<asp:HyperLink ID="HyperLink1" runat="server"
NavigateUrl='<%#DataBinder.Eval(Container.DataItem,"book_name","chapter_Matches.aspx?book_name={0}")%>'>
<%# DataBinder.Eval(Container.DataItem, "book_name")%></asp:HyperLink>

</td>

and in chapter_Matches.aspx:

str = Request.QueryString["book_name"].ToString();

Example: If the book_name is 'Chemistry', it'll work on this case but if the book_name is 'Chemistry & Physics', it'll truncate the value after '&' sign and only pass 'Chemistry' and the URL is chapter_matches.aspx?book_name=chemistry.

to pass this value(Chemistry & Physics) via QueryString, encode the specific parameter value, by itself and on the receiving page, do the opposite:

<td>
<asp:HyperLink ID="HyperLink1" runat="server"
NavigateUrl='<%# string.Format("chapter_Matches.aspx?book_name={0}"), SanitizeURLString(DataBinder.Eval(Container.DataItem, "book_name").ToString()))%>' Text ='<%# DataBinder.Eval(Container.DataItem, "book_name")%' </asp:HyperLink>

</td>

on aspx.cs page:

protected string SanitizeURLString(string RawURLParameter)
{

string Results;

Results = RawURLParameter;

Results = Results.Replace("<", "%3C");

Results = Results.Replace(">", "%3E");
Results = Results.Replace("#", "%23");
Results = Results.Replace("%", "%25");
Results = Results.Replace("{", "%7B");
Results = Results.Replace("}", "%7D");
Results = Results.Replace("", "%7C");
Results = Results.Replace("\\", "%5C");
Results = Results.Replace("^", "%5E");
Results = Results.Replace("~", "%7E");
Results = Results.Replace("[", "%5B");
Results = Results.Replace("]", "%5D");
Results = Results.Replace("`", "%60");
Results = Results.Replace(";", "%3B");
Results = Results.Replace("/", "%2F");
Results = Results.Replace("?", "%3F");
Results = Results.Replace(":", "%3A");
Results = Results.Replace("@", "%40");
Results = Results.Replace("=", "%3D");
Results = Results.Replace("&", "%26");
Results = Results.Replace("$", "%24");

return Results;

}

On receiving aspx.cs page:

str = DeSanitizeURLString(Request.QueryString["book_name"].ToString());

protected string DeSanitizeURLString(string RawURLParameter)
{

string Results;

Results = RawURLParameter;

Results = Results.Replace("%3C", "<");

Results = Results.Replace("%3E", ">");
Results = Results.Replace("%23", "#");
Results = Results.Replace("%25", "%");
Results = Results.Replace("%7B", "{");
Results = Results.Replace("%7D", "}");
Results = Results.Replace("%7C", "");
Results = Results.Replace("%5C", "\\");
Results = Results.Replace("%5E", "^");
Results = Results.Replace("%7E", "~");
Results = Results.Replace("%5B", "[");
Results = Results.Replace("%5D", "]");
Results = Results.Replace("%60", "`");
Results = Results.Replace("%3B", ";");
Results = Results.Replace("%2F", "/");
Results = Results.Replace("%3F", "?");
Results = Results.Replace("%3A", ":");
Results = Results.Replace("%40", "@");
Results = Results.Replace("%3D", "=");
Results = Results.Replace("%26", "&");
Results = Results.Replace("%24", "$");

return Results;

}

Happy Programming!!

Thursday, September 23, 2010

Save and display images to and from SQL Database C#

Save images on the Database
Let say, I've a fileupload control to browse images and a button to upload images on SQL database.

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

<asp:Button ID="btnSubmit" runat="server" Text="Submit"
onclick="btnSubmit_Click"/>

write this code inside the button click event:

protected void btnSubmit_Click(object sender, EventArgs e)
{
//to store image into sql database.
if (FileUpload1.PostedFile != null &&
FileUpload1.PostedFile.FileName != "")
{
byte[] imageSize = new byte[FileUpload1.PostedFile.ContentLength];
HttpPostedFile uploadedImage = FileUpload1.PostedFile;
uploadedImage.InputStream.Read(imageSize, 0, (int)FileUpload1.PostedFile.ContentLength);

// Create SQL Command
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "INSERT INTO Pictures(ImageName,Image)" +
" VALUES (@ImageName,@Image)";
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;

SqlParameter ImageName = new SqlParameter
("@ImageName", SqlDbType.VarChar, 50);
ImageName.Value = strImageName.ToString();
cmd.Parameters.Add(ImageName);

SqlParameter UploadedImage = new SqlParameter("@Image", SqlDbType.Image, imageSize.Length);
UploadedImage.Value = imageSize;
cmd.Parameters.Add(UploadedImage);
conn.Open();
int result = cmd.ExecuteNonQuery();
conn.Close();
if (result > 0)
lblMessage.Text = "File Uploaded";
lblSuccess.Text = "Successful !";

}
}

Display the images from database:
Let's display the image on Gridview
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1"
AutoGenerateColumns="False" DataKeyNames="ID" Width="245px">
<Columns>
<asp:BoundField DataField="TreatmentID" HeaderText="ID" Visible="false"
SortExpression="TreatmentID" />
<asp:BoundField DataField="imageName" HeaderText="ImageName"
SortExpression="imageName" />
<asp:TemplateField HeaderText="Image">
<ItemTemplate>
<asp:Image ID="Image1" runat="server"
ImageUrl='<%#"Handler.ashx?ID=" + Eval("ID")%>'/>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TreatmentConnectionString %>"
SelectCommand="SELECT * FROM [Pictures]">

and add a handler class with the below code:

using System;
using System.Web;
using System.Data.SqlClient;

public class Handler : IHttpHandler {

public void ProcessRequest (HttpContext context)
{

try
{
SqlConnection con = new SqlConnection(GetConnectionString());

// Create SQL Command
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "Select ID,imageName, Image from Pictures" +
" where ID =@ID";
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;

SqlParameter ImageID = new SqlParameter
("@ID", System.Data.SqlDbType.Int);
ImageID.Value = context.Request.QueryString["ID"];
cmd.Parameters.Add(ImageID);
con.Open();
SqlDataReader dReader = cmd.ExecuteReader();
dReader.Read();
context.Response.BinaryWrite((byte[])dReader["Image"]);
dReader.Close();
con.Close();
}

catch(Exception ex)
{
ex.Message.ToString();
}
}

public bool IsReusable {
get {
return false;
}
}

}

Friday, August 20, 2010

Mobile Device detection in ASP.NET and use CSS to display

There are basically two ways to detect mobile devices:
-in terms of display (480px/800px)
-in terms of user agent.

My code used to detect using the display but last week Android updated their software (changed the display from 480px to 800px) so all the mobile tweaks I made for our apps are no longer work on Droid Phones.

That's why I want to follow the second method to detect the mobile devices and in future no matter whether they channged the display setting or not.

Let's identify the User Agent(e.g. Android) and switch style sheets depending. (I don't want to redirect to another page, just switch style sheets).

In aspx.cs page:

protected override void OnInit(EventArgs e)
{
string userAgent = Request.UserAgent;
if (userAgent.Contains("BlackBerry")
(userAgent.Contains("iPhone") (userAgent.Contains("Android"))))
{
//add css ref to header from code behind
HtmlLink css = new HtmlLink();
css.Href = ResolveClientUrl("~/mobile.css");
css.Attributes["rel"] = "stylesheet";
css.Attributes["type"] = "text/css";
css.Attributes["media"] = "all";
Page.Header.Controls.Add(css);
}
}
Mobile.cs:

#header {
display: none;
}
#body {
margin: 0;
width: 100%;
min-width: 100%;
font-size: 12px;
background-image: none;
empty-cells: hide;

}
#column_left {
float: none;
width: 100%;
padding-left: 5px;
}
#column_right {
float: none;
width: 100%;
padding-left: 5px;
}
#menuwrapper {
display: none;
}
#sssubsearch {
display: none;
}
h2 {
font-size: 16px;
text-align: center;
}
h3, h4 {
font-size: 1em;
/* border-bottom-width: 1px;
border-bottom-style: solid;
border-bottom-color: #666;*/
}
h3 {
/* background-repeat: repeat-x;
background-position: center top;
background-color: #E6E6E6;*/
font-size: 16px;
padding-left: 0;
margin: 0;
padding-top: 10px;
padding-bottom: 0;

}
h4 {
/* background-repeat: repeat-x;
background-position: center top;
background-color: #FFC;*/
font-size: 16px;
color: #600;
padding-left: 0;
margin-left: 0;
empty-cells: hide;
}
table {
width: 98%;
font-size: 14px;
empty-cells: hide;
}
#navigation {
display: none;
}
.left {
float: left;
vertical-align:top;
}
.right {
float: right;
vertical-align:top;
}

hr {display: none;}

Happy Coding !!

Monday, July 19, 2010

Multiple SQL rows merge into single row if the ID is same

Let's create with a table example:

create table mytable (id int identity(1,1), PersonID int, unit varchar(10))
insert into mytable values (1,'Che YYYY')
insert into mytable values (1,'Mat')
insert into mytable values (1,'Phy XXXX')

--Replace space in your column with a special character and remove it in your select statement
UPDATE mytable
SET unit=REPLACE(unit,' ','')

SELECT PersonID, REPLACE(Units,'', ' ') as Units
FROM (SELECT t1.PersonID,
Units =REPLACE( (SELECT Unit AS [data()]
FROM mytable t2
WHERE t2.PersonID = t1.PersonID
ORDER BY Unit
FOR XML PATH('')
), ' ', ',')
FROM mytable t1
GROUP BY PersonID)
t0 ;
drop table mytable

Thursday, June 10, 2010

Merge SQL tables

Let say, I have two tables:
table1:(Fields:PersonID, FirstName, LastName, Role, Department)
table2:(fields: PersonID, Unit).

and I want to create a new table with these fields:
table: ( Fields:PersonID, FirstName, LastName, Role, Department, Unit).

here is the query:

SELECT table1.*, table2.Unit
INTO new_table_name
FROM table1 inner join table2
on table1.PersonID = table2.PersonID

If table1 and table2 has one-to-many relation then first use this postto make it one-to-one relaion; otherwise it'll create multiple PersonID into new table, which you don't want.

Wednesday, May 26, 2010

Bind stored procedure data into Gridview

First, let's create a stored procedure with a name: 'Dynamic_table'

and sqlconnection 'conn' and then do the following:

SqlCommand cmd = new SqlCommand("Dynamic_table", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@tableName",

SqlDbType.VarChar).Direction = ParameterDirection.Input;
cmd.Parameters["@tableName"].Value = Session["tableValue"];

cmd.Connection.Open();

SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);

GridView1.DataSource = dt;
GridView1.DataSourceID = string.Empty;
GridView1.DataBind();
cmd.Connection.Close();

Happy Programming !!!

Stored Procedure to select dynamic table @ runtime

Create Proc Dynamic_table
@tableName varchar(50)

as

begin

declare @sql nvarchar(100)

Set @sql='Select * from '+@tableName

exec sp_executesql @sql

end