Grant Execute Permission To All Stored Procedures

The script:

USE SomeDB    

DECLARE @name varchar(128), @sql varchar(500)    

DECLARE procs CURSOR FAST_FORWARD FOR    

SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name ASC FOR READ ONLY    

OPEN procs    

FETCH next FROM procs INTO @name    

WHILE @@FETCH_STATUS = 0    

BEGIN    

SELECT @sql = 'Grant execute on ' + LTRIM(RTRIM(@name)) + ' to SomeUser'    

EXEC(@sql)    

PRINT @sqlFETCH next FROM procs INTO @name    

END    

CLOSE procs    

DEALLOCATE procs

X509 Certificate Installation

Here are the steps of installing an X509 certificate in windows:

Installation Steps for DEVAssumptions:

  • The Microsoft Windows SDK exists on the machine
  • The certificate name is SomeName

  1. Go to Start > All Programs > Microsoft Windows SDK > CMD Shell
  2. Execute the following command: makecert -r -pe -n “CN=SomeName” -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr LocalMachine -sky exchange -sp “Microsoft RSA SChannel Cryptographic Provider” -sy 12
  3. Copy the executable findprivatekey.exe to an arbitrary folder
  4. Go to Start > Run type cmd
  5. Browse to the folder where the findprivatekey.exe was copied
  6. Execute the following command: findprivatekey.exe My LocalMachine -n “CN=SomeName” -a
  7. Copy the output of that command in Notepad
  8. Remove all line breaks if you see any
  9. Replace the {output from above} by the text from step 8 and execute the following command:  cacls “{output from above}” /E /P NETWORKSERVICE:R Example: cacls “C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys\9e7f481ca4127144bc75102dabb32ad0_c18e0de9-0e80-4436-920c-4ab1cae7939a” /E /P NETWORKSERVICE:R
  10. You’re done!

 Installation Steps for QA/ProdAssumptions:

  • The Microsoft Windows SDK exists on the machine
  • The certificate name is SomeName and is present in the local machine maybe with a different file name
  1. Go to Start > Run
  2. Type mmc
  3. Click on File > Add/Remove Snap-in…
  4. Click the Add
  5. Click on Certificates
  6. Select Computer Account
  7. Click Finish
  8. Click Close to finish adding the Snap-in
  9. Click Ok
  10. Under Console Root, expand Certificates (Local Computer)
  11. Expand Personal
  12. Click Certificates
  13. Right click and click on All Tasks > Import
  14. Click Next
  15. Click Browse, locate the exported certificate (use *.*) and then double click on it
  16. Click Next
  17. Type the password and then click Next
  18. Select Place all certificates in the following store
  19. If Personal isn’t showing in the box under, then click Browse, select Personal and click OK
  20. Click Next
  21. Click Finish
  22. Click OK
  23. Copy the executable findprivatekey.exe to c:\
  24. Go to Start > Run type cmd
  25. Go to the C:\ prompt
  26. Execute the following command: findprivatekey.exe My LocalMachine -n “CN=SomeName” -a
  27. Copy the output of that command in Notepad
  28. Remove all line breaks if you see any
  29. Replace the {output from above} by the text from step 29 and execute the following command:  cacls “{output from above}” /E /P NETWORKSERVICE:R Example: cacls “C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys\9e7f481ca4127144bc75102dabb32ad0_c18e0de9-0e80-4436-920c-4ab1cae7939a” /E /P NETWORKSERVICE:R
  30. You’re done!

The findprivatekey.exe is available here (rename .gif to .exe)

Reusable Data Layer In C#

The goal here is to take advantage of C# 2.0 features as generics and create a reusable data layer capable of calling any stored procedure, with any sets of parameters and returning different sets of results.

The DataUtility class:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;

///
/// Summary description for DataUtility
///
namespace Data
{
    public class DataUtility : Common
    {
        private string conStr;

        private enum ExecuteType
        {
            NonQuery,
            Scalar,
            Reader,
            DataRow,
            DataTable,
            DataSet
        }

        public DataUtility(string conStr)
        {
            this.conStr = conStr;
        }

        public Result ExecuteNonQuery(string storedProcedureName, Hashtable parameters)
        {
            object returnValue;
            return Execute(out returnValue, storedProcedureName, parameters, ExecuteType.NonQuery);
        }

        public Result ExecuteScalar(out object scalar, string storedProcedureName, Hashtable parameters)
        {
            return Execute(out scalar, storedProcedureName, parameters, ExecuteType.Scalar);
        }

        public Result ExecuteReader(out SqlDataReader reader, string storedProcedureName, Hashtable parameters)
        {
            return Execute(out reader, storedProcedureName, parameters, ExecuteType.Reader);
        }

        public Result ExecuteDataRow(out DataRow dataRow, string storedProcedureName, Hashtable parameters)
        {
            return Execute(out dataRow, storedProcedureName, parameters, ExecuteType.DataRow);
        }

        public Result ExecuteDataTable(out DataTable dataTable, string storedProcedureName, Hashtable parameters)
        {
            return Execute(out dataTable, storedProcedureName, parameters, ExecuteType.DataTable);
        }

        public Result ExecuteDataSet(out DataSet dataSet, string storedProcedureName, Hashtable parameters)
        {
            return Execute(out dataSet, storedProcedureName, parameters, ExecuteType.DataSet);
        }

        private Result Execute(out T returnValue, string storedProcedureName, Hashtable parameters, ExecuteType executeType) where T : class
        {
            Result result = new Result();
            returnValue = null;

            try
            {
                using (SqlConnection connection = new SqlConnection(conStr))
                {
                    SqlCommand command = new SqlCommand(storedProcedureName, connection);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Connection.Open();

                    foreach (string name in parameters.Keys)
                    {
                        command.Parameters.AddWithValue(name, parameters[name]);
                    }

                    SqlDataAdapter adapter;
                    DataTable dataTable;

                    switch (executeType)
                    {
                        case ExecuteType.NonQuery:
                            command.ExecuteNonQuery();
                            break;

                        case ExecuteType.Scalar:
                            returnValue = command.ExecuteScalar() as T;
                            break;

                        case ExecuteType.Reader:
                            returnValue = command.ExecuteReader() as T;
                            break;

                        case ExecuteType.DataRow:
                            adapter = new SqlDataAdapter(command);
                            dataTable = new DataTable();
                            adapter.Fill(dataTable);

                            if (dataTable.Rows.Count > 0)
                            {
                                returnValue = dataTable.Rows[0] as T;
                            }

                            break;

                        case ExecuteType.DataTable:
                            adapter = new SqlDataAdapter(command);
                            dataTable = new DataTable();
                            adapter.Fill(dataTable);
                            returnValue = dataTable as T;
                            break;

                        case ExecuteType.DataSet:
                            adapter = new SqlDataAdapter(command);
                            DataSet dataSet = new DataSet();
                            adapter.Fill(dataSet);
                            returnValue = dataSet as T;
                            break;
                    }

                    connection.Close();
                }
            }
            catch (Exception exception)
            {
                result.ResultType = ResultType.Failure;
                result.Exception = exception;
            }

            return result;
        }
    }
}

The Result class:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

///
/// Summary description for Result
///
namespace Data
{
    public class Result : Common
    {
        private ResultType resultType = ResultType.Success;
        private Exception exception = null;

        public Result()
        {
        }

        public Result(ResultType resultType, Exception exception)
        {
            this.resultType = resultType;
            this.exception = exception;
        }

        public Result(ResultType resultType)
        {
            this.resultType = resultType;
        }

        public ResultType ResultType
        {
            get { return resultType; }
            set { resultType = value; }
        }

        public Exception Exception
        {
            get { return exception; }
            set { exception = value; }
        }
    }
}

The Common class

using System;
using System.Collections.Generic;
using System.Text;

namespace Data
{
    public class Common
    {
        public enum ResultType
        {
            Success,
            Failure
        }
    }
}

Example of use:

        public User SignIn(string username, string password)
        {
            User user = null;
            Hashtable parameters = new Hashtable();
            parameters.Add("Name", username);
            parameters.Add("Password", password);
            DataRow dr;

            if ((dataUtility.ExecuteDataRow(out dr, "UserSelect", parameters).ResultType == ResultType.Success) && (dr != null))
            {
                user = new User
                    (
                        Convert.ToInt32(dr["ID"]),
                        dr["Name"].ToString(),
                        dr["Password"].ToString(),
                        dr["Email"].ToString(),
                        dr["Question"].ToString(),
                        dr["Answer"].ToString()
                    );
            }

            return user;
        }

Programmatically Post A Form In ASP.Net

Sometimes, there is a need to post a form from one page to another without any UI element. This is how to do it programmatically:

ASCIIEncoding encoding = new ASCIIEncoding();

byte[] data = encoding.GetBytes(“FormElementName=” + formElementValue);

HttpWebRequest myRequest = (HttpWebRequest)WebRequest.Create(http://somesite/somepage);

myRequest.Method = “POST”;

myRequest.ContentType = “application/x-www-form-urlencoded”;

myRequest.ContentLength = data.Length;

Stream newStream = myRequest.GetRequestStream();

newStream.Write(data, 0, data.Length);

newStream.Close();