Create Sql server Function and call it as requred (2 input parameters)

CREATE FUNCTION [dbo].[ufIsUserHavingDependendency]
(
@UserId int,
@RoleId int
)
RETURNS int
AS

BEGIN

DECLARE @IsAssigned BIT
DECLARE @Count INT
DECLARE @mRoleId INT
SET @mRoleId=@RoleId
SET @Count = 0

--if role is Supervisor or admin

IF @mRoleId='2' OR @mRoleId='1'
BEGIN

--1.Has created or assigned Courses. (Supervisor)
SELECT @Count = COUNT(tblUserCreatedCourses.UserId) FROM tblUserCreatedCourses
WHERE tblUserCreatedCourses.UserId = @UserId AND @mRoleId<>1
IF(@Count>0)
return 1

--2. Has directly assigned Courses. (Student)
SELECT @Count = COUNT(tblCourse.CourseId) FROM tblCourse
WHERE tblCourse.SupervisorUserId = @UserId
IF(@Count>0)
return 1

-- SELECT @Count = COUNT(tblStudentCourse.StudentId) FROM tblStudentCourse
-- WHERE tblStudentCourse.StudentId=@UserId
-- IF(@Count>0)
-- return 1

--06. Has created ad hoc Question/s? (Supervisor)
SELECT @Count = COUNT(tblCourse.CourseId) FROM tblCourse
INNER JOIN tblQuestion
ON tblQuestion.CourseId= tblCourse.CourseId
WHERE tblCourse.SupervisorUserId = @UserId AND tblQuestion.CourseId <>'0'
IF(@Count>0)
return 1

--07. Has created Course based Question/s? (Supervisor)
SELECT @Count = COUNT(tblQuestion.QuestionId) FROM tblQuestion
WHERE tblQuestion.OwnerId = @UserId AND tblQuestion.CourseId ='0'
IF(@Count>0)
return 1

--09. Has non confirmed Course based Exam/s. (Supervisor)
SELECT @Count = COUNT(tblQuestionPaper.PaperId) FROM tblQuestionPaper
WHERE tblQuestionPaper.OwnerId = @UserId AND tblQuestionPaper.CourseId ='0'
IF(@Count>0)
return 1

--10
SELECT @Count = COUNT(tblCourse.CourseId) FROM tblCourse
INNER JOIN tblQuestionPaper
ON tblCourse.CourseId= tblQuestionPaper.CourseId
WHERE tblCourse.SupervisorUserId = @UserId AND tblQuestionPaper.CourseId <>'0'
IF(@Count>0)
return 1

--17 To 21
SELECT @Count = COUNT(tblUserExam.UserId) FROM dbo.tblUserExam
INNER JOIN dbo.tblQuestionPaper
ON tblQuestionPaper.PaperId=tblUserExam.PaperId
INNER JOIN dbo.tblCourse
ON tblCourse.CourseId=tblQuestionPaper.CourseId
WHERE tblCourse.SupervisorUserId =@UserId AND tblQuestionPaper.CourseId<>'0'
IF(@Count>0)
return 1

--Add hoc
SELECT @Count = COUNT(tblUserExam.UserId) FROM dbo.tblUserExam
INNER JOIN dbo.tblQuestionPaper
ON tblQuestionPaper.PaperId=tblUserExam.PaperId
WHERE dbo.tblQuestionPaper.OwnerId=@UserId AND tblQuestionPaper.CourseId='0'

IF(@Count>0)
return 1

--22
SELECT @Count = COUNT(dbo.tblVideoUser.UserId) FROM dbo.tblVideoUser
WHERE dbo.tblVideoUser.UserId=@UserId
IF(@Count>0)
return 1

END

ELSE IF @mRoleId='3'
BEGIN
--05
SELECT @Count = COUNT(dbo.tblCurriculumStudent.StudentId) FROM dbo.tblCurriculumStudent
WHERE tblCurriculumStudent.StudentId=@UserId
IF(@Count>0)
return 1

--23
SELECT @Count = COUNT(tblNotifications.NotificationID) FROM tblNotifications
INNER JOIN dbo.tblUserNotifications
ON tblNotifications.NotificationID=tblUserNotifications.NotificationID
WHERE dbo.tblUserNotifications.UserID=@UserId AND dbo.tblNotifications.NotificationType='1'--for alert
IF(@Count>0)
return 1

--26
SELECT @Count = COUNT(tblCourseRequest.StudentId) FROM tblCourseRequest
WHERE tblCourseRequest.StudentID=@UserId AND tblCourseRequest.ApprovedStatus='Pending'
IF(@Count>0)
return 1

--27
SELECT @Count = COUNT(tblExamRequest.StudentID) FROM tblExamRequest
WHERE tblExamRequest.StudentID=@UserId
IF(@Count>0)
return 1

--12
SELECT @Count = COUNT(tblUserExam.UserId) FROM tblUserExam
WHERE tblUserExam.UserId=@UserId
IF(@Count>0)
return 1

--13 check student is involved to a course
SELECT @Count = COUNT(tblStudentCourse.StudentId) FROM tblStudentCourse
WHERE tblStudentCourse.StudentId=@UserId
IF(@Count>0)
return 1
END

ELSE

BEGIN
return 0
END

return -3
END
//


//Calling the function sql stored procedure

SELECT tblUsers.Title,
tblUsers.UserID,
tblUsers.UserName,
tblUsers.FirstName,
tblUsers.LastName,
tblUnit.UnitName,
tblRole.RoleId,
tblRole.Description As RoleName,
tblGroups.GroupName,
tblUsers.LastLogDate,
tblUsers.IsActive,
tblUsers.IsSelfRegister,
dbo.ufIsUserHavingDependendency(tblUsers.UserID,tblUsers.RoleID) AS IsUserHavingDependendency,

Data row bound event hadling for hyperlink url whit query string

Data row bound event handling for hyper link url whit query string

protected void gvCourseList_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
HyperLink lnkBtn = (HyperLink)e.Row.Controls[2].Controls[0];
if(lnkBtn != null)
{
lnkBtn.NavigateUrl = "AssignMedia.aspx?CourseId=" + gvCourseList.DataKeys[e.Row.RowIndex].Values["CourseId"].ToString();
}

}
}

Image Curser to hand html and javascript

//Image Curser to hand
style ="cursor:pointer;"

Handaling data row bound event as required C# Asp.net

Handaling data row bound event

protected void gvAvailableVideos_RowDataBound(object sender, GridViewRowEventArgs e)
{
try
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
LinkButton lbtnVideoFile = ((LinkButton)e.Row.Cells[0].FindControl("lbtnVideoFile"));
if (lbtnVideoFile != null)
{
lbtnVideoFile.Text = gvAvailableVideos.DataKeys[rowno]["VideoFileName"].ToString();
DataView dv = DsMediaFiles.Tables[0].DefaultView;
dv.Sort = "id";
int rowId = dv.Find(gvAvailableVideos.DataKeys[rowno]["Id"]);
string VideoPath = string.Empty;
int VideoId;
if (rowId != -1)
{
VideoPath = System.Configuration.ConfigurationManager.AppSettings["StreamingServerPath"].ToString() + dv[rowId]["VideoPath"].ToString();
VideoId = Convert.ToInt32(dv[rowId]["Id"].ToString());
lbtnVideoFile.Attributes.Add("OnClick", "return SetMovie('" + VideoPath + "','" + VideoId + "')");
}

}

HyperLink hlnkAttachment = ((HyperLink)e.Row.Cells[2].FindControl("hlnkAttachment"));
if (hlnkAttachment != null)
{
hlnkAttachment.Text = gvAvailableVideos.DataKeys[rowno]["AttachmentName"].ToString();
hlnkAttachment.NavigateUrl = gvAvailableVideos.DataKeys[rowno]["AttachmentPath"].ToString();
hlnkAttachment.Target = "_blank";
}
rowno += 1;

if (e.Row.Cells[1].Text.Length > Convert.ToInt32(Constants.GridCharactorLength))
{
e.Row.Cells[1].ToolTip = e.Row.Cells[1].Text;
e.Row.Cells[1].Text = e.Row.Cells[1].Text.Substring(0, Convert.ToInt32(Constants.GridCharactorLength)) + "....";
//e.Row.Cells[1].Text = e.Row.Cells[1].Text.ToUpper();

}

}
}

Changing a dataset and datagrid values as you required

////////this is CreateVideoDataSet()
private DataSet CreateVideoDataSet()
{
try
{ //id,videfilename,videopath,Attachment path,attachmentName,course title
DataSet dsNewVideo = new DataSet();
DataTable dt = new DataTable();
DataColumn dc;


dc = new DataColumn();
dc.DataType = System.Type.GetType("System.Int64");
dc.ColumnName = "Id";
dc.Unique = true;
dt.Columns.Add(dc);

dc = new DataColumn();
dc.DataType = System.Type.GetType("System.String");
dc.ColumnName = "VideoFileName";
dc.Unique = false;
dt.Columns.Add(dc);

dc = new DataColumn();
dc.DataType = System.Type.GetType("System.String");
dc.ColumnName = "VideoPath";
dc.Unique = false;
dt.Columns.Add(dc);

dc = new DataColumn();
dc.DataType = System.Type.GetType("System.String");
dc.ColumnName = "AttachmentPath";
dc.Unique = false;
dt.Columns.Add(dc);

dc = new DataColumn();
dc.DataType = System.Type.GetType("System.String");
dc.ColumnName = "AttachmentName";
dc.Unique = false;
dt.Columns.Add(dc);

dc = new DataColumn();
dc.DataType = System.Type.GetType("System.String");
dc.ColumnName = "CourseTitle";
dc.Unique = false;
dt.Columns.Add(dc);

dc = new DataColumn();
dc.DataType = System.Type.GetType("System.Boolean");
dc.ColumnName = "IsAvailableToAll";
dc.Unique = false;
dt.Columns.Add(dc);

dsNewVideo.Tables.Add(dt);
return dsNewVideo;
}

//in the page load
public void FillDataToGrid()
{
try
{
IVideo objIVideo = new IVideo();
DsMediaFiles = objIVideo.GetAvailableVideos();
DataSet dsNewVideo = CreateVideoDataSet();

foreach (DataRow dr in DsMediaFiles.Tables[0].Rows)
{
string LotNos = string.Empty;
DataRow[] found = DsMediaFiles.Tables[0].Select("Id=" + Convert.ToInt32(dr["Id"]));
if (found.Length > 0)
{
for (int i = 0; i < found.Length; i++)
{

LotNos += found[i]["CourseTitle"].ToString() + ",";
}

DataRow[] drows = dsNewVideo.Tables[0].Select("id=" + Convert.ToInt32(dr["id"]));
if (drows.Length == 0)
{
DataRow row1 = dsNewVideo.Tables[0].NewRow();
row1["Id"] = dr["Id"];
row1["VideoFileName"] = dr["VideoFileName"];
row1["VideoPath"] = dr["VideoPath"];
row1["AttachmentPath"] = dr["AttachmentPath"];
row1["AttachmentName"] = dr["AttachmentName"];
row1["IsAvailableToAll"] = dr["IsAvailableToAll"];
row1["CourseTitle"] = LotNos.TrimEnd(',');

if (row1["IsAvailableToAll"].ToString() == "True")
{
row1["CourseTitle"] = "";
}

dsNewVideo.Tables[0].Rows.Add(row1);
}



}
}

if (dsNewVideo != null && dsNewVideo.Tables[0].Rows.Count > 0)
{
gvAvailableVideos.DataSource = dsNewVideo.Tables[0];
gvAvailableVideos.DataBind();
Session["DsMediaFiles"] = dsNewVideo;
}

else
{
gvAvailableVideos.DataSource = null;
gvAvailableVideos.DataBind();
TrNoRecords.Visible = true;
gvAvailableVideos.Visible = false;

}
}

format sql date and time only date like format

CAST( (convert(varchar(12), @expDate,102)) as varchar)

Configure sql mail - Sql server

Configure sql mail Basic steps
//Run followings in sql query window

use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
--go
--sp_configure 'SQL Mail XPs',0
go
reconfigure
go





EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'MyMailAccount',
@description = 'Mail account for Database Mail',
@email_address = 'Sanjeewa@mail.com',
@display_name = 'MyAccount',
@username='Sanjeewa@alliontechnologies.com',
@password='tExt123',
@mailserver_name = 'mail.yourmail.com'


EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'MyMailProfile',
@description = 'Profile used for database mail'

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'MyMailProfile',
@account_name = 'MyMailAccount',
@sequence_number = 1

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'MyMailProfile',
@principal_name = 'public',
@is_default = 1 ;

//Send a test mail
declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' My First Database Email xxxyyii'
EXEC msdb.dbo.sp_send_dbmail @recipients='Sanjeewa@Mail.com',
@subject = 'My Mail Test',
@body = @body1,
@body_format = 'HTML' ;

cast dateTime in the data base to Same Format And Compare Two days acordingly

SELECT * FROM dbo.tblCourse where convert(varchar(12), [ExpireDate],102)=convert(varchar(12), getdate(),102)


//Example 2

SELECT UserId,Email,firstName,tblCourse.CourseTitle FROM tblUsers INNER JOIN tblCourse
ON tblUsers.UserId = tblCourse.CreatedBy
WHERE UserId IN (SELECT DISTINCT CreatedBy
FROM dbo.tblCourse)
AND convert(varchar(12), [ExpireDate],102)=convert(varchar(12), getdate(),102)

Restricting max lenth of message box - java script

function MaxLengthRestrictTextBox(s,e)
{

if(e.Value.length > 6000 )
{
e.IsValid = false;
return false;
}

e.IsValid = true;
}

changing image button delete to disable in row bound event C#

protected void gvCourses_RowDataBound(object sender, GridViewRowEventArgs e)
{
try
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
ImageButton btnEdit = (ImageButton)e.Row.FindControl("btnEdit");
if (btnEdit != null)
{

btnEdit.PostBackUrl = "EditCourse.aspx?Tab=1&CourseId=" + gvCourses.DataKeys[e.Row.RowIndex].Values["CourseId"].ToString();
}

ImageButton btnDelete = (ImageButton)e.Row.FindControl("btnDelete");
if (btnDelete != null)
{
string IsAssinged = gvCourses.DataKeys[e.Row.RowIndex].Values["IsCourseAssignedtoaUser"].ToString();
string DoesCourseHasRequest = gvCourses.DataKeys[e.Row.RowIndex].Values["CourseHasRequests"].ToString();
if (IsAssinged == "1")
{
btnDelete.Enabled = false;
btnDelete.ToolTip = Constants.MSG_Course_Assignedorexamcreated;
btnDelete.ImageUrl = "~/Images/btnRomoveDisable.gif";
}
else
{
btnDelete.Enabled = true;
btnDelete.Attributes.Add("onclick", "return ConfirmCourseDelete('" + DoesCourseHasRequest + "');");

}
}
}
}

catch (Exception ex)
{

}
}

Create a Html table dynamically by c# Code

private void LoadForumItems(int mainForumId)
{
ForumItem objForumItems = new ForumItem();
DataSet dsForumItems = objForumItems.GetForumItemsByForumId(mainForumId);

int MainForumId = 0;
int x = 1;
//int j = 1;
HtmlTable htmlMainTable = new HtmlTable();
TrForumItems.Cells[0].Controls.Add(htmlMainTable);
htmlMainTable.Attributes.Add("width", "100%");

//if check the ds empty here
foreach (DataRow dr in dsForumItems.Tables[0].Rows)
{
if (MainForumId != Convert.ToInt32(Request.QueryString["MainForumId"]))
{
HtmlTableRow htmlForumItemRow = new HtmlTableRow();

//Empty Left Cell
HtmlTableCell htmlForumItemEmptyCellLeft = new HtmlTableCell();
htmlForumItemEmptyCellLeft.InnerHtml = " ";
htmlForumItemEmptyCellLeft.Attributes.Add("width", "10");
htmlForumItemEmptyCellLeft.Attributes.Add("class", "tableItems");
htmlForumItemRow.Cells.Add(htmlForumItemEmptyCellLeft);

//Description Cell
HtmlTableCell htmlForumItemCellDescription = new HtmlTableCell();
htmlForumItemCellDescription.InnerHtml = dr["Description"].ToString();
htmlForumItemCellDescription.Attributes.Add("width", "400");
htmlForumItemCellDescription.Attributes.Add("class", "tableItems");
htmlForumItemRow.Cells.Add(htmlForumItemCellDescription);

//Cell reply button
HtmlTableCell htmlForumItemCellButton = new HtmlTableCell();
HyperLink link = new HyperLink();
link.CssClass = "";
link.ToolTip = "Reply";
link.NavigateUrl = "ReplyForum.aspx?ParentId=" + dr["ForumItemId"].ToString() + "&MainForumId=" + dr["MainForumId"].ToString();
link.ImageUrl = "~/Images/btnPostReply.gif";
htmlForumItemCellButton.Attributes.Add("class", "tableItems");

//Create empty cell
HtmlTableCell htmlForumItemEmptyCell = new HtmlTableCell();
htmlForumItemEmptyCell.InnerHtml = "";
htmlForumItemEmptyCell.Attributes.Add("width", "100");
htmlForumItemEmptyCell.Attributes.Add("valign", "top");
htmlForumItemEmptyCell.Controls.Add(link);
htmlForumItemEmptyCell.Attributes.Add("class", "tableItems");
htmlForumItemRow.Cells.Add(htmlForumItemEmptyCell);

//Owner Name cell
HtmlTableCell htmlForumItemCellOwnerName = new HtmlTableCell();
htmlForumItemCellOwnerName.InnerHtml = dr["OwnerName"].ToString();
htmlForumItemCellOwnerName.Attributes.Add("class", "tableItems");
htmlForumItemRow.Cells.Add(htmlForumItemCellOwnerName);

//Cell date time
HtmlTableCell htmlForumItemCellDate = new HtmlTableCell();
htmlForumItemCellDate.InnerHtml = dr["CreateDate"].ToString();
htmlForumItemCellDate.Attributes.Add("class", "tableItems");
htmlForumItemRow.Cells.Add(htmlForumItemCellDate);

//Add rows to html table
htmlMainTable.Rows.Add(htmlForumItemRow);

if (x > 1)
{
//Create empty row
HtmlTableRow htmlEmptyRow = new HtmlTableRow();
HtmlTableCell htmlEmptyCell = new HtmlTableCell();
htmlMainTable.Rows.Add(htmlEmptyRow);

HtmlTableRow htmlEmptyRow2 = new HtmlTableRow();
HtmlTableCell htmlEmptyCell2 = new HtmlTableCell();
htmlMainTable.Rows.Add(htmlEmptyRow2);
}
x += 1;
}
}
}