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

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

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

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

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;
}
}

}

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

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.

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

SQL query to add Zero prefix

Let say,
I have a Database column 'ISSN' and data in it, should be 8 digits long. When I import data from excel to SQL table, it truncates the initial 1-3 zeros because it doesn't like zero as first digits when you mark the column as int but if you mark as text that's fine.
Now, let's write SQL query so that if the ISSN is 5 digits long then add 3 zeros to prefix.
e.g If the ISSN is 45678 then it should be '00045678'
If it is 6 digits long, then add two zeros to prefix....If 8 digits long keep as it is.

Hence, the SQL query is:

update table
set ISSN = RIGHT('00000000'+ISSN,8)

Happy Programming !!!

Special characters globally changes in SQL server

I wrote a SQL query in this blog to replace special characters globally. Now , I am going to modify this query with another ASCII UTF-8 table concept.

Look ASCII UTF-8 table and find the character whose description is 'Acute accent, spacing acute', it's not apostrophe.

If you have this character in your database table data, it doesn't allow you to update your table. It can be replaced manually but if you have this character in many places you should have query to replace those bad characters.

Let's look that table, you can see the Raw Encoding for that bad character is:

0xB4

which is in hexdecimal notation.

let's convert to base 10 value:

B means 11=>16*11=176

B4=>176+4=180

so,

180 = ' (x b4)

147, 148 = " (x 93, 94)

Hence the queries:

//replace by apostrophe
UPDATE table
SET ColumnName= replace(ColumnName, CHAR(180), '''')

UPDATE table
SET ColumnName= replace(ColumnName, CHAR(148), '"');

UPDATE table
SET ColumnName= replace(ColumnName, CHAR(147), '"');

Happy Coding !!!

Calling Stored Procedure from C# & Display the records

Let's create a sample stored procedure first,

Create procedure GetSchoolName (@ID int)

as

select SchoolName, Date from School where ID like @ID;

return;

The code includes @ID parameter which is an input parameter that obtains the search string to perform a "like" search in school table.

In our c# code, we are going to pass variable ID (at run time) to this procedure and getting back the records that matches with that ID

Hence, the c# code:

try

{

SqlDataReader rdr = null;

//Create a connection to the SQL Server

SqlConnection conn = new SqlConnection(DataAccess.GetConnectionString());

//Create a command object & then set to the connection

SqlCommand cmd = new SqlCommand("dbo. GetSchoolName", conn);

//Set the command type as storedProcedure

cmd.CommandType = CommandType.StoredProcedure;

//get the variable ID from textbox control

int id = Convert.ToInt32(id.Text);

//Create & add a parameter to parameters collection for stored procedure

cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id;

//Open the connection

conn.Open();

//execute command & read the data using SqlDataReader

rdr = cmd.ExecuteReader();

//display records into listbox

while (rdr.Read())

{

listBox1.Items.Add(rdr.GetValue(0).ToString());

}

Or

//Records display in a table

TableRow tr;

TableCell tc;

while (rdr.Read())

{

tr = new TableRow();

tc = new TableCell();

tc.Text = rdr["SchoolName"].ToString() + " " + rdr["Date"].ToString();

tr.Cells.Add(tc);

table1.Rows.Add(tr);

}

conn.Close(); //close connection

rdr.Close(); // close SqlDataReader

}

Happy Coding !!!

Sqlparameter is already contained by another Sqlparameter Collection

"Sqlparameter is already contained by another Sqlparameter Collection" --This

is the problem I faced last week while I created two SqlCommand. First one, to

find the variable Id 'a' and second one pass that 'a' to the stored procedure.

The solution, I added the SqlCommand.Parameters.Clear() to the code and the problem gone.

As you can in the code, I created SqlCommand Command and should be destroyed at the end because

C# is the managed language that handles garbage collections.

Code example:

SqlConnection conn =

new SqlConnection(DataAccess.GetConnectionString());

string strSQLCommand =

"select Id from Schools WHERE SchoolName= '" + name + "'";

SqlCommand command =

new SqlCommand(strSQLCommand, conn);

int a = Convert.ToInt32(command.ExecuteScalar());

//write this line of code before using another command execute function

command.Parameters.Clear();

...................................................

...................................................

SqlDataReader rdr = null;

SqlCommand cmd = new SqlCommand("dbo.GetSchoolName", conn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@ID", SqlDbType.Int).Value = a;

rdr = cmd.ExecuteReader();

...................................................

...................................................

Happy Coding !!!

Return value from stored procedure to c#

Suppose you have a stored procedure which returns a bit value 0 or 1. If you want to call that procedure from c# and get that bit value, do like this below:

let say, the stored procedure has:

Name: dbo.sp_dateCheck

Variables:

@laptop varchar(50),

@startdate DateTime,

@enddate DateTime

and return bit variable: @CondFlag

SqlCommand com = new SqlCommand("dbo.sp_dateCheck", conn);

com.CommandType = CommandType.StoredProcedure;

try

{

conn.Open();

com.Parameters.Add(new SqlParameter("@laptop", Laptop.Text));

com.Parameters.Add(new SqlParameter("@startdate", startDate. DateTime));

com.Parameters.Add(new SqlParameter("@enddate", endDate. DateTime));

//read the returned value from stored procedure

com.Parameters.Add("@CondFlag", SqlDbType.Bit);

com.Parameters["@CondFlag"].Direction = ParameterDirection.ReturnValue;

com.ExecuteNonQuery();

int ReturnedVal = (int)com.Parameters["@CondFlag"].Value;

if (ReturnedVal == 0)

{ // do this

}

else

{//do this

}

}

catch

{

}

Happy Programming!!!

Running a SQL query to replace certain characters in a table

Let say,

You have a database that contains many characters and symbols. If you want to replace those , just run the following SQL query:

(Here, suppose a symbol ~! need to replace by - then your query will be:)

UPDATE table

SET ColumnName= replace(LTRIM(RTRIM(ColumnName)), '~!', '-')

Happy Programming!:)

SQL Trigger-To check the data at Notes Field

I am going to write a Trigger to validate the column field in the table name 'LenderAddressAll' and the column name is 'Notes'.My trigger checks the data entered into that column and if it valid that's ok otherwise it'll pop up 'error'.The data format should be 000 xxxxxx.First three numbers should be 0's and then empty space and after that it should be any six digits(Total varchar(10)).

Create trigger tri

on

LenderAddressesALL

after

insert

AS
select lender.Notes from LenderAddressesALL lender join inserted i on i.Notes = lender.Notes

where i.Notes is NOT null
declare @temp varchar(10)

select @temp = Notes from inserted

if(len(@temp) = 10 and substring(@temp,1, 4) = '000 '

and cast(substring(@temp, 5, 10) as int) > 0

and cast(substring(@temp, 5, 10) as int) <>

return

else

begin print 'error!!!!'

RAISERROR ('Error!!', 16, 1)

rollback transaction

end
GO

Happy Programming!!