Learn C# ASP.NET MVC WCF SQL Angular: 2015

Tuesday, December 29, 2015

how to read xml file in sql server stored procedure

In this article, I am going to explain how to read or select xml data in SQL.

Firstly I show you xml which one is ready in SQL procedure. The given below xml have Empcode,name,DOB and age fileds, which one shown by sql in a table

<EmpDetails>
    <NewDataSet>
        <EmpCode>EMP014</EmpCode>
        <name>Sharad</name>
        <DOB>12/24/1978 12:00:00 AM</DOB>
        <age>35</age>
        <location>x</location>
        <gender>Male</gender>
        <StateID>1</StateID>
        <CityID>1</CityID>
        <designation>1 </designation>
    </NewDataSet>
    <NewDataSet>
        <EmpCode>EMP015</EmpCode>
        <name>Devesh </name>
        <DOB>12/24/1983 12:00:00 AM</DOB>
        <age>31</age>
        <location>Delhi</location>
        <gender>Male</gender>
        <StateID>1</StateID>
        <CityID>1</CityID>
        <designation>1</designation>
    </NewDataSet>
</EmpDetails>

Now i write a Procedure that read xml and return result in table format.

Create PROCEDURE usp_ReadXml (

@xmlString NVARCHAR(MAX) = '<EmpDetails>

<name>Sharad</name>

</NewDataSet>

<name>Devesh </name>

<location>Delhi</location>

</NewDataSet>

</EmpDetails>

)

BEGIN

DECLARE @xmlHandle INT

EXEC sp_xml_preparedocument @xmlHandle OUTPUT, @xmlString

SELECT NAME,

Age,

Gender,

Location,

designation,

StateID,

CityID,

EmpCode,

DOB

FROM OPENXML(@xmlHandle, '/EmpDetails/NewDataSet', 2) WITH (

name NVARCHAR(100),

age INT,

gender VARCHAR(10),

location VARCHAR(50),

designation INT,

StateID INT,

CityID INT,

EmpCode VARCHAR(50),

DOB DATETIME

)

EXEC sp_xml_removedocument @xmlHandle

END

Output is

Run this command "Exec usp_ReadXml" and now output is

Convert DataSet and DataTable to Xml String

In this article I am going to explain how to convert DataTable or DataSet in Xml.

Convert DataTable to XML

public static string ToStringAsXml(DataSet ds)

{

StringWriter sw = new StringWriter();

ds.WriteXml(sw, XmlWriteMode.IgnoreSchema);

string s = sw.ToString();

return s;

}

Convert DataSet to XML

public static string ToStringAsXml(DataTable dt)

{

StringWriter sw = new StringWriter();

dt.WriteXml(sw, XmlWriteMode.IgnoreSchema);

string s = sw.ToString();

return s;

}

In the given below code , I write to data tables function CreateTable() and CreateTable1() that return table and by using that table we convert into xml. The "string dtXml" accepts xml of data table and "string dsxml" accepts the data set xml.

Code

using System;

using System.Data;

using System.IO;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

DataTable dt = CreateTable();

dt.TableName="Employee";

string dtXml = ToStringAsXml(dt);

DataTable dt1 = CreateTable();

dt1.TableName = "Employee1";

DataSet ds = new DataSet();

ds.Tables.Add(dt);

ds.Tables.Add(dt1);

string dsxml = ToStringAsXml(ds);

}

public DataTable CreateTable()

{

DataTable dt = new DataTable();

dt.Columns.Add("Name");

dt.Columns.Add("Age");

DataRow rr = dt.NewRow();

rr["Name"] = "Sharad";

rr["Age"] = 25;

dt.Rows.Add(rr);

DataRow rr1 = dt.NewRow();

rr1["Name"] = "Devesh";

rr1["Age"] = 25;

dt.Rows.Add(rr1);

return dt;

}

public DataTable CreateTable1()

{

DataTable dt = new DataTable();

dt.Columns.Add("Name");

dt.Columns.Add("Age");

DataRow rr = dt.NewRow();

rr["Name"] = "Sharad";

rr["Age"] = 25;

dt.Rows.Add(rr);

DataRow rr1 = dt.NewRow();

rr1["Name"] = "Devesh";

rr1["Age"] = 25;

dt.Rows.Add(rr1);

return dt;

}

public static string ToStringAsXml(DataSet ds)

{

StringWriter sw = new StringWriter();

ds.WriteXml(sw, XmlWriteMode.IgnoreSchema);

string s = sw.ToString();

return s;

}

public static string ToStringAsXml(DataTable dt)

{

StringWriter sw = new StringWriter();

dt.WriteXml(sw, XmlWriteMode.IgnoreSchema);

string s = sw.ToString();

return s;

}

Sunday, December 27, 2015

Data Type in SQL

SQL have following data types

1-Integer value

Integer value have following data types

Type	Storage
TINY	One(1) byte
SMALL INT	Two(2) byte
INT	Four(4) byte
BIG INT	Eight (8) byte

2-Float/Decimal values

Syntax

decimal(p,s)

where p=(Permission)

The permission means total number of digits can be present in the value (maximum value is 38 digits).

s=(scale)

It means your number of digits that can present after decimal.

3 - Numeric (p,s)

Syntax

Numeric (p,s)

It is equal to decimal data type.

4 - Boolean Values

Syntax

bit

It is bit type and take 1 byte space. It holds either 0 or 1 where 0 means "false" and 1 means true.

5 - Character Values

Syntax

char(n)

The maximum value of n is 8000. It is called as fixed data type. once you specified n value, the sql uses all the size.

6 - Varchar values

Syntax

varchar(n)

The maximum value of n is 8000. It is variable length character data type. In this case it does not fill any blank space.

7 - Text

Syntax

Text

It is in 2 GB size. It is variable length character data type. In this case it does not fill any blank space.

8 - NChar

Syntax

nchar(n)

It is similar to the char data type but can store multi language character. The maximum capacity is 4000 characters.

9 - Currency Values

It can only use for US currency format, The currency values are two type

a- Small Money - 4 bytes

b- Money - 8 bytes

10 - For Storing Binary Values

If you want to store images or video files or audio files in the database, you can use binary data type

a - Binary (n) - The maximum value is 8000 bytes, it is fixed length data type similar to char and nchar data type.

b- VarBinary(n) - The maximum value is 8000 bytes.

c - Image - The maximum Size is 2 GB.

11 - For Storing Date Values

a - Small Date Time

Syntax

smalldatetime

It is 4 bytes, It can hold data ranging between Jan 1, 1900 to Jun 6,2079.

b - Date Time

Syntax

datetime

Size of Date Time is 8 bytes.It can hold data ranging between January 1,1753 to Dec 31, 9999

c- Time Stamp

Syntax

timestamp

If this data type is used in column wherever a modification is made to a record SQL Server will enter the date & time into the column. The size is 8 bytes

12 - For Storing XML Values

It is size in 2 GB.

System database in sql server

System Database

     This database is used for various activities that are going to be performed within SQL Server.

(1) Master: - This database record all the system level into or operations that are going to be performed with in SQL Server (Root Database of the SQL Server).

(2) Model: - It acts as a template for every new database that is created in SQL Server when we crate a database SQL Server will copy the complete structure from the model and using that structure it will create the





(3) Temp database: - This database holds the temporary objects that are created in SQL Server because temporary objects requires to be destroyed whenever SQL Server is shutdown, So without going and verifying in to each database to identify the temporary objects, It uses these mechanism. Whenever SQL Server is shutdown the complete tempDB is dropped so all the temporary tables present in it are destroyed and whenever SQL Server restarts it will recreated a tempDB database again by copying the structure from model database.



(4) MSDB: - It is a database, which is used for storing information of jobs, alerts and schedules.

         JOB: - It is an activity that has to be performed a schedule specifies when the action or activity has to be performed.

         ALERT: - It is messages that pop up to the use on some prescribed situations.

Note : Every Database System or User is internally collection of two files

(1) .mdf: - Stands for Master Data File this contains the information of the actual tables that are present in the data along with their data.

(2) .ldf: - Stands for log data file which holds the information of the DML activities that are performed on the database.

Note

        The .mdf, .ldf files sits in the data folder of the SQL Server installation folder.

Friday, December 25, 2015

Create CAPTCHA in ASP.NET C#

In this article going to explain how to create CAPTCHA in asp.net c#.

Before creating the captcha first we want to know why we using captcha?

Captcha code ids kind of image is used to validate client browser window really has a human typing into it or not Or simply say captcha is only be read by human not by robots.( Robots ia a kind of program that automativally submit the data to the website) and when we want to prevent it we use the captcha.

Now Lets move with creating captcha in asp.net c#

Firstly Create a "GenerateCaptcha.aspx" page

code of GenerateCaptcha.aspx"

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="GenerateCaptcha.aspx.cs" Inherits="GenerateCaptcha" %>

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

</head>

<body>

<div>

</div>

</form>

</body>

</html>

code of GenerateCaptcha.aspx.cs"

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Drawing;

using System.Drawing.Drawing2D;

using System.Drawing.Imaging;

public partial class GenerateCaptcha : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

Response.Clear();

int height = 30;

int width = 100;

Bitmap bmp = new Bitmap(width, height);

RectangleF rectf = new RectangleF(10, 5, 0, 0);

Graphics g = Graphics.FromImage(bmp);

g.Clear(Color.White);

g.SmoothingMode = SmoothingMode.AntiAlias;

g.InterpolationMode = InterpolationMode.HighQualityBicubic;

g.PixelOffsetMode = PixelOffsetMode.HighQuality;

g.DrawString(Session["captcha"].ToString(), new Font("Segoe UI", 15, FontStyle.Strikeout), Brushes.Red, rectf);

g.DrawRectangle(new Pen(Color.Bisque), 1, 1, width - 2, height - 2);

g.Flush();

Response.ContentType = "image/jpeg";

bmp.Save(Response.OutputStream, ImageFormat.Jpeg);

g.Dispose();

bmp.Dispose();

}

Now Create another Default.aspx page

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

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

</head>

<body>

<div>

<asp:Panel ID="pnlCheckCaptcha" runat="server" GroupingText="<b>Check Captcha"></b> <table>

<tr>

<td>Write Catcha Here</td>

</tr>

<tr>

<td>

<asp:Button ID="btnRefresh" runat="server" Text="Refresh" OnClick="btnRefresh_Click" />

</td>

</tr>

<tr>

<td colspan="2" align="center"><asp:Button ID="btnchekcatcha" runat="server"

Text="Check" onclick="btnchekcatcha_Click"/>

<asp:Label ID="lblmsg" runat="server" ForeColor="Red"></asp:Label>

</td>

</tr>

</table>

</asp:Panel>

</div>

</form>

</body>

</html>

Default.aspx.cs page code

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Drawing;

using System.Drawing.Drawing2D;

using System.Drawing.Imaging;

using System.Text;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

try

{

if (!IsPostBack)

{

GetCapctha();

}

catch (Exception ex)

{

lblmsg.Text = ex.Message.ToString();

}

void GetCapctha()

{

try

{

Random random = new Random();

string combination = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789";

StringBuilder captcha = new StringBuilder();

for (int i = 0; i < 6; i++)

captcha.Append(combination[random.Next(combination.Length)]);

Session["captcha"] = captcha.ToString();

imgCaptcha.ImageUrl = "GenerateCaptcha.aspx?" + DateTime.Now.Ticks.ToString();

}

catch

{

throw;

}

protected void btnRefresh_Click(object sender, EventArgs e)

{

try

{

GetCapctha();

}

catch (Exception ex)

{

lblmsg.Text = ex.Message.ToString();

}

protected void btnchekcatcha_Click(object sender, EventArgs e)

{

try

{

if (Session["captcha"].ToString() != txtCaptcha.Text)

lblmsg.Text = "Invalid Captcha Code";

else

lblmsg.Text = "Valid Captcha Code";

GetCapctha();

}

catch (Exception ex)

{

lblmsg.Text = ex.Message.ToString();

}

Output