Iterate through a result set using Cursor or While Loop

There are different ways in which you can looping records of your table I just share some examples, Let you decide which fit best in you scenario.

DECLARE @Customer_id numeric
DECLARE @cur_emp as CURSOR;

SET @cur_emp = CURSOR FORWARD_ONLY FOR
Select  Coin_ID From test_auto

OPEN @cur_emp;
FETCH NEXT FROM @cur_emp INTO @Customer_id;
 WHILE @@FETCH_STATUS = 0
BEGIN
      print @Customer_id
     FETCH NEXT FROM @cur_emp INTO @Customer_id;
END
CLOSE @cur_emp;
DEALLOCATE @cur_emp;


GO

DECLARE @Customer_id numeric
DECLARE @cur_emp as CURSOR;

SET @cur_emp = CURSOR FAST_FORWARD  FOR
Select  Coin_ID From test_auto

OPEN @cur_emp;
FETCH NEXT FROM @cur_emp INTO @Customer_id;
 WHILE @@FETCH_STATUS = 0
BEGIN
      print @Customer_id
     FETCH NEXT FROM @cur_emp INTO @Customer_id;
END
CLOSE @cur_emp;
DEALLOCATE @cur_emp;


GO

DECLARE @Customer_id numeric
 DECLARE cur_emp CURSOR
STATIC FOR
Select  Coin_ID From test_auto
OPEN cur_emp
IF @@CURSOR_ROWS > 0
 BEGIN
 FETCH NEXT FROM cur_emp INTO @Customer_id
 WHILE @@Fetch_status = 0
 BEGIN
 print @Customer_id

 FETCH NEXT FROM cur_emp INTO @Customer_id
 END
END
CLOSE cur_emp
DEALLOCATE cur_emp
SET NOCOUNT OFF

GO


DECLARE @count INT
 DECLARE @row INT
 Declare @Coin_ID int
 SET @row = 1
 SELECT @count = COUNT(Coin_ID) FROM test_auto
 WHILE (@row <= @count)
 BEGIN
     Select  @Coin_ID = Coin_ID From test_auto

    --Do some processing here
print @Coin_ID
 SET @row += 1
END


Refer links:
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-transact-sql?view=sql-server-2017
https://sqlperformance.com/2012/09/t-sql-queries/cursor-options
https://www.techrepublic.com/blog/the-enterprise-cloud/comparing-cursor-vs-while-loop-performance-in-sql-server-2008/
http://stevestedman.com/2015/03/simple-cursor-example-forward_only-vs-fast-forward/

Insert Bulk data in to table in .NET

In the last post I discuss how we insert bulk data by using SQL Server you can find here but there is an issue with that approach it only if your comma separated file exist on that SQL Server machine.
Let suppose I have a requirement in which user upload a comma separated text file from website and I need to insert that file in to SQL table  what I am facing right now my IIS Server and my Database server both are separate machine so in this case my previous post was not useful in this case .
I have a same file format which is

Coin_ID | Customer_ID | Coin_Address | BitCoins | Status_ID | Is_Active | Created_By | Updated_By | Updated_Date | Transaction_ID | Transfer_Type


7635 | 209285 | 1HqrLzm2jUqcNGi4YQtMstUcadNBzfuz39 | 0.019 | 4 | 1 | Finance | Finance | 06/08/2018 02:31:54 PM | 334fb8d949d4f2783060cac4ac238e13bb12gfgf3ce3c4943d9f8f5639e88 | 1

there are thirty thousand record if one record at a time using a SqlCommand along with a INSERT INTO statement is very costly and slow. You need an efficient solution to insert large number of records quickly thank God in .net SqlBulkCopy we can use this to fix this issue .

This is the main method that will insert in to table
   Using con As New SqlConnection("connectionstring")
            con.Open()
            Using sqlTransaction As SqlTransaction = con.BeginTransaction()
                Using sqlBulkCopy As New SqlBulkCopy(con, SqlBulkCopyOptions.Default, sqlTransaction)
                    sqlBulkCopy.DestinationTableName = "test_auto"
                    Try
                        sqlBulkCopy.WriteToServer(dt1) ' Pass datatable in to that
                        sqlTransaction.Commit()
                    Catch
                        sqlTransaction.Rollback()
                    End Try
                End Using
            End Using
            con.Close()
        End Using

Now I am sharing my whole code .

    Dim sValues As String()
        Dim lines As String()
 ' Read a textfile from that path
        lines = System.IO.File.ReadAllLines("C:\testinglargedate.txt")

' declare all my column that I previously share my table format
        Dim dt1 = New DataTable()
        dt1.Columns.Add("Coin_ID")
        dt1.Columns.Add("Customer_ID")
        dt1.Columns.Add("Coin_Address")
        dt1.Columns.Add("BitCoins")
        dt1.Columns.Add("Status_ID")
        dt1.Columns.Add("Is_Active")
        dt1.Columns.Add("Created_By")
        dt1.Columns.Add("Updated_By")
        dt1.Columns.Add("Updated_Date")
        dt1.Columns.Add("Transaction_ID")
        dt1.Columns.Add("Transfer_Type")

        Dim Coin_ID As Integer
        Dim Customer_ID As Integer
        Dim Coin_Address As String
        Dim BitCoins As Decimal
        Dim Status_ID As Integer
        Dim Is_Active As Integer
        Dim Created_By As String
        Dim Updated_By As String
        Dim Updated_Date As DateTime
        Dim Transaction_ID As String
        Dim Transfer_Type As String

'Looping of each row and skipping the header the fill the values accordingly i Just add one for sample
        For Each sLine As String In lines
                sLine = Trim(sLine.TrimEnd())
            sValues = sLine.Split(" | ")
            If sValues.Length >= 11 AndAlso (sValues(0) <> "Coin_ID " And sValues(0) <> "Coin_ID") Then  'Checking NULL and Skipping the Header Row



                If Not String.IsNullOrEmpty(Convert.ToString(sValues(0))) Then
                    Coin_ID = Convert.ToInt32(sValues(0))
                Else
                    Coin_ID = Convert.ToInt32(0)
                End If

             

                dt1.Rows.Add(Coin_ID, Customer_ID, Coin_Address, BitCoins, Status_ID, Is_Active, Created_By, Updated_By,Updated_Date , Transaction_ID, Transfer_Type)



            End If

        Next



   Using con As New SqlConnection("connectionstring")
            con.Open()
            Using sqlTransaction As SqlTransaction = con.BeginTransaction()
                Using sqlBulkCopy As New SqlBulkCopy(con, SqlBulkCopyOptions.Default, sqlTransaction)
                    sqlBulkCopy.DestinationTableName = "test_auto"
                    Try
                        sqlBulkCopy.WriteToServer(dt1) ' Pass datatable in to that
                        sqlTransaction.Commit()
                    Catch
                        sqlTransaction.Rollback()
                    End Try
                End Using
            End Using
            con.Close()
        End Using

Refer link :
Great Help

Bulk Insert in SQL (Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server)

I was working on one task in which I need to insert thousands of record  in database . I have a txt file in which pipe separate values are there, My file format is like this

Coin_ID | Customer_ID | Coin_Address | BitCoins | Status_ID | Is_Active | Created_By | Updated_By | Updated_Date | Transaction_ID | Transfer_Type


7635 | 209285 | 1HqrLzm2jUqcNGi4YQtMstUcadNBzfuz39 | 0.019 | 4 | 1 | Finance | Finance | 06/08/2018 02:31:54 PM | 334fb8d949d4f2783060cac4ac238e13bb12gfgf3ce3c4943d9f8f5639e88 | 1


One thing bulk will not support decimal values so the table you make that column as float

and number of columns should be the same as in csv and in table otherwise you get an error check last link

So my table format is like this

CREATE TABLE [dbo].[test_auto](
[Coin_ID] [int] NULL,
[Customer_ID] [varchar](50) NULL,
[Coin_Address] [varchar](100) NULL,
[BitCoins] float NULL,
[Status_ID] [int] NULL,
[Is_Active] [int] NULL,
[Created_By] [varchar](50) NULL,
[Updated_By] [varchar](50) NULL,
[Updated_Date] [datetime] NULL,
[Transaction_ID] [varchar](100) NULL,
[Transfer_Type] [int] NULL
) ON [PRIMARY]
GO

There are two ways you can make SP and pass the value or if you are using only one time then use the normal script . i will share both of them

Simple Script :

BULK
INSERT test_auto
FROM 'C:OutputFile_08062018143155.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n',
FIRSTROW=2

)
let me explain line by line
I have create a table with the name of test_auto in which i have to insert my all values after that i define my path where is my file located.Third thing FIELDTERMINATOR  is a parameter in which you define what value separate the two words right now mine was pipe sign you can use what ever you want. ROWTERMINATOR  is end of the row .FIRSTROW is  which line you want to read my first row is my heading so I want to start from second row.

Store Procedure:

CREATE PROC BulkInserttest (@FilePath VARCHAR(100), @FileFieldterminator VARCHAR(5))
AS
BEGIN
SET NOCOUNT ON;
--Truncate the existing data from table
TRUNCATE TABLE dbo.test_auto
--Declare the local variable
DECLARE @bulkinsert NVARCHAR(2000)

SET @bulkinsert = 
   'BULK INSERT dbo.test_auto
   FROM ''' +  @FilePath + '''
   WITH
   (
    FIRSTROW = 2,
    FIELDTERMINATOR = '''+ @FileFieldterminator +''',
    ROWTERMINATOR = ''\n''
   )'
--Execute the BULK INSERT statement    
EXEC (@bulkinsert)
--Print the number of rows affected
PRINT CAST(@@ROWCOUNT AS VARCHAR(5))+ ' Rows Inserted'
RETURN (@@ROWCOUNT)
--The number of rows affected by a Transact-SQL statement will return thereafter
SET NOCOUNT OFF;
END
GO

Now if you want to run this :

exec BulkInserttest  'C:OutputFile_08062018143155.txt','|'

Refer links:
Other Parameters
Help
Bulk Insert with Decimal values

https://stackoverflow.com/questions/5475729/bulk-upload-unexpected-end-of-file-on-new-server

Fancy Box-Refresh parent page and close iframe popup from Server Side(C# and Vb.net)

I have a scenario in which i need to close Popup forcefully when the user hit save or reject button and refresh the parent page.
just add this line on button click

//parent.showloader() is my loder function in my main page to show loading image

ClientScript.RegisterStartupScript([GetType](), "Javascript", "parent.$.fancybox.close(); parent.showloader(); parent.location.reload(true);", True)

SQL Server Store Procedure Log

I was currently going through one process in which i need to fix some urgent issues. As a developer we normally use error log in our server side code but what if there some Database logging  as well.
let suppose your end user face any issue then you need to replicate the same scenario again by connecting you code with database and passing the same arguments it is time consuming obviously.
So i was thinking if i know the exact parameter list of user and at what line database error occur so I can fix easily and quickly .
So I create a table structure like this you can add more columns if you like but for me its quite enough to  rectify the issue.

Table Structure:

CREATE TABLE [StoreProcedureLog](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[EntryDatetime] [datetime] NULL,
[ErrorLine] [int] NULL,
[ErrorMessage] [nvarchar](1000) NULL,
[ProcedureName] [varchar](1000) NULL,
[AdditionalInfo] [varchar](1000) NULL,
[Status] [int] NULL,
 CONSTRAINT [PK_StoreProcedureLog] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [StoreProcedureLog] ADD  CONSTRAINT [DF_StoreProcedureLog_EntryDatetime]  DEFAULT (getdate()) FOR [EntryDatetime]
GO
ALTER TABLE [StoreProcedureLog] ADD  CONSTRAINT [DF_StoreProcedureLog_Status]  DEFAULT ((1)) FOR [Status]
GO

let me explain column one by one
ID = Auto Increment Column
EntryDatetime = It give you idea at what time error occur and no need to write insert for this as i set default value is Getdate()
ErrorLine = It will tell you the exact line number of error
ErrorMessage = It will tell you the error message
ProcedureName = It will tell you the name of procedure
AdditionalInfo = Pass extra information for this I normally pass my parameters list so I can know what was the exact scenario
Status= I set default value is 1 ( I use this when I fix the issue I marked that status =0 Its mean i solve that issue)

Store Procedure :

CREATE PROCEDURE Insertsplog @ERROR_PROCEDURE NVARCHAR(max) = NULL,
                            @AdditionalInfo  NVARCHAR(max) = NULL
AS
  BEGIN
      --SET NOCOUNT ON;
      IF EXISTS (SELECT 1
                 FROM   information_schema.tables
                 WHERE  table_name = 'StoreProcedureLog')
        BEGIN
            INSERT INTO storeprocedurelog
                        (errorline,
                         errormessage,
                         procedurename,
                         additionalinfo)
            VALUES      (Error_line(),
                         Error_message(),
                         @ERROR_PROCEDURE,
                         @AdditionalInfo);
        END

  END

Calling SP:

CREATE PROC Sp
AS
  BEGIN
      BEGIN try
       -- Your Sp logic here
      END try
      BEGIN catch
          IF EXISTS (SELECT 1
                     FROM   information_schema.routines
                     WHERE  routine_name = 'InsertSpLog')
            BEGIN
                DECLARE @ERROR_PROCEDURE NVARCHAR(1000);
DECLARE @Comments NVARCHAR(1000)
                 SET  @Comments='Id: ' +CONVERT(varchar(10), @Id) -- Passing Information so easy to find the root cause for crashing the store procedure

                SET @ERROR_PROCEDURE=Error_procedure(); -- it will pass the Store Proc name

                --EXEC Insertsplog  @ERROR_PROCEDURE -- Passing without Comments
         EXEC Insertsplog  @ERROR_PROCEDURE,@Comments -- Passing with Comments
            END
      END catch

  END

This was the following result

ASP.NET Session Timeout

In web.config file add this line of code
<configuration>
  <system.web>
   <sessionState timeout="90" />  <!--90 minutites-->
  </system.web>
</configuration>

Reference link 

Generate Random String in C# and Vb.net

In this method just pass the length , it will generate a random string.

VB.net :

    Public Function GetRandomString(ByVal length As Integer) As String
        'use the following string to control your set of alphanumeric characters to choose from
        'you could also include lowercase or punctuation too
        Const alphabet As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"


        Dim result As New StringBuilder(length)


        ' Random is not truly random,
        ' so we try to encourage better randomness by using a seed value
        Static rnd As New Random(Convert.ToInt32(DateTime.Now.Ticks And Integer.MaxValue))

        Dim prevChar As String = String.Empty
        Dim nextChar As String
        Do While result.Length < length
            nextChar = alphabet.Substring(rnd.[Next](0, alphabet.Length), 1)
            If nextChar.Equals(prevChar) = False Then
                result.Append(nextChar)
                prevChar = nextChar
            End If
        Loop
        Return result.ToString
    End Function


C#

   private static Random random = new Random();
        public static string GetRandomString(int length)
        {
            const string chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
            return new string(Enumerable.Repeat(chars, length)
              .Select(s => s[random.Next(s.Length)]).ToArray());
        }