Stored Procedure to validate User Login and return the User Entity From SQL Server

-- ============================================= 

-- Description: Validate User Login and return the User Entity 

-- ============================================= 

CREATE PROCEDURE [dbo].[SP_User_Login]  

 @LoginID AS VARCHAR(20), 

 @Password AS VARCHAR(20) 

AS 

BEGIN 

 

 

 DECLARE @UserID AS INT 

 DECLARE @LastLogon AS DATETIME 

 

 SELECT @UserID = UserID, @LastLogon = LastLogon  

 FROM [User] 

 WHERE LOWER(LoginID) = LOWER(@LoginID) 

  AND Password = @Password AND IsActive = 1 

 

 IF @UserID IS NOT NULL 

 BEGIN 

  

  UPDATE [User] 

  SET LastLogon = GETDATE() 

  WHERE UserID = @UserID 

 END 

  

 SELECT UserID, UserType, FirstName, LastName, LoginID, Designation,  

 PhoneOffice, PhoneResidence, PhoneMobile, PhoneOther, Email,  

 Title, @LastLogon AS LastLogon, IsActive 

 FROM [User] 

 WHERE UserID = @UserID 

 

END

How to get formatting string for number

How to get formatting string for number

string.Format("TEST_{0,2:D4}_{1,2:D4}", 3, 6); //TEST_0003_0006

Retrieve Excel Workbook Sheet Names

C# - Retrieve Excel Workbook Sheet Names

Originally posted on 

http://www.codeproject.com/Articles/8096/C-Retrieve-Excel-Workbook-Sheet-Names

/// <summary>

/// This mehtod retrieves the excel sheet names from an excel workbook.

/// </summary>

/// <param name="excelFile">The excel file.</param>

/// <returns>String[]</returns>

private String[] GetExcelSheetsNames(string excelFile)

{

    OleDbConnection objConn = null;

    System.Data.DataTable dt = null;

    try

    {

        // Connection String. Change the excel file to the file you will search.

        String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +

            "Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";

        // Create connection object by using the preceding connection string.

        objConn = new OleDbConnection(connString);

        // Open connection with the database.

        objConn.Open();

        // Get the data table containg the schema guid.

        dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        if (dt == null)

        {

            return null;

        }

        String[] excelSheets = new String[dt.Rows.Count];

        int i = 0;

        // Add the sheet name to the string array.

        foreach (DataRow row in dt.Rows)

        {

            excelSheets[i] = row["TABLE_NAME"].ToString();

            i++;

        }

        // Loop through all of the sheets if you want too...

        for (int j = 0; j < excelSheets.Length; j++)

        {

            // Query each excel sheet.

        }

        return excelSheets;

    }

    catch (Exception ex)

    {

        return null;

    }

    finally

    {

        // Clean up.

        if (objConn != null)

        {

            objConn.Close();

            objConn.Dispose();

        }

        if (dt != null)

        {

            dt.Dispose();

        }

    }

}

How to deploy web application that is using SAMS certificate to authenticate site

How to deploy web application that is using SAMS certificate to authenticate site

Permissions

1.       For Windows 2008 server, grant ‘read/write’ permission to user everyone for folder ‘C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys’.

2.       For Windows 2003 server, grant ‘read/write’ permission to user everyone for folder ‘C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys’.

3.       Allow firewall to access URL ‘https: //api.sams.itm.test.semantico.net’.  

Installation of client certificate in Personal folder

1.       Open a Command Prompt window.

2.       Type mmc and press the ENTER key. Note that to view certificates in the local machine store; you must be in the Administrator role.

3.       On the File menu, click Add/Remove Snap In.

4.       Click Add.

5.       In the Add Standalone Snap-in dialog box, select Certificates.

6.       Click Add.

7.       In the Certificates snap-in dialog box, select Computer account and click Next.

8.       Now highlight personal

9.       Right click all tasks import.

10.    Select p12 certificate given by Semantico.

Installation of client certificate in Trusted Root Certification Authorities folder

1.       Open a Command Prompt window.

2.       Type mmc and press the ENTER key. Note that to view certificates in the local machine store; you must be in the Administrator role.

3.       On the File menu, click Add/Remove Snap In.

4.       Click Add.

5.       In the Add Standalone Snap-in dialog box, select Certificates.

6.       Click Add.

7.       In the Certificates snap-in dialog box, select Computer account and click Next.

8.       Now highlight Trusted Root Certification Authorities

9.       Right click Certificates, all tasks import.

10.    Select p12 certificate given by Semantico

Installation of MSXML4

Download and Install setup from http://www.microsoft.com/en-us/download/details.aspx?id=19662 .

Add or Remove items from windows Combobox

public class ComboBoxItem

{

  
    private string display;

    private object data;

   
    public ComboBoxItem(string display, object data)

    {

      this.display = display;

      this.data = data;

    }

   
    public override string ToString()

    {

      return display;

    }

}

private void Form1_Load(object sender, EventArgs e)

{

    comboBox1.Items.Add(new ComboBoxItem("a", 1));

    comboBox1.Items.Add(new ComboBoxItem("b", 2));

    comboBox1.Items.Add(new ComboBoxItem("c", 3));

    comboBox1.Items.Add(new ComboBoxItem("d", 4));

}

private void button1_Click(object sender, EventArgs e)

{

    comboBox1.Items.RemoveAt(2);

}

Exporting Grid view or data Grid to Excel ,Formatting cells with style sheet mso-number-format

When you export the gridview to Excel it looses it format. It means that maybe your gridview has string field which consisted of numbers say '002675'. But when you export the grid and see it in excel file you will find that the number changed to '2675'.

You can solve this issue using ‘mso-number-format’

e.Row.Cells[i].Attributes["style"] = "mso-number-format:\\#\\,\\#\\#0\\.00_\\)\\;\\[Black\\]\\\\(\\#\\,\\#\\#0\\.00\\\\)";
mso-number-format:"0" No Decimals
mso-number-format:"0\.00" 2 Decimals
mso-number-format:"mm\/dd\/yy" Date format
mso-number-format:"m\/d\/yy\ h\:mm\ AM\/PM" D -T AMPM
mso-number-format:"Short Date" 05/06/-2008
mso-number-format:"Medium Date" 05-jan-2008
mso-number-format:"Short Time" 8:67
mso-number-format:"Medium Time" 8:67 am
mso-number-format:"Long Time" 8:67:25:00
mso-number-format:"Percent" Percent - two decimals
mso-number-format:"0\.E+00" Scientific Notation
mso-number-format:"\@" Text
mso-number-format:"\#\ ???\/???" Fractions - up to 3 digits (312/943)
mso-number-format:"\0022£\0022\#\,\#\#0\.00" £12.76
mso-number-format:"\#\,\#\#0\.00_ \;\[Red\]\-\#\,\#\#0\.00\ " 2 decimals, negative numbers in red and signed
(1.86 -1.66)
mso-number-format:”\\#\\,\\#\\#0\\.00_\\)\\;\\[Black\\]\\\\(\\#\\,\\#\\#0\\.00\\\\)” Accounting Format –5,(5)

Master Page Fix Height

<form id="form2" runat="server">

<div>

<table border="0" width="100%">

<tr>

<td colspan="2" height="150" bgcolor="gray" valign="middle"  align="center">

HEADER

</td>

</tr>

<tr>

<td width="20%" bgcolor="silver">SIDEBAR</td>

<td width="80%" height="400" bgcolor="white">

<asp:contentplaceholder id="ContentPlaceHolder1" runat="server">

</asp:contentplaceholder>

</td>

</tr>

<tr>

<td colspan="2" align="center" height="20" bgcolor="gray">

FOOTER

</td>

</tr>

</table>

</div>

</form>