C# - How to get file Properties and insert into SQL Server table by using C Sharp

You are working as C# developer, you need to write a program that can read file's information such as

•   FolderPath
•   FileName
•   LastWriteTime
•   CreateTime
•   FileSizeinKB

from a table and write into SQL Server table. Also as part of file information , you would like to insert folder from which we are reading the file properties.


Step 1: 
First of all you need to create a table in SQL Server database in which you would like to insert file information.

CREATE TABLE [dbo].[FileInformation](
id int identity(1,1),
FolderPath VARCHAR(255),
FileName VARCHAR(255),
[LastWriteTime] DateTime,
[CreateTime] Datetime,
FileSizeinKB Int)

Step 2:
Create new Project and then choose Console Application and use below script.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Data.SqlClient;

namespace _01_WriteFileProperitestoTable
{
    class Program
    {
        static void Main(string[] args)
        {
            //Declare Variable and set value,Provide the folder which contains files
            string VarDirectoryPath = "C:\\Source\\";
            //Create Connection to SQL Server
            SqlConnection SQLConnection = new SqlConnection();
            SQLConnection.ConnectionString= "Data Source = (local); Initial Catalog =TechBrothersIT; "

               + "Integrated Security=true;";

            //get all files from directory or folder to get file's information
            string[] files = Directory.GetFiles(VarDirectoryPath);
            SqlCommand SqlCmd = new SqlCommand();
            SqlCmd.Connection = SQLConnection;
            SQLConnection.Open();

            //loop through files
            foreach (string filename in files)
            {

                FileInfo file = new FileInfo(filename);

SqlCmd.CommandText = "Insert into dbo.FileInformation(";
SqlCmd.CommandText += "[FolderPath],[FileName],[LastWriteTime],[CreateTime],[FileSizeinKB])";
SqlCmd.CommandText +=" Values('"
                  + VarDirectoryPath + "','"
                  + file.Name + "','"
                  + file.LastWriteTime + "','"
                 + file.CreationTime 
                 + "','" + file.Length / 1024 
                 + "')";
                 SqlCmd.ExecuteNonQuery();
            }
            SQLConnection.Close();
        }
    }
}

Save the script and then execute. It should read the file properties from the folder you have given and insert into SQL Server Table.

How to Find Duplicate Records in All the Tables in SQL Server Database - SQL Server / TSQL Tutorial

You are working on this big project called Data Analysis. you need to find all the duplicate records in all the tables in a database. The database contains hundreds of tables. It is taking forever to write query for each table and then run it and find the duplicate records. You want something that can run in one shot and provide you all the list of tables, columns, duplicate values and duplicate count.

Solution:

We are going to use Cursor so we can loop through tables in our database and use dynamic sql to build our query to find duplicate record. We will run this query for each table and then insert the records in #Results Temp Table. 

At the end you will have one nice and neat table with Database Name, Schema Name,Table Name, Column List, Duplicate Value,Total Table Row count and Duplicate Count.

Few things to remember before you go ahead and run this script on Production. As this script is going to generate queries on fly and run them one after one. If your database is big and has tables with a lot or records, it can take long time to run. Find the best when there is very minimum activity on server or try to schedule on weekend when have very limited processes running on server. 

USE yourdbname 

go 

DECLARE @SchemaName VARCHAR(100) 
DECLARE @TableName VARCHAR(100) 
DECLARE @DatabaseName VARCHAR(100) 

--Create Temp Table to Save Results 
IF Object_id('tempdb..#Results') IS NOT NULL 
  DROP TABLE #results 

CREATE TABLE #results 
  ( 
     databasename       VARCHAR(100), 
     schemaname         VARCHAR(100), 
     tablename          VARCHAR(100), 
     columnlist         VARCHAR(max), 
     duplicatevalue     VARCHAR(max), 
     totaltablerowcount INT, 
     duplicaterowcnt    INT 
  ) 

DECLARE cur CURSOR FOR 
  SELECT table_catalog, 
         table_schema, 
         table_name 
  FROM   information_schema.tables 
  WHERE  table_type = 'BASE TABLE' 

OPEN cur 

FETCH next FROM cur INTO @DatabaseName, @SchemaName, @TableName 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      --Get List of the Columns from Table without Identity Column 
      DECLARE @ColumnList NVARCHAR(max)=NULL 

      SELECT @ColumnList = COALESCE(@ColumnList + '],[', '') + c.NAME 
      FROM   sys.columns c 
             INNER JOIN sys.tables t 
                     ON c.object_id = t.object_id 
      WHERE  Object_name(c.object_id) = @TableName 
             AND Schema_name(schema_id) = @SchemaName 
             AND is_identity = 0 

      SET @ColumnList='[' + @ColumnList + ']' 

      --Print @ColumnList 
      DECLARE @ColumnListConcat VARCHAR(max)=NULL 

      SET @ColumnListConcat=Replace(Replace(Replace(Replace(@ColumnList, '[', 
                                                    'ISNULL(Cast(['), ']', 
                                            '] AS VARCHAR(MAX)),''NULL'')'), 
                                            ',ISNULL', '+ISNULL'), '+', 
                            '+'',''+') 

      --Create Dynamic Query for Finding duplicate Records 
      DECLARE @DuplicateSQL NVARCHAR(max)=NULL 

      SET @DuplicateSQL= ';With CTE as   (select  ''' 
                         + @DatabaseName + ''' AS DBName,' + '''' 
                         + @SchemaName + ''' AS SchemaName,' + '''' 
                         + @TableName + ''' AS TableName,' + '''' 
                         + @ColumnList + ''' AS ColumnList,' 
                         + @ColumnListConcat 
                         + ' AS ColumnConcat,    (Select count(*) from [' + @SchemaName 
                         + '].[' + @TableName 
                         + '] With (Nolock))             AS TotalTableRowCount    ,RN = row_number()             over(PARTITION BY ' 
                         + @ColumnList + '  order by ' + @ColumnList 
                         + ')             from [' + @SchemaName + '].[' 
                         + @TableName + ']  ) Select * From CTE WHERE RN>1' 

      PRINT @DuplicateSQL 

      INSERT INTO #results 
      EXEC(@DuplicateSQL) 

      FETCH next FROM cur INTO @DatabaseName, @SchemaName, @TableName 
  END 

CLOSE cur 

DEALLOCATE cur 

SELECT * 
FROM   #results 
--drop table #Results 

I ran this script on my database, My Database is small so it took few seconds to complete. I got below results for my tables.It found all the duplicate records in all the table if there were any in SQL Server Database.

How to search for a String in all Columns in all tables in SQL Server Database - SQL Server / TSQL Tutorial

You are working as SQL Server developer / TSQL Developer for finical firm. You are working on analysis data from one of the database. You need to find String let say "Aamir" in all the columns of all the table in SQL server database.

Solution:

This analysis can really help us to find if we are strong a lot of duplicate data and what type of index will work better for us. We often get this type of request from business where they like to check the data in all the tables and further dig into it if found.

We are going to use Cursor to find all the string in all the columns in all the tables in SQL Server Database. As this query is going to run for all the columns of datatype char, nchar, varchar, nvarchar,text and ntext. This can be expensive. If your database is small , you might be good to run.
In cases where your database is big in size, you might want to take the backup and restore on server where users are not connected and run your query there. Or run during those hours when you have less or almost no activity.

USE YourDBName
GO

Declare @SearchString VARCHAR(100)
--Provide the String here. I am using Aamir for search
SET @SearchString='aamir'
DECLARE @DatabaseName VARCHAR(100)
DECLARE @SchemaName VARCHAR(100)
DECLARE @TableName VARCHAR(100)
DECLARE @ColumnName VARCHAR(100)
DECLARE @FullyQualifiedTableName VARCHAR(500)
Declare @DataType VARCHAR(50)

--Create Temp Table to Save Results
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results

CREATE TABLE #Results (
    DatabaseName VARCHAR(100)
    ,SchemaName VARCHAR(100)
    ,TableName VARCHAR(100)
    ,ColumnName VARCHAR(100)
    ,ColumnDataType VARCHAR(50)
    ,TotalTableRowCount INT
    ,StringOccuranceRecordCount INT
    )

DECLARE Cur CURSOR
FOR
SELECT C.Table_CataLog
    ,C.Table_Schema
    ,C.Table_Name
    ,C.Column_Name
    ,'[' + C.Table_CataLog + ']' + '.[' + C.Table_Schema + '].' 
    + '[' + C.Table_Name + ']' AS FullQualifiedTableName,
    C.Data_Type
FROM information_schema.Columns C
INNER JOIN information_Schema.Tables T ON C.Table_Name = T.Table_Name
    AND T.Table_Type = 'BASE TABLE'
    and (C.Data_Type like '%CHAR%'
    or C.Data_Type like '%Text%')


OPEN Cur
FETCH NEXT
FROM Cur
INTO @DatabaseName
    ,@SchemaName
    ,@TableName
    ,@ColumnName
    ,@FullyQualifiedTableName
    ,@DataType

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL VARCHAR(MAX) = NULL
    SET @SQL = ' Select ''' + @DatabaseName + ''' AS DatabaseName, ''' 
    + @SchemaName + ''' AS TableName,
      ''' + @TableName + ''' AS SchemaName,
      ''' + @ColumnName + ''' AS ColumnName,
      ''' + @DataType + ''' AS ColumnName,
      (Select count(*) from ' + @FullyQualifiedTableName + ' With (Nolock)) 
      AS TotalTableRowCount,
      count(*) as StringOccuranceRecordCount from ' + @FullyQualifiedTableName 
      + 'With (Nolock) Where  '+@ColumnName+' like '''+'%'+ @SearchString+'%'''

   -- Print @SQL
    INSERT INTO #Results
    EXEC (@SQL)

    FETCH NEXT
    FROM Cur
    INTO @DatabaseName
        ,@SchemaName
        ,@TableName
        ,@ColumnName
        ,@FullyQualifiedTableName
        ,@DataType
END
CLOSE Cur
DEALLOCATE Cur

SELECT *,
Cast((StringOccuranceRecordCount/Cast(TotalTableRowCount as Numeric(13,1)))*100 

AS Numeric(4,1)) AS StringOccurancePercentPerColumn
from #Results
Where StringOccuranceRecordCount<>0
--drop table #Results

Here are my test results

TSQL - How to Get Row Count Of All The Tables In SQL Server Database

Sometime we want to get the Row Count quickly for all the tables in our Database for analysis. There are multiple ways to do that. We can use TSQL Cursor to loop through all the tables and use count(*) to get the row count.

I am using Catalog views to get this information by using below query. This is quick way to find the row count for all the tables in a database. The Cursor with Count(*) can be slow as it has to count rows for each of the table.

USE YourDBName

GO

SELECT OBJECT_NAME(id) AS TableName,
       rowcnt          AS [RowCount]

FROM   sysindexes s
       INNER JOIN sys.tables t
               ON s.id = t.OBJECT_ID

WHERE  s.indid IN ( 0, 1, 255 )
       AND is_ms_shipped = 0

I ran above query on TestDB and got below information.

Fig 1: Get Row Count for all the Tables in SQL Server Database

How to prevent user login to SQL Management Studio

CREATE TRIGGER [TR_LOGON_APP] ON ALL SERVER FOR LOGON AS BEGIN DECLARE @program_name nvarchar(128) DECLARE @host_name nvarchar(128) SELECT @program_name = program_name, @host_name = host_name FROM sys.dm_exec_sessions AS c WHERE c.session_id = @@spid IF ORIGINAL_LOGIN() IN('YOUR_APP_LOGIN_NAME') AND @program_name LIKE '%Management%Studio%' BEGIN RAISERROR('This login is for application use only.',16,1) ROLLBACK; END END;

9 Tax saving investments to save lakhs on your income tax

Income tax is something all of us would like to reduce it to the maximum. And what better than tax saving investments underSection 80C to reduce our taxes to some extent. Choose one of the following tax saving investments before 31st March.

PPF (Public Provident Fund)

It is one of the traditional yet highly preferred retirement planning investments. It is also a great long-term tax saving investment. The maximum amount that is allowed as an investment in the scheme is Rs 1.5 lakh. Interest income on PPF and the amount received on maturity are both tax free.

ELSS (Equity Linked Saving Scheme)

ELSS is a mutual fund that comes within the ambit of tax saving investments. With a lock in period of 3 years, this investment option offers an exemption of maximum Rs 1.5 lakh in a financial year. The interest rate depends on the performance of this scheme in a given year and the maturity amount from the investment is tax free.

FD (Fixed Deposits)

Fixed deposit is another popular tax saving Investment. The interest rate varies from one bank or post office to another. Maximum exemption allowed is Rs 1.5 lakh for a minimum duration of 5 years. The interest earned and the maturity amount are taxable.

NSC (National Saving Certificate)

NSC’s are tax saving investments issued by the Indian Post Office. It has a 5 year lock in period. They offer guaranteed and tax-free returns till maturity, although the interests earned is taxable.

EPF (Employee Provident Fund)

This scheme helps save a maximum of Rs 1.5 lakh. In this fund, up to 12 % of a person’s basic salary gets deducted and the other 12 % is contributed by the employer.The amount at maturity is tax free.

Life Insurance

Life insurance is the most popular tax saving investment under Section 80C of the Income Tax Act. With a maximum deduction of Rs 1.5 lakh is allowed in a given financial year. The amount received at maturity or in the case of death is tax free. Apart from the tax saving benefits life insurance helps one plan for the unforeseen events in his or her life.

ULIP (Unit Linked Insurance Plan)

ULIP is a unique combination of investment and insurance that results in a tax saving of Rs 1.5 lakh per year. The premium paid by you is split between insurance and investment. The corpus received at maturity is exempt from tax.

NPS (National Pension System)

The National Pension System is an additional tax saving investment. It is a long term product with strict penalties on withdrawal. It is primarily to encourage people to save for their retirement. Your contribution in the scheme is deducted from income tax up to a maximum of Rs.50,000. This deduction is over and above the limit of Rs 1.5 lakh of Section 80C.

However, if your employer contributes to your NPS account, it would be tax free without any limit. And this contribution is also exempt from income tax over and above the 80C limit.

Sukanya Samriddhi Yojana

A Sukanya Samriddhi Yojana account can only be opened on a girl child’s name by her parents or legal guardians.The account can be opened anytime from the birth of the girl child till she attains the age of 10 years and it is valid up to 21 years of age. The maximum deduction of up to Rs 1,50,000 can be claimed every year under Section 80C. The maturity proceeds from the scheme is tax-free.