How to extract a string in between the characters from the string SQL?

Please check with this code, I was used in my project for extracting numeric value

Declare @str varchar(50)= 'userid7f(203001)31f469-NAIsasdsd'

select substring(@str,patindex('%(%',@str)+1,patindex('%)%',@str)-patindex('%(%',@str)-1)
OR

select substring(@str,charindex('(',@str)+1,charindex(')',@str)-charindex('(',@str)-1)

OUTPUT= '203001'

How to get the only numeric part from a string SQL?

How to get the only numeric part from a string SQL?

Please check with this code, I was used in my project for extracting numeric value

DECLARE @string varchar(100)

SET @string = 'sk123;fja4567afj;as8901sfsfsfa2'

WHILE PATINDEX('%[^0-9]%',@string) <> 0

SET @string = STUFF(@string,PATINDEX('%[^0-9]%',@string),1,'')

SELECT @string  

   
OUTPUT= '123456789012'
  




The await Expression:

The await expression specifies a task to be done asynchronously.

Syntax:

await task

There are now a number of BCL methods that return objects of type Task<T>, you’ll most likely have your own methods that you want to use as the task for an await expression. The easiest way to do that is to create a Task from your method using the Task.Run method.

the most important fact about the Task.The run method is that it runs your method on a different thread.

One signature of the Task.Run method is,it takes a Func delegate as a parameter.

Task Run( Func<TReturn> func

So, to pass your method to the Task.Run method, you need to create a delegate from it.

There are three ways to do this.

In the code, method Get20 has a form compatible with a Func<int> delegate since it takes no parameters and returns an int.

• In the first instance, which is in the first two lines of method DoWorkAsync, a Func delegate named twenty is created using Get20. That delegate is then used in the Task.Run method in the next line.
• In the second instance, a Func delegate is created right in the Task.Run method’s parameter list.
• The last instance doesn’t use the Get20 method at all. It uses the return statement that comprises the body of the Get20 method, and uses it as the body of a lambda expression compatible with a Func<int> delegate. The lambda expression is implicitly converted to the delegate.

 class MyClass
    {
        public int Get20() // Func<int> compatible
        {
            return 20;
        }
        public async Task DoWorkAsync()
        {
            Func<int> twenty = new Func<int>(Get20);
            int a = await Task.Run(twenty);
            int b = await Task.Run(new Func<int>(Get20));
            int c = await Task.Run(() => { return 20; });
            Console.WriteLine("{0} {1} {2}", a, b, c);
        }
        class Program
        {
            static void Main()
            {
                Task t = (new MyClass()).DoWorkAsync();
                t.Wait();
            }
        }
    }

Exception Handling and the await Expression:

You can use await expression inside a try statement.

 class Program 
   {   
    static void Main(string[] args) 
       {   
        Task t = BadAsync();
            t.Wait();
            Console.WriteLine("Task Status : {0}", t.Status); 
           Console.WriteLine("Task IsFaulted: {0}", t.IsFaulted);
        }        
    static async Task BadAsync()
        { 
           try 
           { 
               await Task.Run(() => { throw new Exception(); });
            }
            catch 
           { 
               Console.WriteLine("Exception in BadAsync");
            } 
       }
    }

Cancelling an async Operation :

You can cancel your own async operation.There are two classes in the System.Threading.Tasks namespace that are designed for this purpose: Cancellation Token and CancellationTokenSource.

• A Cancellation Token object contains the information about whether a task should be canceled or not.
• A task that has a CancellationToken object needs to periodically inspect it to see what the token’s state is. If the CancellationToken object’s
  Is Cancellation Requested property is set to true, the task should halt its operations and return.
• A Cancellation Token is nonreversible and can only be used once. That is, once it’s IsCancellationRequested property is set to true, it can’t be changed.
• A CancellationTokenSource object creates a CancellationToken object, which can then be given to various tasks. Any objects holding a cancellationTokenSource can call its Cancel method, which sets the CancellationToken’s IsCancellationRequested property to true.

class Program
    {
        static void Main()
        {
            CancellationTokenSource cts = new CancellationTokenSource();
            CancellationToken token = cts.Token;
            MyClass mc = new MyClass();
            Task t = mc.RunAsync(token);
            //Thread.Sleep( 3000 ); // Wait 3 seconds.
            //cts.Cancel(); //cancel the operation.
            t.Wait();
            Console.WriteLine("Was Cancelled: {0}", token.IsCancellationRequested);
        }
    }
    class MyClass
    {
        public async Task RunAsync(CancellationToken ct)
        {
            if (ct.IsCancellationRequested)
                return;
            await Task.Run(() => CycleMethod(ct), ct);
        }
        void CycleMethod(CancellationToken ct)
        {
            Console.WriteLine("Starting CycleMethod");
            const int max = 5;
            for (int i = 0; i < max; i++)
            {
                if (ct.IsCancellationRequested) // Monitor the CancellationToken.
                    return;
                Thread.Sleep(1000);
                Console.WriteLine(" {0} of {1} iterations completed", i + 1, max);
            }
        }
    }

Output:

Starting CycleMethod
1 of 5 iterations completed
2 of 5 iterations completed
3 of 5 iterations completed
4 of 5 iterations completed
5 of 5 iterations completed
Was Cancelled: False

If you uncomment the Thread.Sleep and Cancel statements in method Main, the task is canceled after three seconds and below is the output:

await taskStarting CycleMethod 1 of 5 iterations completed2 of 5 iterations completed3 of 5 iterations completedWas Cancelled: True

Waiting Asynchronously for Tasks in the async Method:

In your async method, if you want to wait on Tasks as your await expression. This allows your async method to return to the calling method, but allows the async method to wait for completion of one or all of a set of tasks. The calls that allow this are the Task.WhenAll and Task.WhenAny methods.

Output:

class MyDownloadString
    {
        public void DoRun()
        {
            Task<int> t = CountCharactersAsync("http://www.csharpstar.com", "http://www.techkatak.com");
            Console.WriteLine("DoRun: Task {0}Finished", t.IsCompleted ? "" : "Not ");
            Console.WriteLine("DoRun: Result = {0}", t.Result);
        }
        private async Task<int> CountCharactersAsync(string site1, string site2)
        {
            WebClient wc1 = new WebClient();
            WebClient wc2 = new WebClient();
            Task<string> t1 = wc1.DownloadStringTaskAsync(new Uri(site1));
            Task<string> t2 = wc2.DownloadStringTaskAsync(new Uri(site2));
            List<Task<string>> tasks = new List<Task<string>>();
            tasks.Add(t1);
            tasks.Add(t2);
            await Task.WhenAll(tasks);
            Console.WriteLine(" CCA: T1 {0}Finished", t1.IsCompleted ? "" : "Not ");
            Console.WriteLine(" CCA: T2 {0}Finished", t2.IsCompleted ? "" : "Not ");
            return t1.IsCompleted ? t1.Result.Length : t2.Result.Length;
        }
    }
    class Program
    {
        static void Main()
        {
            MyDownloadString ds = new MyDownloadString();
            ds.DoRun();
        }
    }

DoRun: Task Not Finished
CCA: T1 Finished
CCA: T2 Finished
DoRun: Result = 105212 

async keyword in C#

What is an async Method?

An async method is a method that returns to the calling method before completing all its work, and then completes its work while the calling method continues its execution.

An async method has the following characteristics:

• An async method must have the async keyword in its method header, and it must be before the return type.
• This modifier doesn’t do anything more than signal that the method contains one or more await expressions.
• It contains one or more await expressions. These expressions represent tasks that can be done asynchronously.
• It must have one of the following three return types.
  − void :If the calling method just wants the async method to execute, but doesn’t need any further interaction with it
  − Task : If the calling method doesn’t need a return value from the async method, but needs to be able to check on the async method’s state
  − Task<T> :If the calling method is to receive a value of type T back from the call, the return type of the async method must be Task
• An async method can have any number of formal parameters of any types but it cannot be out or ref parameters.
• The name of an async method should end with the suffix Async.
• Otherthan Methods, lambda expressions and anonymous methods can also act as async objects.

Using an async method that returns a Task<int> object:

class Program
  {
      static void Main()
      {
          Task<int> value = DoAsyncWork.CalculateSumAsync(10, 11);
          //Do Other processing
          Console.WriteLine("Value: {0}", value.Result);      
      }
  }

    static class DoAsyncWork
    {
        public static async Task<int> CalculateSumAsync(int i1,int i2)
        {
            int sum = await Task.Run(() => GetSum(i1,i2));
            return sum;
        }

        private static int GetSum(int i1, int i2)
        {
        return i1+i2;
        }
    }

Output:

Using an async method that returns a Task object:

class Program
  { 
     static void Main()
      {  
        Task value = DoAsyncWork.CalculateSumAsync(10, 11); 
         //Do Other processing 
         value.Wait();  
        Console.WriteLine("Async stuff is done"); 
              } 
 }    
 static class DoAsyncWork 
   { 
       public static async Task CalculateSumAsync(int i1,int i2)
         { 
           int sum = await Task.Run(() => GetSum(i1,i2));
            Console.WriteLine("Value: {0}", sum);
        }
         private static int GetSum(int i1, int i2)
        { 
       return i1+i2;
        } 
   }

 Value: 21

Async stuff is done

Using an async method that returns a void object:

class Program
  {
      static void Main()
      {
           DoAsyncWork.CalculateSumAsync(10, 11);
          //Do Other processing
           Thread.Sleep(200);
          Console.WriteLine("Program Exiting");      
      }
  }

    static class DoAsyncWork
    {
        public static async void CalculateSumAsync(int i1,int i2)
        {
            int sum = await Task.Run(() => GetSum(i1,i2));
            Console.WriteLine("Value: {0}", sum);
        }

        private static int GetSum(int i1, int i2)
        {
        return i1+i2;
        }
    }

 Output:

Value: 21
Program Exiting

The Flow of Control in an Async Method :

The body of an async method has divided mainly into three sections.

• Before the first await expression : This includes all the code at the beginning of the method up until the first await expression. This section contains very minimal code that doesn’t require too much processing.
• The await expression: This expression represents the task to be performed asynchronously.
• Continuation: This is the rest of the code in the method, following the await expression. This includes the information about which thread it’s on, the values of the variables currently in scope, and other things it’ll need in order to resume execution later, after the await expression completes

msdb.dbo.sp_send_dbmail

Sends an e-mail message to the specified recipients. The message may include a query result set, file attachments, or both. When mail is successfully placed in the Database Mail queue, sp_send_dbmail returns the mailitem_id of the message. This stored procedure is in the msdb database.

Syntax

sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]  
    [ , [ @recipients = ] 'recipients [ ; ...n ]' ]  
    [ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]  
    [ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]  
    [ , [ @from_address = ] 'from_address' ]  
    [ , [ @reply_to = ] 'reply_to' ]   
    [ , [ @subject = ] 'subject' ]   
    [ , [ @body = ] 'body' ]   
    [ , [ @body_format = ] 'body_format' ]  
    [ , [ @importance = ] 'importance' ]  
    [ , [ @sensitivity = ] 'sensitivity' ]  
    [ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]  
    [ , [ @query = ] 'query' ]  
    [ , [ @execute_query_database = ] 'execute_query_database' ]  
    [ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]  
    [ , [ @query_attachment_filename = ] query_attachment_filename ]  
    [ , [ @query_result_header = ] query_result_header ]  
    [ , [ @query_result_width = ] query_result_width ]  
    [ , [ @query_result_separator = ] 'query_result_separator' ]  
    [ , [ @exclude_query_output = ] exclude_query_output ]  
    [ , [ @append_query_error = ] append_query_error ]  
    [ , [ @query_no_truncate = ] query_no_truncate ]   
    [ , [ @query_result_no_padding = ] @query_result_no_padding ]   
    [ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]  

Arguments

[ @profile_name= ] 'profile_name'
Is the name of the profile to send the message from. The profile_name is of type sysname, with a default of NULL. The profile_name must be the name of an existing Database Mail profile. When no profile_name is specified, sp_send_dbmail uses the default private profile for the current user. If the user does not have a default private profile, sp_send_dbmail uses the default public profile for the msdb database. If the user does not have a default private profile and there is no default public profile for the database, @profile_name must be specified.

[ @recipients= ] 'recipients'
Is a semicolon-delimited list of e-mail addresses to send the message to. The recipients list is of type varchar(max). Although this parameter is optional, at least one of @recipients, @copy_recipients, or @blind_copy_recipients must be specified, or sp_send_dbmail returns an error.

[ @copy_recipients= ] 'copy_recipients'
Is a semicolon-delimited list of e-mail addresses to carbon copy the message to. The copy recipients list is of type varchar(max). Although this parameter is optional, at least one of @recipients, @copy_recipients, or @blind_copy_recipients must be specified, or sp_send_dbmail returns an error.

[ @blind_copy_recipients= ] 'blind_copy_recipients'
Is a semicolon-delimited list of e-mail addresses to blind carbon copy the message to. The blind copy recipients list is of type varchar(max). Although this parameter is optional, at least one of @recipients, @copy_recipients, or @blind_copy_recipients must be specified, or sp_send_dbmail returns an error.

[ @from_address= ] 'from_address'
Is the value of the 'from address' of the email message. This is an optional parameter used to override the settings in the mail profile. This parameter is of type varchar(MAX). SMTP security settings determine if these overrides are accepted. If no parameter is specified, the default is NULL.

[ @reply_to= ] 'reply_to'
Is the value of the 'reply to address' of the email message. It accepts only one email address as a valid value. This is an optional parameter used to override the settings in the mail profile. This parameter is of type varchar(MAX). SMTP security settings determine if these overrides are accepted. If no parameter is specified, the default is NULL.

[ @subject= ] 'subject'
Is the subject of the e-mail message. The subject is of type nvarchar(255). If no subject is specified, the default is 'SQL Server Message'.

[ @body= ] 'body'
Is the body of the e-mail message. The message body is of type nvarchar(max), with a default of NULL.

[ @body_format= ] 'body_format'
Is the format of the message body. The parameter is of type varchar(20), with a default of NULL. When specified, the headers of the outgoing message are set to indicate that the message body has the specified format. The parameter may contain one of the following values:

• TEXT
• HTML

Defaults to TEXT.

[ @importance= ] 'importance'
Is the importance of the message. The parameter is of type varchar(6). The parameter may contain one of the following values:

• Low
• Normal
• High

Defaults to Normal.

[ @sensitivity= ] 'sensitivity'
Is the sensitivity of the message. The parameter is of type varchar(12). The parameter may contain one of the following values:

• Normal
• Personal
• Private
• Confidential

Defaults to Normal.

[ @file_attachments= ] 'file_attachments'
Is a semicolon-delimited list of file names to attach to the e-mail message. Files in the list must be specified as absolute paths. The attachments list is of type nvarchar(max). By default, Database Mail limits file attachments to 1 MB per file.

[ @query= ] 'query'
Is a query to execute. The results of the query can be attached as a file, or included in the body of the e-mail message. The query is of type nvarchar(max), and can contain any valid Transact-SQL statements. Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.

[ @execute_query_database= ] 'execute_query_database'
Is the database context within which the stored procedure runs the query. The parameter is of type sysname, with a default of the current database. This parameter is only applicable if @query is specified.

[ @attach_query_result_as_file= ] attach_query_result_as_file
Specifies whether the result set of the query is returned as an attached file. attach_query_result_as_file is of type bit, with a default of 0.

When the value is 0, the query results are included in the body of the e-mail message, after the contents of the @body parameter. When the value is 1, the results are returned as an attachment. This parameter is only applicable if @query is specified.

[ @query_attachment_filename= ] query_attachment_filename
Specifies the file name to use for the result set of the query attachment. query_attachment_filename is of type nvarchar(255), with a default of NULL. This parameter is ignored when attach_query_result is 0. When attach_query_result is 1 and this parameter is NULL, Database Mail creates an arbitrary filename.

[ @query_result_header= ] query_result_header
Specifies whether the query results include column headers. The query_result_header value is of type bit. When the value is 1, query results contain column headers. When the value is 0, query results do not include column headers. This parameter defaults to 1. This parameter is only applicable if @query is specified.

[ @query_result_width = ] query_result_width
Is the line width, in characters, to use for formatting the results of the query. The query_result_width is of type int, with a default of 256. The value provided must be between 10 and 32767. This parameter is only applicable if @query is specified.

[ @query_result_separator= ] 'query_result_separator'
Is the character used to separate columns in the query output. The separator is of type char(1). Defaults to ' ' (space).

[ @exclude_query_output= ] exclude_query_output
Specifies whether to return the output of the query execution in the e-mail message. exclude_query_output is bit, with a default of 0. When this parameter is 0, the execution of the sp_send_dbmail stored procedure prints the message returned as the result of the query execution on the console. When this parameter is 1, the execution of the sp_send_dbmail stored procedure does not print any of the query execution messages on the console.

[ @append_query_error= ] append_query_error
Specifies whether to send the e-mail when an error returns from the query specified in the @query argument. append_query_error is bit, with a default of 0. When this parameter is 1, Database Mail sends the e-mail message and includes the query error message in the body of the e-mail message. When this parameter is 0, Database Mail does not send the e-mail message, and sp_send_dbmail ends with return code 1, indicating failure.

[ @query_no_truncate= ] query_no_truncate
Specifies whether to execute the query with the option that avoids truncation of large variable length data types (varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, and user-defined data types). When set, query results do not include column headers. The query_no_truncate value is of type bit. When the value is 0 or not specified, columns in the query truncate to 256 characters. When the value is 1, columns in the query are not truncated. This parameter defaults to 0.

[ @query_result_no_padding ] @query_result_no_padding
The type is bit. The default is 0. When you set to 1, the query results are not padded, possibly reducing the file size.If you set @query_result_no_padding to 1 and you set the @query_result_width parameter, the @query_result_no_padding parameter overwrites the @query_result_width parameter.

In this case no error occurs.

If you set the @query_result_no_padding to 1 and you set the @query_no_truncate parameter, an error is raised.

[ @mailitem_id= ] mailitem_id [ OUTPUT ]
Optional output parameter returns the mailitem_id of the message. The mailitem_id is of type int.

Ref:- https://msdn.microsoft.com/en-in/library/ms190307.aspx

REST Design Principles

Everything is a Resource

• Everything that needs to be identified on web should be treated as Resource and be uniquely identified.
•  Resource is usually Noun and not verb
•  Each piece of data available on the Internet has a format that could be described by a content type
• Example: “http://Csharpstar.com/Employee/1234

Each resource is identifiable by a unique identifier (URI)

• The Internet contains so many different resources, they all should be accessible via URIs and should be identified uniquely
• The URI keeps the data self-descriptive and eases further development on it.
• The URIs helps you to reduce the risk of logical errors in your programs to a minimum.
• URIs expose different types of resources in a straightforward manner
• Example:

    – Images (http://Csharpstar.com/Images)  
    – Videos (http://Csharpstar.com/Videos)  
    – XML documents (http://Csharpstar.com/Archieves)

Use the standard HTTP methods

   Use standard Http methods to operate on identified resources
    – Get : Request an existing Resource
    – Post :Update an existing Resource
    – Put : Create or Update a Resource
    – Delete:Remove/Delete a Resource

Allow multiple representations for same Resource

•  A key feature of a resource is that they may be represented in a different form than the one it is stored. so, it can be requested or posted in different representations.As long as the specified format is supported, the REST-enabled endpoint should use it
• The resource should be presented to client in multiple formats as the client desire. The format can be anything like XML,JSON,JPG,PDF,HTML etc..

Communication should be always stateless   

• REST services are stateless by nature. So, the clients state can not be stored on the server.However, the client can still maintain state and pass it to the service with each request
• In a production environment, it is likely that incoming requests are served by a load balance, ensuring availability and high availability. Once exposed via a load balance, the idea of keeping your application state at server side gets compromised. This doesn’t mean that you are not allowed to keep the state of your application. It just means that you should keep it in a RESTful way.

C# 7.0

Visual studio 2017 RC which was released on November 16, 2016. Let us see some of the new features of C# 7.0, which is the default language of this version.

the new C# 7.0 features coming down the pipeline. Although C# 7.0 is still in development, most of the new features noted by Microsoft have been enabled in Visual Studio 15 Preview 5.

According to Mads Torgersen, Microsoft program manager for C#; the biggest features are tuples, which make it easy to have multiple results, and pattern matching, which simplifies code that is conditional on the shape of data.

Current features and beyond can be followed at Roslyn on GitHub.

Some of the new features in C# 7.0 are

• Out variables
• Pattern matching
• Tuples
• Deconstruction
• Local functions
• Ref returns and locals
• Generalized async return types