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 == ',');

How to max 250 character in textbox by javascript?

Html.TextAreaFor(model => model.SeoDescription, new { @class = "text", @style = "width:98%", @id = "SeoDesc", @maxlength = "250", onkeyup = "countSeoDesc(this);", @rows = "3" })
<p id="messageSeoDesc" class="text-right">[250 char]</p>

<script type="text/javascript">
function countSeoDesc() {
debugger;
var area = document.getElementById("SeoDesc");
var message = document.getElementById("messageSeoDesc");
var maxLength = 250;
if (area.value.length <= maxLength) {
message.innerHTML = "[" + (maxLength - area.value.length) + " char" + "]";
}
}
</script>

How to check email is valid or not?

public bool isEmail(string inputEmail)
{
inputEmail = Convert.ToString(inputEmail);
string strRegex = @"^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}" +
@"\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\" +
@".)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$";
Regex re = new Regex(strRegex);
if (re.IsMatch(inputEmail))
return (true);
else
return (false);
}

How to replace special character one by one from string?

public static string ReplaceSpecialCharecters(string stringtoreplce, string charwithreplace)
{
string str = string.Empty;

if (stringtoreplce.Contains(" "))
stringtoreplce = stringtoreplce.Trim().Replace(" ", "-");

if (stringtoreplce.Contains(enDash))
stringtoreplce = stringtoreplce.Replace(enDash, '-');

if (stringtoreplce.Contains('~'))
stringtoreplce = stringtoreplce.Replace('~', '-');

if (stringtoreplce.Contains('`'))
stringtoreplce = stringtoreplce.Replace('`', '-');

if (stringtoreplce.Contains('!'))
stringtoreplce = stringtoreplce.Replace('!', '-');

if (stringtoreplce.Contains('@'))
stringtoreplce = stringtoreplce.Replace('@', '-');

if (stringtoreplce.Contains('#'))
stringtoreplce = stringtoreplce.Replace('#', '-');

if (stringtoreplce.Contains('$'))
stringtoreplce = stringtoreplce.Replace('$', '-');

if (stringtoreplce.Contains('%'))
stringtoreplce = stringtoreplce.Replace('%', '-');

if (stringtoreplce.Contains('^'))
stringtoreplce = stringtoreplce.Replace('^', '-');

if (stringtoreplce.Contains('&'))
stringtoreplce = stringtoreplce.Replace('&', '-');

if (stringtoreplce.Contains('*'))
stringtoreplce = stringtoreplce.Replace('*', '-');

if (stringtoreplce.Contains('<'))
stringtoreplce = stringtoreplce.Replace('<', '-');

if (stringtoreplce.Contains('>'))
stringtoreplce = stringtoreplce.Replace('>', '-');

if (stringtoreplce.Contains(','))
stringtoreplce = stringtoreplce.Replace(',', '-');

if (stringtoreplce.Contains('/'))
stringtoreplce = stringtoreplce.Replace('/', '-');

if (stringtoreplce.Contains(';'))
stringtoreplce = stringtoreplce.Replace(';', '-');

if (stringtoreplce.Contains(':'))
stringtoreplce = stringtoreplce.Replace(':', '-');

if (stringtoreplce.Contains('|'))
stringtoreplce = stringtoreplce.Replace('|', '-');

if (stringtoreplce.Contains('\\'))
stringtoreplce = stringtoreplce.Replace('\\', '-');

if (stringtoreplce.Contains('"'))
stringtoreplce = stringtoreplce.Replace('"', '-');

if (stringtoreplce.Contains(rsingleQuote))
stringtoreplce = stringtoreplce.Replace(rsingleQuote, '-');

if (stringtoreplce.Contains('.'))
stringtoreplce = stringtoreplce.Replace('.', '-');

while (stringtoreplce.Contains("--"))
stringtoreplce = stringtoreplce.Replace("--", "-");

return stringtoreplce;
}

Thursday, 4 February 2016

Could not load file or assembly 'EntityFramework' or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.

If project a website then-:

Click the Debug menu after that click the properties then its change the target framework version 4.5

Wednesday, 3 February 2016

How to Export datatable to dbf file in ASP.Net C#?

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.OleDb;

using System.IO;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

namespace Demo

{

public partial class Create_DBF_File : System.Web.UI.Page

{

OleDbConnection dBaseConnection = null;

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

{

CreateDBFFile();

}

private DataTable GenerateData()

{

DataTable dt = new DataTable();

DataRow dr = null;

dt.Columns.Add(new DataColumn("Column1", typeof(string)));

dt.Columns.Add(new DataColumn("Column2", typeof(string)));

dt.Columns.Add(new DataColumn("Column3", typeof(string)));

dt.Columns.Add(new DataColumn("Column4", typeof(DateTime)));

int totalRow = 25;

for (int i = 0; i < totalRow; i++)

{

dr = dt.NewRow();

dr["Column1"] = i + 1;

dr["Column2"] = "Row" + i;

dr["Column3"] = "Row" + i;

dr["Column4"] = DateTime.Now;

dt.Rows.Add(dr);

}

return dt;

}

private void CreateDBFFile()

{

string filepath = null;

filepath = Server.MapPath("~//File//");

string TableName = "T" +DateTime.Now.ToLongTimeString().Replace(":", "").Replace("AM","").Replace("PM", "");

using(dBaseConnection = newOleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " + " Data Source=" + filepath + "; " + "Extended Properties=dBase IV"))

{

dBaseConnection.Open();

OleDbCommand olecommand = dBaseConnection.CreateCommand();

if ((System.IO.File.Exists(filepath + "" + TableName +".dbf")))

{

System.IO.File.Delete(filepath + "" + TableName + ".dbf");

olecommand.CommandText = "CREATE TABLE [" + TableName + "] ([Column1] int, [Column2] varchar(10), [Column3] varchar(10), [Column4] datetime)";

olecommand.ExecuteNonQuery();

}

else

{

olecommand.CommandText = "CREATE TABLE [" + TableName + "] ([Column1] int, [Column2] varchar(10), [Column3] varchar(10), [Column4] datetime)";

olecommand.ExecuteNonQuery();

}

OleDbDataAdapter oleadapter = newOleDbDataAdapter(olecommand);

OleDbCommand oleinsertCommand = dBaseConnection.CreateCommand();

foreach (DataRow dr in GenerateData().Rows)

{

string Column1 = dr["Column1"].ToString();

string Column2 = dr["Column2"].ToString();

string Column3 = dr["Column3"].ToString();

DateTime Column4 = Convert.ToDateTime(dr["Column4"]);

oleinsertCommand.CommandText = "INSERT INTO [" + TableName + "] ([Column1], [Column2],[Column3],[Column4]) VALUES ('" + Column1 + "','" + Column2 + "','" + Column3 + "','" + Column4 + "')";

oleinsertCommand.ExecuteNonQuery();

}

FileStream sourceFile = new FileStream(filepath + "" + TableName +".dbf", FileMode.Open);

float FileSize = 0;

FileSize = sourceFile.Length;

byte[] getContent = newbyte[Convert.ToInt32(Math.Truncate(FileSize))];

sourceFile.Read(getContent, 0,Convert.ToInt32(sourceFile.Length));

sourceFile.Close();

Response.ClearContent();

Response.ClearHeaders();

Response.Buffer = true;

Response.ContentType = "application/dbf";

Response.AddHeader("Content-Length", getContent.Length.ToString());

Response.AddHeader("Content-Disposition", "attachment; filename=Demo.dbf;");

Response.BinaryWrite(getContent);

Response.Flush();

System.IO.File.Delete(filepath + "" + TableName + ".dbf");

Response.End();

}

Don't forgate to create File folder inside your project root folder.

If you are getting the below error:-

The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.

Means, In your local machine AccessDatabaseEngine not registered.

So, You need to download it from Microsoft web site check following link for downloading AccessDatabaseEngine

http://www.microsoft.com/download/en/details.aspx?id=13255

If you are getting the below error in 64 bit machine:-

The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.

then your machine IIS