UPDATE A SET A.Colx = B.Coly FROM Table1 A JOIN Table2 B ON A.col1 = B.col5 WHERE A.Col3 = 'xyz'
/************************************************************************** ----------------------- Developers Note ----------------------- Object : StoredProcedure [dbo].[canon_UpdateValidUserFromXML] Script Date : 19/11/2012 Developed By : Rohit Shram Used For : Update Valid Users Implementaion : SELECT * FROM [USER] where ID in ( 80309 ,80310) GO UpdateValidUserFromXML '<UsersList> <Users> <ID>80309</ID> <UserName>KARL TYLER</UserName> <Email>CRAIG.WASHINGTON80309@testmail.com</Email> </Users> <Users> <ID>80310</ID> <UserName>CHAD MARTIN</UserName> <Email>LEWIS.CUNNINGHAM80310@testmail.com</Email> </Users> </UsersList>', 'File1234', 80311 GO SELECT * FROM [USER] where ID in ( 80309 ,80310) *************************************************************************************/ CREATE PROCEDURE [dbo].[UpdateValidUserFromXML] @UsersXml TEXT --'<UsersList><Users><ID>12</ID><UserName>KAYR</UserName><Email>CRAI@test.com</Email></Users></UsersList>' AS BEGIN SET NOCOUNT ON; BEGIN TRANSACTION Update BEGIN TRY DECLARE @idoc INT DECLARE @ID INT DECLARE @UserName NVARCHAR(500) DECLARE @Email NVARCHAR(500) -- Used to Keep ErrorList for all Users Data as Table DECLARE @UsersErrorList TABLE ( ID INT, Name NVARCHAR(500), Email NVARCHAR(500), Error NVARCHAR(MAX) ) -- Used to Keep XML Data as Table DECLARE @UsersList TABLE ( ID INT, UserName NVARCHAR(500), Email NVARCHAR(500) ) -- Prepare XML Doc EXEC sp_xml_preparedocument @idoc OUTPUT, @UsersXml -- Insert into Table Variable From XML INSERT @UsersList ( ID, UserName, Email ) SELECT ID, UserName, Email FROM OPENXML (@idoc, '/UsersList/Users') WITH ( ID INT 'ID', UserName NVARCHAR(500) 'UserName', Email NVARCHAR(500) 'Email' ) -- Remove XML Do after getting Data EXEC sp_xml_removedocument @idoc -- Declare Cursor DECLARE Cursor CURSOR FOR SELECT ID, UserName, Email FROM @UsersList -- Open Cursor OPEN Cursor -- Fetch Cursor FETCH NEXT FROM Cursor INTO @ID, @UserName, @Email -- Traverse through Data WHILE @@FETCH_STATUS = 0 BEGIN -- Update User's Name and Email UPDATE [USER] SET Email = @Email, UserName = @UserName WHERE ID = @ID -- Fetch Cursor FETCH NEXT FROM Cursor INTO @ID, @UserName, @Email END -- WHILE @@FETCH_STATUS = 0 CLOSE Cursor DEALLOCATE Cursor END TRY BEGIN CATCH -- Rollback Transaction If any Transaction Occured IF @@ROWCOUNT > 0 ROLLBACK TRANSACTION Update -- Insert Into Temp table to Keep Error Details for all Users INSERT INTO @UsersErrorList VALUES ( @ID , @UserName , @Email , ERROR_MESSAGE()) END CATCH -- Returns Error ResultSet SELECT * FROM @UsersErrorList; -- Commit Transaction If any Transaction Occured IF @@TRANCOUNT > 0 COMMIT TRANSACTION Update SET NOCOUNT OFF; END
/****************************************************************************** ----------------------- Developers Note ----------------------- Object : StoredProcedure [dbo].[sp_SetUsersDetails] Script Date : 19/11/2012 Developed By : Rohan Sharma Used For : Add/Update User Implementaion : sp_SetUsersDetails 5, 'Rohan Sharma', 'Block-B', '87867675765' *****************************************************************************/ CREATE PROCEDURE [dbo].[sp_SetUsersDetails] -- Parameters Here @ID INT, @Name NVARCHAR(500), @Address NVARCHAR(500) = NULL, @Phone NVARCHAR(500) = NULL, AS BEGIN SET NOCOUNT ON; BEGIN TRANSACTION UpdateLMS BEGIN TRY -- Put Your Tansaction DB Codes Here END TRY BEGIN CATCH -- Rollback Transaction If any Transaction Occured IF @@ROWCOUNT > 0 ROLLBACK TRANSACTION UpdateLMS -- Put Any ErrorLog Codes Here END CATCH -- Commit Transaction If any Transaction Occured IF @@TRANCOUNT > 0 COMMIT TRANSACTION UpdateLMS SET NOCOUNT OFF; END
using System.IO; using System.Reflection; using Microsoft.Office.Interop.Excel;Code :
/// <summary>
/// This function convert uploaded Ecxcel sheet into DataTable and then returns
/// </summary>
/// <param name="fileToConvert"></param>
/// <param name="fileForDs"></param>
/// <param name="uniqueFileName"></param>
/// <returns></returns>
public System.Data.DataTable ReadFromExcelFile(string excelFileName)
{
OleDbConnection connection = null;
//this datatable contains Active Sheet records
System.Data.DataTable dtImprtExcel = new System.Data.DataTable();
try
{
// Get Active Sheet Name
string activeSheetName = getActiveSheetName(excelFileName);
//If Active Sheet Name is not Null or Blank
if (!string.IsNullOrEmpty(activeSheetName))
{
// <add key="ExcelConnectionString" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Path;Extended Properties=Excel 8.0;"/>
string connectionString = ConfigurationManager.AppSettings.Get("ExcelConnectionString").Replace("Path", excelFileName);
OleDbConnection excelCon = new OleDbConnection(connectionString);
excelCon.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [" + activeSheetName + "$]", excelCon);
adapter.Fill(dtImprtExcel);
excelCon.Close();
//cleans up the temporary file that was stored
File.Delete(excelFileName);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection != null)
{
if (connection.State == ConnectionState.Open)
{
//close a open connection
connection.Close();
}
}
}
return dtImprtExcel;
}
using System.IO;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
/// <summary>
/// Get Excel Sheet Name
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
private string getActiveSheetName(string strFileName)
{
string activeSheetName = string.Empty;
try
{
Application oXL;
Workbook oWB;
Worksheet oSheet;
// Start Excel and get Application object.
oXL = new Application();
// Set some properties
oXL.Visible = false;
oXL.DisplayAlerts = false;
// Open the workbook.
oWB = oXL.Workbooks.Open(strFileName);
// Get the active sheet
oSheet = (Worksheet)oWB.ActiveSheet;
activeSheetName = oSheet.Name;
// Save the sheet and close
oSheet = null;
oWB.SaveAs(strFileName, Excel.XlFileFormat.xlWorkbookNormal,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
XlSaveAsAccessMode.xlExclusive,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
oWB.Close(Missing.Value, Missing.Value, Missing.Value);
oWB = null;
oXL.Quit();
// Clean up
// NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
catch (Exception ex)
{
throw ex;
}
return activeSheetName;
}
using System.IO; using System.Reflection; using Microsoft.Office.Interop.Excel;Code :
/// <summary>
/// Get Excel Sheet Name
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
private string getActiveSheetName(string strFileName)
{
string activeSheetName = string.Empty;
try
{
Application oXL;
Workbook oWB;
Worksheet oSheet;
// Start Excel and get Application object.
oXL = new Application();
// Set some properties
oXL.Visible = false;
oXL.DisplayAlerts = false;
// Open the workbook.
oWB = oXL.Workbooks.Open(strFileName);
// Get the active sheet
oSheet = (Worksheet)oWB.ActiveSheet;
activeSheetName = oSheet.Name;
// Save the sheet and close
oSheet = null;
oWB.SaveAs(strFileName, Excel.XlFileFormat.xlWorkbookNormal,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
XlSaveAsAccessMode.xlExclusive,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
oWB.Close(Missing.Value, Missing.Value, Missing.Value);
oWB = null;
oXL.Quit();
// Clean up
// NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
catch (Exception ex)
{
throw ex;
}
return activeSheetName;
} /// <summary>
/// Download Excel template with some existing data
/// </summary>
/// <param name="fileName"></param>
private void DownloadExcel(string fileName)
{
try
{
FileInfo file = new FileInfo(fileName);
if (file.Exists)
{
// Clear the content of the response
Response.ClearContent();
Response.Clear();
// add the header that specifies the default filename for the Download/SaveAs dialog
Response.AddHeader("Content-Disposition", "attachment; filename=LMSUsers.xls");
// add the header that specifies the file size, so that the browser can show the download progress
Response.AddHeader("Content-Length", file.Length.ToString());
// specify that the response is a stream that cannot be read by the client and must be downloaded
Response.ContentType = "application/vnd.ms-excel";
// send the file stream to the client
Response.WriteFile(file.FullName);
Response.BufferOutput = true;
Response.Flush();
Response.Close();
//Delete the file if Required
file.Delete();
}
}
catch (Exception ex)
{
throw new Exception(ex.InnerException.Message);
}
}using System.IO; using System.Reflection; using Microsoft.Office.Interop.Excel;Code :
/// <summary>
/// Creates the excel file from the provided dataset.
/// </summary>
/// <param name="dtSource">The dt source.</param>
/// <param name="strFileName">Name of the STR file.</param>
private void GenerateExcel(System.Data.DataTable dtSource, string strFileName)
{
Application oXL;
Workbook oWB;
Worksheet oSheet;
Range oRange;
// Start Excel and get Application object.
oXL = new Application();
// Set some properties
oXL.Visible = false;
oXL.DisplayAlerts = false;
// Get a new workbook.
oWB = oXL.Workbooks.Add(Missing.Value);
// Get the active sheet
oSheet = (Worksheet)oWB.ActiveSheet;
oSheet.Name = "LMS Users";
int rowCount = 1;
// Add Headrer to Excel
for (int i = 1; i < dtSource.Columns.Count + 1; i++)
{
oSheet.Cells[1, i] = dtSource.Columns[i - 1].ColumnName;
oRange = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, i];
oRange.Font.Bold = true;
}
// Add the Data Rows to the Excel
if (dtSource.Rows.Count > 0)
{
foreach (DataRow dr in dtSource.Rows)
{
rowCount += 1;
for (int i = 1; i < dtSource.Columns.Count + 1; i++)
{
if (rowCount > 1)
{
oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
}
}
}
}
// Resize the columns
oRange = oSheet.get_Range(oSheet.Cells[1, 1],
oSheet.Cells[rowCount, dtSource.Columns.Count]);
oRange.EntireColumn.AutoFit();
// Save the sheet and close
oSheet = null;
oRange = null;
oWB.SaveAs(strFileName, Excel.XlFileFormat.xlWorkbookNormal,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
XlSaveAsAccessMode.xlExclusive,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
oWB.Close(Missing.Value, Missing.Value, Missing.Value);
oWB = null;
oXL.Quit();
// Clean up
// NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}using System.Diagnostics; using System.Reflection;Function where Error Occurred :
/// <summary>
/// Set AssetName for the AssetID
/// </summary>
/// <param name="programId"></param>
/// <param name="languageId"></param>
/// <param name="assetId"></param>
/// <param name="assetName"></param>
public void SetAssetName(Int32 progId, Int32 langId, int astId, string astName)
{
AssetClient assetClient = null;
try
{
assetClient = new AssetClient(appBindingAssets);
//return assetClient.SetAssetName(progmId, langId, astId, astName);
}
catch (Exception ex)
{
//call ErrorLog using the File Details
LogError(ex);
}
finally
{
if (assetClient != null)
{
assetClient.Close();
}
}
}
ErrorLog Function accepts Exception object as Parameter : /// <summary>
/// Logs the exception.
/// </summary>
/// <param name="ex"></param>
public static void LogError(Exception ex)
{
// Get the File Details from Exception
StackTrace st = new StackTrace(ex);
// Get the object of MethodBase from StackTrace
MethodBase mb = st.GetFrame(st.FrameCount - 1).GetMethod();
// Get FileName with NameSpace
// Ex: IGroup.Modules.SalesZone.Controller.SalesZoneController"
string source = mb.DeclaringType.FullName;
// Get Function Name "GetPhraseTranslationList"
string methodName = mb.Name;
//Implement ErrorLog Logics here using the File Details
//AddLogError(source, ex, methodName);
}
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Single Event for Multiple Controls</title>
<script src="http://code.jquery.com/jquery-1.7.2.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(function() {
var btns = $('#btnA,#btnC,#btnE');
$.each(btns, function() {
$(this).click(function() {
alert(this.id);
});
});
});
</script>
</head
<body>
<input id="btnA" type="button" value="Button A" />
<input id="btnB" type="button" value="Button B" />
<input id="btnC" type="button" value="Button C" />
<input id="btnD" type="button" value="Button D" />
<input id="btnE" type="button" value="Button E" />
</body>
</html>