Convert Datatable or Export Datatable to Excel file

private void ExporttoExcel(DataTable table)
{
    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.ClearContent();
    HttpContext.Current.Response.ClearHeaders();
    HttpContext.Current.Response.Buffer = true;
    HttpContext.Current.Response.ContentType = "application/ms-excel";
    HttpContext.Current.Response.Write(@""-//W3C//DTD HTML 4.0 Transitional//EN"">");
    HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=Reports.xls");
   
    HttpContext.Current.Response.Charset = "utf-8";
    HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");
      //sets font
    HttpContext.Current.Response.Write("");
    HttpContext.Current.Response.Write("


");
    //sets the table border, cell spacing, border color, font of the text, background, foreground, font height
    HttpContext.Current.Response.Write(" + 
      "borderColor='#000000' cellSpacing='0' cellPadding='0' " + 
      "style='font-size:10.0pt; font-family:Calibri; background:white;'>
");
    //am getting my grid's column headers
int columnscount = GridView_Result.Columns.Count;

    for (int j = 0; j < columnscount; j++)
    {      //write in new column
        HttpContext.Current.Response.Write(");
        //Get column headers  and make it as bold in excel columns
        HttpContext.Current.Response.Write("");
        HttpContext.Current.Response.Write(GridView_Result.Columns[j].HeaderText.ToString());
        HttpContext.Current.Response.Write("

    
"

"); HttpContext.Current.Response.Write(""); } HttpContext.Current.Response.Write(""); foreach (DataRow row in table.Rows) {//write in new row HttpContext.Current.Response.Write(" " ); for (int i = 0; i < table.Columns.Count; i++) { HttpContext.Current.Response.Write(""); HttpContext.Current.Response.Write(row[i].ToString()); HttpContext.Current.Response.Write(""); } HttpContext.Current.Response.Write(""); } HttpContext.Current.Response.Write(""); HttpContext.Current.Response.Write(""); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); }

Convert Generic List to DataTable

public static DataTable ToDataTable<T>(this IList<T> data)
{
    PropertyDescriptorCollection props =
        TypeDescriptor.GetProperties(typeof(T));
    DataTable table = new DataTable();
    for(int i = 0 ; i < props.Count ; i++)
    {
        PropertyDescriptor prop = props[i];
        table.Columns.Add(prop.Name, prop.PropertyType);
    }
    object[] values = new object[props.Count];
    foreach (T item in data)
    {
        for (int i = 0; i < values.Length; i++)
        {
            values[i] = props[i].GetValue(item);
        }
        table.Rows.Add(values);
    }
    return table;        }

JQuery and MVC with RZOR framework ...

http://www.codeproject.com/Articles/344292/ASP-NET-MVC3-Razor-With-jQuery-For-Beginners

Compare Two database structure By Query only

This will give you the list of Columns along with Table Name, Column Name and Data Type that is in master but not exists or different from test.

SELECT  
    T.[name] AS [table_name], AC.[name] AS [column_name],  
        TY.[name] AS system_data_typeFROM    [master].sys.[tables] AS T  
    INNER JOIN [master].sys.[all_columns] AC ON T.[object_id] = AC.[object_id] 
    INNER JOIN [master].sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]
EXCEPT
SELECT  
    T.[name] AS [table_name], AC.[name] AS [column_name],  
        TY.[name] AS system_data_typeFROM    test.sys.[tables] AS T  
    INNER JOIN test.sys.[all_columns] AC ON T.[object_id] = AC.[object_id] 
    INNER JOIN test.sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]

http://dbcomparer.com/Download/Default.aspx

Know the sql database table wise size

SET NOCOUNT ON

DBCC UPDATEUSAGE(0)

-- DB size.
EXEC sp_spaceused

-- Table row counts and sizes.
CREATE TABLE #t
(
    [name] NVARCHAR(128),
    [rows] int,
    reserved VARCHAR(18),
    data VARCHAR(18),
    index_size VARCHAR(18),
    unused VARCHAR(18)
)

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

SELECT *
FROM   #t order by rows desc

-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM   #t

DROP TABLE #t

Know the SQL database size in mb kb and GB

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB,
(size*8) / 1024 AS '(MB)',
((size*8) / 1024) / 1024 AS '(GB)'
FROM sys.master_files
WHERE DB_NAME(database_id) = 'Chromalox'

Insert/Update Sql table data using PowerShell Script

## open database connection
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=servername; Initial Catalog=databasename; Integrated Security=SSPI")
$conn.Open()

$cmd = $conn.CreateCommand()
$cmd.CommandText ="INSERT tblTemp VALUES ('Server1', 'C:', 456)"
$cmd.ExecuteNonQuery()
$conn.Close()

Save the above code as .ps1 in your machine and then use below code in powershell.exe window to execute your script ....

powershell -ExecutionPolicy ByPass -File D:\Projects\PowerShell_ImportProject\TestScript_InsertSQL.ps1


And you are done .. same way you can use update statement or so .... you can use for loop as per your requirement to insert data into sql server....

Detect if JavaScript is enabled in ASPX

 if (Session["JSChecked"] == null)
    //JSChecked -indicates if it tried to run the javascript version
    {
        // prevent infinite loop
        Session["JSChecked"] = "Checked";
        string path = Request.Url + "?JScript=1";
        Page.ClientScript.RegisterStartupScript(this.GetType(), "redirect", 
          "window.location.href='" + path + "';", true);
    }
    if (Request.QueryString["JScript"] == null)
        Response.Write("JavaScript is not enabled.");
    else
        Response.Write("JavaScript is enabled.");

convert string to JSON object

 public static string ToJsonString(this string s)
        {
            return s.Replace(@"\", @"\\") // Escape \
                .Replace(@"""", @"\""") // Escape "
                .Replace("\n", "\\n") // Escape \n
                .Replace("\r", "") // Throw away \r
                .Replace("\t", "\\t");
        }

Convert datatable to JSONArray in .Net

public static string ToJsonArray(this DataTable table, List propertiesToInclude, List propertiesToExclude)
        {
            StringBuilder sb = new StringBuilder();
            int rowsAdded = 0;
            List skipColumns = new List(); // A list of column indexes that should be skipped when serializing the table.

            for (int i = 0; i < table.Columns.Count; ++i)
            {
                // If the calling method has requested to skip any columns, we need to add it to the skipColumns
                if ((propertiesToInclude.Count > 0 && !propertiesToInclude.Exists(x => x.Equals(table.Columns[i].ColumnName, StringComparison.InvariantCultureIgnoreCase))) ||
                    propertiesToExclude.Exists(x => x.Equals(table.Columns[i].ColumnName, StringComparison.InvariantCultureIgnoreCase)))
                {
                    skipColumns.Add(i);
                }
            }

            for (int i = 0; i < table.Rows.Count; i++)
            {
                sb.Append(rowsAdded > 0 ? ", {" : "{");
                int colsAdded = 0;
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    if (skipColumns.Contains(j))
                    {
                        continue;
                    }

                    if (table.Columns[j].DataType == typeof(string))
                    {
                        sb.Append((colsAdded > 0 ? ", \"" : "\"") + table.Columns[j].ColumnName + "\":");
                        sb.Append("\"" + table.Rows[i][j].ToString().ToJsonString() + "\"");
                        colsAdded++;
                    }
                    else if (table.Columns[j].DataType == typeof(int))
                    {
                        sb.Append((colsAdded > 0 ? ", \"" : "\"") + table.Columns[j].ColumnName + "\":");
                        if (table.Rows[i][j] != DBNull.Value)
                        {
                            sb.Append(table.Rows[i][j]);
                        }
                        else
                        {
                            sb.Append("null");
                        }
                        colsAdded++;
                    }
                    else if (table.Columns[j].DataType == typeof(float))
                    {
                        sb.Append((colsAdded > 0 ? ", \"" : "\"") + table.Columns[j].ColumnName + "\":");
                        if (table.Rows[i][j] != DBNull.Value)
                        {
                            sb.Append(table.Rows[i][j]);
                        }
                        else
                        {
                            sb.Append("null");
                        }
                        colsAdded++;
                    }
                    else if (table.Columns[j].DataType == typeof(decimal))
                    {
                        sb.Append((colsAdded > 0 ? ", \"" : "\"") + table.Columns[j].ColumnName + "\":");
                        if (table.Rows[i][j] != DBNull.Value)
                        {
                            sb.Append(table.Rows[i][j]);
                        }
                        else
                        {
                            sb.Append("null");
                        }
                        colsAdded++;
                    }
                    else if (table.Columns[j].DataType == typeof(double))
                    {
                        sb.Append((colsAdded > 0 ? ", \"" : "\"") + table.Columns[j].ColumnName + "\":");
                        if (table.Rows[i][j] != DBNull.Value)
                        {
                            sb.Append(table.Rows[i][j]);
                        }
                        else
                        {
                            sb.Append("null");
                        }
                        colsAdded++;
                    }
                    else if (table.Columns[j].DataType == typeof(DateTime))
                    {
                        sb.Append((colsAdded > 0 ? ", \"" : "\"") + table.Columns[j].ColumnName + "\":");
                        sb.Append("\"" + table.Rows[i][j] + "\"");
                        colsAdded++;
                    }
                    else if (table.Columns[j].DataType == typeof(bool))
                    {
                        sb.Append((colsAdded > 0 ? ", \"" : "\"") + table.Columns[j].ColumnName + "\":");
                        if (table.Rows[i][j] != DBNull.Value)
                        {
                            sb.Append(table.Rows[i][j].ToString().ToLower());
                        }
                        else
                        {
                            sb.Append("null");
                        }
                        colsAdded++;
                    }
                    else if (table.Columns[j].DataType == typeof(Guid))
                    {
                        sb.Append((colsAdded > 0 ? ", \"" : "\"") + table.Columns[j].ColumnName + "\":");
                        sb.Append("\"" + table.Rows[i][j] + "\"");
                        colsAdded++;
                    }
                }
                sb.Append("}");
                rowsAdded++;
            }

            return sb.ToString();
        }

Simple Ajax Call using JQUERY and Prepare dynamic HTML based on Result

how to connect disconnect network router through c#.net

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Management;
using ROOT.CIMV2.Win32;

namespace NetworkRouterRestart
{
    class Program
    {
        static void Main(string[] args)
        {
            //System.Management.ManagementObjectSearcher searcher = new System.Management.ManagementObjectSearcher("root\\CIMV2", "select * from Win32_NetworkAdapter");
            //ManagementObjectCollection coll = searcher.Get();
            //foreach (ManagementObject obj in coll)
            //{
            //    Console.WriteLine(obj.ClassPath.ClassName);
            //    string name = obj.Properties["Name"].Value.ToString();
            //    if (name.Contains("Wireless"))
            //        obj.InvokeMethod("Disable", null);
            //}
            //Console.WriteLine("Disabled. Press a key to reenable.");
            //Console.ReadLine();

            //foreach (ManagementObject obj in coll)
            //{
            //    string name = obj.Properties["Name"].Value.ToString();
            //    if (name.Contains("Wireless"))
            //        obj.InvokeMethod("Enable", null);
            //}
            //Console.WriteLine("Enabled. Press any key to continue...");
            //Console.ReadLine();

            SelectQuery query = new SelectQuery("Win32_NetworkAdapter", "NetConnectionStatus=2");
            ManagementObjectSearcher search = new ManagementObjectSearcher(query);
            foreach (ManagementObject result in search.Get())
            {
                NetworkAdapter adapter = new NetworkAdapter(result);

                // Identify the adapter you wish to disable here.
                // In particular, check the AdapterType and
                // Description properties.

                // Here, we're selecting the LAN adapters.
                if (adapter.AdapterType.Equals("Ethernet 802.3"))
                {
                    adapter.Disable();
                }
            }

            query  = new SelectQuery("Win32_NetworkAdapter", "NetConnectionStatus=0");
            search = new ManagementObjectSearcher(query);
            foreach (ManagementObject result1 in search.Get())
            {
                NetworkAdapter adapter1 = new NetworkAdapter(result1);
                adapter1.Enable();
            }
        }
    }
}

HOW-TO: Disable/Enable Network Connections Programmatically under Vista

I got an email last week asking about how to disable a particular network connection under Vista. The specific scenario, how to disable an active 3G connection, is not something I'm going to cover, but what I present below could be used as basis for that scenario.

With Vista, Microsoft introduced two new methods to the Win32_NetworkAdapter class under WMI:Enable and Disable. Before can call either of those methods, we need to know how to enumerate the network connections.

The .NET Framework SDK provides a helpful utility called mgmtclassgen.exe, which can be used to create .NET-friendly wrappers of the WMI classes. Open up a Visual Studio command prompt and enter the following:

mgmtclassgen Win32_NetworkAdapter -p NetworkAdapter.cs

This will generate a file called NetworkAdapter.cs which will contain a C# representation of the WMI Win32_NetworkAdapter class. You can add this source code file to your C# project and then access all the properties without too much extra effort.

To filter and disable the specific adapters, you do something like this:

view plaincopy to clipboardprint

1. SelectQuery query = new SelectQuery("Win32_NetworkAdapter", "NetConnectionStatus=2");  
2. ManagementObjectSearcher search = new ManagementObjectSearcher(query);  
3. foreach(ManagementObject result in search.Get())  
4. {  
5.     NetworkAdapter adapter = new NetworkAdapter(result);  
6.   
7.     // Identify the adapter you wish to disable here.   
8.     // In particular, check the AdapterType and   
9.     // Description properties.  
10.   
11.     // Here, we're selecting the LAN adapters.  
12.     if (adapter.AdapterType.Equals("Ethernet 802.3"))   
13.     {  
14.         adapter.Disable();  
15.     }  
16. }  

Don't forget to add a reference to System.Management.dll!

http://msdn.microsoft.com/en-us/library/windows/desktop/aa394216(v=vs.85).aspx

Value	Meaning
0 (0x0)	Disconnected
1 (0x1)	Connecting
2 (0x2)	Connected
3 (0x3)	Disconnecting
4 (0x4)	Hardware not present
5 (0x5)	Hardware disabled
6 (0x6)	Hardware malfunction
7 (0x7)	Media disconnected
8 (0x8)	Authenticating
9 (0x9)	Authentication succeeded
10 (0xA)	Authentication failed
11 (0xB)	Invalid address
12 (0xC)	Credentials required