How to split values in sql server?

 Here i am showing how to split values in Sql server as Split function did  in c#

Why should we take some string value from a table and split that on our C# programming why not in the SQL programming? so many times we prefer to save a One to Many relation type with a single field in a SQL db table by concatenating the selected item with a separator like"1,4,8,7". But as we know SQL don't have a function like C# has Split(). So many programmer takes that value from db and split it in the C# code. But i am not happy with that as always going for better option and i implement this Function for that and thanks god its works..

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create function [dbo].[fnSplit](
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
--select * from dbo.fnSplit('1,2,34,5', ',') where item=34
--select * from dbo.fnSplit('1,2,34,5', ',')

Hope this will helps you tooooo :)

How to Reseed Indentyty In SQL Server 2008

When we delete a table in sql server db all values are deleted but the Seed set to the last identity.Means if there was 50 fields in that table and and the last identity was 50 with identity increment with 1 and here we delete the table but if we insert again in this table the identity will be 51 instead on 1. If you wants the identity to be one then you need to reseed the identity to 0 so the next identity will be 1.
 Note : This is the main difference between the TRUNCATE table and DELETE table. TRUNCATE reset the table definition after deleting all fields where DELETE only remove table values

Here is the example to reseed the tbl_SubModel seed to 0

DBCC CHECKIDENT ("dbo.tbl_SubModel", RESEED, 0);

How to Load CSV file to datatable and insert into the database in C#?

Here I'm showing how to load CSV file to DataTable and insert into the databse in C#.
For this you have to choose the csv file formate same as your sql db Destination Table
with the header name and header name aslo need to match with the sql table too. One thing
if you have bit coulmn in sql db table then chnage that data as 0 or 1 in the source csv file

Here GetDataTableFromCSV function convert the CSV file to a datatable and WriteDataTableToTheDatabase
function is for saving the datatable to the server as it's name suggest :)

How to call?
string strNewFilename = Server.MapPath("~/Temp/") + file.FileName;
DataTable dtReaderDescription = GetDataTableFromCSV(strNewFilename);
if(dtReaderDescription.Rows.Count>0)
WriteDataTableToTheDatabase(dtReaderDescription, strNewFilename);

public DataTable GetDataTableFromCSV(string strFileName)
        {
            try
            {
                System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(strFileName) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\"");
                conn.Open();
                string strQuery = "Select * from [" + System.IO.Path.GetFileName(strFileName) + "]";
                System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn);
                System.Data.DataSet ds = new System.Data.DataSet();
                da.Fill(ds);
                return ds.Tables[0];
            }
            catch (Exception ex) { }
            return new DataTable();
        }

================================================
private void WriteDataTableToTheDatabase(DataTable dtTosave,string fileToDelete)
        {
            try
            {
                string strConn = ConfigurationManager.ConnectionStrings["dbBulkCopy"].ConnectionString;
                using (SqlConnection sqlConnection =
                        new SqlConnection(strConn ))
                {
                       SqlBulkCopy bulkCopy =
                        new SqlBulkCopy
                        (
                        sqlConnection ,
                        SqlBulkCopyOptions.TableLock |
                        SqlBulkCopyOptions.FireTriggers |
                        SqlBulkCopyOptions.UseInternalTransaction,
                        null
                        );
                    bulkCopy.DestinationTableName = "ReaderDescription";
                    connection.Open();
            bulkCopy.WriteToServer(dtTosave);
                    connection.Close();

                    try
                    {
                        FileSystemProxy FileSystem = new Microsoft.VisualBasic.Devices.Computer().FileSystem;
                        FileSystem.DeleteFile(fileToDelete);
                        FileSystem = null;
                    }
                    catch (Exception ex) { }
                }
            }
            catch (Exception ex) { }
                  
        }


Cheers!