Wednesday, 27 April 2016

How to randomly data fetch from a table in sql?

select * from tableName order by NEWID()

How to List columns in alphabetical order?

SELECT name FROM sys.columns WHERE [object_id] = OBJECT_ID('tablename') ORDER BY name;

Wednesday, 30 March 2016

How to drag and drop(dynamic control) bind and save the data in asp.net?

<div dir="ltr" style="text-align: left;" trbidi="on">
<html lang="en">
<head>

<title>Add Dynamic Label</title>
<link href="Content/bootstrap.min.css" rel="stylesheet"></link>
<link href="Content/StyleSheet.css" rel="stylesheet"></link>
<link href="Content/Print.css" media="print" rel="stylesheet"></link>
<script src="Scripts/jquery-1.11.1.min.js"></script>
<script src="Scripts/bootstrap.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/floatthead/1.3.2/jquery.floatThead.js"></script>
<script src="/Scripts/jquery.visible.min.js"></script>
<style>
#div1 {
width: 500px;
height: 300px;
padding: 10px;
border: 1px solid #aaaaaa;
}
</style>

<script type="text/javascript">
function insertWebForm() {
debugger;
if (document.getElementById('<%=txtPage.ClientID%>').value == '') {
document.getElementById('<%=txtPage.ClientID%>').focus();
return false;
}
var pageName = document.getElementById('<%=txtPage.ClientID%>').value;
var isCheck = document.getElementById('<%=chkstatus.ClientID%>').checked;
document.getElementById('<%=hdlField.ClientID%>').value = "";
var label = document.getElementById('<%=hdlField.ClientID%>').value;

var fname = document.getElementById("FName");
var title = document.getElementById("Title");
var lname = document.getElementById("LName");
var company = document.getElementById("Company");
var phone = document.getElementById("Phone");

if (fname) {
document.getElementById('<%=hdlField.ClientID%>').value = document.getElementById('<%=hdlField.ClientID%>').value + "/" + fname.id;
}
if (title) {
document.getElementById('<%=hdlField.ClientID%>').value = document.getElementById('<%=hdlField.ClientID%>').value + "/" + title.id;
}
if (lname) {
document.getElementById('<%=hdlField.ClientID%>').value = document.getElementById('<%=hdlField.ClientID%>').value + "/" + lname.id;
}

if (company) {
document.getElementById('<%=hdlField.ClientID%>').value = document.getElementById('<%=hdlField.ClientID%>').value + "/" + company.id;
}
if (phone) {
document.getElementById('<%=hdlField.ClientID%>').value = document.getElementById('<%=hdlField.ClientID%>').value + "/" + phone.id;
}

if (mobile) {
document.getElementById('<%=hdlField.ClientID%>').value = document.getElementById('<%=hdlField.ClientID%>').value + "/" + mobile.id;
}

$.ajax({
type: "POST",
url: "DragAndDrop.aspx/insertPageNamewithLabel",
data: '{pageName: "' + pageName + '", isCheck: "' + isCheck + '", label: "' + document.getElementById('<%=hdlField.ClientID%>').value + '"}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: OnSuccess,
failure: function (response) {
alert(response.d);
}
});

}

function OnSuccess(response) {
debugger;
if (response.d == "true") {
document.getElementById('<%=hdlField.ClientID%>').value = "";
var abc = document.getElementById('<%=hdlField.ClientID%>').value;
window.location = '/DynamicLabelList.aspx';
}
else {
document.getElementById('<%=hdlField.ClientID%>').value = "";
var abc = document.getElementById('<%=hdlField.ClientID%>').value;
alert("Page Name already inserted");
}
}

function RemoveLabelControl(div) {
document.getElementById("labelContainer").removeChild(div.parentNode);
}
</script>

<script type="text/javascript">
function drag(ev) {
document.getElementById('<%=hdnLabel.ClientID%>').value = ev.target.innerHTML;
}

function allowDrop(ev) {
debugger;
var counter = 0;
var label = document.getElementById('<%=hdnLabel.ClientID%>').value;
if (label) {
var div = document.createElement('DIV');

var fname = document.getElementById(label);
if (fname) {
}
else {
debugger;
div.innerHTML = '<label class="col-xs-4" style="margin-left: 17px;">' + label + '</label> ' +
'<input name = "DynamicTextBox" type="text" id = "' + label + '" /> ' +
'<input id="Button' + counter + '" type="button" ' +
'value="Remove" onclick = "RemoveLabelControl(this)" />' +
'<br/><br/>';
}
document.getElementById("div1").appendChild(div);
counter++;
}
}

function RemoveLabelControl(div) {
document.getElementById("div1").removeChild(div.parentNode);
}
</script>

</head>
<body>

<form id="form1" runat="server">
<div class="container">
<div class="col-md-4">
<br />
<br />
<asp:label id="lblMsg" runat="server" style="color: green; margin-left: 500px;">
<asp:hiddenfield id="hdnLabel" runat="server">
<asp:hiddenfield id="hdlField" runat="server">
<asp:hiddenfield id="hdnTextValue" runat="server">
<br />
<span style="font-family: Arial;">Click to Lead Field</span>  
<br />
<label draggable="true" for="FName" ondragstart="drag(event)">FName</label>
<br />
<label draggable="true" for="LName" ondragstart="drag(event)">LName</label>
<br />
<label draggable="true" for="Title" ondragstart="drag(event)">Title</label>
<br />
<label draggable="true" for="Company" ondragstart="drag(event)">Company</label>
<br />
<label draggable="true" for="Phone" ondragstart="drag(event)">Phone</label>

<br />

<br />
<br />
</asp:hiddenfield></asp:hiddenfield></asp:hiddenfield></asp:label></div>
<div class="col-md-8">
<h1>
Web Form</h1>
<br />
<div class="col-xs-12">
<label class="col-sm-4 control-label" for="Scheduler Name">*Page Name</label>
<div class="col-sm-8">
<asp:textbox id="txtPage" runat="server"></asp:textbox>
</div>
</div>
<br />
<br />
<div class="col-xs-12">
<label class="col-sm-4 control-label" for="Custom View" style="padding-bottom: 28PX;">Status</label>
<div class="col-sm-8">
<asp:checkbox id="chkstatus" runat="server">
</asp:checkbox></div>
</div>
<div class="col-xs-6">
</div>
<br />
<br />
<br />
<br />

Drag the DynamicLabels into the Rectangle:<br />

<div id="div1" ondragover="allowDrop(event)" style="overflow: auto;">
</div>
<br />

<br />

</div>
<asp:button class="btn btn-default" id="btnSave" onclientclick="insertWebForm()" runat="server" style="margin-left: 210px;" text="Save">
 
<asp:button class="btn btn-default" id="btnBack" onclick="btnBack_Click" runat="server" text="Back">
</asp:button></asp:button></div>
</form>
</body>
</html></div>

In Code Behind-:

public static string insertPageNamewithLabel(string pageName, string isCheck, string label)
{
string result = "true";

DataTable dt = new DataTable();
SqlDataAdapter da;
SqlConnection con = new SqlConnection();
con.ConnectionString = @"Data Source="";Initial Catalog="";Persist Security Info=True;User ID="";Password=""";

SqlCommand cmd = new SqlCommand("select * from page where Name='" + pageName + "'", con);
da = new SqlDataAdapter(cmd);
da.Fill(dt);
con.Open();
cmd.ExecuteNonQuery();
if (dt.Rows.Count > 0)
{
result = "false";
//lblMsg.Text = "Page Name already inserted";
//lblMsg.ForeColor = System.Drawing.Color.Green;
}
else
{
SqlConnection coninsert = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["con"].ConnectionString);
coninsert.Open();
string strquery = "INSERT INTO [dbo].[Page]([Name],[Status]) VALUES (@name,@status);SELECT CAST(scope_identity() AS int)";
SqlCommand cmdinsert = new SqlCommand(strquery, coninsert);
cmdinsert.Parameters.AddWithValue("@name", pageName);
if (isCheck == "false")
{
cmdinsert.Parameters.AddWithValue("@status", false);
}
else
{
cmdinsert.Parameters.AddWithValue("@status", true);
}
int pageId = (int)cmdinsert.ExecuteScalar();
if (pageId > 0)
{
if (label.Length > 0)
{
string[] splitId = label.Split(new string[] { "/" }, StringSplitOptions.None);
if (splitId.Count() > 0)
{
for (int i = 0; i < splitId.Count(); i++)
{
if (splitId[i] != "")
{

SqlConnection mycon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["con"].ConnectionString);
mycon.Open();
string strquery1 = "insert into PageField (PageID, ControlToDisplay, FieldLabel, RequiredValidation, OrderBy, OptionalValue)values(@pageid,@Control,@fieldlabel,@required,@orderby,@optionalvalue);SELECT CAST(scope_identity() AS int)";

SqlCommand cmdPageLabel = new SqlCommand(strquery1, mycon);

cmdPageLabel.Parameters.AddWithValue("@pageid", pageId);
cmdPageLabel.Parameters.AddWithValue("@Control", "Single Line Text");
cmdPageLabel.Parameters.AddWithValue("@fieldlabel", splitId[i].ToString());
cmdPageLabel.Parameters.AddWithValue("@required", false);
cmdPageLabel.Parameters.AddWithValue("@orderby", 1);
cmdPageLabel.Parameters.AddWithValue("@optionalvalue", "");

int pagefieldId = (int)cmdPageLabel.ExecuteScalar();
if (pagefieldId > 0)
{

}
}
}
}
}
result = "true";
}
}

return result;
}

Add Dynamic Label

Click to Lead Field
FName
LName
Title
Company
Phone

Web Form

*Page Name

Status

Drag the DynamicLabels into the Rectangle:

Monday, 22 February 2016

How to search data character by character in SQL server?

Table - t_Event

CREATE TABLE [dbo].[t_Event](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[OrganizationName] [nvarchar](250) NULL,
[OrganizationID] [int] NULL,
[Title] [nvarchar](500) NULL,
[DescriptionShort] [nvarchar](500) NULL,
[DescriptionLong] [nvarchar](4000) NULL,
[Location] [nvarchar](500) NULL,
[EventDate] [smalldatetime] NULL,
[ExpirationDate] [smalldatetime] NULL,
[URL] [nvarchar](250) NULL,
[FileName] [nvarchar](250) NULL,
[Active] [bit] NULL,
[EventTime] [nvarchar](250) NULL,
CONSTRAINT [PK_t_Event] PRIMARY KEY CLUSTERED
(
[EventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER PROC [dbo].[Sp_Fetch_AllEventByString](@SearchName VARCHAR(200))
AS
BEGIN
IF ISNULL(@SearchName,'') = ''
BEGIN
SELECT EventID, OrganizationName, OrganizationID, Title, DescriptionShort, DescriptionLong,
SUBSTRING(FileName, CHARINDEX('.', FileName) + 1, LEN(FileName)) as extension ,
Location, EventDate, ExpirationDate, URL, FileName, Active,EventTime from dbo.t_Event
END
ELSE
BEGIN
DECLARE @Count INT;
DECLARE @i INT = 0

WHILE (@i) < LEN(@SearchName)
BEGIN
SELECT @Count = COUNT(*) FROM t_Event
WHERE (OrganizationName LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i) + '%')
OR (Title LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i) + '%')
OR (DescriptionShort LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName)- @i) + '%')
OR (DescriptionLong LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i) + '%')

IF @Count > 0
BEGIN
SELECT EventID, OrganizationName, OrganizationID, Title, DescriptionShort, DescriptionLong,
SUBSTRING(FileName, CHARINDEX('.', FileName) + 1, LEN(FileName)) as extension ,
Location, EventDate, ExpirationDate, URL, FileName, Active,EventTime from dbo.t_Event
WHERE (OrganizationName LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i) + '%')
OR (Title LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i) + '%')
OR (DescriptionShort LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i) + '%')
OR (DescriptionLong LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName) - @i) + '%')
order by Title

END
SET @i = @i + 1

END
END
END

How to search data in SQL server?

Table- Blog

CREATE TABLE [dbo].[Blog](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Title] [nvarchar](max) NULL,
[ShortDescription] [nvarchar](max) NULL,
[Description] [nvarchar](max) NULL,
[ImageUrl] [nvarchar](255) NULL,
[CategoryId] [int] NULL,
[TagKeyWord] [nvarchar](255) NULL,
[SeoTitle] [nvarchar](255) NULL,
[SeoKeyword] [nvarchar](255) NULL,
[SeoDescription] [nvarchar](max) NULL,
[Status] [bit] NULL CONSTRAINT [DF_Blog_Status] DEFAULT ((0)),
[UserType] [nvarchar](255) NULL,
[UserId] [bigint] NULL,
[CreatedDate] [datetime] NULL CONSTRAINT [DF_Blog_CreatedDate] DEFAULT (getdate()),
CONSTRAINT [PK_Blog] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Table - BlogCategory
CREATE TABLE [dbo].[BlogCategory](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ParentID] [int] NOT NULL,
[Name] [varchar](max) NULL,
[Active] [bit] NOT NULL CONSTRAINT [DF_BlogCategory_Active] DEFAULT ((1)),
[seo_keywords] [varchar](255) NULL,
[seo_description] [varchar](255) NULL,
CONSTRAINT [PK_BlogCategory] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Create PROC [dbo].[usp_Blog_Search](@SearchName VARCHAR(200))
AS
BEGIN
IF ISNULL(@SearchName,'') = ''
BEGIN
SELECT b.ID, b.Title, b.ShortDescription, b.Description, b.ImageUrl, b.CategoryId, b.TagKeyWord, b.SeoTitle, b.SeoKeyword, b.SeoDescription,
b.Status, b.UserType, b.UserId, b.CreatedDate, c.Name,
(case UserType when 'U1' then (select FullName from dbo.UserDetail u where u.ID = b.UserId)
when 'A1' then (select FirstName + ' ' + LastName from dbo.AdminUser a where a.Id = b.UserId) end) as UserName,
(select COUNT(bv.BlogID) from BlogVisits bv where bv.BlogId=b.ID) as ViewCount,
(select COUNT(br.ID) from BlogReply br where br.BlogId=b.ID and br.Status=1) as CommentCount,(select COUNT(br.ID) from BlogReply br where br.BlogId=b.ID) as CommentCountWithoutStatus,
(case when [Status]=1 then 'Active' when [Status]=0 then 'InActive' end) as StatusActiveDeActive
from Blog b
inner join dbo.BlogCategory c on c.ID= b.CategoryId
where b.[Status]=1 order by b.ID desc
END
ELSE
BEGIN
SELECT b.ID, b.Title, b.ShortDescription, b.Description, b.ImageUrl, b.CategoryId, b.TagKeyWord, b.SeoTitle, b.SeoKeyword, b.SeoDescription,
b.Status, b.UserType, b.UserId, b.CreatedDate, c.Name,
(case UserType when 'U1' then (select FullName from dbo.UserDetail u where u.ID = b.UserId)
when 'A1' then (select FirstName + ' ' + LastName from dbo.AdminUser a where a.Id = b.UserId) end) as UserName,
(select COUNT(bv.BlogID) from BlogVisits bv where bv.BlogId=b.ID) as ViewCount,
(select COUNT(br.ID) from BlogReply br where br.BlogId=b.ID and br.Status=1) as CommentCount,(select COUNT(br.ID) from BlogReply br where br.BlogId=b.ID) as CommentCountWithoutStatus,
(case when [Status]=1 then 'Active' when [Status]=0 then 'InActive' end) as StatusActiveDeActive
from Blog b
inner join dbo.BlogCategory c on c.ID= b.CategoryId
WHERE ((Title LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName)) + '%')
or(TagKeyWord LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName)) + '%')
or(Name LIKE '%' + SUBSTRING(@SearchName,1,LEN(@SearchName)) + '%'))
and [Status]=1 order by ID desc
END
END

Friday, 19 February 2016

How to use bootstrap editor in MVC?

How to count a specific character by java script in mvc?

@Html.TextBoxFor(model => model.TagKeyWord, new { @class = "text", @style = "width:98%", @id = "seotagkeyword", onkeyup = "counttagkeyword(this);" })

<script type="text/javascript">
function counttagkeyword() {
debugger;
var area = document.getElementById("seotagkeyword").value;
var count = (area.match(/,/g) || []).length;
alert(count);
}
</script>

In C#
______________________________
string character = model.TagKeyWord;
int count = character.Count(x => x == ',');