අපේ කුප්පිය

   public static class DatabaseUtility
    {
        public static SqlConnection OpenConnection()
        {
            SqlConnection _conn = new SqlConnection();
            //_conn.ConnectionString = "Data Source=IT-093;Initial Catalog=TEST;Integrated Security=True";
            //_conn.ConnectionString = @"Data Source=DASUN\DASUN_PC;Initial Catalog=DestinyCIP;Integrated Security=True";
            _conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["CIP"].ConnectionString;
            _conn.Open();

            return _conn;

        }

        public static void CloseConnection(SqlConnection _conn)
        {
            if (_conn.State == System.Data.ConnectionState.Open)
                _conn.Close();
        }

        public static DataTable SelectData(string storedProcedureName, Dictionary<string, object> inputParameter)
        {
            DataSet dataSet = new DataSet();
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand command = new SqlCommand();
            DataTable dataTable = new DataTable();

            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = storedProcedureName;
            command.Connection = DatabaseUtility.OpenConnection();
            command.CommandTimeout = 0;
            adapter.SelectCommand = command;

            foreach (KeyValuePair<string, object> items in inputParameter)
            {
                SqlParameter parameter = new SqlParameter(items.Key, items.Value == null ? DBNull.Value : items.Value);
                parameter.Direction = ParameterDirection.Input;
                command.Parameters.Add(parameter);
            }
            adapter.Fill(dataTable);

            DatabaseUtility.CloseConnection(command.Connection);

            return dataTable;
        }


        public static int PopulateData(string storedProcedureName, Dictionary<string, object> inputParameter, Dictionary<string, object> outputParameter, Dictionary<string, object> outputParameterValues)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = storedProcedureName;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = DatabaseUtility.OpenConnection();
            cmd.CommandTimeout = 0;

            foreach (KeyValuePair<string, object> items in inputParameter)
            {
                SqlParameter parameter = new SqlParameter(items.Key, items.Value == null ? DBNull.Value : items.Value);
                parameter.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(parameter);
            }

            foreach (KeyValuePair<string, object> items in outputParameter)
            {
                ArrayList parameterItem = (ArrayList)items.Value;
                SqlParameter parameter = new SqlParameter(items.Key, (SqlDbType)parameterItem[0], (int)parameterItem[1]);
                parameter.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(parameter);
            }

            int noOfRecords = cmd.ExecuteNonQuery();

            DatabaseUtility.CloseConnection(cmd.Connection);

            foreach (SqlParameter parameter in cmd.Parameters)
            {
                if (parameter.Direction == ParameterDirection.Output)
                    outputParameterValues.Add(parameter.ParameterName, parameter.Value);
            }

            return noOfRecords;
        }
    }