SUJOY SANTRA: August 2014

Wednesday 27 August 2014

Upload XML into SQL Server

Pass XML parameter to Stored Procedure in C#
In this article I will explain how to insert data XML to SQL server.
In previous article I explain how pass XML as a parameter in stored procedure.

Now We will see how it works in ASP.NET, C#,

Sample XML:
<?xmlversion="1.0"standalone="yes"?>
<Customers>
<CustomerId ="1">
    <Name>Sujoy Santra</Name>
    <Country>Indias</Country>
</Customer>
<CustomerId = "2">
    <Name>Mithun Patra</Name>
    <Country>India</Country>
</Customer>
    <CustomerId ="3">
    <Name>Pradipta Chowdhury</Name>
    <Country>India</Country>
</Customer>
    <CustomerId ="4">
    <Name>Sumit Kumar Sen</Name>
    <Country>India</Country>
</Customer>
</Customers>

Take one fileuploader control and one button in ASPX page.

<asp:FileUpload ID = "FileUpload1" runat = "server" />
<asp:Button ID="Button1" Text="Upload XML" runat="server" OnClick="UploadXML" />

Write the following code in C# page:

protected void UploadXML(object sender, EventArgs e)
{
    string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
    string filePath = Server.MapPath("~/Uploads/") + fileName;
    FileUpload1.SaveAs(filePath);
    string xml = File.ReadAllText(filePath);
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("InsertXML"))
        {
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@xml", xml);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}

Run the code.

Pass XML parameter to Stored Procedure

Pass XML parameter to Stored Procedure

The Stored Procedure for parsing XML data
The below stored procedure is accepting a parameter of type XML (which would be passed from the code behind). This XML object is parsed and the Attribute and Tag values are fetched and inserted into the Table.
The nodes function of the XML data type is uses XQuery expression to pull out the XML nodes from the XML, for this case I need to fetch the Customer nodes and hence the expression is i.e. /Customers/Customer where Customers is the Root Node and Customer is the child node.
Once the nodes are fetched we need to extract the attribute and tag Inner Text values. For fetching the Inner Text values between the Tags we need to make use of the values function.
The values function can read the Attribute as well as the Inner Text.
Attribute
In order to read the attribute we need to pass the name of the Attribute prefix with @ and its data type, in this example the attribute Id is fetched using Customer.value('@Id', 'INT').
Inner Text
In order to fetch the inner text we need to pass the name of the Tag and its data type. The Inner Text of the XML tag is fetched using text function and we also make use of an index [1] which means it should fetch only the first matched value.
Finally the values are inserted into the CustomerDetails table.

CREATE PROCEDURE [dbo].[InsertXML]
@xml XML
AS
BEGIN
      SET NOCOUNT ON;

      INSERT INTO CustomerDetails
      SELECT
      Customer.value('@Id','INT') AS Id, --ATTRIBUTE
      Customer.value('(Name/text())[1]','VARCHAR(100)') AS Name, --TAG
      Customer.value('(Country/text())[1]','VARCHAR(100)') AS Country --TAG
      FROM
      @xml.nodes('/Customers/Customer')AS TEMPTABLE(Customer)
END

Convert Text to Image in ASP.NET

Convert Text to Image in ASP.NET

In this article I will explain how to create Text to image in ASP.NET.

Take a text box,button and image control.

In HTML page add the following Code:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <asp:TextBox runat="server" ID="txtText"></asp:TextBox>
    <asp:Button ID="btnConvert" runat="server" Text="Convert" OnClick="btnConvert_Click" />
    <hr />
    <asp:Image ID="imgText" runat="server" Visible="false" />
    </form>
</body>
</html>

In C# page add the following Code:

using System.Drawing.Text;
using System.Drawing;
using System.Drawing.Drawing2D;
using System.IO;
using System.Drawing.Imaging

Add the following code in button click event:
protected void btnConvert_Click(object sender, EventArgs e)
{
    string text = txtText.Text.Trim();
    Bitmap bitmap = new Bitmap(1, 1);
    Font font = new Font("Arial", 25, FontStyle.Regular, GraphicsUnit.Pixel);
    Graphics graphics = Graphics.FromImage(bitmap);
    int width = (int)graphics.MeasureString(text, font).Width;
    int height = (int)graphics.MeasureString(text, font).Height;
    bitmap = new Bitmap(bitmap, new Size(width, height));
    graphics = Graphics.FromImage(bitmap);
    graphics.Clear(Color.White);
    graphics.SmoothingMode = SmoothingMode.AntiAlias;
    graphics.TextRenderingHint = TextRenderingHint.AntiAlias;
    graphics.DrawString(text, font, new SolidBrush(Color.FromArgb(255, 0, 0)), 0, 0);
    graphics.Flush();
    graphics.Dispose();
    string fileName = Path.GetFileNameWithoutExtension(Path.GetRandomFileName()) + ".jpg";
    bitmap.Save(Server.MapPath("~/images/") + fileName, ImageFormat.Jpeg);
    imgText.ImageUrl = "~/images/" + fileName;
    imgText.Visible = true;
}

Run the code.

Tuesday 26 August 2014

Create a ASPX Page Dynamically in ASP.NET

This article will describe how to create aspx page dynamically in asp.net using c#.

First create one new web application and create a new page name "DynamicPage.aspx". open it and write the following code.

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<table>
<tr>
<td><b>Enter Page Name:</b></td>

Write the following code in .CS Page

using System;
using System.IO;
public partial class DynamicPage: System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnCreate_Click(object sender, EventArgs e)
{
string[] aspxLines = {"<%@ Page Language=\"C#\" AutoEventWireup=\"true\"CodeFile=\""+txtpagename.Text.Trim()+".aspx.cs\" Inherits=\"generate_page_runtime."+txtPageName.Text.Trim()+"\" %>",
"<!DOCTYPE html>",
"<head>",
"<title>The New Page</title>",
"</head>",
"<body>",
"   <form id=\"form1\" runat=\"server\">",
"       <div>",
"           <asp:literal id=\"output\" runat=\"server\"/>",
"       </div>",
"   </form>",
"</body>",
"</html>"};
string[] csLines = {"using System;",
"using System.Web.UI.WebControls;",
"namespace generate_page_runtime {",
"    public partial class "+txtPageName.Text.Trim()+" : System.Web.UI.Page {",
"        protected void Page_Load(object sender, EventArgs e) {",
"            output.Text = \"Our new page\";",
"        }",
"    }",
"}"};
File.WriteAllLines(Server.MapPath("" + txtPageName.Text.Trim() + ".aspx"), aspxLines);
File.WriteAllLines(Server.MapPath("" + txtPageName.Text.Trim() + ".aspx.cs"), csLines);
lblmsg.Text = "Aspx Page Created Successfully";
}
protected void btnRedirect_Click(object sender, EventArgs e)
{
Response.Redirect("" + txtPageName.Text.Trim() + ".aspx");
}
}

Now run the code.

Monday 18 August 2014

Create date,Modified date Table,Procedure ,Function in SQL

Create date,Modified date Table,Procedure ,Function in SQL

SELECT name, create_date, modify_date
FROM sys.objects
order by create_date desc

Sunday 17 August 2014

Pen drive Folder are not showing?

Step One:

Plug your USB Pendrive or External HDD or Memory Card which is Affected.

Step Two:

Go to Start -> Run-> Type CMD and Enter

Step Three:

Find the drive letter for the connected USB drive. For Example, I:

In command prompt, type I:

and Hit enter. Command Prompt will show I:/

Step Four:

Then type

attrib -s -h /s /d *.*

Make sure that you put space between each elements in the code.

and hit enter and wait for few seconds.

Read more at http://imacify.com/2011/02/how-to-recover-hidden-files-from-virus-infected-usb-pendrive/#XFCzA20Xb7xmJ5Yh.99

Tuesday 12 August 2014

Display Date Suffix in C#

private string GetDateSuffix(string day)

{

string strDatesuffix = "th";

if (int.Parse(day) < 11 || int.Parse(day) > 20)

{

day = day.ToCharArray()[day.ToCharArray().Length - 1].ToString();

switch (day)

{

case "1":

strDatesuffix = "st";

break;

case "2":

strDatesuffix = "nd";

break;

case "3":

strDatesuffix = "rd";

break;

}

return strDatesuffix ;

}

Csv To StringTable in SQL

CREATE FUNCTION CsvToStringTable(@csvString nvarchar(4000))
RETURNS @stringTable TABLE
                (Value nvarchar(1000))
AS
BEGIN
    DECLARE @separator char(1)
    SET @separator = ','

    DECLARE @separator_position int
    DECLARE @array_value nvarchar(1000)

    SET @csvString = @csvString + ','

    WHILE PATINDEX('%,%', @csvString) <> 0
    BEGIN
        SELECT @separator_position = PATINDEX('%,%', @csvString)
        SELECT @array_value = LEFT(@csvString, @separator_position - 1)

        INSERT @stringTable
        VALUES (RTRIM(LTRIM(Cast(@array_value as nvarchar(1000)))))

        SELECT @csvString = STUFF(@csvString, 1, @separator_position, '')
    END

    RETURN
END

Friday 8 August 2014

Send (Pass) DataTable as parameter to Stored Procedure in C#

Database

create a simple table.

create a User Defined Table Type in SQL Server using the following query
CREATE TYPE [dbo].[CustomerType] AS TABLE(
      [Id] [int] NULL,
      [Name] [varchar](100) NULL,
      [Country] [varchar](50) NULL
)

Finally the following stored procedure
 is created which will accept the DataTable as parameter and then will 
insert all records into the table.

 
CREATE PROCEDURE [dbo].[Insert_Customers]
      @tblCustomers CustomerType READONLY
AS
BEGIN
      SET NOCOUNT ON;
     
      INSERT INTO Customers(CustomerId, Name, Country)
      SELECT Id, Name, Country FROM @tblCustomers
END

HTML :

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
<Columns>
    <asp:TemplateField>
        <ItemTemplate>
            <asp:CheckBox ID="CheckBox1" runat="server" />
        </ItemTemplate>
    </asp:TemplateField>
    <asp:BoundField DataField="Id" HeaderText="Id" ItemStyle-Width="30" />
    <asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-Width="150" />
    <asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="150" />
</Columns>
</asp:GridView>
<br />
<asp:Button ID="Button1" Text="Bulk Insert" OnClick="Bulk_Insert" runat="server" />

XML:

<?xmlversion="1.0"standalone="yes"?>
<Customers>
 <Customer>
    <Id>1</Id>
    <Name>John Hammond</Name>
    <Country>United States</Country>
 </Customer>
 <Customer>
    <Id>2</Id>
    <Name>Mudassar Khan</Name>
    <Country>India</Country>
 </Customer>
 <Customer>
    <Id>3</Id>
    <Name>Suzanne Mathews</Name>
    <Country>France</Country>
 </Customer>
 <Customer>
    <Id>4</Id>
    <Name>Robert Schidner</Name>
    <Country>Russia</Country>
 </Customer>
</Customers>

C#:

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataSet ds = new DataSet();
        ds.ReadXml(Server.MapPath("~/Customers.xml"));
        GridView1.DataSource = ds.Tables[0];
        GridView1.DataBind();
    }
}

Bulk Insert:

protected void Bulk_Insert(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
                    new DataColumn("Name", typeof(string)),
                    new DataColumn("Country",typeof(string)) });
    foreach (GridViewRow row in GridView1.Rows)
    {
        if ((row.FindControl("CheckBox1") as CheckBox).Checked)
        {
            int id = int.Parse(row.Cells[1].Text);
            string name = row.Cells[2].Text;
            string country = row.Cells[3].Text;
            dt.Rows.Add(id, name, country);
        }
    }
    if (dt.Rows.Count > 0)
    {
        string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(consString))
        {
            using (SqlCommand cmd = new SqlCommand("Insert_Customers"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = con;
                cmd.Parameters.AddWithValue("@tblCustomers", dt);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }
}

Use and access Session variables in Generic Handler in ASP.Net

By default Session is disabled inside the Generic handler and hence in order to use and access Session variables we need to inherit the IRequiresSessionState interface.

C# Page:

  protected void Submit(object sender, EventArgs e)

  {

      Session["Name"] = txtName.Text;

      Response.Redirect("~/HandlerCS.ashx");

  }

Generic Handler:

  <%@ WebHandler Language="C#" Class="Handler" %>

  using System;

  using System.Web;

  using System.Web.SessionState;

  public class Handler : IHttpHandler, IRequiresSessionState

  {

      public void ProcessRequest (HttpContext context) {

          string name = context.Session["Name"].ToString();

          context.Response.ContentType = "text/plain";

          context.Response.Write(name);

      }

      public bool IsReusable {

          get {

              return false;

          }

      }

  }

Show Loading Progress Indicator using GIF Image when UpdatePanel is updating in ASP.Net

Show Loading Progress Indicator using GIF Image when UpdatePanel is updating in ASP.Net

HTML Page:
<asp:ScriptManager runat="server">
</asp:ScriptManager>
<asp:UpdateProgress ID="UpdateProgress1" runat="server" AssociatedUpdatePanelID="UpdatePanel1">
<ProgressTemplate>
    <div class="modal">
        <div class="center">
            <img alt="" src="loader.gif" />
        </div>
    </div>
</ProgressTemplate>
</asp:UpdateProgress>
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
    <div align="center">
        <h1>
            Click the button to see the UpdateProgress!</h1>
        <asp:Button ID="Button1" Text="Submit" runat="server" OnClick="Button1_Click" />
    </div>
</ContentTemplate>
</asp:UpdatePanel>

C# Page:

protected void Button1_Click(object sender, EventArgs e)
{
    System.Threading.Thread.Sleep(5000);
}

CSS:

<style type="text/css">
body
{
    margin: 0;
    padding: 0;
    font-family: Arial;
}
.modal
{
    position: fixed;
    z-index: 999;
    height: 100%;
    width: 100%;
    top: 0;
    background-color: Black;
    filter: alpha(opacity=60);
    opacity: 0.6;
    -moz-opacity: 0.8;
}
.center
{
    z-index: 1000;
    margin: 300px auto;
    padding: 10px;
    width: 130px;
    background-color: White;
    border-radius: 10px;
    filter: alpha(opacity=100);
    opacity: 1;
    -moz-opacity: 1;
}
.center img
{
    height: 128px;
    width: 128px;
}
</style>

Indian Currency Checking by regular Expression

This regular expression looks for any number of digits. And if dot character is added it will look for decimal places upto 2 decimals.

HTML Page:

<asp:TextBox ID="TextBox1" runat="server" />
<br />
<asp:RegularExpressionValidator ID="Regex1" runat="server" ValidationExpression="((\d+)((\.\d{1,2})?))$"
ErrorMessage="Please enter valid integer or decimal number with 2 decimal places."
ControlToValidate="TextBox1" />

Populate TreeView using recursion in ASP.Net

In this article I will explain how to populate (bind) TreeView control with Nodes having Parent Child relationship from database in ASP.Net using C#.

The TreeView will be populated using recursion.

Database
In order to populate TreeView with Parent Child relationship, I have made use of two tables namely VehicleTypes and VehicleSubTypes. The schema and the data present in both the tables are as follows.
VehicleTypes

HTML Page:
<h3>
    Vehicle Details</h3>
<hr />
<asp:TreeView ID="TreeView1" runat="server" ImageSet="XPFileExplorer" NodeIndent="15">
    <HoverNodeStyle Font-Underline="True" ForeColor="#6666AA" />
    <NodeStyle Font-Names="Tahoma" Font-Size="8pt" ForeColor="Black" HorizontalPadding="2px"
        NodeSpacing="0px" VerticalPadding="2px"></NodeStyle>
    <ParentNodeStyle Font-Bold="False" />
    <SelectedNodeStyle BackColor="#B5B5B5" Font-Underline="False" HorizontalPadding="0px"
        VerticalPadding="0px" />
</asp:TreeView>

C# Page:protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataTable dt = this.GetData("SELECT Id, Name FROM VehicleTypes");
        this.PopulateTreeView(dt, 0, null);
    }
}

private void PopulateTreeView(DataTable dtParent, int parentId, TreeNode treeNode)
{
    foreach (DataRow row in dtParent.Rows)
    {
        TreeNode child = new TreeNode
        {
            Text = row["Name"].ToString(),
            Value = row["Id"].ToString()
        };
        if (parentId == 0)
        {
            TreeView1.Nodes.Add(child);
            DataTable dtChild = this.GetData("SELECT Id, Name FROM VehicleSubTypes WHERE VehicleTypeId = " + child.Value);
            PopulateTreeView(dtChild, int.Parse(child.Value), child);
        }
        else
        {
            treeNode.ChildNodes.Add(child);
        }
    }
}

private DataTable GetData(string query)
{
    DataTable dt = new DataTable();
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                sda.Fill(dt);
            }
        }
        return dt;
    }
}

ASP.Net RequiredFieldValidator not working when OnClientClick event is added to Button

HTML

The HTML markup consists of an ASP.Net TextBox, a RequiredFieldValidator and a Button which has an OnClientClick event handler attached.

Name:

<asp:TextBox ID="txtName" runat="server" />

<asp:RequiredFieldValidator runat="server" ErrorMessage="Required" ControlToValidate="txtName"></asp:RequiredFieldValidator>

<br />

<br />

<asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="Submit" OnClientClick="return Validate();" />

<script type="text/javascript">

    function Validate() {

        return confirm('Do you want to submit data?');

    }

</script>

The Problem

The above code will display confirm 
and will not validate the TextBox using RequiredFieldValidator and the 
PostBack will happen even if the TextBox is left empty.

The Solution

Hence we need to make use of the Page_ClientValidate JavaScript function of ASP.Net Validators to perform validation explicitly as shown below.

Solution HTML

Name:

<asp:TextBox ID="txtName" runat="server" />

<asp:RequiredFieldValidator runat="server" ErrorMessage="Required" ControlToValidate="txtName"></asp:RequiredFieldValidator>

<br />

<br />

<asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="Submit" OnClientClick="return Validate();" />

<script type="text/javascript">

    function Validate() {

        if (Page_ClientValidate()) {

            return confirm('Do you want to submit data?');

        }

        return false;

    }

</script>

Encription & Decription

private string Encrypt(string clearText)
    {
        string EncryptionKey = "MAKV2SPBNI99299"; //MAKV2SPBNI99212
        byte[] clearBytes = Encoding.Unicode.GetBytes(clearText);
        using (Aes encryptor = Aes.Create())
        {
            Rfc2898DeriveBytes pdb = new Rfc2898DeriveBytes(EncryptionKey, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
            encryptor.Key = pdb.GetBytes(32);
            encryptor.IV = pdb.GetBytes(16);
            using (MemoryStream ms = new MemoryStream())
            {
                using (CryptoStream cs = new CryptoStream(ms, encryptor.CreateEncryptor(), CryptoStreamMode.Write))
                {
                    cs.Write(clearBytes, 0, clearBytes.Length);
                    cs.Close();
                }
                clearText = Convert.ToBase64String(ms.ToArray());
            }
        }
        return clearText;
    }

****************

private string Decrypt(string cipherText)
    {
        string EncryptionKey = "MAKV2SPBNI99299"; //MAKV2SPBNI99212
        cipherText = cipherText.Replace(" ", "+");
        byte[] cipherBytes = Convert.FromBase64String(cipherText);
        using (Aes encryptor = Aes.Create())
        {
            Rfc2898DeriveBytes pdb = new Rfc2898DeriveBytes(EncryptionKey, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
            encryptor.Key = pdb.GetBytes(32);
            encryptor.IV = pdb.GetBytes(16);
            using (MemoryStream ms = new MemoryStream())
            {
                using (CryptoStream cs = new CryptoStream(ms, encryptor.CreateDecryptor(), CryptoStreamMode.Write))
                {
                    cs.Write(cipherBytes, 0, cipherBytes.Length);
                    cs.Close();
                }
                cipherText = Encoding.Unicode.GetString(ms.ToArray());
            }
        }
        return cipherText;
    }
**********
Page 1
string name = HttpUtility.UrlEncode(Encrypt(txtName.Text.Trim()));
        string technology = HttpUtility.UrlEncode(Encrypt(ddlTechnology.SelectedItem.Value));
        Response.Redirect(string.Format("~/CS2.aspx?name={0}&technology={1}", name, technology));
**********

Page 2
lblName.Text = Decrypt(HttpUtility.UrlDecode(Request.QueryString["name"]));
            lblTechnology.Text = Decrypt(HttpUtility.UrlDecode(Request.QueryString["technology"]));

Thursday 7 August 2014

Encoding Vs Encryption

Encoding and Encryption are somewhat similar concept but these are different in purpose.

Encoding means transforming data to another format with schemes that are publicly available like base64 encoding scheme.

Later on, anyone can decode to convert it back to original format.
Purpose of encoding might be compressing to save memory or confirming the transfer of data over a
channel etc.

In case of Encryption, transformation of data to another format is done with the purpose of security, so that not everyone can read data except the ones having decryption key/password etc. Encryption id done using some specific key or password.