IMPORT table data using DROP and CREATE table constraints

It’s bit difficult to import data when your tables have constraints on it. It can be achieved easily by dropping the constraints of the tables for which you want to import data but you may not remember the deleted constraints if you want to re-create again once the import process completes.

So it’s better to run the following (create constraints) script first on the database before importing data and save the output script.

-- RECREATE CONSTRAINTS --

SELECT 'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id)+ ']'
+ ' ADD CONSTRAINT ' + '[' +  f.name  +']'+ ' FOREIGN KEY'+'('+COL_NAME(fc.parent_object_id,fc.parent_column_id)+')'
+' REFERENCES ['+OBJECT_NAME (f.referenced_object_id)+']('+COL_NAME(fc.referenced_object_id,
fc.referenced_column_id)+')' as Scripts
FROM .sys.foreign_keys AS f
INNER JOIN .sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
GO

Next step is to run the following (drop constraints) script on the database and copy the output script and paste on the query analyzer and execute.

-- DROP CONSTRAINTS --

SELECT 'IF EXISTS (SELECT * FROM sysobjects WHERE name = ''' + f.name + ''')
BEGIN
      ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id)+ ']'+ ' DROP  CONSTRAINT ' + '[' + f.name  + ']
END' 
FROM .sys.foreign_keys AS f
INNER JOIN .sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

GO

Start the import process; once the import process finishes then run the saved create constraints script to re-create all the dropped constraints in the database.   
No comments :

Sql script to shrink the database log file

USE <DATABASE NAME>
GO

 DBCC SHRINKFILE('<LOG FILE NAME>', 1)


 BACKUP LOG <DATABASE NAME> TO  DISK = N'LOG FILE BACKUP PATH'


 DBCC SHRINKFILE('<LOG FILE NAME>', 1)

 LOG FILE NAME: Go to Database Properties => Files => Database Files => Logical Name => Database_log


LOG FILE BACKUP PATH EXAMPLE: C:\Temp\Database_log.bak
No comments :

Program to search specified folder and its subfolders and write the folder’s content in the following format

C:\abc
  *.txt - 2 files
  *.xlsx - 1 file
    C:\abc\def
      *.csv  - 3 files
    C:\abc\ghi
      no files found
    C:\abc\jkl
       *.exe - 2 files
       C:\abc\jkl\mno
         *.dll - 1 file

C# Program

class Program
{
    //Global Variables

    static int pCount = 0;
   
    static StringBuilder sb = new StringBuilder(); // string which contains the actual output to write to the file

    //Main function

    static void Main(string[] args)
    {
        try
        {
            //Local variables

            string folderName = string.Empty;
           
            string fileName = string.Empty;
           
            bool flag = true;           

            
            Console.WriteLine("Please enter folder name to search :");

            ValidateFolder(ref folderName, flag);

            Console.WriteLine("Folder name : {0}", folderName);

            Console.WriteLine("Please enter file name :");

            ValidateFile(ref fileName, flag);

            Console.WriteLine("File name : {0}", fileName);

            pCount = folderName.Split('\\').Length; // variable to assign parent directory length split by '\'

            DirectorySearch(folderName); // search current and sub folders

            //Write output to the file

            string fileLocation = "C:\\" + fileName.Trim(); // output file location

            FileStream objFileStream = null;

            if (!File.Exists(fileLocation))
            {
                objFileStream = File.Create(fileLocation);
               
                objFileStream.Close();
            }

            StreamWriter objStreamWriter = new StreamWriter(fileLocation);

            objStreamWriter.Write(sb);
           
            objStreamWriter.Close();

            Console.WriteLine("Output file {0} created successfully in {1}.", fileName, fileLocation);           
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }

    //Function to search folders and sub folders

    public static void DirectorySearch(string directory)
    {
        try
        {
            Output(directory);

            foreach (var subdirectory in Directory.GetDirectories(directory))
            {
                DirectorySearch(subdirectory);
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }   

    //Function to write actual output to a string

    public static void Output(string directory)
    {
        try
        {
            var objDirectoryInfo = new DirectoryInfo(directory);

            var iCount = (objDirectoryInfo.FullName.Split('\\').Length) - pCount; // variable to create tree view structure

            sb.AppendLine(new String(' ', iCount) + objDirectoryInfo.FullName);

            var Files = objDirectoryInfo.EnumerateFiles("*.*", SearchOption.TopDirectoryOnly)
                                    .GroupBy(x =&gt; x.Extension)
                                    .Select(x =&gt; new { Extension = x.Key, Count = x.Count() })
                                    .ToList();

            if (Files.Count &gt; 0)
            {
                foreach (var fg in Files) // fg - file group
                {
                    string formatString = "{2}" + " " + "*{0} - {1}";

                    sb.AppendLine(string.Format(formatString, fg.Extension, fg.Count, new String(' ', iCount + 1)));
                }
            }
            else
            {
                sb.AppendLine(new String(' ', iCount + 1) + "No files found");
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }  

    //Function to validate the foleder name in the directory

    public static void ValidateFolder(ref string folderName, bool flag)
    {
        do
        {
            folderName = Console.ReadLine().Trim();

            if (string.IsNullOrEmpty(folderName))
            {
                flag = false;
            }
            else
            {
                flag = Regex.IsMatch(folderName, @"^(?:[a-zA-Z]\:|\\)(\\[\w\.$ ]+)+\\?$");
            }

            if (!flag)
            {
                Console.WriteLine(@"Folder name format: [a-zA-Z]:\[a-zA-Z0-9]\...");
            }
            else
            {
                flag = Directory.Exists(folderName);

                if (!flag)
                    Console.WriteLine(@"Folder doesn't exists in the directory.");
            }

        } while (!flag);
    }

    //Function to validate the output file name

    public static void ValidateFile(ref string fileName, bool flag)
    {
        do
        {
            fileName = Console.ReadLine().Trim();

            if (string.IsNullOrEmpty(fileName))
            {
                flag = false;
            }
            else
            {
                flag = Regex.IsMatch(fileName, @"^(?:[a-zA-Z0-9]+)+\.(?i)(txt)$");
            }

            if (!flag)
                Console.WriteLine("File name format: [a-zA-Z0-9].[txt]");

        } while (!flag);
    }   
}
No comments :

Function to reverse the sentence

C#

 public static string ReverseSentence(string sentence)
{
string[] words = sentence.Split(' ');

 Array.Reverse(words);

 for (var i = 0; i < words.Length; i++)
{
char[] letters = words[i].ToCharArray();

 Array.Reverse(letters);

 words[i] = new string(letters);
}

 return string.Join(" ", words);
}

 VB.NET

 Public Shared Function ReverseSentence(sentence As String) As String

Dim words As String() = sentence.Split(" "C)

 Array.Reverse(words)

 For i As var = 0 To words.Length - 1

Dim letters As Char() = words(i).ToCharArray()

 Array.Reverse(letters)

 words(i) = New String(letters)

Next

 Return String.Join(" ", words)

End Function

 Input:  Hello! World

 Output:  dlroW !olleH
No comments :

Function to reverse the order of each letter in a word

C#

 public static string ReverseWord(string sentence)
{
string[] words = sentence.Split(' ');

 for (var i = 0; i < words.Length; i++)
{
char[] letters = words[i].ToCharArray();

 Array.Reverse(letters);

 words[i] = new string(letters);
}

 return string.Join(" ", words);
}

 VB.NET

 Public Shared Function ReverseWord(sentence As String) As String

Dim words As String() = sentence.Split(" "C)

 For i As var = 0 To words.Length - 1

Dim letters As Char() = words(i).ToCharArray()

 Array.Reverse(letters)

 words(i) = New String(letters)

Next

 Return String.Join(" ", words)

End Function

 Input:  Hello! World

 Output:  !olleH dlroW
No comments :

Function to reverse the order of words

C#

 public static string Reverse(string sentence)
{
        string[] words = sentence.Split(' ');

         Array.Reverse(words);

         return string.Join(" ", words);
}

 VB.NET

 Public Shared Function Reverse(sentence As String) As String

          Dim words As String() = sentence.Split(" "C)

           Array.Reverse(words)

           Return String.Join(" ", words)

 End Function

 Input:  Hello! World

 Output:  World Hello!
1 comment :