Code Simplified – Viral Sarvaiya

Code Simplified – Viral Sarvaiya, Web Developer Friends, dot net Developer, Sql Server Developer

String.Split – by Multiple Character Delimiter in C#.Net

Posted by Viral Sarvaiya on October 31, 2012


 

Basically String Split function split the string with the single character delimiter;
we take one example of single string of the multiple emails.
If we have comma separated emails then we can use Split function as like below

string strEmailText = "abc.abc@yahoo.com,xyz.xyz@gmail.com,some.text@yahoo.co.in";
string[] strSplitText = strEmailText.Split(",");

this will give us string array of the Emails.

But what if we want to use split with 2 or more than one character?
Means what if i have string as like below

string strEmailText = "abc.abc@yahoo.com,xyz.xyz@gmail.com;some.text@yahoo.co.in,pqr.xyz@msn.com";

at this time above split function will not work with this string. so for that below solution works well.

string strEmailText = "abc.abc@yahoo.com,xyz.xyz@gmail.com;some.text@yahoo.co.in,pqr.xyz@msn.com";
string[] strSplitText = strEmailText.Split(new char[] { ',', ';' }, StringSplitOptions.RemoveEmptyEntries);

this will give you string array of the Emails.

Thanks.

Advertisements

Posted in .Net, ASP.NET, asp.net feature, LINQ | Tagged: , , , , , , , , , | Leave a Comment »

Find Foreign Keys in Sql Server

Posted by Viral Sarvaiya on October 12, 2012


Hi,

Here is the query to find list of foreign keys of the particular database,

Here this query gives name of foreign keys, table name, column name, referenced table name and referenced column name.


;With CTE As
(
Select Object_Name(constraint_object_id) Constraint_Name, Object_Name(parent_object_id) Table_Name, C.name Column_Name
From sys.foreign_key_columns FK
Inner Join sys.columns C On FK.parent_object_id = C.object_id And FK.parent_column_id = C.column_id
)
SELECT C.Constraint_Name,C.Table_Name,C.Column_Name, Object_Name(FK.referenced_object_id) Referenced_Table_Name,SC.name Referenced_Column_Name
from CTE C
Inner Join sys.foreign_key_columns FK On C.Constraint_Name = Object_Name(FK.constraint_object_id)
Inner Join sys.columns SC On FK.referenced_object_id = SC.object_id And FK.referenced_column_id = SC.column_id

Thanks.

Posted in Sql Server | Tagged: , , , , , , , , | 1 Comment »

Search Particular Text used in Data of All Tables of database in Sql Server

Posted by Viral Sarvaiya on September 18, 2012


When I am searching particular text from Stored Procedure in sql server as I wrote in my last blog post https://codesimplified.com/2012/09/10/search-particular-text-used-in-stored-procedure-in-sql-server/  I get strike that is there any way to search any particular text which is used in data of the all table in database?

Here is the SP which return the list of the table name with column name and whole text if that text is in between the big text.

/****** Object:  StoredProcedure [dbo].[SearchTextFromAllTables]    Script Date: 09/10/2012 15:00:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
	-- Purpose: To search all columns of all tables for a given search string
	-- Written by: Narayana Vyas Kondreddi
	-- Site: http://vyaskn.tripod.com
	-- Tested on: SQL Server 7.0, SQL Server 2000, SQL server 2005
	-- Date modified: 28th July 2002 22:50 GMT

CREATE PROCEDURE [dbo].[SearchTextFromAllTables]
(
@StrSearch nvarchar(100)
)
AS
BEGIN

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @StrSearch2 nvarchar(110)
SET  @TableName = ''
SET @StrSearch2 = QUOTENAME('%' + @StrSearch + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM     INFORMATION_SCHEMA.TABLES
WHERE         TABLE_TYPE = 'BASE TABLE'
AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND    OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM     INFORMATION_SCHEMA.COLUMNS
WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
AND    TABLE_NAME    = PARSENAME(@TableName, 1)
AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND    QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @StrSearch2
)
END
END
END

SELECT distinct ColumnName,ColumnValue FROM #Results
END

for run this query


exec SearchTextFromAllTables 'TextValue'

You will find the result as below.

Hope this will helps you..

Thanks.

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Posted in Sql Server | Tagged: , , , , , , , , , , , , , , | Leave a Comment »

Search Particular Text used in Stored Procedure in Sql Server

Posted by Viral Sarvaiya on September 10, 2012


Few days ago I get very tedious job in database,
I have to search all stored procedure which have use one particular table.

I get following 1 option of query from sys table of sqlserver

SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%TBL_NAME%'    AND ROUTINE_TYPE='PROCEDURE'
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%TBL_NAME%'

but this query only table table’s name, if We want particular string not table then this query will fail so below stored procedure will work.

CREATE PROCEDURE [dbo].[sp_search_code]
(
@SearchStr  varchar(100),
@RowsReturned int = NULL OUT
)
AS
BEGIN
SET NOCOUNT ON

SELECT DISTINCT USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Object name',
CASE
WHEN OBJECTPROPERTY(c.id, 'IsReplProc') = 1
THEN 'Replication stored procedure'
WHEN OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1
THEN 'Extended stored procedure'
WHEN OBJECTPROPERTY(c.id, 'IsProcedure') = 1
THEN 'Stored Procedure'
WHEN OBJECTPROPERTY(c.id, 'IsTrigger') = 1
THEN 'Trigger'
WHEN OBJECTPROPERTY(c.id, 'IsTableFunction') = 1
THEN 'Table-valued function'
WHEN OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1
THEN 'Scalar-valued function'
WHEN OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
THEN 'Inline function'
END AS 'Object type',

'EXEC sp_helptext ''' + USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) + '''' AS 'Run this command to see the object text'

FROM syscomments c
INNER JOIN
sysobjects o
ON c.id = o.id
WHERE c.text LIKE '%' + @SearchStr + '%' AND
encrypted = 0    AND
(
OBJECTPROPERTY(c.id, 'IsReplProc') = 1  OR
OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1 OR
OBJECTPROPERTY(c.id, 'IsProcedure') = 1  OR
OBJECTPROPERTY(c.id, 'IsTrigger') = 1  OR
OBJECTPROPERTY(c.id, 'IsTableFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
)

ORDER BY 'Object type', 'Object name'

SET @RowsReturned = @@ROWCOUNT
END

Run this query as like below

exec sp_search_code 'SearchText'

You will get require output of list of stored procedure name.

In this Stored Procedure i take one output parameter for check number of rows return by Stored Procedure.

Hope this will help you.

Thanks.

Posted in Sql Server | Tagged: , , , , , , , , , , , | 1 Comment »

Difference Between LINQ to SQL and Entity Framework

Posted by Viral Sarvaiya on August 14, 2012


In my current company we have discussion about what is the difference between LINQ to SQL Vs Entity Framework and I get following conclusion from the surfing from internet (off course from google. 🙂 )

LINQ to SQL supports one to one mapping of database table or views or procedures or functions
Entity Framework map single class to multiple tables of database. Means you can map one table to multiple entities or multiple table to one entities.

LINQ to SQL does not support the creation of complex types
Entity Framework support the creation of complex types.

LINQ to SQL is easy to use
Entity framework is more complex compared to LINQ to SQL.

While introduction of LINQ to SQL it supports only sql server and letter on “DBLINQ” that can use with mysql, sqllite or other DBs
Entity Framework  plug with any database server like DB2, Sybase, Oracle, SQL Azure and other.

In LINQ to SQL, inheritance is difficult.
Entity Framework is simple to apply because it supports Table per class and table per Type.

LINQ to SQL file type is DBML
Entity Framework File type is EDMX.

LINQ to SQL has DataContext object which we can query the database,
Entity Framework, we can query database using LINQ To Entities through the ObjectContext object and ESQL(provides SQL like query language). In addition, Entity Framework has ObjectQuery class(used with Object Services for dynamically constructing queries at runtime) and EntityClient provider(runs query against conceptual model).

LINQ to SQL is slow for the first time run, after first run acceptable performance.
Entity Framework is also slow for the first but  performance is good then LINQ to SQL after first run.

LINQ to SQL has not capability to generate database from Model
Entity Framework has capability to generate database from Model.

Thanks.

Posted in .Net, ASP.NET, asp.net feature, LINQ | Tagged: , , , , , | Leave a Comment »

Comparison/Difference of IPv4 and IPv6

Posted by Viral Sarvaiya on July 20, 2012


Key differences between IPv4 and IPv6.

IPv4

IPv6

Source and destination addresses are 32 bits (4 ytes) in length. Source and destination addresses are 128 bits (16 bytes) in length.
IPsec header support is optional. IPsec header support is required.
No identification of packet flow for prioritized delivery handling by routers is present within the IPv4 header. Packet flow identification for prioritized delivery handling by routers is present within the IPv6 header using the Flow Label field.
Fragmentation is performed by the sending host and at routers, slowing router performance. Fragmentation is performed only by the sending host.
Has no link-layer packet-size requirements and must be able to reassemble a 576-byte packet. Link layer must support a 1,280-byte packet and be able to reassemble a 1,500-byte packet.
Header includes a checksum. Header does not include a checksum.
Header includes options. All optional data is moved to IPv6 extension headers.
ARP uses broadcast ARP Request frames to resolve an IPv4 address to a link-layer address. ARP Request frames are replaced with multicast Neighbor Solicitation messages.
Internet Group Management Protocol (IGMP) is used to manage local subnet group membership. IGMP is replaced with Multicast Listener Discovery (MLD) messages.
ICMP Router Discovery is used to determine the IPv4 address of the best default gateway and is optional. ICMPv4 Router Discovery is replaced with ICMPv6 Router Solicitation and Router Advertisement messages, and it is required.
Broadcast addresses are used to send traffic to all nodes on a subnet. There are no IPv6 broadcast addresses. Instead, a linklocal scope all-nodes multicast address is used.
Must be configured either manually or through DHCP for IPv4. Does not require manual configuration or DHCP forIPv6.
Uses host address (A) resource records in the Domain Name System (DNS) to map host names to IPv4 addresses. Uses AAAA records in the DNS to map host names to IPv6 addresses.
Uses pointer (PTR) resource records in the IN-ADDR.ARPA DNS domain to map IPv4 addresses to host names. Uses pointer (PTR) resource records in the IP6.ARPA DNS domain to map IPv6 addresses to host names.

Posted in General | Tagged: , , , , , , , , , , | Leave a Comment »

Features of IPv6

Posted by Viral Sarvaiya on July 19, 2012


The following list summarizes the features of the IPv6 protocol:
1) New header format
2) Large address space
3) Stateless and stateful address configuration
4) IPsec header support required
5) Better support for prioritized delivery
6) New protocol for neighboring node interaction
7) Extensibility

1) New Header Format:
The IPv6 header has a new format that is designed to minimize header processing. This is achieved by moving both nonessential and optional fields to extension headers that are placed after the IPv6 header. The streamlined IPv6 header is more efficiently processed at intermediate routers. IPv4 headers and IPv6 headers are not interoperable. IPv6 is not a superset of functionality that is backward compatible with IPv4. A host or router must use an implementation of both IPv4 and IPv6 to recognize and process both header formats. The new default IPv6 header is only twice the size of the default IPv4 header, even though the number of bits in IPv6 addresses is four times larger than in IPv4 addresses.

2) Large Address Space:
IPv6 has 128-bit (16-byte) source and destination addresses. Although 128 bits can express over 3.4 ~ 1038 possible combinations, the large address space of IPv6 has been designed to allow for multiple levels of subnetting and address allocation, from the Internet backbone to the individual subnets within an organization. Even with all of the addresses currently assigned for use by hosts, plenty of addresses are available for future use. With a much larger number of available addresses, address-conservation techniques, such as the deployment of NATs, are no longer necessary.

3) Stateless and Stateful Address Configuration:
To simplify host configuration, IPv6 supports both stateful address configuration (such as address configuration in the presence of a DHCP for IPv6, or DHCPv6, server) and stateless address configuration (such as address and routing configuration in the absence of a DHCPv6 server). With stateless address configuration, hosts on a link automatically configure themselves with IPv6 addresses for the link (called link-local addresses), with IPv6 transition addresses, with addresses derived from prefixes advertised by local routers, and local subnet and default routes. Both stateless and stateful addressing can be used at the same time. Even in the absence of a router, hosts on the same link can automatically configure themselves with link-local addresses and communicate without manual configuration. Link-local addresses are autoconfigured within seconds, and communication with neighboring nodes on the link is possible immediately. In comparison, some IPv4 hosts using DHCP must wait a full minute before abandoning DHCP configuration and selfconfiguring an IPv4 address.

4) IPsec Header Support Required:
Support for the IPsec headers is an IPv6 protocol suite requirement. This requirement provides a standards-based solution for network protection needs and promotes interoperability between different IPv6 implementations. IPsec consists of two types of extension headers and a protocol to negotiate security settings. The Authentication header (AH) provides data integrity, data authentication, and replay protection for the entire IPv6 packet (excluding fields in the IPv6 header that must change in transit). The Encapsulating Security Payload (ESP) header and trailer provide data integrity, data authentication, data confidentiality, and replay protection for the ESP-encapsulated payload. The protocol typically used to negotiate IPsec security settings for unicast communication is the Internet Key Exchange (IKE) protocol. The requirement to process IPsec headers does not make IPv6 inherently more secure. IPv6 packets are not required to be protected with IPsec, and IPsec is not a requirement of an IPv6 deployment. Additionally, the IPv6 standards do not require an implementation to support any specific encryption methods, hashing methods, or negotiation protocol (such as IKE).

5) Better Support for Prioritized Delivery:
New fields in the IPv6 header define how traffic is handled and identified. Traffic is prioritized by using a Traffic Class field, which specifies a DSCP value just like IPv4. A Flow Label field in the IPv6 header allows routers to identify and provide special handling for packets that belong to a flow (a series of packets between a source and destination). Because the traffic is identified in the IPv6 header, support for prioritized delivery can be achieved even when the packet payload is encrypted with IPsec and ESP.

6) New Protocol for Neighboring Node Interaction:
The Neighbor Discovery protocol for IPv6 is a series of Internet Control Message Protocol for IPv6 (ICMPv6) messages that manages the interaction of neighboring nodes (nodes on the same link). Neighbor Discovery replaces and extends the combination of the Address Resolution Protocol (ARP) (broadcast-based), ICMPv4 Router Discovery, and ICMPv4 Redirect messages with efficient multicast and unicast Neighbor Discovery messages.

7) Extensibility:
IPv6 can easily be extended for new features by adding extension headers after the IPv6 header. Unlike options in the IPv4 header, which can support only 40 bytes of options, the size of IPv6 extension headers is constrained only by the size of the IPv6 packet.

Posted in General | Tagged: , , , , , , , , | Leave a Comment »

Random in Linq

Posted by Viral Sarvaiya on July 12, 2012


In sql query if we want to get one random row form the whole set of data we get from NEWID() in select statement’s order by clouse as like below.

Select top 1 * from Customers where IsActive = 1 order by newid()

What if we are using ORM like linq?

We can do this at the database, by using a fake UDF; in a partial class, add a method to the data context

partial class MyDataContext {
    [Function(Name="NEWID", IsComposable=true)]
    public Guid Random()
    { // to prove not used by our C# code...
        throw new NotImplementedException();
    }
 }

Then just order by ctx.Random(); this will do a random ordering at the SQL-Server courtesy of NEWID(). i.e.

var cust = (from row in ctx.Customers
   .where row.IsActive // your filter
   .orderby ctx.Random()
   select row).FirstOrDefault();

Note that this is only suitable for small-to-mid-size tables; for huge tables, it will have a performance impact at the server, and it will be more efficient to find the number of rows (Count), then pick one at random (Skip/First).

var qry = from row in ctx.Customers select row;

int count = qry.Count(); // 1st round-trip
int index = new Random().Next(count);

Customer cust = qry.Skip(index).FirstOrDefault(); // 2nd round-trip

We can also do woth Guid class

var cust = ctx.Customers
 .Where(x => x.IsActive)
 .OrderBy(x => Guid.NewGuid())
 .FirstOrDefault();

Hope this will helps you.

Thank you.

Posted in .Net, ASP.NET, LINQ | Tagged: , , , , , , , | Leave a Comment »

ROWCOUNT in Sql server

Posted by Viral Sarvaiya on June 6, 2012


Causes SQL Server to stop processing the query after the specified number of rows are returned.

SET ROWCOUNT 10;
 SELECT * FROM Employee;

If Employee table has 100 reocrds, query return only 10 records. SET ROWCOUNT works as a TOP.

Syntex:
SET ROWCOUNT { number | @number_var }

where “number | @number_var” is Integer value rows to be processed before stopping the specific query.

To set this option off so that all rows are returned, specify SET ROWCOUNT 0.

SET ROWCOUNT overrides the SELECT statement TOP keyword if the rowcount is the smaller value.

The setting of SET ROWCOUNT is set at execute or run time and not at parse time.

SET ROWCOUNT is not affect DELETE, INSERT and UPDATE statement so for that similar behavior use TOP in select Statement.

http://msdn.microsoft.com/en-us/library/ms188774.aspx

Posted in Sql Server | Tagged: , , , , , | Leave a Comment »

What is wrong with IsNumeric()?

Posted by Viral Sarvaiya on May 18, 2012


Today I face very strange thing with IsNumeric() function in sql

select IsNumeric('1234D234')
select IsNumeric('123A123')

Both query have alphanumeric value and both give different answer. very strange.

After a long time spent on google i get solution. We can say or cannot say that there are bug in IsNumeric() function.

IsNumeric() can falsely interpret non-numeric letters and symbols (such as D, E, and £, $), and even tabs (CHAR(9)) as numeric.

With this character, Why IsNumeric() can not give right answer? because D stand for Decimal. that value consider as a decimal value, E stand for exponential and $,£ is stand for currency symbol. so Isnumeric() fail to convert those values which has like D,E,$,£ characters.

so to get around this problem, we can create user define function which find the value is integer/numeric. as below.

CREATE FUNCTION dbo.CheckNumeric
(
@num VARCHAR(64)
)
RETURNS BIT
BEGIN
IF LEFT(@num, 1) = '-'
SET @num = SUBSTRING(@num, 2, LEN(@num))

DECLARE @pos TINYINT

SET @pos = 1 + LEN(@num) - CHARINDEX('.', REVERSE(@num))

RETURN CASE
WHEN PATINDEX('%[^0-9.-]%', @num) = 0
AND @num NOT IN ('.', '-', '+', '^')
AND LEN(@num)>0
AND @num NOT LIKE '%-%'
AND
(
((@pos = LEN(@num)+1)
OR @pos = CHARINDEX('.', @num))
)
THEN
1
ELSE
0
END
END
GO

Or

CREATE FUNCTION dbo.CheckInteger
(
@num VARCHAR(64)
)
RETURNS BIT
BEGIN
IF LEFT(@num, 1) = '-'
SET @num = SUBSTRING(@num, 2, LEN(@num))

RETURN CASE
WHEN PATINDEX('%[^0-9-]%', @num) = 0
AND CHARINDEX('-', @num) <= 1
AND @num NOT IN ('.', '-', '+', '^')
AND LEN(@num)>0
AND @num NOT LIKE '%-%'
THEN
1
ELSE
0
END
END
GO

Learn From: http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html
Thanks.

Posted in Sql Server | Tagged: , , , , | 4 Comments »

 
%d bloggers like this: