.Net Tips and Solutions: May 2013

Wednesday, May 29, 2013

How to Convert Rows to Columns in SQL Server Without Using Pivot?

If you observe above table we have name “Suresh” which repeated for 3 times but it’s having different mobiles number in this case I want to display single username with all mobiles numbers like as shown below

To implement this kind of functionality we need to write the query like as shown below

DECLARE @tempUserInfo TABLE (UserName VARCHAR(50),Mobile VARCHAR(50))

INSERT INTO @tempUserInfo VALUES ('Suresh',9999999999)

INSERT INTO @tempUserInfo VALUES ('Suresh',8888888888)

INSERT INTO @tempUserInfo VALUES ('Suresh',7777777777)

INSERT INTO @tempUserInfo VALUES ('Mahesh',9000000000)

INSERT INTO @tempUserInfo VALUES ('Mahesh',9005000000)

INSERT INTO @tempUserInfo VALUES ('Madhav',8125000000)

INSERT INTO @tempUserInfo VALUES ('Madhav',8160000000)

INSERT INTO @tempUserInfo VALUES ('Madhav',8165555000)

;WITH UserDetails AS

( SELECT

*,ROW_NUMBER() OVER(PARTITION BY UserName ORDER BY UserName) AS ColumnNumber

FROM @tempUserInfo

)

SELECT DISTINCT

t.UserName

,t1.Mobile AS website1,t2.Mobile AS website2,t3.Mobile AS website3

FROM @tempUserInfo t

LEFT OUTER JOIN UserDetails t1 ON t.UserName=t1.UserName AND t1.ColumnNumber=1

LEFT OUTER JOIN UserDetails t2 ON t.UserName=t2.UserName AND t2.ColumnNumber=2

LEFT OUTER JOIN UserDetails t3 ON t.UserName=t3.UserName AND t3.ColumnNumber=3 Order byt.userName desc

Above query will change row values to column values and the ouptput like as shown below

How to pass table name as parameter to the Stored procedure in SQL server

If we pass table name as parameter to the stored procedure , we can use single stored procedure for different tables which contains same column names and data types. For this follow the stored procedure below

create procedure Test(@table_name varchar(max))
as
declare @va varchar(max)
begin
set @va = 'select * from ' + @table_name
EXEC(@va)
RETURN
end

How to convert Number to Roman Numericals in C#

In the Below post V` means=5000,
VM` =6000,
MC`=9000.

public partial class NumberConversion : Form
{
public string[] strOnes = { "", "I", "II", "III", "IV", "V", "VI", "VII", "VIII", "IX" };
public string[] strTens = { "", "X", "XX", "XXX", "XL", "L", "LX", "LXX", "LXXX", "XC" };
public string[] strHundreds = { "", "C", "CC", "CCC", "CD", "D", "DC", "DCC", "DCCC", "CM" };
public string[] strThousands = { "", "M", "MM", "MMM", "MMMM","V`","VM`","VMM`","VMMM`","MC`" };
int intOnes, intTens, intHundrs;
public string strRomanNum;

public NumberConversion()
{
InitializeComponent();
}

private void button2_Click(object sender, EventArgs e)
{
if (txtNumber.TextLength < 5)
{
int intNum = Convert.ToInt32(txtNumber.Text);
int intlength = intNum.ToString().Length;

int intTh = intNum / 1000;
intNum = intNum % 1000;

int intH = intNum / 100;
intHundrs = intNum % 100;

int intTe = intHundrs / 10;
intTens = intHundrs % 10;

intOnes = intTens;

strRomanNum = strThousands[intTh] + strHundreds[intH] + strTens[intTe] + strOnes[intOnes];
txtRomanNumber.Text = strRomanNum;
}
else
{
MessageBox.Show("please enter the vlaue lessthan 9999");
txtNumber.Clear();
txtRomanNumber.Clear();
}
}
}

output::::

Monday, May 27, 2013

How to convert Image Data type in Sqlserver to original image in ASP.Net

We have Generic handler class in .Net frame work. Add generic handler class to your project like --> right click on your project -->Add --> Select New Item --> select generic handler class.
For our example name generic handler as Handler1.ashx(generic handler class extension is .ashx)
pass query string Handler1 class .
In this example i am passing query string to handler class in DataList Item template field. see below
My table tblComputers Having 'Uid' column

And this my C# code for DataList to Bind Data

SqlConnection cn = new SqlConnection("server=.;DataBase=HareeshServices;uid=sa;pwd=roopasoft");
SqlDataAdapter da;
SqlCommand cmd;
DataTable dt;
public void GetImage()
{
dt = new DataTable();
cmd = new SqlCommand("select * from tblComputers", cn);
da = new SqlDataAdapter(cmd);
da.Fill(dt);
dlComputers.DataSource = dt;
dlComputers.DataBind();
}
Now goto Handler1.ashx class Write following Code

public class Handler1 : IHttpHandler
{
SqlConnection cn = new SqlConnection("server=.;DataBase=HareeshServices;uid=sa;pwd=roopasoft");
SqlCommand cmd;
//SqlDataReader dr;
public void ProcessRequest(HttpContext context)
{
if (context.Request.QueryString["Uid"] == null) return;
string Uid= context.Request.QueryString["Uid"];

cmd = new SqlCommand("select ImageByte from tblComputers where Uid=@Uid", cn);
cmd.Parameters.Add(new SqlParameter("@Uid",Uid));
cn.Open();
//using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
//{
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
dr.Read();
context.Response.BinaryWrite((Byte[])dr[dr.GetOrdinal("ImageByte")]);
dr.Close();
//}

}

}

Here Handler1 class fires when we bind data to datasource

Friday, May 24, 2013

How to Remove COPY, PASTE and CUT option for a Textbox in ASP.Net

This scenario will comes when you are creating any Password and Confirm Password Textboxes in Your Asp.Net Application.

For those three , Just write 3 Properties in your TextBox Control.

1) oncopy="return false"
Explanation:: When you use above property in your textbox control, the user can not copy the text from the textbox.

2)onpaste="return false"
Explanation:: When you use above property in your textbox control, the user can not paste any text in your textbox.

3)oncut="return false"
Explanation:: When you use above property in your textbox control, the user can not cut any text in your textbox.

Syntax For Updating Multiple Columns

update table1

set col1 = a.col1, col2 = a.col2, col3 = a.col3 from

(select col1, col2, col3 from table2 where <expression>) as a

where table1.col1 <expression>

(or)

UPDATE  Table1
SET     Field1 = Table2.Field1,
        Field2 = Table2.Field2,
        other columns...
FROM    Table2
WHERE   Table1.ID = Table2.ID

Example:-

UPDATE SHIPMENT
SET
  OrgAddress1     = BD.OrgAddress1,
  OrgAddress2     = BD.OrgAddress2,
  OrgCity         = BD.OrgCity,
  OrgState        = BD.OrgState,
  OrgZip          = BD.OrgZip,
  DestAddress1    = BD.DestAddress1,
  DestAddress2    = BD.DestAddress2,
  DestCity        = BD.DestCity,
  DestState       = BD.DestState,
  DestZip         = BD.DestZip
FROM
   BookingDetails BD
WHERE 
   SHIPMENT.MyID2 = @MyID2
   AND
   BD.MyID = @MyID

How to Rename Database Name in Sql Server

Syntax:: sp_renamedb <oldDatabaseName>,<NewDatabaseName>

Thursday, May 23, 2013

Create INVOICE using Asp.net:

step1: Create Aspx page with controles


Phone/mobileNo.
Email
Date
Time
Number of Passengers
languages
Vehicle
pickup point
pickup Lan dmark

client side code for all controles:(print.aspx)

table align="center" style="width:100%;background-color:Background; height: 259px;">

<tr>

<td style="width:50%;height:250px"><table align="center" style="background-image:url('image\black.jpg');width:50%; height: 150px;">

<tr><td class="style3" style="color:Orange";>Name</td><td class="style1" style="color:Orange;"><asp:TextBox ID ="txtnam" runat="server" />

<asp:RequiredFieldValidator ID="reqfieldFname" runat="server" ControlToValidate="txtnam" Display="Dynamic"

ErrorMessage="Please enter your name"></asp:RequiredFieldValidator>

<asp:RegularExpressionValidator ID="RegexpFname" runat="server" ControlToValidate="txtnam"

ValidationExpression="^[A-Za-z]+$" ErrorMessage="Please enter charectrers only"></asp:RegularExpressionValidator>

</td></tr>

<tr><td class="style3" style="color:Orange">Phone/mobileNo.</td><td class="style1" style="color:Orange;"><asp:TextBox ID="txtph" runat="server" />

<asp:RequiredFieldValidator ID="reqfieldContact" runat="server" ControlToValidate="txtph" Display="Dynamic"

ErrorMessage="Please enter mobile number"></asp:RequiredFieldValidator>

<asp:RegularExpressionValidator ID="regexpContact" runat="server" ControlToValidate="txtph"

ErrorMessage="Please enter correct mobile number"

ValidationExpression="\d{10}"></asp:RegularExpressionValidator>

</td></tr>

<tr><td class="style3" style="color:Orange";>Email</td><td style="color:Orange;">

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

<asp:RequiredFieldValidator ID="reqfieldEmail" runat="server" ControlToValidate="txtEmail" Display="Dynamic"

ErrorMessage="Please enter Email Id"></asp:RequiredFieldValidator>

<asp:RegularExpressionValidator ID="regexpEmailId" runat="server" ControlToValidate="txtEmail"

ErrorMessage="Please enter Email Correctly"

ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"></asp:RegularExpressionValidator>

</td></tr>

<tr><td class="style3"style="color:Orange"> Date</td><td class="style1" style="color:Orange;"><asp:TextBox ID ="txtdat" runat="server" />

<asp:RegularExpressionValidator ID="RegularExpressdate" runat="server"

ControlToValidate="txtdat"

ValidationExpression="^(((0?[1-9]|[12]\d|3[01])[\.\-\/](0?[13578]|1[02])[\.\-\/]((1[6-9]|[2-9]\d)?\d{2}|\d))|((0?[1-9]|[12]\d|30)[\.\-\/](0?[13456789]|1[012])[\.\-\/]((1[6-9]|[2-9]\d)?\d{2}|\d))|((0?[1-9]|1\d|2[0-8])[\.\-\/]0?2[\.\-\/]((1[6-9]|[2-9]\d)?\d{2}|\d))|(29[\.\-\/]0?2[\.\-\/]((1[6-9]|[2-9]\d)?(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00)|00|[048])))$">enter correct date formate</asp:RegularExpressionValidator>

</td></tr>

<tr><td class="style3"style="color:Orange">Time</td><td class="style1" style="color:Orange;"><asp:TextBox ID ="txttime" runat="server" />

<asp:RegularExpressionValidator id="RegularExprestime" runat="server" ErrorMessage="* Please enter in correct format for FROM Time." Display="Dynamic" ControlToValidate="txttime" ValidationExpression="^((0?[1-9]|1[012])(:[0-5]\d){0,2}(\ [AP]M))$|^([01]\d|2[0-3])(:[0-5]\d){1,2}$"></asp:RegularExpressionValidator>

</td></tr>

<tr><td class="style3"style="color:Orange">Number of Passengers</td><td class="style1" style="color:Orange;"><asp:TextBox ID ="Txtpass" runat="server" />

<asp:RequiredFieldValidator ID="RequiredFielpassengers" runat="server" ControlToValidate="Txtpass" Display="Dynamic"

ErrorMessage="Please enter passengers "></asp:RequiredFieldValidator>

<asp:RegularExpressionValidator ID="RegularExpresspasseng" runat="server" ControlToValidate="Txtpass"

ErrorMessage="Please enter correct number"

ValidationExpression="\d{2}"></asp:RegularExpressionValidator>

</td></tr>

<tr><td class="style3"style="color:Orange">languages</td><td class="style1" style="color:Orange;"><asp:TextBox ID ="Txtlangue" runat="server" />

<asp:RequiredFieldValidator ID="RequiredFiellan" runat="server" ControlToValidate="Txtlangue" Display="Dynamic"

ErrorMessage="Please enter your language"></asp:RequiredFieldValidator>

<asp:RegularExpressionValidator ID="RegularExpreslang" runat="server" ControlToValidate="Txtlangue"

ValidationExpression="^[A-Za-z]+$" ErrorMessage="Please enter charectrers only"></asp:RegularExpressionValidator>

</td></tr>

<tr><td class="style3"style="color:Orange">Vehicle</td><td class="style1">

<asp:DropDownList ID="Dplist" runat="server"

Width="150px">

<asp:ListItem Text="--Select--" Value="-1"></asp:ListItem>

<asp:ListItem>INDIGO</asp:ListItem>

<asp:ListItem>SCARPIO</asp:ListItem>

<asp:ListItem>INDIGO MERINA</asp:ListItem>

<asp:ListItem>ENOVA</asp:ListItem>

</asp:DropDownList> <asp:RequiredFieldValidator ID="RequiredFielvehi" runat="server" ControlToValidate="Dplist" Display="Dynamic"

ErrorMessage="Please select vehicle" InitialValue="-1"></asp:RequiredFieldValidator> </td></tr>

<tr><td class="style3"style="color:Orange">pickup point</td><td class="style1" style="color:Orange;"><asp:TextBox ID ="Txtpic" runat="server" />

<asp:RequiredFieldValidator ID="RequiredFieldVapivpont" runat="server" ControlToValidate="Txtpic" Display="Dynamic"

ErrorMessage="Please enter pic up point"></asp:RequiredFieldValidator>

</td></tr>

<tr><td class="style3"style="color:Orange">pickup Landmark</td><td class="style1" style="color:Orange;"><asp:TextBox ID ="Txtland" runat="server" /><asp:RequiredFieldValidator ID="RequiredFieldValand" runat="server" ControlToValidate="Txtland" Display="Dynamic"

ErrorMessage="Please enter landmark"></asp:RequiredFieldValidator></td></tr>

<tr><td class="style3"style="color:Orange">Drop point</td><td class="style1" style="color:Orange;"><asp:TextBox ID ="Txtpoint" runat="server" /><asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="Txtpoint" Display="Dynamic"

ErrorMessage="Please entter drop point"></asp:RequiredFieldValidator></td></tr>

<tr>

<asp:Button ID="btnprnt" Text="Print" runat="server"

Width="86px" onclick="btnprnt_Click1" /></td></tr>

Step2: Create One button(btnprnt_Click)

print.aspx.cs

Button1:

protected void btnprnt_Click1(object sender, EventArgs e)

{

string htmlcode = "";

htmlcode += "<table width=97% class='mylabel' cellpadding=10 align=center>";

htmlcode += "<tr><td colspan=2><hr></tr>";

htmlcode += "<tr><td align=right>Name:</td><td> " + txtnam.Text + "</td></tr>";

htmlcode += "<tr><td align=right>Phone Number:</td><td>"+txtph.Text+"</td></tr>";

htmlcode += "<tr><td align=right>Date:</td><td>" + txtdat.Text + "</td></tr>";

htmlcode += "<tr><td align=right>Time:</td><td>" + txttime.Text + "</td></tr>";

htmlcode += "<tr><td align=right>No.of passengers:</td><td>" + Txtpass.Text + "</td></tr>";

htmlcode += "<tr><td align=right>language:</td><td>" + Txtlangue.Text + " </td></tr>";

htmlcode += "<tr><td align=right>:Vehicle:</td><td>" + Dplist.Text + "</td></tr>";

htmlcode += "<tr><td align=right>Picup point:</td><td>" + Txtpic.Text + "</td></tr>";

htmlcode += "<tr><td align=right>pickup landmark:</td><td>" + Txtland.Text + "</td></tr>";

htmlcode += "<tr><td align=right>Drop point:</td><td>" + Txtpoint.Text + "</td></tr>";

htmlcode += "</table>";

Session["htmlcodetoprint"] = htmlcode;

Response.Write("<script language='javascript'>window.open('print.aspx','prn','toolbar=no,menubar=no,scrollbars=yes,location=no');</script>");

}

Step3: create one page write in that"Print.aspx"

protected void Page_Load(object sender, EventArgs e)

{

if (Page.IsPostBack == false)

{

string htmlcode = Convert.ToString(Session["htmlcodetoprint"]);

Session["htmlcodetoprint"] = null;

Response.Write(htmlcode);

Response.Write("<script language='javascript'>window.print();</script>");

}

Thank you friend,

Ramarao,

Regards.

Purpose Of Trim Function in C#.Net

It enables you to remove the unwanted characters from both beginning and ending of the string. By Default C# string trimfunction trims the white spaces from both ends of the given string. Additional parameters of the String Trim function provide the functionality to remove more than one special character from both ends of the string. C# String Function cannot remove the special characters from within the string.

Eg:

strText = "\"ASP.Net \"C#\" Trim Function:\"";
Response.Write(strText + "<br />");
Response.Write(strText.Trim(new char[] { '"' }) + "<br />");
char[] specialChars = new char[] { '"', ':' };
Response.Write(strText.Trim(specialChars) + "<br />");

How we will generate Row Numbers from a table in sql server

I have table which contains the following information, I didn't have row numbers, just see the below image

If you want row numbers for that table just you need to add some logic to you query, that should be look like this
select Row_Number() over (order by PID)as RowNum,PID,ProjectName,Statuss,FromDate,ToDate from tbProjects

Then the output will look like below image

when you observer the above image you will find the row number.

It will be used, when you want to display the data in the front end, at the same time you will bind the row numbers fro front end.

See the above image, both column number and project name is displaying. This is the case when you want to display column numbers in the gridview with other stuff.

How we will Copy data from one table to another table

Syntax :: select * into <NewTableName> from <OldTablename>

Example:: Suppose tbEmployee is one table we want to copy the whole table to tbEmployeeInformation table, so do the below process
select * into tbEmployeeInformation from tbEmployee

If we want to copy only schema of the table but not data of the table mean do the below process
Syntax:: select * into <NewTableName> from <OldTableName> where 1=2

NOTE:: Only Schema and date will be copied, The constraints(Primary key,Foreign Key...) willn't be copied into new table.

Wednesday, May 22, 2013

How to insert image into Image data type in SQL Server

Create table with some columns like

create table tblimages (ImageId int,photo image,Model varchar(50))
Then insert data into table like below

insert into tblimages values(1,(select BulkColumn from openrowset(Bulk 'ImagePath',single_blob) as img),'ModelName')

Here in ImagePath place you have to specify the image path like 'D:\img\image.jpg'
Now the image will convert to Byte form and store in Photo column in table tblimages

Tuesday, May 21, 2013

How to handle when database is returning null value

When we are trying to retrieve some thing from database there is a chance of returning null value so at that time we have to check whether it is returning null value or not like...i.e...

if (dt.Rows[0][0] == DBNull.Value) //we have to check like this weather returning any null value
{
homeobj.status = 0;
}
else //if not returning any null value
{
homeobj.status = Convert.ToInt32(dt.Rows[0][0]);
}

How to Change Column Name in Sql Server

In Query Analyzer write the below Code

sp_rename '<TableName>.<OldColumnName>','<NewColumnName>','<Column>'

Example:::--> our column name is 'ProductId' in tbProducts table name, if we want to change this column to 'ProductName' then do the below process.

sp_rename 'tbProducts.ProductId','ProductName','column'

How To Change Table Name in Sql Server

In Query Analyzer write the below Code

sp_rename '<OldTableName>','<NewTableName>'

Example: Our table name is tbProducts, if we want to change our table name tbProducts to tbProductDetails,
then we have to write like this

sp_rename 'tbProducts','tbProductDetails'

How To delete NULL value rows in SQL server

Instead of writing '=' operator in where condition write 'is'.
Example shown in below,

delete tblMobiles where ImageId is NULL

Here tblMobile is table name in Database and ImageId is column name in that table

Saturday, May 18, 2013

How to append date to dropdownlist by using html?