COPY EXCELL SHEET DATA TO MS SQL DATA BASE WITH C#.NET

using System.Data.SqlClient;
using System.Data;
using System.Data.OleDb;

public class CopyExcellToMSSql
{
string _sourceConnectionString=;
string _destinationConnectionString;

public CopyExcellToMSSql(string sourceConnectionString,
string destinationConnectionString)
{
_sourceConnectionString =
sourceConnectionString;
_destinationConnectionString =
destinationConnectionString;
}

public void CopyTable(string Ftable,string Ttable)
{
using (OleDbConnection source =
new OleDbConnection(_sourceConnectionString))
{
string sql = string.Format("SELECT * FROM [{0}]",
Ftable);

OleDbCommand command = new OleDbCommand(sql, source);

source.Open();
IDataReader dr = command.ExecuteReader();

using (SqlBulkCopy copy =
new SqlBulkCopy(_destinationConnectionString))
{
copy.DestinationTableName = Ttable;
copy.WriteToServer(dr);
}
}
}
}

Calling above method

public void CopyData{
string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\\Documents and Settings\\Dinesh\\Desktop\\D-Garment\\work detailes.xls; Extended Properties=""Excel 8.0;HDR=Yes"";";

string sqlConnectionString ="Data Source=KIT\MYSERVER; Initial Catalog=EMS;User ID=ems; Password=ems123";

CopyExcellToMSSql cpLogic = new CopyExcellToMSSql(excelConnectionString, sqlConnectionString);
cpLogic.CopyTable("Employee$", "TempEMPLOYEE");// Employee is work sheet and "TempEMPLOYEE" is table

Insert Multiple Rows to Table in SQL

INSERT INTO [tabale1]
([col1]
,[col2]
,[col3]
,[col4])

(SELECT '11','1','1','1' UNION ALL 
SELECT '22','2','2','2')

How to call Dynamic Javascript Function from Gridview - Asp.Net

We can call dynamic java script function from grid view as fallow.say we need to call following java script function dynamically from gride view then we need to create java script function in .aspx page.

<script type="text/jscript">
function funcShow(id)
{
aleart(id);
}
</script>

After that we have to add Template filed with Hyperlink filed to Grdeview as fallowing

<asp:GridView ID="dg1" runat="server"                      
<Columns>
<asp:BoundField DataField="FileName" HeaderText="Description">
</asp:BoundField>
<asp:TemplateField>
<ItemTemplate>
<asp:HyperLink ID="HyperLink3" runat="server" NavigateUrl='<%# RetrunClick(Eval("ID"))%>'>Click</asp:HyperLink>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

Then we need to add following function to the code behind page that will call the java script function .

public string RetrunClick(object obj)
{
string str = string.Empty;
if (obj != null)
{
str = "javascript:funcShow('" + obj.ToString() + "')";
}
return str;
}

Calculate Running Sum of the Texboxses in the ASP.NET with Javascript

If we need to get running total of the text boxes in asp.net page while allowing automatically calculate the sum when changed the any text box .
Ex:
we have 3 Text Boxes as "txtCallCharges","txtRentTaxies","txtTotalAmount" and we need to get sum of the entered values to the "txtTotalAmount" text box .and also It should be allowed to automatically get total when text changed in nay text box.
Solution:
Add following code to the Code behind page.

protected override void OnLoad(EventArgs e)
{
txtCallCharges.Attributes.Add("onblur", "javascript:calculateTotal();");
txtRentTaxies.Attributes.Add("onblur", "javascript:calculateTotal();");
}

Then add following javascript to .aspx page

function calculateTotal()
{
var ctrl1 = null;
var ctrl2=null;
var ctrl3=null;          
ctrl1= document.getElementById("<%=txtCallCharges.ClientID %>");
ctrl2= document.getElementById("<%=txtRentTaxies.ClientID %>");
ctrl3= document.getElementById("<%=txtTotalAmount.ClientID %>");
var total =null;
if(ctrl1.value != "")
{
total=total+parseFloat(ctrl1.value);
}
if(ctrl2.value != "")
{
total=total+parseFloat(ctrl2.value);
}
ctrl3.value= total;
}

Update multiple columns of one table from another table- SQL

UPDATE [tableA]
SET
A1 = B.B1+B.B2,
A2=B.B3
FROM tableA, tableB as B WHERE tableA.ID = B.ID

Refresh a content page without refreshing master page Vs Avoid flicker when click on the link

The master page class derives from the UserControl class. When the application is executing, the master page just like a child control. So we can say the master page is not a true page. “.And, when the page loads, we can notice the navigationURL of the Browser address bar is the content page's, but not the master page's.Because of that we cannot refresh a content page without refreshing master page .But we can avoid flickering with fallowing code by put one <HEAD> tag in the master page.

<meta http-equiv="Page-Enter" content="blendTrans(Duration=0)"/>

<meta http-equiv="Page-Exit" content="blendTrans(Duration=0)"/>

ASP.NET Master page Set Background Image to Table

Problem :When we set ASP Net - Master Page background image it only visible from pages in root directory
Solution : The images which are used in Masterpage should be set in the code behind file as fallow .
The runat='Server" attribute should be added to the controller which are going to set background image and ID should be set to that controller.
<td runat="server" id="tb1">set back ground image with code</td>

In the code behind page image should be set using ResolveClientUrl() method
protected void Page_Load(object sender, EventArgs e)
{
tb1.Style[HtmlTextWriterStyle.BackgroundImage] = ResolveClientUrl("~/App_Themes/Images/1-sri-lanka.JPG");
}