Code Simplified – Viral Sarvaiya

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

Convert data from Generic List to DataTable.

Posted by Viral Sarvaiya on May 9, 2013


Today i am sharing very good a function which convert all Generic list’s data to Datatable is as below

public DataTable LINQToDataTable<T>(IEnumerable<T> varlist)
{
  DataTable dtReturn = new DataTable();

  // column names
  PropertyInfo[] oProps = null;

  if (varlist == null) return dtReturn;

  foreach (T rec in varlist)
  {
    // Use reflection to get property names, to create table, Only first time, others will follow
    if (oProps == null)
    {
      oProps = ((Type)rec.GetType()).GetProperties();
      foreach (PropertyInfo pi in oProps)
      {
        Type colType = pi.PropertyType;

        if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
        {
          colType = colType.GetGenericArguments()[0];
        }

        dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
      }
    }
    DataRow dr = dtReturn.NewRow();

    foreach (PropertyInfo pi in oProps)
    {
      dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue(rec, null);
    }

    dtReturn.Rows.Add(dr);
  }
  return dtReturn;
}

Hope this will helps you.
Thanks.

Posted in .Net, C#, General, LINQ | Tagged: , , , , , , , , , , | Leave a Comment »

Find Difference between 2 dates in Year, month, day, hour, minute, second and millisecond.

Posted by Viral Sarvaiya on May 3, 2013


After a long time i get time to post in my blog.

Few day ago i get very good function from my one of the good friend in my current company which finds difference between 2 dates.

Below is the class named “DateTimeSpan.cs”

using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;

public struct DateTimeSpan
{
  private readonly int m_years;
  private readonly int m_months;
  private readonly int m_days;
  private readonly int m_hours;
  private readonly int m_minutes;
  private readonly int m_seconds;

  private readonly int m_milliseconds;
  public DateTimeSpan(int years, int months, int days, int hours, int minutes, int seconds, int milliseconds)
  {
    this.m_years = years;
    this.m_months = months;
    this.m_days = days;
    this.m_hours = hours;
    this.m_minutes = minutes;
    this.m_seconds = seconds;
    this.m_milliseconds = milliseconds;
  }

  public int Years
  {
    get { return m_years; }
  }
  public int Months
  {
    get { return m_months; }
  }
  public int Days
  {
    get { return m_days; }
  }
  public int Hours
  {
    get { return m_hours; }
  }
  public int Minutes
  {
    get { return m_minutes; }
  }
  public int Seconds
  {
    get { return m_seconds; }
  }
  public int Milliseconds
  {
    get { return m_milliseconds; }
  }

  private enum Phase
  {
    Years,
    Months,
    Days,
    Done
  }

  public static DateTimeSpan CompareDates(DateTime date1, DateTime date2)
  {
    if (date2 < date1)
    {
      dynamic sub = date1;
      date1 = date2;
      date2 = sub;
    }

    DateTime current = date1;
    int years = 0;
    int months = 0;
    int days = 0;

    Phase phase__1 = Phase.Years;
    DateTimeSpan span = new DateTimeSpan();

    while (phase__1 != Phase.Done)
    {
      switch (phase__1)
      {
        case Phase.Years:
          if (current.AddYears(years + 1) > date2)
          {
            phase__1 = Phase.Months;
            current = current.AddYears(years);
          }
          else
          {
            years += 1;
          }
          break; // TODO: might not be correct. Was : Exit Select
        break;

        case Phase.Months:
          if (current.AddMonths(months + 1) > date2)
          {
            phase__1 = Phase.Days;
            current = current.AddMonths(months);
          }
          else
          {
            months += 1;
          }
          break; // TODO: might not be correct. Was : Exit Select
        break;

        case Phase.Days:
          if (current.AddDays(days + 1) > date2)
          {
            current = current.AddDays(days);
            dynamic timespan = date2 - current;
            span = new DateTimeSpan(years, months, days, timespan.Hours, timespan.Minutes, timespan.Seconds, timespan.Milliseconds);
            phase__1 = Phase.Done;
          }
          else
          {
            days += 1;
          }
          break; // TODO: might not be correct. Was : Exit Select
        break;
       }
    }
    return span;
  }
}

Now in default.aspx page i am using this structure to get difference between 2 dates as below.

protected void Page_Load(object sender, EventArgs e)
{
  DateTimeSpan datetimespan = new DateTimeSpan(); // Create object of constructer to get difference.

  string date1 = "3-May-2013 9:26:10.011 AM";  //Date 1
  string date2 = "4-June-2014 6:50:20.136 PM";  // date2
  datetimespan = DateTimeSpan.CompareDates(Convert.ToDateTime(date1), Convert.ToDateTime(date2));  //Call static function of DateTimeSpan structure. which return difference of 2 dates.

  Response.Write("Date 1 : " + date1 + "  <br>");
  Response.Write("Date 2 : " + date2 + " <br><br>");

  Response.Write("Years : " + datetimespan.Years + "<br>");
  Response.Write("Months : " + datetimespan.Months + "<br>");
  Response.Write("Days : " + datetimespan.Days + "<br>");
  Response.Write("Hours : " + datetimespan.Hours + "<br>");
  Response.Write("Minutes : " + datetimespan.Minutes + "<br>");
  Response.Write("Seconds : " + datetimespan.Seconds + "<br>");
  Response.Write("Milliseconds : " + datetimespan.Milliseconds + "<br>");

}

This will give difference in all possible ways as below output.

Output

Hope this will helps you.
Thanks.

Posted in .Net, C#, General | Tagged: , , , , , , , , | Leave a Comment »

Delete multiple Procedure/Table/Trigger form sqlserver database

Posted by Viral Sarvaiya on December 14, 2012


In sql server we use one query for drop table/procedure/trigger like below


Drop table table_name
Drop procedure Procedure_name
Drop trigger Trigger_name

But if we want to delete some or all Tables/Procedure/Trigger then what?
have to delete one by one?
We can create one loop with cursor to drop table/procedure/trigger for that as like below.

we can create SP to delete perticulers of the database as passing text and type as a parameter.

For that we can use sys.objects table

For table Type = ‘U’
For procedure Type = ‘P’
For Trigger Type = ‘TR’

For more type of sys.objects please refer
http://msdn.microsoft.com/en-us/library/ms190324.aspx

Stored procedure is as below.


ALTER PROCEDURE DeleteEntities
@Type VARCHAR(2),
@SearchText VARCHAR(50)
AS
BEGIN

DECLARE @procName varchar(500)
DECLARE @StrDrop VARCHAR(50)

IF (@type != '')
BEGIN

DECLARE cur cursor

FOR SELECT [name] FROM sys.objects WHERE TYPE = @Type AND NAME LIKE '%'+  @SearchText +'%'
open cur
fetch next from cur into @procName
while @@fetch_status = 0
BEGIN
SET @StrDrop = ((CASE WHEN @Type = 'U' THEN  'drop table '
WHEN @Type = 'P' THEN 'drop Procedure '
WHEN @Type = 'TR' THEN 'Drop trigger '
ELSE ''
END) + @procName)
IF (@StrDrop != '')
BEGIN
EXEC @StrDrop
END

fetch next from cur into @procName
end
close cur
deallocate cur
END

END

As like this we can also drop table constraints.

Hope this will helps you.

Thanks

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

Handling Window services thru C#

Posted by Viral Sarvaiya on November 23, 2012


Hello.

Open Run window form start menu or Press Window key + R, Run window will open, in textbox type “services.msc” and Hit Enter, Service window will open.

Service window has list of the services installed in the computer.

To these all service we can call Window service in technical word.

Window service enable you to create long-running executable applications.

To know how to create window service please click here

C# provide you functionality to handle installed services, that will include in namespace System.ServiceProcess

Here i am explain, how to handle window service.

1. To get list of the Window service.

You can get list of the window service installed in computer from below code,

In System.ServiceProcess namespace we have a from that we can get list of the window service.


ServiceController[] ListServices = ServiceController.GetServices();

2. Search particular service from list.


ServiceController[] ListServices = ServiceController.GetServices();

string TextService = "My Sample Service"

if (LstServices.Where(a => a.ServiceName == TextService).Count() > 0)
{
ServiceController Myservice = new ServiceController(TextService);
}
else
{
MessageBox.Show(TextService + " is not Installed");
}

ServiceController.GetServices() gives list of the service so i take that in array of the ServiceController class.

And from linq query we can check that particular service is in the list or not and if that list count is greater than zero then we get that service is installed in the computer else not.

And to get that particular service, here i have creating object of the ServiceController class.

3. Check the status of the Service.

If the window service is installed in computer then we can also check the status of that window service.

Now we assume that we find the service “My Sample Service”.

So from below code we can get the status of the service whether it is Running, Paused, Stopped.


ServiceControllerStatus ServiceStatus = Myservice.Status;

ServiceControllerStatus is Enum for all service statuses.

If we want to compare status then we can use as like below.


if (ServiceStatus == ServiceControllerStatus.Running)
{
//Do something
}

4. Start/Stop the Service

From ServiceController class we can start or stop the particular Service with object of the ServiceController.

To start the service you can use


Myservice.Start();

And to stop the service you can use


Myservice.Stop();

5. To Restart the Service

To restart the service first we have to check that service is running or not and if it is running then we can stop the service and start the service.


ServiceController[] ListServices = ServiceController.GetServices();

string TextService = "My Sample Service"

if (LstServices.Where(a => a.ServiceName == TextService).Count() > 0)
{
ServiceController Myservice = new ServiceController(TextService);

ServiceControllerStatus ServiceStatus = Myservice.Status;

if (ServiceStatus == ServiceControllerStatus.Running)
{
Myservice.Stop();

TimeSpan timeout = TimeSpan.FromMilliseconds(2000);

Myservice.WaitForStatus(ServiceControllerStatus.Stopped, timeout);  //To wait 2 second for Stop Service

Myservice.Start();

Myservice.WaitForStatus(ServiceControllerStatus.Running, timeout);   // To wait 2 second for Start Service
}
}
else
{
MessageBox.Show(TextService + " is not Installed");
}

hope this will helpful

Thanks.

Posted in .Net, Window Service | Tagged: , , , , , , , , , , | Leave a Comment »

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.

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
http://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 »

 
Follow

Get every new post delivered to your Inbox.

Join 44 other followers

%d bloggers like this: