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

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

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>