Split string function in SQL Server to break Comma separated strings

Sometimes we need to insert – save multiple checked checkbox values in SQL Server.For that we can use Split function.

Split function of SQL Server is not in-built function,We have to add it manually in our Table-valued function as you can see in below image.

Split function in SQL Server

Below you can find query for splitting the string in SQL.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[Split]   (   @RowData nvarchar(MAX), @SplitOn nvarchar(5) )     RETURNS @ReturnValue TABLE   (Data NVARCHAR(MAX))   AS BEGIN Declare @Counter int Set @Counter = 1 While (Charindex(@SplitOn,@RowData)>0) Begin     Insert Into @ReturnValue (data)     Select Data =       ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))   Set @RowData =       Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))   Set @Counter = @Counter + 1   End Insert Into @ReturnValue (data)   Select Data = ltrim(rtrim(@RowData))   Return   END

Now,I will explain how to use this split function.Once you have created above split function in your database,It will add to Table-valued function.

Now whenever you want to separate string by comma,just pass your string to the function,it will return Data column.

Here’s I will use my table that I created in Case Statement in SQL Server.You can find create,insert script for Product table from that article.On select of product table,you will find below image.

Note : To use this tutorial,allow NULL values to Price & Availability columns.

Now,here I will pass ProductNames variable as comma separated string,in your case it may be checked items or anything else.But to use split function of this SQL server,you need to pass comma separated string to function.

I will add MotherBoard & other 2 products in table using below script.You can also make stored procedure according to your requirement.

1 2 3 4

DECLARE @ProductNames Varchar(MAX) SET @ProductNames='MotherBoard,Graphics Card,HardDisk' INSERT INTO dbo.product(ProductName) SELECT Data FROM dbo.Split(@ProductNames,',')

Now after running above script,you can find below image Product table as 3 rows inserted in Product table.

This is how you can save multiple checked checkbox values tho SQL Server table.

Hope,you enjoy this article.You can also check other SQL Tutorial.

Second WAY:

Introduction: 

Here I will explain simple split function in SQL Server to split comma separated string into table values inSQL Server database or How to split comma separated string with custom split() function in SQL Server. 

To split comma separated string in SQL Server we need to write custom method for that we need to create one function like as shown below

Custom Split function to split comma separated string into table

CREATE FUNCTION dbo.Split(@String nvarchar(4000), @Delimiter char(1)) RETURNS @Results TABLE (Items nvarchar(4000)) AS BEGIN DECLARE @INDEX INT DECLARE @SLICE nvarchar(4000) -- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z --     ERO FIRST TIME IN LOOP SELECT @INDEX = 1 WHILE @INDEX !=0 BEGIN -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER SELECT @INDEX = CHARINDEX(@Delimiter,@STRING) -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE IF @INDEX !=0 SELECT @SLICE = LEFT(@STRING,@INDEX - 1) ELSE SELECT @SLICE = @STRING -- PUT THE ITEM INTO THE RESULTS SET INSERT INTO @Results(Items) VALUES(@SLICE) -- CHOP THE ITEM REMOVED OFF THE MAIN STRING SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX) -- BREAK OUT IF WE ARE DONE IF LEN(@STRING) = 0 BREAK END RETURN END

Once we create custom function Split() run sample query like as shown below

SELECT items FROM [dbo].[Split] ('861,739,10,1670', ',')

Once we run above query we will get output like as shown below

creating a simple WCF secure service

This article discusses the simplest way to write, configure and consume Windows Communication Foundation (WCF) service, using Visual Studio 2010.

Problem Statement

Assume that ZYZ Inc is one of the leading mobile menufacturing companies in India. Currently ABC Inc uses an XML based web service that provides the details of the various mobile models available for sale. The management of the ZYZ Inc wants their Web service to accommodate features such as reliable messaging, transactions, security and this web service should be accessible from any application of all platforms.

The developers of ZYZ Inc know that they can fullfill all the above requirements by developing the service in WCF.

Prerequisite: Create tblProduct in SqlServer for this Demo.



Solution

Task 1: Creating a WCF Service
To create a WCF service you need to perform the following tasks:

1. Open Microsoft Visual Studio.
2. Select "File" - "New" - "Website". A dialog box appears, as shown in the following figure.
   
   
   
3. Select the WCF Service template under the Visual Studio installed templates section.
4. Click on the "OK" Button. The "App_Code/Service.cs" file appears, as shown in the following figure.
   
   
   
5. Add the following namespaces to the "App_Code/Service.cs" file:
   using System.Data;using System.Data.SqlClient;
    
6. Remove the following code snippet from the Service class:
   public string GetData(int value)
   {    return string.Format("You entered: {0}", value);}
   public CompositeType GetDataUsingDataContract(CompositeType composite)
   {    if (composite == null)
       {        throw new ArgumentNullException("composite");
       }    if (composite.BoolValue)
       {
           composite.StringValue += "Suffix";
       }    return composite;}
    
7. Open "App_Code/Iservice.cs" and remove the following highlighted code from the interface:
   [ServiceContract]public interface IService{
       [OperationContract]
       string GetData(int value);
       [OperationContract]
       CompositeType GetDataUsingDataContract(CompositeType composite);
       // TODO: Add your service operations here} // Use a data contract as illustrated in the sample below to add composite types to service operations.[DataContract]public class CompositeType{
       bool boolValue = true;
       string stringValue = "Hello ";
       [DataMember]
       public bool BoolValue
       {
           get { return boolValue; }
           set { boolValue = value; }
       }
       [DataMember]
       public string StringValue
       {
           get { return stringValue; }
           set { stringValue = value; }
       }}
    
8. Add the following namespace in the "App_Code/Iservice.cs" interface:
   using System.Data;
    
9. Type the following code snippet for the "Iservice" interface as shown in the following figure.
   
   
   
10. Type the following code for the "Service.cs" file as shown in the following figure.
    
    
    
11. Verifying WCF Service.
    
    Press F5 to run your service; you will get a browser window as shown in the following figure.
    
    

Task 2: Creating ASP.Net Client Application

1. Open Visual Studio then click on "File" - "New" - "Web Site" - "ASP.Net website".
2. Drag and drop a TextBox, Button and GrideView as shown in the following figure.
   
   
   
3. Switch to the Solution Explorer. Right-click on the root directory (the solution) and click on "Add Service Reference". Provide the WSDL file link in the Address TextBox, as shown in the following figure.
   
   
   
4. Click the "Ok" Button
5. For the Click event of the Show Button write the following code:
   int Id = Convert.ToInt32(TextBox1.Text);
   ServiceReference1.ServiceClient proxy = new ServiceReference1.ServiceClient();
   GridView1.DataSource = proxy.QueryProduct(Id).Tables[0];GridView1.DataBind();
    
6. Press F5 to run your ASP.Net application. Enter a product id in the TextBox and click on the Show Button. If everything goes well you will get the following output as shown in the following figure.
   
   

How to Create Windows Service in C# ?

I'm going to explain you , How will you can create simple windows service , Where You can do schedule activity.

Windows Services are applications that run in the background and perform various tasks. The applications do not have a user interface or produce any visual output. Windows Services are started automatically when computer is booted. They do not require a logged in user in order to execute and can run under the context of any user including the system. Windows Services are controlled through the Service Control Manager where they can be stopped, paused, and started as needed.


Step 1. Create Skeleton of the Service

To create a new Window Service, pick Windows Service option from your Visual C# Projects, give your service a name, and click OK.

Open visual studio --> Select File --> New -->Project--> select Windows Service

And give name as WinServiceSample (What ever your service name , Create with that name).

After give WinServiceSample name click ok button after create our project that should like this

In Solution explorer select Service1.cs file and change Service1.cs name to ScheduledService.cs because in this project I am using this name if you want to use another name for your service you should give your required name.

After change name of service open ScheduledService.cs in design view right click and select Properties now one window will open in that change Name value to ScheduledService and change ServiceName to ScheduledService. Check below properties window that should be like this

After change Name and ServiceName properties again open ScheduledService.cs in design view and right click on it to Add Installer files to our application.

The main purpose of using Windows Installer is an installation and configuration service provided with Windows. The installer service enables customers to provide better corporate deployment and provides a standard format for component management.

After click on Add installer a designer screen added to project with 2 controls: serviceProcessInstaller1 and ServiceInstaller1

Now right click on serviceProcessInstaller1 and select properties in that change Account to LocalSystem

After set those properties now right click on ServiceInstaller1 and change following StartType property to Automatic and give proper name for DisplayName property

After completion of setting all the properties now we need to write the code to run the windows services at scheduled intervals.

If you observe our project structure that contains Program.cs file that file contains Main() method otherwise write the Main() method like this in Program.cs file

 

 

/// <summary> /// The main entry point for the application. /// </summary> static void Main() { ServiceBase[] ServicesToRun; ServicesToRun = new ServiceBase[] { new ScheduledService() }; ServiceBase.Run(ServicesToRun); }

After completion of adding Main() method open ScheduledService.cs file and add following namespaces in codebehind of ScheduledService.cs file

using System.IO; using System.Timers;

If you observe code behind file you will find two methods those are

protected override void OnStart(string[] args) { } protected override void OnStop() { }

We will write entire code in these two methods to start and stop the windows service. Write the following code in code behind to run service in scheduled intervals

 

//Initialize the timer Timer timer = new Timer(); public ScheduledService() { InitializeComponent(); } //This method is used to raise event during start of service protected override void OnStart(string[] args) { //add this line to text file during start of service TraceService("start service"); //handle Elapsed event timer.Elapsed += new ElapsedEventHandler(OnElapsedTime); //This statement is used to set interval to 1 minute (= 60,000 milliseconds) timer.Interval = 90000; //enabling the timer timer.Enabled = true; } //This method is used to stop the service protected override void OnStop() { timer.Enabled = false; TraceService("stopping service"); } private void OnElapsedTime(object source, ElapsedEventArgs e) { TraceService("Another entry at "+DateTime.Now); } private void TraceService(string content) { //set up a filestream FileStream fs = new FileStream(@"d:\ScheduledService.txt",FileMode.OpenOrCreate, FileAccess.Write); //set up a streamwriter for adding text StreamWriter sw = new StreamWriter(fs); //find the end of the underlying filestream sw.BaseStream.Seek(0, SeekOrigin.End); //add the text sw.WriteLine(content); //add the text to the underlying filestream sw.Flush(); //close the writer sw.Close();  // Event Log  this.EventLog = new System.Diagnostics.EventLog(); this.EventLog.Source = this.ServiceName; this.EventLog.Log = "Application";   this.EventLog.WriteEntry("My Eventlog message.", EventLogEntryType.Information);   }

If you observe above code in OnStart method I written event ElapsedEventHandler this event is used to run the windows service for every one minute

After completion code writing build the application and install windows service. To install windows service check this post here I explained clearly how to install windows service and how to start windows service. 

Now the service is installed. To start and stop the service, go to Control Panel --> Administrative Tools --> Services.  Right click the service and select Start. 

Now the service is started, and you will be able to see entries in the log file we defined in the code.

Now open the log file in your folder that Output of the file like this