Reindex all table in Sql Database

 declare @tableName nvarchar(255)

declare myCursor CURSOR FOR

select TABLE_SCHEMA+'.'+TABLE_NAME

from INFORMATION_SCHEMA.TABLES

where TABLE_TYPE = 'base table'

open myCursor

Fetch next from myCursor into @tableName

While @@FETCH_STATUS = 0

Begin

print 'Working on: '+@tableName

DBCC DBREINDEX(@TableName,' ',90)

Fetch next from myCursor into @tableName

end

close myCursor

Deallocate myCursor

Import Excel data in DataTable in Asp.Net

Dim excelPath As String = Server.MapPath("~/") + Path.GetFileName(FileUpload1.PostedFile.FileName)

FileUpload1.SaveAs(excelPath)

Dim connString As String = String.Empty

Dim extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)

Select Case extension

    Case ".xls"

        'Excel 97-03

        connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & excelPath & ";Extended Properties='Excel 8.0'"

        Exit Select

    Case ".xlsx"

        'Excel 07 or higher

        connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & excelPath & ";Extended Properties='Excel 12.0 Xml;HDR=Yes'"

        Exit Select

End Select

Using excel_con As New OleDbConnection(connString)

    excel_con.Open()

    Dim sheet1 As String = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing).Rows(0)("TABLE_NAME").ToString()

    Dim dtExcelData As New DataTable()

    Using oda As New OleDbDataAdapter("SELECT * FROM [" & sheet1 & "]", excel_con)

        oda.Fill(dtExcelData)

    End Using

    excel_con.Close()

    Dim count As Int16

    count = dtExcelData.Columns.Count

    Dim conString As String = ConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString

    Using con As New SqlConnection(conString)

        Using sqlBulkCopy As New SqlBulkCopy(con)

            'Set the database table name

            sqlBulkCopy.BulkCopyTimeout = 0

            sqlBulkCopy.DestinationTableName = "dbo.employee"

            '[OPTIONAL]: Map the Excel columns with that of the database table

            'Below mapping required when your column name with excel is not matched

            'sqlBulkCopy.ColumnMappings.Add("employee_id", "employee_id")

            'sqlBulkCopy.ColumnMappings.Add("name", "name")

            con.Open()

            sqlBulkCopy.WriteToServer(dtExcelData)

            con.Close()

        End Using

    End Using

End Using

SQL Server Query Analyzer ShortCuts Tips

Here, I am going to write some SQL Server Query Analyzer Shortcuts, which may save the time to developer during write query.

1. Comment Text              - CTRL+K, CTRL+C

2. UnComment Text         - CTRL+K, CTRL+U

3. Show or Hide Query Result Pane - CTRL+R

4. Canceling the Query execution     - ALT+Break

Reset Identity Column Value in SQL Server

Today, I am describing, How to Reset Identity column value in Sql Server. Identity column is column which is increment auto number after insert a row in the table. We don't need to manually  put value in Auto Generate column which even is use for ID Column for table.

I am taking tbl_employee table for describe Identity Column.

--Script for create tbl_employee with identity column
create table tbl_employee(emp_id int identity(1,1) primary key,
emp_name varchar(50),address varchar(200))

--Insert two row in this table


insert into tbl_employee values('Rakesh kumar','Delhi,India')
insert into tbl_employee values('Sumit Tyagi','Gurgaon,India')

-- After insert two record in tbl_employee, we have two row as

select * from tbl_employee

-------------------------------------
emp_id   emp_name        address
-------------------------------------
1        Rakesh kumar    Delhi,India
2        Sumit Tyagi     Gurgaon,India

If we will delete all rows of this table and when will insert next record in this table than next emp_id will 3.

But we need to Reset emp_id column with value 1

we can use of below sql query for Reset Identity column of tbl_employee.

-- Query Syntax for Reset Identity Column

-- DBCC CHECKIDENT (<table_name>, reseed, Reset_column_value)

--Example :

DBCC CHECKIDENT (tbl_employee, reseed, 0)

Convert a timespan into days,hours and minutes in Asp.Net

Today, I am going to describe how to convert timeSpan value which may be difference between two dates. Even need to developer for calculate total duration between from date and To Date.

If we need to calculate duration between two dates we can use of TimeSpan.
I am giving some example in VB and C# code to calculate duration between two dates in days,hours,minutes as . .

//C# Code

DateTime Now = DateTime.Now;
DateTime Preveous = Now.AddHours(-5.5);
TimeSpan Diference = Now - Preveous;
Response.Write(Diference.Days.ToString() + " Days, " + Diference.Hours.ToString() + " Hours, " 

+ Diference.Minutes.ToString() + " Minutes");

'VB Code
'---------------
Dim Now As DateTime = DateTime.Now
Dim Preveous As DateTime = Now.AddHours(-5.5)
Dim Diference As TimeSpan = Now - Preveous
Response.Write(Diference.Days.ToString() & " Days, " & Diference.Hours.ToString() & " Hours, " & Diference.Minutes.ToString() & " Minuts")

Non www to www Redirection with web.config in Asp.net

301 Redirection like non www to www for domain useful for SEO Purpose. I am going to describe how to done in asp.net with configuration in web.config . This is mostly develop how will be do except .htaccess file. On Shared hosting .htaccess file not supported. There fore we do setting in web.config.

Below code is for following scenario.
1. non www to www redirect
2. www.yourdomainname.com/default.aspx to www.yourdomainname.com

I am describing Step for do this Redirection non www to www in asp.net, There is following step for this:

Step 1. Add tag in System.webServer Tag

         

<rewrite>
      <rules>
        <rule name="Redirect to WWW" stopProcessing="true">
          <match url=".*" />
          <conditions>
            <add input="{HTTP_HOST}" pattern="^yourdomainname.com$" />
          </conditions>
          <action type="Redirect" url="http://www.yourdomainname.com/{R:0}" redirectType="Permanent" />
        </rule>
       
        <rule name="Default Document" stopProcessing="true">
          <match url="(.*?)/?default\.aspx$" />
          <action type="Redirect" url="{R:1}/" />
        </rule>
      </rules>
    </rewrite>