Anil Diggiwal - Software Developer Jaipur India

Saturday, February 11, 2012

Find IP Address in ASP.NET Behind Proxy

public string IpAddress()
{
string strIpAddress;
strIpAddress = Request.ServerVariables["HTTP_X_FORWARDED_FOR"];
if (strIpAddress == null)
{
strIpAddress = Request.ServerVariables["REMOTE_ADDR"];
}
return strIpAddress;
}

To find IP address of a machine behind LAN you can use this code

string strHostName = System.Net.Dns.GetHostName();
string clientIPAddress = System.Net.Dns.GetHostAddresses
(strHostName).GetValue(1).ToString();

jQuery Fixed Header Scrollable GridView

Read Scrollable GridView With Fixed Headers Using CSS if you want to create scrollable fixed header gridview without using jQuery or JavaScript.

First of all add jquery library and fixed header scrollable gridview jquery plugin in project and add reference to it between <head></head> section of HTML source of aspx page.

<head runat="server">
<title>jQuery Fixed Header Scrollable GridView</title>
<script src="jquery-1.4.1.min.js" type="text/javascript">
</script>
<script src="ScrollableGrid.js" type="text/javascript">
</script>
</head>


<script type="text/javascript" language="javascript">
$(document).ready(function() 
{
$('#<%=fixedHeaderScrollableGridView.ClientID %>').Scrollable();
}
)
</script>


<asp:GridView ID="fixedHeaderScrollableGridView" runat="server" 
              DataSourceID="SqlDataSource1" 
              AutoGenerateColumns="False" 
              DataKeyNames="ProductID" 
              AllowPaging="True" 
              PageSize="30">
<Columns>
<asp:BoundField DataField="ProductID" HeaderText="ProductID"/>
<asp:BoundField DataField="ProductName" HeaderText="ProductName" /> 
<asp:BoundField DataField="QuantityPerUnit" HeaderText="QuantityPerUnit" /> 
<asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" /> 
<asp:BoundField DataField="CategoryName" HeaderText="CategoryName" /> 
</Columns>
</asp:GridView>
        
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
SelectCommand="SELECT [ProductID], [ProductName], [QuantityPerUnit], 
[UnitPrice], [CategoryName] FROM [Alphabetical list of products]">
</asp:SqlDataSource>

Thursday, February 2, 2012

Transferring Data Using SqlBulkCopy

Transferring data from one source to another is common practice in software development. This operation is preformed in many different scenarios which includes migration of the old system to the new system, backing up the data and collecting data from different publishers. ASP.NET 2.0 includes the SqlBulkCopy class that helps to copy the data from different data sources to the SQL SERVER database. In this article, I will demonstrate the different aspects of the SqlBulkCopy class.

SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
sqlBulk.DestinationTableName = "Details";
sqlBulk.ColumnMappings.Add("ID", "ID");
sqlBulk.ColumnMappings.Add("Name", "Name");
sqlBulk.WriteToServer(dReader);

private static void PerformBulkCopy()
{
    string connectionString =
            @"Server=localhost;Database=Northwind;Trusted_Connection=true";
    // get the source data
    using (SqlConnection sourceConnection = 
            new SqlConnection(connectionString))
    {
        SqlCommand myCommand =
            new SqlCommand("SELECT * FROM Products_Archive", sourceConnection);
        sourceConnection.Open();
        SqlDataReader reader = myCommand.ExecuteReader();

        // open the destination data
        using (SqlConnection destinationConnection =
                    new SqlConnection(connectionString))
        {
            // open the connection
            destinationConnection.Open();

            using (SqlBulkCopy bulkCopy =
            new SqlBulkCopy(destinationConnection.ConnectionString))
            {
                bulkCopy.BatchSize = 500;
                bulkCopy.NotifyAfter = 1000;
                bulkCopy.SqlRowsCopied +=
                    new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
                bulkCopy.DestinationTableName = "Products_Latest";
                bulkCopy.WriteToServer(reader);
            }
        }
        reader.Close();
    }
}

Tuesday, December 20, 2011

Getting Last Modified/Created Table, Stored Procedure, Function.. in Sql Server

Sometimes, we want to know how many tables, stored procedures or functions we have created on a particular day, or on which tables, sps or functions, modifications have occured. All this can be known through a very simple sql query, which is as below:

select name,type,type_desc, create_date, modify_date from sys.objects

where convert(varchar,modify_date,101) = convert(varchar,getdate(),101)

order by modify_date desc

Here

name : – “Its the table or sp or function name”

type: – “To identify a table or sp or function etc.. P, S, U”

type_desc: -”Description whether it is table, sp etc..”

- S = System Table, PK = Primary Key,U = User Table,

- P= Stored Procedure, TF = Table Value Function,

- FN = Scalar Function, D= Default Constraint

create_date: – “Date when it is created”

modify_date: – “Last modified datetime of table, sp,.. etc”

In above query, in place of getdate(), you can pass the date you want, also you can get complete list after removing where condition, just giving order by modified date from sys objects..

Very important when we want to generate scripts for particular tables, sps or function for a particular date.