Next step of programming

Some time we way find a situtation where we need to get the information about the backup file or device before restoring that backup for example we may want the file it is containing or the media information for that purpose we can use the following command

To get the information  regarding the file it contains

Restore FilelistOnly From Disk = N'C:\Sample.Bak'

This will return the information about the file which is there in the backup and if you want to see the labels only then you can use

Restore LabelOnly From Disk = N'C:\Sample.Bak'

Other useful arguments can be REWINDONLY and VERFIYONLY. For more details on this visit MSDN

http://msdn.microsoft.com/en-us/library/ms173778.aspx

Most of the users will be doing ping to a system to check whether it is running or responding or not. First thing what they will do is start a command promt and type the ping command. But doing it in C# is also very easy. We can implement in our programs and use to check whether system are responding. It can be used in various network application

public partial class frmPingSystem : Form    
{
    public frmPingSystem()
    {
        InitializeComponent();
    }

    private void pingButton_Click(object sender, EventArgs e)
    {
        int timeout = 12000;
        Ping ping = new Ping();
        byte[] buffer = Encoding.ASCII.GetBytes("ooooooooooooooooooooooo");
        PingOptions options = new PingOptions(64,true);

        AutoResetEvent waiter = new AutoResetEvent(false);
        ping.PingCompleted += new PingCompletedEventHandler(ping_PingCompleted);
        ping.SendAsync(addressTextBox.Text, timeout, buffer, options, null);
    }

    void ping_PingCompleted(object sender, PingCompletedEventArgs e)
    {
        string output = "Status : " + e.Reply.Status.ToString();
        output += "\r\nAddress : " + e.Reply.Address.ToString();
        output += "\r\nRound Trip Time : " + e.Reply.RoundtripTime.ToString();
        output += "\r\nBuffer Length : " + e.Reply.Buffer.Length.ToString();
        output += "\r\nTime to live : " + e.Reply.Options.Ttl.ToString();
        output += "\r\nDont Framment : " + e.Reply.Options.DontFragment.ToString();
        resultTextBox.Text = output;
    }
}

If you want to check the result in the same line where you are sending ping then you must use Send function in place SendAsync

Getting the TCP/IP Statistic is very easy through C#. Since all the required methods and properties are there in framework. To get the TCP/IP Statistic we have to use the following nampespace

using System.Net.NetworkInformation;

Now to get the statistic we have to IPGlobalProperties class and extract the information out of it to our TCP/IP statistic object

IPGlobalProperties properties = IPGlobalProperties.GetIPGlobalProperties();
TcpStatistics tcpstat = null;

now with following line you will get all the details for IPv4

tcpstat = properties.GetTcpIPv4Statistics(); 

and to get the details of IPv6 you use following line

tcpstat = properties.GetTcpIPv6Statistics(); 

now we can get details from this object. Details like data sent, recieved, total connections and various other

We can utilize the query of listing foreign key with respective table and fields to generate the alter query for adding constraint. For that we will use the XML and temp table and will generate the list of the alter queries

SELECT TABS.NAME AS [REFTABLE],
   (SELECT NAME
       FROM SYS.TABLES AS CTAB
       WHERE (OBJECT_ID = FK.PARENT_OBJECT_ID)) AS [PARENTTABLE], 
          FK.NAME AS [KEYNAME],
          AC.NAME AS [COLUMNNAME] INTO #TEMPTABLE
FROM SYS.TABLES AS TABS INNER JOIN             
SYS.FOREIGN_KEYS AS FK ON FK.REFERENCED_OBJECT_ID = TABS.OBJECT_ID INNER JOIN             
SYS.FOREIGN_KEY_COLUMNS AS FKC ON FK.OBJECT_ID = FKC.CONSTRAINT_OBJECT_ID INNER JOIN             
SYS.ALL_COLUMNS AS AC ON AC.COLUMN_ID = FKC.PARENT_COLUMN_ID
		     AND AC.OBJECT_ID = FKC.PARENT_OBJECT_ID

SELECT 'ALTER TABLE ' + [PARENTTABLE] + ' ADD CONSTRAINT ' + 
	[KEYNAME] + ' FOREIGN KEY (' + REPLACE((SELECT 
[COLUMNNAME]  AS 'data()' FROM  #TEMPTABLE AS TT 
WHERE TT.[KEYNAME] = #TEMPTABLE.[KEYNAME] FOR XML PATH('')),' ',',')
+ ') REFERENCES (' + REPLACE((SELECT [COLUMNNAME]  AS 'data()' 
FROM  #TEMPTABLE AS TT WHERE TT.[KEYNAME] = #TEMPTABLE.[KEYNAME] 
FOR XML PATH('')),' ',',') + ') ON UPDATE CASCADE ON DELETE CASCADE'    
FROM 
	#TEMPTABLE GROUP BY [KEYNAME], [PARENTTABLE], [REFTABLE]

DROP TABLE #TEMPTABLE

To list the all the foreign keys available in database and there associated tables and fields we can utilize following query

SELECT tabs.name AS [Reference Table],
   (SELECT name FROM sys.tables AS ctab
       WHERE (object_id = fk.parent_object_id)) AS [Parent Table], 
       fk.name AS [Key Name],
        ac.name AS [Column Name] 
FROM sys.tables AS tabs INNER JOIN             
sys.foreign_keys AS fk ON fk.referenced_object_id = tabs.object_id inner join             
sys.foreign_key_columns as fkc on fk.object_id = fkc.constraint_object_id inner join             
sys.all_columns as ac on ac.column_id = fkc.parent_column_id
		     and ac.object_id = fkc.parent_object_id 
ORDER BY tabs.name, 2

Here is one sample class which can be used for 4 different kind of symmetric encryption
1. DES
2. RC2
3. Rijndeal
4. TripleDES

Before using the class there is one important thing to know which is the size of the key. It varies as you change the encryption type for example

• DES support 64 bits.
• RC2 40 to 128 Bits with increment of 8 Bits only.
• Rijndeal support key lengths of 128, 192, or 256 bits.
• and TripleDES supports 128 bits to 192 bits in increments of 64 bits.

using System;
using System.IO;
using System.Text;
using System.Security.Cryptography;

namespace Crypto
{
    public enum SymmetricAlgorimthType{ DES, RC2, Rijndael, TripleDES };

    class SymmetricCryptogarphy    
    {
        private SymmetricAlgorimthType _algorithmType = 
SymmetricAlgorimthType.DES;

        public SymmetricCryptogarphy(SymmetricAlgorimthType algorithmKey)
        {
            _algorithmType = algorithmKey;
        }

        public SymmetricAlgorimthType AlgorithmType
        {
            get { return _algorithmType; }
        }

        public SymmetricAlgorithm GenerateKey()
        {
            SymmetricAlgorithm algoKey;

            algoKey = GetCryptoServiceProvider(_algorithmType);
            algoKey.GenerateKey();
            algoKey.GenerateIV();
            return algoKey;
        }

        public SymmetricAlgorithm GenerateKey(int keySize)
        {
            SymmetricAlgorithm algoKey;

            algoKey = GetCryptoServiceProvider(_algorithmType);
            algoKey.KeySize = keySize;
            algoKey.GenerateKey();
            algoKey.GenerateIV();
            return algoKey;
        }

        public SymmetricAlgorithm GenerateKey(int blockSize, int keySize)
        {
            SymmetricAlgorithm algoKey;

            algoKey = GetCryptoServiceProvider(_algorithmType);

            algoKey.BlockSize = blockSize;
            algoKey.KeySize = keySize;
            algoKey.GenerateKey();
            algoKey.GenerateIV();
            return algoKey;
        }

        public byte[] EncryptData(SymmetricAlgorithm key, string data)
        {
            MemoryStream memoryStream = new MemoryStream();
            CryptoStream encStream = new CryptoStream(memoryStream,
              key.CreateEncryptor(), CryptoStreamMode.Write);
            StreamWriter sw = new StreamWriter(encStream);

            sw.WriteLine(data);
            sw.Close();
            encStream.Close();

            byte[] buffer = memoryStream.ToArray();
            memoryStream.Close();

            return buffer;
        }

        public string DecryptData(SymmetricAlgorithm key, byte[] data)
        {
            MemoryStream memoryStream = new MemoryStream(data);

            CryptoStream encStream = new CryptoStream(memoryStream,
              key.CreateDecryptor(),CryptoStreamMode.Read);
              StreamReader sr = new StreamReader(encStream);

            string val = sr.ReadLine();
            sr.Close();
            encStream.Close();
            memoryStream.Close();

            return val;
        }

        private SymmetricAlgorithm GetCryptoServiceProvider(SymmetricAlgorimthType algorithmType)
        {
            switch (algorithmType)
            {
                case SymmetricAlgorimthType.DES:
                    return new DESCryptoServiceProvider();
                case SymmetricAlgorimthType.RC2:
                    return new RC2CryptoServiceProvider();
                case SymmetricAlgorimthType.Rijndael:
                    return new RijndaelManaged();
                case SymmetricAlgorimthType.TripleDES:
                    return new TripleDESCryptoServiceProvider();
                default:
                    return new DESCryptoServiceProvider();
            }
        }
    }
}

Some time there can be requirment in which we need to list the types and methods of assembly. For that purpose we have to use the reflection with help of that we can get all the details of the assembly even the IL code also. one samle to list the Types, associated methods and there properties is given below

//Get the assembly object                
Assembly assembly = Assembly.LoadFrom(openFileDlg.FileName);

//List them all                
foreach (Type type in assembly.GetTypes())
{
    //Create a node of types first                     
    TreeNodetreeNode = assemblyTreeView.Nodes.Add(type.Name);

    foreach (MethodInfo mthdInfo in type.GetMethods())
    {
        //Get the node as method name                        
        TreeNodemethodNode = treeNode.Nodes.Add(mthdInfo.Name);

        //List the details of the method as child node
        methodNode.Nodes.Add("Return Type : " +
            mthdInfo.ReturnType.ToString());
        methodNode.Nodes.Add("IsAbstract : " +
            mthdInfo.IsAbstract.ToString());
        methodNode.Nodes.Add("Access Modifier : " +
            (mthdInfo.IsPublic ? "Public" : "Private"));
        methodNode.Nodes.Add("IsStatic : " +
            mthdInfo.IsStatic.ToString());
    }
}

It is not limited to this much even you can create a type out of it and even can invoke methods of those type. This can be very useful when you want to create instance of class at runtime after locating in the various assemblies

In the part 1 we added one server as linked server to another instance of the mssql server now we will right a sample query which will retrun the result from both the server tables and will give single output

select * from master.sys.objects
union
select * from [inventory].master.sys.objects

it will return the result after combining the result of both the databases of different instance. So you will get the list of objects which are there in both the instances. Now we will take another sample in which we will have the product database in one instance and customer database in another instance we will join them on one field and will get the result after joining them

select productname, productid, customername, customerid from products.dbo.productlist as prolist
	inner join [inventory].customers.dbo.customerlist as cuslist on (cuslist.productid = prolist.productid)

here result from the one instance and customer data is coming from the inventory server

There can be case or a requirement to attach the another sql server to your existing sql server instance so that you can work on the both server at same time. Get data from both the server from one query as a single result, managing all server from one location or for various work you can utilise it.

for that we have to use sp_addlinkedserver. one of the example

EXEC sp_addlinkedserver   
@server='inventory', 
@srvproduct='',
   @provider='SQLNCLI', 
@datasrc='server\mssql2005'

for more information on this visit sp_addlinkedserver (Transact-SQL) – MSDN

Here we added the server\mssql2005 as the inventory server as linked server now we have to add the login information for that we have to use

EXEC sp_addlinkedsrvlogin 'inventory', 
'false', 'mydomain\user2', 
'sa', 'sapassword

To get more information on this checkout sp_addlinkedsrvlogin (Transact-SQL) – MSDN

Now that server is linked and ready to be user. we can use the following query to get the data from the server

SELECT * FROM [inventory].master.dbo.sysobjects

Following is the sample which shows how can you use relations in the LINQ for query and get the data out of various objects

class CustomerOrders
{
public IEnumerable GetCustomerOrders()
{
Customer[] customers = {
new Customer{ CustomerID =1, CustomerName = "Pratap Singh"},
new Customer{ CustomerID =2, CustomerName = "Sikandar"},
new Customer{ CustomerID =3, CustomerName = "Ram Parsad Patnaik"}
};

Order[] orders = {
new Order{ CustomerID = 1, OrderID = 1 , OrderDate = Convert.ToDateTime("10/Apr/2004")},
new Order{ CustomerID = 1, OrderID = 2 , OrderDate = Convert.ToDateTime("3/Jan/2005")},
new Order{ CustomerID = 3, OrderID = 1 , OrderDate = Convert.ToDateTime("23/Mar/2006")},
new Order{ CustomerID = 2, OrderID = 1 , OrderDate = Convert.ToDateTime("14/Nov/2005")},
new Order{ CustomerID = 3, OrderID = 2 , OrderDate = Convert.ToDateTime("19/Dec/2008")}
};

var Query = from clist in customers
join oList in orders
on clist.CustomerID equals oList.CustomerID
select clist;

return Query;
}

}

Here in sample look at the join part of the LINQ join oList in orders on clist.CustomerID equals oList.CustomerID. In smiliar way you can create various joins and create a relational model in LINQ